Effective Paging with GridView Control in ASP.NET

• Download source - 4.38 KB

Introduction

All of us who have experience using GridView in ASP.NET know paging is an easy job using GridView control in ASP.NET. Paging is an important thing for thousands of thousand data if we want to develop a scalable application.

Background

For this demonstration, I will develop an application in ASP.NET. Firstly, I will show paging using GridView control usually. Then I will enhance paging so that it will be effective.

Let’s Get Started

Open Microsoft SQL Server Management Studio 2005. Design a table as the following structure or you may have a different one.

Some records form Profile table.

Creating ASP.NET Website in Visual Studio 2010

1. Create a website project and name it EffectivePagingAndSorting.
2. Add a web form in this project and rename the page to NormalPaging.aspx.

Add a GridView control in NormalPaging.aspx page. Rename it to gvProfile. Add a SqlDataSource control in NormalPaging.aspx page. Rename it to profileDataSource. Configure the SqlDataSource control like below:

<asp:SqlDataSource ID="profileDataSource" runat="server" 
    ConnectionString = "<%$ ConnectionStrings:ApplicationServices %>" 
     SelectCommand="SELECT [ProfileId],[Name],[Address],[Email],[Mobile],
 Active = CASE [IsActive] WHEN 1 THEN 'Active' WHEN 0 THEN 'DeActive' 
    END FROM [dbo].[Profile]" >

You may have a different connection other than this in web.config file. In select command SQL I have used a SQL case statement to show Active instead of 1 and DeActive instead of 0 from Profile table.

Now configure GridView control to show profile data. Set DataSourceID to profileDataSource, AutoGenerateColumns to false so that column will set manually, AllowPaging to true for pagination, AllowSorting to true for allowing sorting by clicking a column head, PageSize to 5 for grid will show only 5 records at a time. And other style property like the below markup or as you want.

 <asp:GridView ID="gvProfile" DataSourceID="profileDataSource" runat="server"
 AutoGenerateColumns="false" AllowPaging="true" AllowSorting="true" 
PageSize="5" HeaderStyle-Font-Names="Verdana" Font-Size="Small" 
HeaderStyle-HorizontalAlign="Left" 
HeaderStyle-Font-Underline="false" Width="55%" 
HeaderStyle-BackColor="BurlyWood" HeaderStyle-ForeColor="Navy">
            <alternatingrowstyle backcolor="</span>Aquamarine"> />
            <columns>
   <asp:BoundField  DataField="ProfileId" HeaderText="Profile Id" 
    SortExpression="ProfileId" ItemStyle-Width="6%"/>
   <asp:BoundField  DataField="Name" HeaderText="Name" 
    SortExpression="Name" ItemStyle-Width="13%"/>
   <asp:BoundField  DataField="Address" HeaderText="Address" 
    SortExpression="Address" ItemStyle-Width="18%"/>
   <asp:BoundField  DataField="Email" HeaderText="Email" 
    SortExpression="Email" ItemStyle-Width="8%"/>
   <asp:BoundField  DataField="Mobile" HeaderText="Mobile" 
    SortExpression="Mobile" ItemStyle-Width="9%"/>
   <asp:BoundField  DataField="IsActive" HeaderText="Status" 
    SortExpression="IsActive" ItemStyle-Width="4%"/>    

In the above markup, grid column has been bound to data source manually.
Run the application to browse NormalPaging.aspx. You will find the result in web browser like below:

How It Works

TO display these records in NormalPaging.aspx page using GridView, I wrote select command for SqldataSource then assigned SqlDataSource as a data source for GridView control. Actually for every load of NormalPaging.aspx page retrieve all of the records from underling table Profile into memory. From where Gridview displays these above records page by page. Think if the underlying table exists millions of records what will happen then. You may surely agree with me that it will retrieve all the millions of records when the page will be loaded. So it will take a long time to load. And after it had loaded, it must take a huge amount of system resource. As a result by this way paging will degrade application scalability. So this type of paging is not effective for thousand of records.

Effective Paging

To make paging and sorting effective, the first step is to create stored procedures in SQL.

1. Open SQL Server Management Studio
2. Take a New Query

Create a stored procedure with some technique in your database to retrieve records form Profile table. Like the below SQL:

CREATE PROCEDURE [dbo].[Profile_GET]
    @PageSize int = null,
    @CurrentPage int = null,
    @SortExpression    nvarchar(max) = null
AS

