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

<%@ 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>

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; 
        } 
}

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

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