Visual Basic 9 (2008)DBAVisual Studio 2008.NET 3.0设计/图形LINQ架构师高级CSS.NET 3.5SQL Server 2005AjaxC# 3.0HTML中级开发Visual StudioSQL Server.NETVisual BasicASP.NETC#
使用 Linq 为 ObjectDataSource 实现分页。
4.68/5 (11投票s)
在这篇文章中，我将通过一个示例，展示如何将 GridView 控件绑定到 ObjectDataSource，并使用 Linq 查询来实现真正的分页。
引言
SQL Server 2005 发布时，我曾写过一篇关于 使用 ROW_NUMBER() 在 SQL Server 2005 中分页数据的文章。今天，我将探讨一种在同一主题上的替代方法。在这篇文章中，我将回顾使用绑定到内存中持久化 ObjectDataSource 的 GridView 来实现数据分页。
本文涵盖的主题
- 将 GridView 控件绑定到 ObjectDataSource。
- 单例集合。
- 在 Asp.net AJAX UpdatePanel 中使用 GridView。
- 使用 Linq 查询对象。
- 在 Linq 中使用 Skip() 和 Take()。
- 自定义数据分页。
数据分页
如果您不熟悉数据分页，首先需要了解的是，开箱即用的 Asp.net 控件会使用 UI 分页。简单来说，这意味着所有数据都会返回到您的控件，然后在 aspx 将 HTML 渲染到客户端浏览器之前进行过滤。在某些情况下这可能没问题，但通常对性能不利。使用数据分页技术，您只会返回控件将显示的数据。如果您已经查看过此项目的在线演示，您已经看到了与此相比，数据分页技术的性能要好得多。
背景
此项目是使用 Linq 查询 单例集合的一个示例。这基本上意味着该集合在 IIS 进程的应用程序级别持久化在内存中。这是通过使用静态或共享实例，或通过使用 .net web.caching，或通过其他第三方缓存机制（如 zcache 或 ncache）来实现的。如果您不想将数据持久化在内存中，那么 rownumber 是实现数据分页的最佳方式。
使用代码
下载的 zip 文件包含 Visual Studio 2008 项目和运行在线演示所需的源代码文件，以及一个包含邮政编码数据库的 csv 文件和一个用于加载数据并引用的存储过程。在导入邮政编码数据并运行存储过程脚本后，您需要更新 web.config 文件中的连接字符串以指向您的数据源。
关注点
使用 Linq 语法从您的集合中检索“子集合”是经典方法的绝佳替代方案。使用经典方法，您通常需要创建对象的第二个实例，循环遍历第一个对象，并将每个成员复制到新的新对象中。在此项目中，我仅使用以下方法返回一个子集合。
C# /// <summary />
/// GetZipCodes
/// </summary />
/// <param name="ResultsPerPage" /></param />
/// <param name="PageNumber" /></param />
/// <returns />
/// IEnumerable(Of ZipCode)
/// </returns />
/// <remarks />
/// Page Clone of Instance Data Using Linq
/// </remarks />
public static IEnumerable<ZipCodeObjects.ZipCode> GetZipCodes(int ResultsPerPage, int PageNumber)
{
//number of records to skip
int skip = (PageNumber - 1) * ResultsPerPage;
//number of results per page.
int take = ResultsPerPage;
//execute Linq query for result set
IEnumerable result = (from zipcodes in ZipCodeInstance select zipcodes).Skip(skip).Take(ResultsPerPage);
//return result
return result;
}
Vb.net
''' <summary>
''' GetZipCodes
''' </summary>
''' <param name="ResultsPerPage"></param>
''' <param name="PageNumber"></param>
''' <returns>
''' IEnumerable(Of ZipCode)
''' </returns>
''' <remarks>
''' Page Clone of Instance Data Using Linq
''' </remarks>
Public Shared Function GetZipCodes(ByVal ResultsPerPage As Integer, _
ByVal PageNumber As Integer) As IEnumerable(Of ZipCode)
'number of records to skip
Dim skip As Integer = (PageNumber - 1) * ResultsPerPage
'number of results per page.
Dim take As Integer = ResultsPerPage
'execute query for result set
Dim result As IEnumerable(Of ZipCode) = _
(From zipcodes In ZipCodeInstance).Skip(skip).Take(take)
'return result
Return result
End Function
这是完整的类，为了确保集合只有一个实例，构造函数被设置为私有。此外，还使用了锁或同步锁来确保线程安全。
您集合的内存实例不会被分页或修改，修改此实例会影响所有用户。因此，我们在用户请求时返回对象的较小副本。
C#using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Linq;
using System.Collections.Generic;
namespace ZipCodeObjects
{
/// <summary>
/// ZipCodeCollection
/// </summary>
/// <remarks>
/// Contains Collection of zip codes and meta.
/// </remarks>
public class ZipCodeCollection
{
/// <summary>
/// ReadLock
/// </summary>
/// <remarks>
/// Used By SyncLock to ensure Thread Safety
/// </remarks>
private static readonly object ReadLock = new object();
/// <summary>
/// ZipCodeInstance
/// </summary>
/// <remarks>
/// ZipCodeInstance
/// Singleton Collection of Zip Codes and meta.
/// </remarks>
private static List<ZipCode> m_ZipCodeInstance;
public static List<ZipCode> ZipCodeInstance
{
get
{
// initialize if not already done
if (m_ZipCodeInstance == null)
{
//only allow 1 person to load data at once.
lock (ReadLock)
{
if (m_ZipCodeInstance == null)
{
m_ZipCodeInstance = LoadData();
}
}
}
return m_ZipCodeInstance;
}
}
/// <summary>
/// GetZipCodes
/// </summary>
/// <param name="ResultsPerPage"></param>
/// <param name="PageNumber"></param>
/// <returns>
/// IEnumerable(Of ZipCode)
/// </returns>
/// <remarks>
/// Page Clone of Instance Data Using Linq
/// </remarks>
public static IEnumerable<ZipCodeObjects.ZipCode> GetZipCodes(int ResultsPerPage, int PageNumber)
{
//number of records to skip
int skip = (PageNumber - 1) * ResultsPerPage;
//number of results per page.
int take = ResultsPerPage;
//execute Linq query for result set
IEnumerable<ZipCodeObjects.ZipCode> result = (from zipcodes in ZipCodeInstance select zipcodes).Skip(skip).Take(ResultsPerPage);
//return result
return result;
}
/// <summary>
/// SelectCount
/// </summary>
/// <returns>
/// Integer
/// </returns>
/// <remarks>
/// Returns total number of records in instance.
/// Uses Linq
/// </remarks>
public static int SelectCount()
{
return (from zipcodes in ZipCodeInstance select zipcodes).Count();
}
/// <summary>
/// LoadData
/// </summary>
/// <returns>
/// List(Of ZipCode)
/// </returns>
/// <remarks>
/// Load collection of zip codes from database.
/// </remarks>
private static List<ZipCode> LoadData()
{
//create new instance of zip code collection
List<ZipCode> ziplist = new List<ZipCode>();
//setup database connection
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["ProgrammersJournal"].ConnectionString;
//open connection
conn.Open();
try
{
//define sql command
SqlCommand cmd = new SqlCommand("pj_getallzipcodes", conn);
//execute and loop through reader.
using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
//add zip object to list.
ziplist.Add(new ZipCode(reader.GetSqlString(0).ToString(), reader.GetSqlString(1).ToString(), reader.GetSqlString(2).ToString(), Convert.ToDecimal(reader.GetSqlString(3).ToString()), Convert.ToDecimal(reader.GetSqlString(4).ToString()), Convert.ToInt32(reader.GetSqlString(5).ToString()), Convert.ToInt16(reader.GetSqlString(6).ToString())));
}
}
}
catch (Exception ex)
{
//bubble exception
throw new Exception(ex.Message);
}
finally
{
//close connection
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
//instance is populated
//force garbage collection
GC.Collect();
GC.WaitForPendingFinalizers();
}
//return new instance of zip code collection
return ziplist;
}
/// <summary>
/// New
/// </summary>
/// <remarks>
/// Conrtructor set to private to ensure
/// instances of the obect cannot be created
/// outside of this class.
/// </remarks>
private ZipCodeCollection()
{
}
}
/// <summary>
/// ZipCode
/// </summary>
/// <remarks>
/// Single Zip code record and associated meta.
/// </remarks>
public class ZipCode
{
/// <summary>
/// Zip
/// </summary>
/// <remarks>
/// Zip Code
/// </remarks>
private string m_Zip;
public string Zip
{
get { return m_Zip; }
}
/// <summary>
/// City
/// </summary>
/// <remarks>
/// City where zip code is located.
/// </remarks>
private string m_City;
public string City
{
get { return m_City; }
}
/// <summary>
/// State
/// </summary>
/// <remarks>
/// State where zip code is located.
/// </remarks>
private string m_State;
public string State
{
get { return m_State; }
}
/// <summary>
/// Latitude
/// </summary>
/// <remarks>
/// Latitude reference for this zip code.
/// </remarks>
private decimal m_Latitude;
public decimal Latitude
{
get { return m_Latitude; }
}
/// <summary>
/// Longitude
/// </summary>
/// <remarks>
/// Longitude reference for this zip code.
/// </remarks>
private decimal m_Longitude;
public decimal Longitude
{
get { return m_Longitude; }
}
/// <summary>
/// TimeZone
/// </summary>
/// <remarks>
/// TimeZone reference for this zip code.
/// </remarks>
private int m_TimeZone;
public int TimeZone
{
get { return m_TimeZone; }
}
/// <summary>
/// Dst
/// </summary>
/// <remarks>
/// Dst reference for this zip code.
/// </remarks>
private short m_Dst;
public short Dst
{
get { return m_Dst; }
}
/// <summary>
/// New
/// </summary>
/// <remarks>
/// parameterless constructor
/// </remarks>
public ZipCode()
{
}
/// <summary>
/// New
/// </summary>
/// <param name="zip"></param>
/// <param name="city"></param>
/// <param name="state"></param>
/// <param name="latitude"></param>
/// <param name="longitude"></param>
/// <param name="timeZone"></param>
/// <param name="dst"></param>
/// <remarks>
/// Custom Contructor
/// </remarks>
public ZipCode(string zip, string city, string state, decimal latitude, decimal longitude, int timeZone, short dst)
{
this.m_Zip = zip;
this.m_City = city;
this.m_State = state;
this.m_Latitude = latitude;
this.m_Longitude = longitude;
this.m_TimeZone = timeZone;
this.m_Dst = dst;
}
}
}
Vb.net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Linq
Namespace ZipCodeObjects
''' <summary>
''' ZipCodeCollection
''' </summary>
''' <remarks>
''' Contains Collection of zip codes and meta.
''' </remarks>
Public Class ZipCodeCollection
''' <summary>
''' ReadLock
''' </summary>
''' <remarks>
''' Used By SyncLock to ensure Thread Safety
''' </remarks>
Private Shared ReadOnly ReadLock As New Object()
''' <summary>
''' ZipCodeInstance
''' </summary>
''' <remarks>
''' ZipCodeInstance
''' Singleton Collection of Zip Codes and meta.
''' </remarks>
Private Shared m_ZipCodeInstance As List(Of ZipCode)
Public Shared ReadOnly Property ZipCodeInstance() As List(Of ZipCode)
Get
' initialize if not already done
If m_ZipCodeInstance Is Nothing Then
'only allow 1 person to load data at once.
SyncLock ReadLock
If m_ZipCodeInstance Is Nothing Then
m_ZipCodeInstance = LoadData()
End If
End SyncLock
End If
Return m_ZipCodeInstance
End Get
End Property
''' <summary>
''' GetZipCodes
''' </summary>
''' <param name="ResultsPerPage"></param>
''' <param name="PageNumber"></param>
''' <returns>
''' IEnumerable(Of ZipCode)
''' </returns>
''' <remarks>
''' Page Clone of Instance Data Using Linq
''' </remarks>
Public Shared Function GetZipCodes(ByVal ResultsPerPage As Integer, _
ByVal PageNumber As Integer) As IEnumerable(Of ZipCode)
'number of records to skip
Dim skip As Integer = (PageNumber - 1) * ResultsPerPage
'number of results per page.
Dim take As Integer = ResultsPerPage
'execute query for result set
Dim result As IEnumerable(Of ZipCode) = _
(From zipcodes In ZipCodeInstance).Skip(skip).Take(take)
'return result
Return result
End Function
''' <summary>
''' SelectCount
''' </summary>
''' <returns>
''' Integer
''' </returns>
''' <remarks>
''' Returns total number of records in instance.
''' Uses Linq
''' </remarks>
Public Shared Function SelectCount() As Integer
Return (From zipcodes In ZipCodeInstance).Count()
End Function
''' <summary>
''' LoadData
''' </summary>
''' <returns>
''' List(Of ZipCode)
''' </returns>
''' <remarks>
''' Load collection of zip codes from database.
''' </remarks>
Private Shared Function LoadData() As List(Of ZipCode)
'create new instance of zip code collection
Dim ziplist As New List(Of ZipCode)
'setup database connection
Dim conn As New System.Data.SqlClient.SqlConnection()
conn.ConnectionString = ConfigurationManager.ConnectionStrings("ProgrammersJournal").ConnectionString
'open connection
conn.Open()
Try
'define sql command
Dim cmd As New SqlCommand("pj_getallzipcodes", conn)
'execute and loop through reader.
Using reader As SqlClient.SqlDataReader = cmd.ExecuteReader()
While reader.Read()
'add zip object to list.
ziplist.Add(New ZipCode(reader.GetSqlString(0).ToString(), _
reader.GetSqlString(1).ToString(), _
reader.GetSqlString(2).ToString(), _
Convert.ToDecimal(reader.GetSqlString(3).ToString()), _
Convert.ToDecimal(reader.GetSqlString(4).ToString()), _
Convert.ToInt32(reader.GetSqlString(5).ToString()), _
Convert.ToInt16(reader.GetSqlString(6).ToString())))
End While
End Using
Catch ex As Exception
'bubble exception
Throw New Exception(ex.Message)
Finally
'close connection
If conn.State <> ConnectionState.Closed Then
conn.Close()
End If
'instance is populated
'force garbage collection
GC.Collect()
GC.WaitForPendingFinalizers()
End Try
'return new instance of zip code collection
Return ziplist
End Function
''' <summary>
''' New
''' </summary>
''' <remarks>
''' Conrtructor set to private to ensure
''' instances of the obect cannot be created
''' outside of this class.
''' </remarks>
Private Sub New()
End Sub
End Class
''' <summary>
''' ZipCode
''' </summary>
''' <remarks>
''' Single Zip code record and associated meta.
''' </remarks>
Public Class ZipCode
''' <summary>
''' Zip
''' </summary>
''' <remarks>
''' Zip Code
''' </remarks>
Private m_Zip As String
Public ReadOnly Property Zip() As String
Get
Return m_Zip
End Get
End Property
''' <summary>
''' City
''' </summary>
''' <remarks>
''' City where zip code is located.
''' </remarks>
Private m_City As String
Public ReadOnly Property City() As String
Get
Return m_City
End Get
End Property
''' <summary>
''' State
''' </summary>
''' <remarks>
''' State where zip code is located.
''' </remarks>
Private m_State As String
Public ReadOnly Property State() As String
Get
Return m_State
End Get
End Property
''' <summary>
''' Latitude
''' </summary>
''' <remarks>
''' Latitude reference for this zip code.
''' </remarks>
Private m_Latitude As Decimal
Public ReadOnly Property Latitude() As Decimal
Get
Return m_Latitude
End Get
End Property
''' <summary>
''' Longitude
''' </summary>
''' <remarks>
''' Longitude reference for this zip code.
''' </remarks>
Private m_Longitude As Decimal
Public ReadOnly Property Longitude() As Decimal
Get
Return m_Longitude
End Get
End Property
''' <summary>
''' TimeZone
''' </summary>
''' <remarks>
''' TimeZone reference for this zip code.
''' </remarks>
Private m_TimeZone As Integer
Public ReadOnly Property TimeZone() As Integer
Get
Return m_TimeZone
End Get
End Property
''' <summary>
''' Dst
''' </summary>
''' <remarks>
''' Dst reference for this zip code.
''' </remarks>
Private m_Dst As Short
Public ReadOnly Property Dst() As Short
Get
Return m_Dst
End Get
End Property
''' <summary>
''' New
''' </summary>
''' <remarks>
''' parameterless constructor
''' </remarks>
Public Sub New()
End Sub
''' <summary>
''' New
''' </summary>
''' <param name="zip"></param>
''' <param name="city"></param>
''' <param name="state"></param>
''' <param name="latitude"></param>
''' <param name="longitude"></param>
''' <param name="timeZone"></param>
''' <param name="dst"></param>
''' <remarks>
''' Custom Contructor
''' </remarks>
Public Sub New(ByVal zip As String, _
ByVal city As String, _
ByVal state As String, _
ByVal latitude As Decimal, _
ByVal longitude As Decimal, _
ByVal timeZone As Integer, _
ByVal dst As Short)
Me.m_Zip = zip
Me.m_City = city
Me.m_State = state
Me.m_Latitude = latitude
Me.m_Longitude = longitude
Me.m_TimeZone = timeZone
Me.m_Dst = dst
End Sub
End Class
End Namespace
使用 ObjectDataSource 可以非常轻松地将 GridView 绑定到您的集合。
<asp:ObjectDataSource ID="ZipCodeObjectDataSource" runat="server" SelectMethod="GetZipCodes"
TypeName="ZipCodeObjects.ZipCodeCollection">
<SelectParameters>
<asp:SessionParameter DefaultValue="20" Name="ResultsPerPage" SessionField="ResultsPerPage"
Type="Int32" />
<asp:SessionParameter DefaultValue="1" Name="PageNumber" SessionField="PageNumber"
Type="Int32" />
</SelectParameters>
</asp:ObjectDataSource>
从这里开始，剩下的就是简单的分页机制和 UI。下载演示项目以获取完整的源代码和数据库。
历史
- 2008/3/19：发布了代码样本和文章
- 2008/3/19：上传了示例项目
- 2008/3/20：仅复制编辑。