Gridview使用LINQ与ObjectDataSource实现自动分页和排序
-
在数据库中创建表如下:
CREATE TABLE [Expense](
[Id] [int] IDENTITY(1,1) NOT NULL,
[PayMode] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[EmployeeCode] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[EmployeeName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Dept] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ExpenseTotalAmount] [float] NOT NULL,
[Remark] [nvarchar](2000) COLLATE Chinese_PRC_CI_AS NULL,
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_BW_Expense_CreateDate] DEFAULT (getdate()),
CONSTRAINT [PK_Expense] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
-
创建类库工程linqCTX,选择.Net3.5如下图:
-
从microsoft网站上下载C# Samples for Visual Studio 2008 –CsharpSamples文件,从中找到Dynamic.cs文件加入到linqCTX工程中. Dynamic.cs文件作用是增加了linq的动态查询功能扩展,如Gridview的排序语法转换成linq语句.
-
删除linqCTX中的Class1.cs文件,添加新项(linq to sql classes)CTX.dbml如下图:
打开此文件在Server Explorer中建立数据库连接.把前面建立的表Expense拖拉到CTX.dbml界面上.如下:
到此linq部分结束.下面是在BLL中调用linq部分.
-
添加ExpenseBLL.cs文件.代码如下:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Text;
using System.Linq.Dynamic;
namespace Weldon.linqCTX
{
public class ExpenseBLL
{
private CTXDataContext ctx = new CTXDataContext();
public int Insert(Expense obj)
{
ctx.Expenses.InsertOnSubmit(obj);
ctx.SubmitChanges();
return obj.Id;
}
public void Delete(int id)
{
ctx.Expenses.DeleteAllOnSubmit(from expense in ctx.Expenses where expense.Id == id select expense);
ctx.SubmitChanges();
}
public void Update(Expense obj)
{
ctx.Expenses.Attach(obj);
ctx.Refresh(RefreshMode.KeepCurrentValues, obj);
ctx.SubmitChanges(ConflictMode.ContinueOnConflict);
}
public Expense GetExpenseById(int id)
{
Expense expense = null;
try
{
expense = ctx.Expenses.Single(b => b.Id == id);
}
catch (Exception e)
{
expense = null;
}
return expense;
}
//记录的总数.
public int GetAllExpenseCount()
{
return ctx.Expenses.Count();
}
//
public IList<Expense> GetAllExpense()
{
List<Expense> expenselist;
expenselist = (from list in ctx.Expenses select list).ToList();
return expenselist;
}
public IList<Expense> GetAllExpense(int skipResults, int maxResults)
{
return (from list in ctx.Expenses select list).Skip(skipResults).Take(maxResults).ToList();
}
//skipResults当前页数(page*pagesize),maxResults为页大小,sortBy排序.
public IList<Expense> GetAllExpense(int skipResults, int maxResults, string sortBy)
{
// var list = (sortBy.Trim() == string.Empty) ? ctx.Expenses.Skip(skipResults).Take(maxResults) : ctx.Expenses.Skip(skipResults).Take(maxResults).OrderBy(p=>p.Dept);
//return list.ToList();
//return ctx.Expenses.OrderBy(p => p.Dept).Skip(skipResults).Take(maxResults).ToList();
//注意此处一定要先排序在分面不然会出现分页少数据的问题.
return (sortBy.Trim() == string.Empty) ? ctx.Expenses.OrderBy(p=>p.Id).Skip(skipResults).Take(maxResults).ToList() : ctx.Expenses.OrderBy(sortBy).Skip(skipResults).Take(maxResults).ToList();
//return (from list in ctx.Expenses orderby list.Dept descending
// select list).Skip(skipResults).Take(maxResults).ToList();
//return (from list in ctx.Expenses
//select list).Skip(skipResults).Take(maxResults).OrderBy(sortBy).ToList();
}
}
}
-
-
创建网站项目WebApp.
-
在default.aspx页面加入gridview与objectdatasource代码如下
-
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AllowSorting="True" AutoGenerateColumns="False"
DataSourceID="ObjectDataSource1" PageSize="3">
<Columns>
<asp:BoundField DataField="Id" HeaderText="Id" SortExpression="Id" />
<asp:BoundField DataField="PayMode" HeaderText="PayMode"
SortExpression="PayMode" />
<asp:BoundField DataField="EmployeeCode" HeaderText="EmployeeCode"
SortExpression="EmployeeCode" />
<asp:BoundField DataField="EmployeeName" HeaderText="EmployeeName"
SortExpression="EmployeeName" />
<asp:BoundField DataField="Dept" HeaderText="Dept" SortExpression="Dept" />
<asp:BoundField DataField="ExpenseTotalAmount" HeaderText="ExpenseTotalAmount"
SortExpression="ExpenseTotalAmount" />
<asp:BoundField DataField="Remark" HeaderText="Remark"
SortExpression="Remark" />
<asp:BoundField DataField="CreateDate" HeaderText="CreateDate"
SortExpression="CreateDate" />
</Columns>
</asp:GridView>
<!—MaximumRowsParameterName值为GetAllExpense参数maxResults,另外二个参数名称也要和GetallExpense的参数名称一样. -->
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
DataObjectTypeName="Weldon.linqCTX.Expense" EnablePaging="True"
InsertMethod="Insert" MaximumRowsParameterName="maxResults"
SelectCountMethod="GetAllExpenseCount" SelectMethod="GetAllExpense"
SortParameterName="sortBy" StartRowIndexParameterName="skipResults"
TypeName="Weldon.linqCTX.ExpenseBLL"></asp:ObjectDataSource>
点F5运行页面结果如下,本文到此介绍结束.