Gridview使用LINQ与ObjectDataSource实现自动分页和排序

Gridview使用LINQ与ObjectDataSource实现自动分页和排序

 

1. 在数据库中创建表如下:

   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]

2. 创建类库工程linqCTX,选择.Net3.5如下图:

   

   1. 从microsoft网站上下载C# Samples for Visual Studio 2008 –CsharpSamples文件,从中找到Dynamic.cs文件加入到linqCTX工程中. Dynamic.cs文件作用是增加了linq的动态查询功能扩展,如Gridview的排序语法转换成linq语句.
   2. 删除linqCTX中的Class1.cs文件,添加新项(linq to sql classes)CTX.dbml如下图:

      

      打开此文件在Server Explorer中建立数据库连接.把前面建立的表Expense拖拉到CTX.dbml界面上.如下:

      

      到此linq部分结束.下面是在BLL中调用linq部分.

   3. 添加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();

   }

    

   }

   }

3. 创建网站项目WebApp.
   1. 在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运行页面结果如下,本文到此介绍结束.