GridView Custom Paging in ASP.NET 3.5 with SQL Server Stored Procedure

GridView Custom Paging in ASP.NET 3.5 with SQL Server Stored Procedure

GridView — Displays a set of data items in an HTML table. ASP.NET GridView control enables you to display, sort, page, select, and edit data.

Default gridview paging works best when you deal with limited pages. If there are more pages then, the performance suffers. In this case direct jump to desire page is a good alternative.

I have deal with a problem on GridView while working on user control (.ascx). I have a user control (.ascx) with GridView in it. I have used Stored Procedure to retrieve data. Bounding data to gridview display all the records and Default paging setting not work for me. Here Custom Paging helps me and only those database records that need to be displayed get retrieved. Using GridView Custom Paging solve the Issue for me.

Here, I am going to demonstrate a sample on GridView Custom Paging. I want to display records from two different tables depending on search pattern, let’s consider, I have student table contain sName, sAddress, ClassId and class table contain classId and ClassName. Now I want to display Name, Address and ClassName based on string pattern. Here I will get Ramdom Records based on search pattern. In this example we are going to use ASP.Net DropDownList along with ASP.Net GridView for Pagination to provide Jump To Page Number facility to the user.

First of all take a look at our table structure. Create New table based on below structure and add some dummy data in it…

Table 1 – tblStudent
SrNo sName sAddress ClassId
1 Lucy Redmond 1
2 Sam SmallVilla 1
3 Mitchell Mumbai 1
4 Scott SmallVilla 2
5 Kathryn Redmond 3
6 Frank Mumbai 1
2000 Jay Mumbai 3

Table 2 – tblClass
ClassId ClassName
1 Electronics Engineering
2 Computer Engineering
3 Mechanical Engineering

Create New table based on above structure and add some dummy data in it.

GridView Markup

First you’ll have to drag an ASP.Net GridView control and a DropDownList control to the Asp.Net web page. Following piece of code will do it for you.

<asp:TextBox ID="txtSearch" runat="server" Text=""></asp:TextBox> &nbsp;<asp:Button ID="btnSearch" runat="server" Text="Go" />
<asp:GridView ID="grdUserDtl" runat="server" AutoGenerateColumns = "false"
    AllowPaging = "true" PageSize = "10" PagerSettings-Visible = "false">
        <asp:BoundField DataField="sName" HeaderText="Student Name" />
        <asp:BoundField DataField="sAddress" HeaderText="Address" />
        <asp:BoundField DataField="ClassName" HeaderText="Class" />        
    No company found for your search
Jump To:
<asp:DropDownList ID="ddlJumpToPage" runat="server"
    OnSelectedIndexChanged = "PageNumberChanged" AutoPostBack = "true">

Stored Procedure for Custom Paging

We are ready with database, gridview and DropDownList. Now I am going to design a stored procedure to get the Customer records from the tables tblStudent and tblClass.

CREATE procedure sp_StudentDtl
    @strSearchText varchar(100),
    @intPageSize int,
    @intCurrentPage int,
    @intTotalRecords int output
