Introduction
When SQL server 2005 first launched I wrote about using ROW_NUMBER() to paginate your data with SQL Server 2005. Today I am going to look at an alternate approach on the same subject. In this article I will review data pagination using a grid view bound to an ObjectDataSource that is persistent in memory.
Topics Covered in this project:
- Binding a GridView control to an ObjectDataSource.
- Singleton Collections.
- Using a GridView inside an Asp.net AJAX UpdatePanel.
- Querying objects using Linq.
- Using Skip() and Take() with Linq.
- Custom Data Pagination.
Data Pagination
If you are unfamiliar with data pagination what you need to first need to understand is that out of the box you asp.net controls will paginate using UI pagination. Simply stated this means that all of the data is returned to your control and then filtered before the aspx renders the html to the client browser. This may be fine in some instances but generally this is bad for performance. Using a data pagination technique you will only return the data which will be displayed by your control. If you have already taken a look at the online demo for this project you already have seen how much better the performance of a data pagination technique is in comparison.
Background
This project is an example of using Linq to query a Singleton collection. This basically states that the collection persists in memory at the application level of your IIS process. This is achieved by using static or shared instances, or by using .net web.caching, or other third party cache mechanisms such as zcache or ncache. If you do not want to persist your data in memory then rownumber is the best way for you to achieve data pagination.
Using the code
The download zip file contains the Visual Studio 2008 project and source files necessary to run the online demo as well as a csv containing a zip code database and a stored procedure used to load the data and referenced inside the code. You will need to update the connection string located in the web.config file to point to your data source after you have imported the zip code data and ran the stored procedure script.
Points of Interest
Using Linq syntax to retrieve "sub-collections" from your collections is a great alternative to a classic approach. Using a classic approach you would most times need to create a second instance of an object, loop through the first object and copy each of its members into the new new object. In this project I simply use the methods below to return a sub collection:
c#
public static IEnumerable<ZipCodeObjects.ZipCode> GetZipCodes(int ResultsPerPage, int PageNumber)
{
int skip = (PageNumber - 1) * ResultsPerPage;
int take = ResultsPerPage;
IEnumerable result = (from zipcodes in ZipCodeInstance select zipcodes).Skip(skip).Take(ResultsPerPage);
return result;
}
Vb.net
Public Shared Function GetZipCodes(ByVal ResultsPerPage As Integer, _
ByVal PageNumber As Integer) As IEnumerable(Of ZipCode)
Dim skip As Integer = (PageNumber - 1) * ResultsPerPage
Dim take As Integer = ResultsPerPage
Dim result As IEnumerable(Of ZipCode) = _
(From zipcodes In ZipCodeInstance).Skip(skip).Take(take)
Return result
End Function
Here is the complete class, the constructor is set to private in the collection to ensure only one single instance of the collection. Additionally the lock or sync lock is used to ensure thread safety.
The in memory instance of your collection is not paged or altered, altering this instance would alter the instance for all users. For this reason we return smaller copies of the object when requested by the user.
c#
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Linq;
using System.Collections.Generic;
namespace ZipCodeObjects
{
public class ZipCodeCollection
{
private static readonly object ReadLock = new object();
private static List<ZipCode> m_ZipCodeInstance;
public static List<ZipCode> ZipCodeInstance
{
get
{
if (m_ZipCodeInstance == null)
{
lock (ReadLock)
{
if (m_ZipCodeInstance == null)
{
m_ZipCodeInstance = LoadData();
}
}
}
return m_ZipCodeInstance;
}
}
public static IEnumerable<ZipCodeObjects.ZipCode> GetZipCodes(int ResultsPerPage, int PageNumber)
{
int skip = (PageNumber - 1) * ResultsPerPage;
int take = ResultsPerPage;
IEnumerable<ZipCodeObjects.ZipCode> result = (from zipcodes in ZipCodeInstance select zipcodes).Skip(skip).Take(ResultsPerPage);
return result;
}
public static int SelectCount()
{
return (from zipcodes in ZipCodeInstance select zipcodes).Count();
}
private static List<ZipCode> LoadData()
{
List<ZipCode> ziplist = new List<ZipCode>();
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["ProgrammersJournal"].ConnectionString;
conn.Open();
try
{
SqlCommand cmd = new SqlCommand("pj_getallzipcodes", conn);
using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
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)
{
throw new Exception(ex.Message);
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
return ziplist;
}
private ZipCodeCollection()
{
}
}
public class ZipCode
{
private string m_Zip;
public string Zip
{
get { return m_Zip; }
}
private string m_City;
public string City
{
get { return m_City; }
}
private string m_State;
public string State
{
get { return m_State; }
}
private decimal m_Latitude;
public decimal Latitude
{
get { return m_Latitude; }
}
private decimal m_Longitude;
public decimal Longitude
{
get { return m_Longitude; }
}
private int m_TimeZone;
public int TimeZone
{
get { return m_TimeZone; }
}
private short m_Dst;
public short Dst
{
get { return m_Dst; }
}
public ZipCode()
{
}
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
Public Class ZipCodeCollection
Private Shared ReadOnly ReadLock As New Object()
Private Shared m_ZipCodeInstance As List(Of ZipCode)
Public Shared ReadOnly Property ZipCodeInstance() As List(Of ZipCode)
Get
If m_ZipCodeInstance Is Nothing Then
SyncLock ReadLock
If m_ZipCodeInstance Is Nothing Then
m_ZipCodeInstance = LoadData()
End If
End SyncLock
End If
Return m_ZipCodeInstance
End Get
End Property
Public Shared Function GetZipCodes(ByVal ResultsPerPage As Integer, _
ByVal PageNumber As Integer) As IEnumerable(Of ZipCode)
Dim skip As Integer = (PageNumber - 1) * ResultsPerPage
Dim take As Integer = ResultsPerPage
Dim result As IEnumerable(Of ZipCode) = _
(From zipcodes In ZipCodeInstance).Skip(skip).Take(take)
Return result
End Function
Public Shared Function SelectCount() As Integer
Return (From zipcodes In ZipCodeInstance).Count()
End Function
Private Shared Function LoadData() As List(Of ZipCode)
Dim ziplist As New List(Of ZipCode)
Dim conn As New System.Data.SqlClient.SqlConnection()
conn.ConnectionString = ConfigurationManager.ConnectionStrings("ProgrammersJournal").ConnectionString
conn.Open()
Try
Dim cmd As New SqlCommand("pj_getallzipcodes", conn)
Using reader As SqlClient.SqlDataReader = cmd.ExecuteReader()
While reader.Read()
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
Throw New Exception(ex.Message)
Finally
If conn.State <> ConnectionState.Closed Then
conn.Close()
End If
GC.Collect()
GC.WaitForPendingFinalizers()
End Try
Return ziplist
End Function
Private Sub New()
End Sub
End Class
Public Class ZipCode
Private m_Zip As String
Public ReadOnly Property Zip() As String
Get
Return m_Zip
End Get
End Property
Private m_City As String
Public ReadOnly Property City() As String
Get
Return m_City
End Get
End Property
Private m_State As String
Public ReadOnly Property State() As String
Get
Return m_State
End Get
End Property
Private m_Latitude As Decimal
Public ReadOnly Property Latitude() As Decimal
Get
Return m_Latitude
End Get
End Property
Private m_Longitude As Decimal
Public ReadOnly Property Longitude() As Decimal
Get
Return m_Longitude
End Get
End Property
Private m_TimeZone As Integer
Public ReadOnly Property TimeZone() As Integer
Get
Return m_TimeZone
End Get
End Property
Private m_Dst As Short
Public ReadOnly Property Dst() As Short
Get
Return m_Dst
End Get
End Property
Public Sub New()
End Sub
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
Binding your GridView to your collection is very easily done using an ObjectDataSource.
<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>
From here all that is left is a simple paging mechanism and UI. Download the demo project for the complete source code and database.
History
- 3/19/2008: Posted Code Samples And Article
- 3/19/2008: Uploaded Sample Projects
- 3/20/2008: Copy Only Edit.
