Using Linq to paginate your ObjectDataSource.

Introduction

• View Linq Pagination Demo
• Download Source in Vb.net (728kb)
• Download Source in c# (691kb)

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#

        /// <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

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
{
    /// <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

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.