Download the code
Hello everyone,
Here I’m going to explain how I managed to implement Custom Paging in my project. It involved an extensive amount of data that was used for searching and I definitely needed a better option than the default paging. In a nutshell, Default paging gets the complete set of records although it needs to show say only 10 – 12 records so when you’re dealing with millions of records this really does put a huge impact on performance. In Custom Paging however, you extract exactly those records that you need to show on the GridView or which ever control you like. So basically this technique is more related to the database rather than the GridView. When I was working on my project I saw a post of implementing Custom Paging with Oracle as the backend on http://www.codeproject.com/, which is offcourse one of my favorite websites. So anyways, without putting in any further delays, I’m gonna present the code I used for implementing this. This is only a scaled down version of the code I originally made.
Well basically we need to use two methods in the presentation layer. One is the Select method that does the extraction of the relevant records as needed on the page and the other one is the Count method which needs to report how many total records there are for the particular search criteria.
First of all here’s the code for the database table I’m using.
Database Table(s)
/////////////////////////////////////
CREATE TABLE [dbo].[BusinessInformation](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_BusinessInformation_Email] DEFAULT (”),
[BusinessDescription] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GeneralDescription] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateAdded] [datetime] NULL CONSTRAINT [DF_BusinessInformation_DateAdded] DEFAULT (getdate()), CONSTRAINT [PK_BusinessInformation] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF)
ON [PRIMARY]) ON [PRIMARY]
/////////////////////////////////////////////////////////////////
Here’s the Stored Procedure for the Count Method I mentioned earlier, as you can see this method is returning the total number of records in that particular search criteria.
CREATE PROCEDURE [dbo].[proc_GetCountofSearchResults]
@Name varchar(300),
@TotalRecords int output
AS
BEGIN
SELECT @totalrecords = Count(*)
FROM
businessinformation
Where
(Name LIKE ‘%’+@Name+’%’ OR businessDescription LIKE ‘%’+@Name+’%')
END
This is the Stored Procedure for the Select Method, notice the two parameter @startRowIndex and @maximumRows. These are very critical for Custom Paging. The startRowIndex is basically the starting record number and the maximumRows is the total number of records to be displayed on the page. In other words, you can say that this is the PageSize of the GridView.
In this Stored Procedure, the functions Row_Number and Over play a very important role. The function Row_number returns the number of row returnd in the set of rows returned which is ordered by ID in the function Over(). The number of rows returned is restricted by the condition:
Where (RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) – 1)
CREATE PROCEDURE [dbo].[proc_LoadSubsetForSearch]
– Add the parameters for the stored procedure here
@startRowIndex int,
@maximumRows int,
@Name varchar(300)
AS
BEGIN
SET @startRowIndex = @startRowIndex + 1
SELECT
ID, Name, Address, Email, BusinessDescription, GeneralDescription, DateAdded
FROM
(SELECT *,
ROW_NUMBER() OVER(ORDER BY ID) as RowNum
FROM dbo.BusinessInformation
Where
(Name LIKE ‘%’+@Name+’%’ OR businessDescription LIKE ‘%’+@Name+’%')
) as bi
Where (RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) – 1)
END
————————————————————————————-
Now comming to the application: I made a Data Access Layer and made this class. There are only two functions in this class.
- public DataTable LoadForSearchSubset(string Name, int startRowIndex, int maximumRows)
- public int GetSearchResultsRowCount(string Name, int startRowIndex, int maximumRows)
Now you might be wondering why we’re passing startRowIndex and maximumRows to the count Method, but the problem is that its kind of a necessary evil.
///////////////////////////////////////////////////////////////////////////
//BUSINESSINFORMATION.CS // in app_code folder
/////////////////////////////////////////
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
///
/// Summary description for BusinessInformation
///
namespace CustomPaging
{
public class BusinessInformation
{
SqlConnection conn;
public BusinessInformation()
{
conn = new SqlConnection(ConfigurationManager.AppSettings["dbConnection"]);
//Accessing the connection string from web.config file
}
public DataTable LoadForSearchSubset(string Name, int startRowIndex, int maximumRows)
{
try
{
//no use searching if this condition is true
if (String.IsNullOrEmpty(Name) (startRowIndex == 0 && maximumRows == 0))
return null;
SqlCommand cmd = new SqlCommand(“proc_LoadSubsetForSearch”, conn);
cmd.Parameters.Add(“@Name”, SqlDbType.VarChar);
cmd.Parameters.Add(“@startRowIndex”, SqlDbType.Int);
cmd.Parameters.Add(“@maximumRows”, SqlDbType.Int);
cmd.Parameters["@startRowIndex"].Value = startRowIndex;
cmd.Parameters["@maximumRows"].Value = maximumRows;
cmd.Parameters["@Name"].Value = String.IsNullOrEmpty(Name) ? “” : Name;
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
}
}
public int GetSearchResultsRowCount(string Name, int startRowIndex, int maximumRows)
{
try
{
//no use searching if this condition is true
if (String.IsNullOrEmpty(Name) (startRowIndex == 0 && maximumRows == 0))
return 0;
//SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["dbConnection"]);
SqlCommand cmd = new SqlCommand(“proc_GetCountofSearchResults”, conn);
cmd.Parameters.Add(“@Name”, SqlDbType.VarChar);
cmd.Parameters["@Name"].Value = String.IsNullOrEmpty(Name) ? “” : Name;
SqlParameter outputParameterTotalRecords = new SqlParameter(“@totalrecords”, SqlDbType.Int, 4, ParameterDirection.Output, false, 9, 0, “”, DataRowVersion.Default, 0);
cmd.Parameters.Add(outputParameterTotalRecords);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
conn.Open();
cmd.ExecuteNonQuery();
return Convert.ToInt32(cmd.Parameters["@totalrecords"].Value);
}
finally
{
if(conn.State == ConnectionState.Open)
conn.Close();
}
}
}
}
//////////////////////////////////////////////////////////////////////////
I’m unable to write the complete code of Default.aspx page because this doesnt allow HTML so I’m just copying the code for the objectDataSource, please remember that YOU MUST set the enablePaging to true because without this it doesn’t starting paging. The objectDataSource passes the StartRowIndex and maximumRows parameters itself. The maximumRows is taken from the pagesize you specify in the GridView. Whereas the startRowIndex is calculated in the onPageIndexChanging event. In my case I had to pass the search criteria to the stored procedures as well, so I needed some mechanism to do that as well. In the end I resorted to making my own function, the ExecuteNormalSearch function, which I called whenever I needed to populate the gridview
//DEFAULT.ASPX
<asp:objectdatasource id=”NormalSearchObjectDataSource” runat=”server” enablepaging=”True” selectmethod=”LoadForSearchSubset” selectcountmethod=”GetSearchResultsRowCount” typename=”CustomPaging.BusinessInformation“></asp:objectdatasource><asp:gridview id=”SearchResultsGridView” runat=”server” pagesize=”2″ allowpaging=”True” datakeynames=”ID” onpageindexchanging=”SearchGridView_PageIndexChanging” autogeneratecolumns=”true”></asp:gridview>
///////////////////////////////////////////////////////
DEFAULT.ASPX.CS
//////////////////////////////////////////////////////
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using CustomPaging;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void SearchBtn_OnClick(object sender, EventArgs e)
{
ExecuteNormalSearch(0, SearchResultsGridView.PageSize, SearchTb.Text);
}
protected void SearchGridView_PageIndexChanging(object s, GridViewPageEventArgs e)
{
ExecuteNormalSearch((e.NewPageIndex + 1) * SearchResultsGridView.PageSize, SearchResultsGridView.PageSize, SearchTb.Text);
}
protected void ExecuteNormalSearch(int startIndex, int maximumRows, string searchCriteria)
{
if (SearchResultsGridView.DataSourceID != “NormalSearchObjectDataSource”)
ChangeDataSource(“NormalSearchObjectDataSource”);
NormalSearchObjectDataSource.SelectParameters.Clear();
NormalSearchObjectDataSource.SelectParameters.Add(“startRowIndex”, TypeCode.Int32, startIndex.ToString());
NormalSearchObjectDataSource.SelectParameters.Add(“maximumRows”, TypeCode.Int32, maximumRows.ToString());
NormalSearchObjectDataSource.SelectParameters.Add(“Name”, TypeCode.String, searchCriteria);
NormalSearchObjectDataSource.SelectMethod = “LoadForSearchSubset”;
NormalSearchObjectDataSource.TypeName = “CustomPaging.BusinessInformation”;
NormalSearchObjectDataSource.SelectCountMethod = “GetSearchResultsRowCount”;
NormalSearchObjectDataSource.Select();
}
private void ChangeDataSource(string ID)
{
SearchResultsGridView.DataSourceID = ID;
SearchResultsGridView.PageIndex = 0;//go to first page
}
}
I hope you find this useful. Please let me know if you have any queries,
Download the code
