FineUI LINQ TO SQL 数据库分页查询

环境：

VS2010 , ASP web Forms , FineUI 4.2.3, LINQ TO SQL

要实现如下的查询，使用数据库分页，一次只查询界面需要的数据。

FineUI的定义：

<f:Grid ID="Grid1" ShowBorder="false" PageSize="15" AllowPaging="true" IsDatabasePaging="true"
                OnPageIndexChange="Grid1_PageIndexChange" ForceFit="true" ShowHeader="false"
                runat="server" EnableCollapse="true" DataKeyNames="Guid">
                <Columns>
                    <f:BoundField DataField="BOXBARCODE" HeaderText="包装箱条码" MinWidth="100px" />
                    <f:BoundField DataField="SCANDATE" HeaderText="日期" DataFormatString="{0:yyyy.MM.dd HH:mm:ss}" />
                    <f:BoundField DataField="LGORT" HeaderText="地点" />
                    <f:BoundField DataField="MATNR" HeaderText="物料" />
                    <f:BoundField DataField="LIFNR" HeaderText="供应商" />
                    <f:BoundField DataField="MENGE" HeaderText="数量" />
                    <f:BoundField DataField="WERKS" HeaderText="工厂" />
                    <f:BoundField DataField="ISGW" HeaderText="归位" />
                </Columns>
            </f:Grid>

ASP页面关键代码：

        DataTable dt = new DataTable();
        linq.DataClasses_GRDataContext db = new linq.DataClasses_GRDataContext();// 需要导出XLS，所以定义为全局变量

  var query = from c in db.GR
                                     where
                                         (
                                         (c.ASN.Contains(TextBox1.Text) || string.IsNullOrEmpty(TextBox1.Text.Trim())) &&
                                         (c.MATNR.Contains(TextBox2.Text) || string.IsNullOrEmpty(TextBox2.Text.Trim())) &&
                                         (c.LIFNR.Contains(TextBox3.Text) || string.IsNullOrEmpty(TextBox3.Text.Trim())) &&
                                         (c.SCANDATE.Value.Date.Equals(DatePicker1.SelectedDate) || string.IsNullOrEmpty(DatePicker1.Text.Trim()))
                                         )
                                     select c;
            

            // 1.控件设置总项数（特别注意：数据库分页一定要设置满足条件的总记录数RecordCount）
            Grid1.RecordCount = query.Count();

            // 2.获取当前分页数据
            var q = query.Skip(Grid1.PageIndex * Grid1.PageSize).Take(Grid1.PageSize);

            dt = mytools.ListToDataTable(q.ToList());

            // 3.绑定到Grid
            Grid1.DataSource = dt;
            Grid1.DataBind();       

看一下LINQ产生的SQL，第一个是count，第二个是15条数据

SELECT COUNT(*) AS [value]
FROM [dbo].[GR] AS [t0]
WHERE ([t0].[ASN] LIKE @p0) AND ([t0].[MATNR] LIKE @p1)
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [%5500005836%]
-- @p1: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [%10005731%]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.18408

SELECT [t1].[ID], [t1].[ASN], [t1].[LGORT], [t1].[TXTID], [t1].[MATNR], [t1].[LIFNR], [t1].[MENGE], [t1].[WERKS], [t1].[BOXBARCODE], [t1].[SCANDATE], [t1].[ISGW]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ID], [t0].[ASN], [t0].[LGORT], [t0].[TXTID], [t0].[MATNR], [t0].[LIFNR], [t0].[MENGE], [t0].[WERKS], [t0].[BOXBARCODE], [t0].[SCANDATE], [t0].[ISGW]) AS [ROW_NUMBER], [t0].[ID], [t0].[ASN], [t0].[LGORT], [t0].[TXTID], [t0].[MATNR], [t0].[LIFNR], [t0].[MENGE], [t0].[WERKS], [t0].[BOXBARCODE], [t0].[SCANDATE], [t0].[ISGW]
    FROM [dbo].[GR] AS [t0]
    WHERE ([t0].[ASN] LIKE @p0) AND ([t0].[MATNR] LIKE @p1)
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p2 + 1 AND @p2 + @p3
ORDER BY [t1].[ROW_NUMBER]
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [%5500005836%]
-- @p1: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [%10005731%]
-- @p2: Input Int (Size = -1; Prec = 0; Scale = 0) [0]
-- @p3: Input Int (Size = -1; Prec = 0; Scale = 0) [15]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.18408

这三行代码，可以查看LINQ的SQL语句。

 #if DEBUG
                db.Log = new  DebugTextWriter(); //该类在mytools文件中
            #endif

注意SCANDATE字段，在数据库中是Datetime类型，FineUI中DatePicker日期控件用SelectedDate属性取到Datetime类型的变量。