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

weixin_34183910

于 2019-01-08 01:34:12 发布

阅读量194

点赞数

文章标签：大数据 ui c#

本文演示了如何使用DLINQ(LINQtoSQL)结合GridView和ObjectDataSource控件实现大数据量分页，并介绍了延迟执行和日志记录的方法。通过Northwind数据库示例，展示了分页查询的T-SQL生成过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

步步为营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