— Author: <Chetankumar Akarte>
— Description: <Return Records depending on @intPageSize and @intCurrentPage.>
DECLARE @tblStudentDtl TABLE(
SrNo int IDENTITY not null,
sName varchar(50) ,
sAddress varchar(100),
ClassName varchar(100)
DECLARE @start int
SET @start=@intPageSize*(@intCurrentPage 1)
INSERT INTO @tblStudentDtl (sName,sAddress,ClassName)
SELECT sName, sAddress,ClassName FROM tblStudent A JOIN tblClass B ON A.ClassId=B.ClassId WHERE sName like @strSearchText +‘%’
SELECT @intTotalRecords=Count(*) FROM tblStudent A JOIN tblClass B ON A.ClassId=B.ClassId WHERE sName like @strSearchText +‘%’
SELECT sName, sAddress,ClassName FROM  @tblStudentDtl WHERE SrNo > @start

In this Stored Procedure we are three input parameter: @strSearchText varchar(100) for search pattern, @intPageSize integer type to define rows display per page, @intCurrentPage integer type to define page number which help us to select desire data.

Binding data from Stored Procedure with the GridView

In web.config we are going to add a key ‘gridPageSize’ at appSettings which will help us to customize pagging efficiently. We also going to set a connectionStrings ‘conString’ to connect to our database.

    <add key="gridPageSize" value="15"/>
    <add name="conString" connectionString="Data Source=SerVerPath\sqlexpress;Initial Catalog=dbSchool;Integrated Security=True" providerName="System.Data.SqlClient"/>

Now we need to bind custom result to our gridview, I have define a private function BindResult which will take currentPage number as a input and papualte result accordingly and return Total record count. Another function populateList use that Total record count to generate page numbers for DropDownList ddlJumpToPage.

Use The System.Data.SqlClient namespace is the.NET Framework Data Provider for SQL Server, comes with collection of classes used to access a SQL Server database in the managed space.
using System.Data.SqlClient;

private int BindResult(int currentPage)
    int TotalRows = 0;
    DataTable dt = new DataTable();
    String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(strConnString);
    SqlDataAdapter sda = new SqlDataAdapter();
    SqlCommand cmd = new SqlCommand("sp_StudentDtl");
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@intTotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
    cmd.Parameters.AddWithValue("@strSearchText", txtSearch.Text.Trim());
    cmd.Parameters.AddWithValue("@intPageSize", Convert.ToInt16(ConfigurationManager.AppSettings["gridPageSize"]));
    cmd.Parameters.AddWithValue("@intCurrentPage", currentPage);
    cmd.Connection = con;
    sda.SelectCommand = cmd;
    TotalRows = (int)cmd.Parameters["@intTotalRecords"].Value;
    grdUserDtl.PageIndex = currentPage 1;
    grdUserDtl.DataSource = dt;
    return TotalRows;

private void populateList(int TotalRows)
    int PageCount = Convert.ToInt32(Math.Floor(Convert.ToDouble((TotalRows / Convert.ToInt32(ConfigurationManager.AppSettings["gridPageSize"]))))); ;
    for (int i = 1; i <= PageCount; i++)
        ddlJumpToPage.Items.Add(new ListItem(i.ToString(), i.ToString()));

On page load we need to call above functions. We want the functionality of filter records based on search pattern so we need to call these functions on click event of search button btnSearch.

protected void Page_Load(object sender, EventArgs e)
    int TotalRows = this.BindResult(1);
protected void btnSearch_Click(object sender, EventArgs e)
    int TotalRows = this.BindResult(1);

Handling the Page Change Event

ASP.Net DropDownList control ddlJumpToPage which is going to take care of page change event through OnSelectedIndexChanged method, we just need to define a PageChanged function for it.

protected void PageChanged(object sender, EventArgs e)
    int Page = Convert.ToInt32(ddlJumpToPage.SelectedItem.Value);


GridView Custom Paging in ASP.NET 3.5 with SQL Server Stored Procedure



10 thoughts on “GridView Custom Paging in ASP.NET 3.5 with SQL Server Stored Procedure”

  1. Your article, Chetankumar Akarte, is a great learning tool for me. But I believe I found a typo:

    protected void PageChanged(object sender, EventArgs e)

    should be

    protected void PageNumberChanged(object sender, EventArgs e)

    I am also having a problem with the grid binding to the data, I am always getting just the first (page) records no matter which page number is selected in the ddlJumpToPage control.

    Any ideas?


  2. In regards to the “PageNumberChanged” vs. “PageChanged” I mentioned before; I see now that your download code is more correct. However, the code presented in the article above is not. Just thought you should know.

    Still debugging the GridView *always* showing the first page of the record set.


  3. Like I said, this article has been a good learning tool. Here’s a change in your code that seems to work for me:

    I added a check for ” if (!IsPostBack) ”

    protected void Page_Load(object sender, EventArgs e)
    if (!IsPostBack)
    int TotalRows = this.BindResult(1);


  4. hi,

    following is script for creating table for this article.

    CREATE TABLE tblStudent
    SrNo int identity(1001,1) primary key,
    sName varchar(50),
    sAddress varchar(500),
    ClassId int

    insert into tblStudent(sName,SAddress,ClassId)

    select * from tblStudent

    CREATE TABLE tblClass
    ClassId int identity(1,1) primary key,
    ClassName varchar(100)

    insert into tblClass(ClassName)
    (‘Information Technology’)

    select * from tblStudent

    Manish Agrahari

  5. Do u have any idea how to use numeric paging that we have with grid view.
    If we have too much pages than there is draw back with DropDownList.

  6. Hi, Thanks for the article, But I am facing a small problem. Suppose I have 22 records and my records per page is 5 then it is showing till 4 pages that is last 2 recored are nt showing. At that time it should show 5 pages and in the last page it should show 2 records. I guess some modification needed in populateList() method..

    Waiting 4 ur reply

  7. Hi, This article realy good for beginner. Who do i use this code if my table does not have srNo column which is used in logic to get page records?

  8. Hi,
    I am beginner so please provide all the grid view events with classification so it would help me a lot how the grid view used

Leave a Reply

Your email address will not be published. Required fields are marked *