BEGIN
    SET NOCOUNT ON

    DECLARE @SqlString nvarchar(max)
    Declare @UpperBand int
    Declare @LowerBand int        
    
    SET @LowerBand  = (@CurrentPage - 1) * @PageSize
    SET @UpperBand  = (@CurrentPage * @PageSize) + 1    

    BEGIN
        SET @SqlString='WITH tempProfile AS
        (                    
            SELECT 
                [ProfileId],
                [Name],
                [Address],
                [Email],
                [Mobile], 
                [Active] = CASE [IsActive] WHEN 1 _
                THEN ''Active'' WHEN 0 THEN ''DeActive'' END,                            
                ROW_NUMBER() OVER (ORDER BY '+ _
                @SortExpression +' ) AS RowNumber                 
                FROM [dbo].[Profile]
        )     

        SELECT 
            [ProfileId],
            [Name],
            [Address],
            [Email],
            [Mobile], 
            [Active]                                        
        FROM 
            tempProfile 
        WHERE 
            RowNumber > ' + CONVERT(VARCHAR,@LowerBand) + _
            '  AND RowNumber < ' + CONVERT(VARCHAR, @UpperBand)
            + ' ORDER BY ' + @SortExpression            
    
    EXEC sp_executesql @SqlString

    END
END

Profile_GET stored procedure takes PageSize, CurrentPage and SortExpression as input parameter.
PageSize - is the number of records that will display in GridView as a page at a time. CurrentPage – which page you are in currently in GridView.
SortExpression <code>– sort records in a page by which field.
All of these above parameters will be passed by GridView Control form web page when the application is running.

In the procedure SET NOCOUNT ON so that query does not produce message like following that increases output volume from query.

(29 row(s) affected)

SET @LowerBand  = (@CurrentPage - 1) * @PageSize
SET @UpperBand  = (@CurrentPage * @PageSize) + 1

The above two equations will calculate upper bound and lower bound for a page. Lower band means the starting row position for a page and upper band means top row position for a page. Say, you have current page 5 and your page size is 5. So the result will be for the above two equations are:

LowerBand = (5 -1) * 5 = 20 
UpperBand = (5 * 5) + 1 = 26

So records that hold position number 21-25 (because I used greater than and less than in where condition later in this procedure) will return by those equations.

WITH tempProfile AS
    (                    
        SELECT 
            [ProfileId],
            [Name],
            [Address],
            [Email],
            [Mobile], 
        [Active] = CASE [IsActive] WHEN 1 THEN 'Active' _
            WHEN 0 THEN 'DeActive' END,                            
            ROW_NUMBER() OVER (ORDER BY '+ _
            @SortExpression +' ) AS RowNumber                 
            FROM [dbo].[Profile]
    )  

WITH SQL statement followed by a table name will produce a temporary table from SELECT SQL statement from its body.

ROW_NUMBER() OVER (ORDER BY '+ @SortExpression +' ) AS RowNumber        

The above SQL statement will add a additional column RowNumber in a temporary table tempProfile and assign a sequential number for each record in temporary table after sort in ascending order by sorting expression parameter. This RowNumber column will be used for paging later.

Some records from temporary tempProfile table where records have been sorted by Profile Id.

Now filter records as per request that has come by PageSize, CurrentPage and SortExpresssion parameters.

SELECT 
[ProfileId],
     [Name],
 [Address],
     [Email],
       [Mobile], 
       [Active]                                        
       FROM 
         tempProfile 
        WHERE 
            RowNumber > ' + CONVERT(VARCHAR,@LowerBand) + '  _
		AND RowNumber < ' + CONVERT(VARCHAR, @UpperBand)
            + ' ORDER BY ' + @SortExpression      

I have executed dynamic SQL by EXEC sp_executesql as for SortExpression come by parameter.

EXEC sp_executesql @SqlString

Now, you need to create another stored procedure.

CREATE PROCEDURE [dbo].[Profile_Total]
AS
BEGIN

    SET NOCOUNT ON
    SELECT COUNT(*) FROM Profile
END

The above Profile_Total will return total number of records.

Now come to Visual Studio. Add a class I have created ProfileDataSource.cs. This type will be used by object data source. So we need to make it data source of object data source.

[DataObject(true)]
public class ProfileDataSource
{
    public ProfileDataSource()
    {
    }

    [DataObjectMethod(DataObjectMethodType.Select, false)]
    public Int32 TotalRowCount(Int32 startRowIndex, 
    	Int32 pageSize, String sortExpression)
    {
        Int32 intTotalProfile = 0;

        using (SqlConnection conn = new SqlConnection
        (ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString()))
        {
            SqlCommand cmdSelect = new SqlCommand();

            conn.Open();
            cmdSelect.CommandText = "Profile_Total";
            cmdSelect.CommandType = CommandType.StoredProcedure;
            cmdSelect.Connection = conn;

            SqlDataReader dataReader = cmdSelect.ExecuteReader();

            dataReader.Read();
            intTotalProfile = Convert.ToInt32(dataReader[0]);
        }
        return intTotalProfile;
    }

    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public static DataTable GetProfileData
    (Int32 startRowIndex, Int32 pageSize, String sortExpression)
    {
        DataTable profileDataTable = new DataTable();

        using (SqlConnection conn = new SqlConnection
        (ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString()))
        {
            SqlCommand cmdSelect = new SqlCommand();

            conn.Open();
            cmdSelect.CommandText = "Profile_GET";
            cmdSelect.CommandType = CommandType.StoredProcedure;
            cmdSelect.Connection = conn;                

            startRowIndex = Convert.ToInt32(startRowIndex / pageSize) + 1;
            
            if (String.IsNullOrEmpty(sortExpression))
                sortExpression = "ProfileId";

            cmdSelect.Parameters.AddWithValue("@CurrentPage", startRowIndex);
            cmdSelect.Parameters.AddWithValue("@PageSize", pageSize);
            cmdSelect.Parameters.AddWithValue("@SortExpression", sortExpression);

            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = cmdSelect;

            dataAdapter.Fill(profileDataTable);                
        }
        return profileDataTable;
    }
}

Add the following namespaces:

using System.Configuration;
using System.Data.SqlClient;
using System.ComponentModel;

Now tag the type ProfileDataSource as data object by adding DataObject attribute.

[DataObject(true)]
public class ProfileDataSource
{
 }

Add two methods in this type. TotalRowCount is to get total number of records from underling table. GetProfileData to get paged records from underling table.

Now make methods for Data Object Select method.

[DataObjectMethod(DataObjectMethodType.Select, false)]
public Int32 TotalRowCount(Int32 startRowIndex, Int32 pageSize, String sortExpression)
{
}

[DataObjectMethod(DataObjectMethodType.Select, true)]
public static DataTable GetProfileData(Int32 startRowIndex, 
	Int32 pageSize, String sortExpression)
{
}

Write body for TotalRowCount method. Parameters of this method will pass by object data source control later. This will execute Ptofile_Total stored procedure that we have already created and will return total number of records to object data source control.

[DataObjectMethod(DataObjectMethodType.Select, false)]
 public Int32 TotalRowCount(Int32 startRowIndex, Int32 pageSize, String sortExpression)
 {
    Int32 intTotalProfile = 0;

    using (SqlConnection conn = new SqlConnection
	(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString()))
    {
         SqlCommand cmdSelect = new SqlCommand();

         conn.Open();
         cmdSelect.CommandText = "Profile_Total";
         cmdSelect.CommandType = CommandType.StoredProcedure;
         cmdSelect.Connection = conn;

         SqlDataReader dataReader = cmdSelect.ExecuteReader();

         dataReader.Read();
         intTotalProfile = Convert.ToInt32(dataReader[0]);
    }
    return intTotalProfile;
}

Write body for GetProfileData method. Parameters of this method will be passed by object data source control. This will execute Profile_GET stored procedure that we have already created with parameter to get paged records for each request. Finally, this method will return a data table to object data source control.

[DataObjectMethod(DataObjectMethodType.Select, true)]
public static DataTable GetProfileData(Int32 startRowIndex, 
	Int32 pageSize, String sortExpression)
{
DataTable profileDataTable = new DataTable();

using (SqlConnection conn = new SqlConnection
	(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString()))
{
SqlCommand cmdSelect = new SqlCommand();

conn.Open();
cmdSelect.CommandText = "Profile_GET";
cmdSelect.CommandType = CommandType.StoredProcedure;
cmdSelect.Connection = conn;                

              startRowIndex = Convert.ToInt32(startRowIndex / pageSize) + 1;

              if (String.IsNullOrEmpty(sortExpression))
                 sortExpression = "ProfileId";

              cmdSelect.Parameters.AddWithValue("@CurrentPage", startRowIndex);
              cmdSelect.Parameters.AddWithValue("@PageSize", pageSize);
              cmdSelect.Parameters.AddWithValue("@SortExpression", sortExpression);

              SqlDataAdapter dataAdapter = new SqlDataAdapter();
              dataAdapter.SelectCommand = cmdSelect;

dataAdapter.Fill(profileDataTable);                
            }
            return profileDataTable;
        }


   startRowIndex = Convert.ToInt32(startRowIndex / pageSize) + 1;

The line of code calculates start row index form startRowIndex and pageSize parameters.

Object data source control passes sortExpression parameter to null when page is first time loaded. In this case, sort expression will be ProfileId that is primary key for the underling table.

if (String.IsNullOrEmpty(sortExpression))
sortExpression = "ProfileId";

EffectivePaging.aspx

Copy GridView markup from NormalPaging.aspx to EffectivePaging.aspx.

<asp:GridView ID="gvProfile" DataSourceID="profileDataSource" runat="server" 
AutoGenerateColumns="false" AllowPaging="true" AllowSorting="true" PageSize="5" 
HeaderStyle-Font-Names="Verdana" Font-Size="Small" 
HeaderStyle-HorizontalAlign="Left" HeaderStyle-Font-Underline="false" 
	Width="55%" HeaderStyle-BackColor="BurlyWood" 
HeaderStyle-ForeColor="Navy">
            <alternatingrowstyle backcolor="</span>Aquamarine"> />
            <columns>
                <asp:BoundField  DataField="ProfileId" HeaderText="Profile Id" 
SortExpression="ProfileId" ItemStyle-Width="6%"/>
                <asp:BoundField  DataField="Name" HeaderText="Name" 
SortExpression="Name" ItemStyle-Width="13%"/>
                <asp:BoundField  DataField="Address" HeaderText="Address" 
SortExpression="Address" ItemStyle-Width="18%"/>
                <asp:BoundField  DataField="Email" HeaderText="Email" 
SortExpression="Email" ItemStyle-Width="8%"/>
                <asp:BoundField  DataField="Mobile" HeaderText="Mobile" 
SortExpression="Mobile" ItemStyle-Width="9%"/>
                <asp:BoundField  DataField="IsActive" HeaderText="Status" 
SortExpression="IsActive" ItemStyle-Width="4%"/>          

Add an Object data source control in the Effectivepaging.aspx page. I have created profileDataSource. Set it for gvProfile data source Id.

DataSourceID="profileDataSource"

<asp:ObjectDataSource ID="profileDataSource" runat="server" 
	SelectMethod="GetProfileData" EnablePaging="true"
MaximumRowsParameterName="pageSize"
        StartRowIndexParameterName="startRowIndex" 
	TypeName="VTS.Web.UI.ProfileDataSource"  SelectCountMethod="TotalRowCount" 
        SortParameterName="sortExpression">
        <SelectParameters>
            <asp:Parameter Name="startRowIndex" Type="Int32" />
            <asp:Parameter Name="pageSize" Type="Int32"/>
            <asp:Parameter Name="sortExpression" Type="String" />            
        </SelectParameters>

Set some properties EnablePaging to true for paging,
MaximumRowsParameterName to pageSize for current page size,
StartRowIndexParameterName to startRowIndex for start row number position,
TypeName to VTS.Web.UI.ProfileDataSource for Data object with namespace,
SelectMethod to GetProfileData to get paged records,
SelectCountMethod to TotalRowCount to get total records from underlining table will be used for paging,
SortParameterName to sortExpression for sorting.

Select parameters for object data source:

<SelectParameters>
<asp:Parameter Name="startRowIndex" Type="Int32" />
<asp:Parameter Name="pageSize" Type="Int32"/>
<asp:Parameter Name="sortExpression" Type="String" />            
</SelectParameters>

Now run the application to browse EffectivePaging.aspx page in the browser. You will get the result as below:

How It works

Open SQL Profile from SQL Management Studio under Tools menu to diagnose how things work behind effective paging.

1. Open New Trace from File menu
2. Login using your credential
3. Select Run
4. Click Clear Trace Window under Edit menu to clear existing trace.
5. Minimze SQL Server Profiler
6. Run the Application from Visual Studio to browse EffectivePaging.aspx page

After you run the application for the first time, you will get the trace like below:

Copy the trace from profiler. And run it in your SQL Database using SQL Management Studio.

exec Profile_GET @CurrentPage=1,@PageSize=5,@SortExpression=N'ProfileId'

You will get the following output:

Now clear already created trace from SQL Profiler and minimize it.

Click page 2 in EffectivePaging.aspx page that is currently running. Come back to the SQL Profiler and get the trace for page 2.

exec Profile_GET @CurrentPage=2,@PageSize=5,@SortExpression=N'ProfileId'

Run it in the SQL Database using SQL Management Studio. You will get the below result:

Continue these steps for all of pages that are available in the bottom of the GridView also for sorting by clicking header of each column. You will get 5 records for each request. So there is no change to load all the records from the underling table. Only the number of records load that you have set to pageSize property in GridVew. Records load faster than earlier normal paging. It will take less system resource. It is very effective for thousands of thousand data.

Conclusion

Effective paging is important for applications that handle large number of records to build scalable applications. This demonstration will help you to create scaleable applications.

Thank You