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.
Figure 1
Some records form Profile
table.
Figure 2
Creating ASP.NET Website in Visual Studio 2010
- Create a website project and name it
EffectivePagingAndSorting
.
- 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:
Figure 3
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.
- Open SQL Server Management Studio
- 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
– 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.
Figure 4
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"> />
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:
Figure 5
How It works
Open SQL Profile from SQL Management Studio under Tools menu to diagnose how things work behind effective paging.
- Open New Trace from File menu
- Login using your credential
- Select Run
- Click Clear Trace Window under Edit menu to clear existing trace.
- Minimze SQL Server Profiler
- 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:
Figure 6
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:
Figure 7
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:
Figure 8
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