步步为营VS 2008 + .NET 3.5(11) - DLINQ(LINQ to SQL)之大数据量分页、延迟执行和日志记录

[索引页]
[源码下载]

步步为营VS 2008 + .NET 3.5(11) - DLINQ(LINQ to SQL)之大数据量分页、延迟执行和日志记录

作者： webabcd


介绍
以Northwind为 示例数据库，DLINQ(LINQ to SQL)之结合 GridView控件和 ObjectDataSource控件演示大数据量分页，同时介绍 延迟执行和日志记录


示例
PagingAndLogging.aspx

<% @ Page Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="PagingAndLogging.aspx.cs"
    Inherits="LINQ_DLINQ_PagingAndLogging" Title="大数据量分页、延迟执行和日志记录"  %>

< asp:Content  ID ="Content1"  ContentPlaceHolderID ="head"  runat ="Server" >
</ asp:Content >
< asp:Content  ID ="Content2"  ContentPlaceHolderID ="ContentPlaceHolder1"  runat ="Server" >
     < asp:GridView  ID ="gvProduct"  runat ="server"  DataSourceID ="odsProduct"  AllowPaging ="True"  PageSize ="5" >
     </ asp:GridView >
     < asp:ObjectDataSource  ID ="odsProduct"  runat ="server"  EnablePaging ="True"  SelectCountMethod ="GetProductCount"
        SelectMethod ="GetProduct"  TypeName ="PagingAndLogging" >
         < SelectParameters >
             < asp:Parameter  Name ="startRowIndex"  Type ="Int32"  DefaultValue ="0"   />
             < asp:Parameter  Name ="maximumRows"  Type ="Int32"  DefaultValue ="10"   />
         </ SelectParameters >
     </ asp:ObjectDataSource >
</ asp:Content >

PagingAndLogging.cs

using  System;
using  System.Data;
using  System.Configuration;
using  System.Linq;
using  System.Web;
using  System.Web.Security;
using  System.Web.UI;
using  System.Web.UI.WebControls;
using  System.Web.UI.WebControls.WebParts;
using  System.Web.UI.HtmlControls;
using  System.Xml.Linq;

using  System.ComponentModel;
using  System.Collections.Generic;
using  System.IO;
using  DAL;

/**/ /// <summary>
/// PagingAndLogging 的摘要说明
/// </summary>
[DataObject]
public   class  PagingAndLogging
{
    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public List<Products> GetProduct(int startRowIndex, int maximumRows)
    {
        NorthwindDataContext ctx = new NorthwindDataContext();

        // System.Data.Linq.DataContext的记录日志的功能
        StreamWriter sw = new StreamWriter(HttpContext.Current.Request.PhysicalApplicationPath + "Log.txt", true);
        ctx.Log = sw;

        var products = (from p in ctx.Products
                        select p).Skip(startRowIndex).Take(maximumRows);

        // products实现了IQueryable<T>接口
        // 所以可以用如下方法从中获取DbCommand
        System.Data.Common.DbCommand cmd = ctx.GetCommand(products);
        string commandText = cmd.CommandText;
        foreach (System.Data.Common.DbParameter param in cmd.Parameters)
        {
            string parameterName = param.ParameterName;
            object value = param.Value;
        }

        // 延迟执行（Deferred Execution）
        // products实现了IEnumerable<T>接口
        // IEnumerable<T>接口的一个特性是，实现它的对象可以把实际的查询运算延迟到第一次对返回值进行迭代（yield）的时候
        // ToList()之前，如果是LINQ to SQL的话，那么就可以通过products.ToString()查看LINQ生成的T-SQL
        // ToList()后则执行运算
        var listProducts = products.ToList();

        // 执行运算后System.Data.Linq.DataContext会记录日志，所以应该在执行运算后Close掉StreamWriter
        sw.Flush();
        sw.Close();

        return listProducts;
    }

    public int GetProductCount(int startRowIndex, int maximumRows)
    {
        NorthwindDataContext ctx = new NorthwindDataContext();

        StreamWriter sw = new StreamWriter(HttpContext.Current.Request.PhysicalApplicationPath + "Log.txt", true);
        ctx.Log = sw;

        // Count查询操作符（不延迟） - 返回集合中的元素个数
        int c = (from p in ctx.Products
                 select 0).Count();

        sw.Flush();
        sw.Close();

        return c;
    }
}

通过查看日志可以发现，单击第1页时DLINQ生成的T-SQL语句如下

SELECT   TOP   5   [ t0 ] . [ ProductID ] ,  [ t0 ] . [ ProductName ] ,  [ t0 ] . [ SupplierID ] ,  [ t0 ] . [ CategoryID ] ,  [ t0 ] . [ QuantityPerUnit ] ,  [ t0 ] . [ UnitPrice ] ,  [ t0 ] . [ UnitsInStock ] ,  [ t0 ] . [ UnitsOnOrder ] ,  [ t0 ] . [ ReorderLevel ] ,  [ t0 ] . [ Discontinued ]
FROM   [ dbo ] . [ Products ]   AS   [ t0 ]
--  Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

SELECT   COUNT ( * )  AS   [ value ]
FROM   [ dbo ] . [ Products ]   AS   [ t0 ]
--  Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

通过查看日志可以发现，单击第10页时DLINQ生成的T-SQL语句如下

SELECT   TOP   5   [ t1 ] . [ ProductID ] ,  [ t1 ] . [ ProductName ] ,  [ t1 ] . [ SupplierID ] ,  [ t1 ] . [ CategoryID ] ,  [ t1 ] . [ QuantityPerUnit ] ,  [ t1 ] . [ UnitPrice ] ,  [ t1 ] . [ UnitsInStock ] ,  [ t1 ] . [ UnitsOnOrder ] ,  [ t1 ] . [ ReorderLevel ] ,  [ t1 ] . [ Discontinued ]
FROM  (
     SELECT  ROW_NUMBER()  OVER  ( ORDER   BY   [ t0 ] . [ ProductID ] ,  [ t0 ] . [ ProductName ] ,  [ t0 ] . [ SupplierID ] ,  [ t0 ] . [ CategoryID ] ,  [ t0 ] . [ QuantityPerUnit ] ,  [ t0 ] . [ UnitPrice ] ,  [ t0 ] . [ UnitsInStock ] ,  [ t0 ] . [ UnitsOnOrder ] ,  [ t0 ] . [ ReorderLevel ] ,  [ t0 ] . [ Discontinued ] )  AS   [ ROW_NUMBER ] ,  [ t0 ] . [ ProductID ] ,  [ t0 ] . [ ProductName ] ,  [ t0 ] . [ SupplierID ] ,  [ t0 ] . [ CategoryID ] ,  [ t0 ] . [ QuantityPerUnit ] ,  [ t0 ] . [ UnitPrice ] ,  [ t0 ] . [ UnitsInStock ] ,  [ t0 ] . [ UnitsOnOrder ] ,  [ t0 ] . [ ReorderLevel ] ,  [ t0 ] . [ Discontinued ]
     FROM   [ dbo ] . [ Products ]   AS   [ t0 ]
    )  AS   [ t1 ]
WHERE   [ t1 ] . [ ROW_NUMBER ]   >   @p0
--  @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [45]
--  Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

SELECT   COUNT ( * )  AS   [ value ]
FROM   [ dbo ] . [ Products ]   AS   [ t0 ]
--  Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

OK
[源码下载]