• Custom Paging in ASP.NET GridView Control

    Posted on March 30, 2012 by in ASP.NET, C#, Dotnet

    The ASP.NET GridView control has a built-in paging capability that supports basic paging functionality. You can use the default paging user interface (UI) or create a custom paging interface. In this article, I would be discussing about the later one i.e. custom paging. Default paging has a major performance drawback. If you have 10,000 records in a table, you have to assign the whole dataset to the grid and grid determines the set of records it is supposed to display and will discard the result.  That is, if the grid was paging through a total of 10,000 records, showing 50 records per page, on each and every page request all 10,000 records would be returned from the database, but only the 50 appropriate ones would be displayed. With Custom Paging, we don’t need to fetch all the records at once. We could do get them page by page depending upon the page index. In above scenario, we only get 50 records at a time from the database.

    Database Connection

    Open web.config and add following entry to connection strings element

    <add name="Sql" connectionString="Data Source=<SERVERNAME>;
         Initial Catalog= TEST;User=testuser;Password=testuser;" 
         providerName="System.Data.SqlClient"/> 

    Page Design

    By default paging is disabled in grid view, so no worries there. We added some bound columns to display product data. Applied some formatting to make the grid look nice and clean.

    <asp:gridview ID="gvProducts" 
     BorderColor="White" BorderStyle="Ridge"
        CellSpacing="1" CellPadding="3" GridLines="None" 
        BackColor="White" BorderWidth="2px"
        AutoGenerateColumns="False"             
      runat="server" DataKeyNames="ProductID">
        <Columns>
          <asp:BoundField DataField="ProductID" HeaderText="Product ID">  
                <HeaderStyle HorizontalAlign="Left"  Width="150px"/>
            </asp:BoundField>                         
           <asp:BoundField DataField="ProductNumber" HeaderText="Product Number">  
                 <HeaderStyle HorizontalAlign="Left" Width="150px" />
                 <ItemStyle HorizontalAlign="Left" />
            </asp:BoundField>
            <asp:BoundField DataField="Name" HeaderText="Product Name">  
                 <HeaderStyle HorizontalAlign="Left" Width="150px" />
                 <ItemStyle HorizontalAlign="Left" />
            </asp:BoundField>    
             <asp:BoundField DataField="ListPrice" HeaderText="Price">  
                 <HeaderStyle HorizontalAlign="Left" Width="50" />
                 <ItemStyle HorizontalAlign="Right" />
            </asp:BoundField>                                   
        </Columns>
    </asp:gridview>  

    Added 4 link buttons to the page.  Their names itself are self explanatory. When you click on “First” button you go to first page, if you click on “Next” it takes to next page. If you click on “Previous” link, it takes you to previous page and when you click on “last”, you land up on the last page.

    <table>
    <tr>     
    <td><asp:LinkButton ID="btnFirst" CommandName="Page" RunAt="server" Text="First"
         CommandArgument="First" OnCommand="PageChangeEventHandler"/></td>
    <td><asp:LinkButton ID="btnPrevious" CommandName="Page" RunAt=server Text="Prev"
         CommandArgument="Previous" OnCommand="PageChangeEventHandler"/></td>
    <td><asp:LinkButton ID="btnNext"  CommandName="Page" RunAt="server" Text="Next"
         CommandArgument="Next" OnCommand="PageChangeEventHandler"/></td>
    <td><asp:LinkButton ID="btnLast"  CommandName="Page" RunAt="server" Text="Last"
        CommandArgument="Last" OnCommand="PageChangeEventHandler"/></td>    
    </tr>
    </table>
    

    In order to display the user about current page he is upon, added couple of placeholders.

    <i>You are viewing page <%=pageIndex%> of <%=pageCount%> </i>

    Source Code:

    We are setting the page size to 5 in our example. Added 3 class variables to hold current page number, total number of pages and total number of rows.

    protected int pageIndex = 1;
    protected int pageCount = 0;
    protected int rowCount = 0;
    private const int CONST_PAGE_SIZE = 5;

    If it is the first time page is loaded, we default page index to 1 and will save the calculated page count in the view state. If the page is reloaded due to the post back event, then we get those values from VIEWSTATE.

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindData();
            //based on the row count & page size, calculate page count
            pageCount = CalcPageCount(rowCount);
            ViewState["PageCount"] = pageCount;
            RefreshPageButtons();
        }
        else
        {
            if (ViewState["PageIndex"] != null)
                pageIndex = Convert.ToInt32(ViewState["PageIndex"]);
            if (ViewState["PageCount"] != null)
                pageCount = Convert.ToInt32(ViewState["PageCount"]);
        }
    }
    private int CalcPageCount(int totalRows)
    {
        return (int)(totalRows / CONST_PAGE_SIZE);            
    }

    The logic to figure out records that were supposed to be shown on the page is in a stored procedure. It makes that decision based on the current page we are on and on the number of records we display on the page.

    CREATE procedure [dbo].[sp_Products_Get]
     (     
         @PageSize int,
         @PageIndex int,
         @RecordCount int output
     )
     AS
     BEGIN    
     DECLARE @tblProduct TABLE(
      ProductID int not null,
      Name varchar(255) ,
      ProductNumber varchar(255),
      ListPrice decimal
     )
     DECLARE @index int
     SET @ index [email protected]*(@PageIndex - 1)  
     INSERT INTO @tblProduct (ProductID, Name,ProductNumber,ListPrice)
     SELECT ProductID, Name,ProductNumber,ListPrice FROM tblProduct
     
     SELECT @RecordCount=Count(*) FROM @tblProduct 
     
     SET ROWCOUNT @PageSize
     SELECT ProductID, Name,ProductNumber,ListPrice FROM  @tblProduct WHERE 
    ProductID > @ index
     SET ROWCOUNT 0  
     END

    ADO.NET API is used to execute [sp_Products_Get] stored procedure and bind the result to the grid. We also get total number of rows as an output parameter, we store it in our class variable for further use.

    private void BindData()
    {
        DataSet dsProducts = RetrieveProducts();
        //Bind the grid view
        gvProducts.DataSource = dsProducts;
        gvProducts.DataBind();          
    }
    private DataSet RetrieveProducts()
    {
        if (ViewState["Products"] != null)
            return (DataSet)ViewState["Products"];
        //fetch the connection string from web.config
        string connString = 
    ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
        DataSet dsProducts = new DataSet();
        //Open SQL Connection
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            //Initialize command object
            using (SqlCommand cmd = new SqlCommand("sp_Products_Get", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                //add input parameters to the SP
                cmd.Parameters.AddWithValue("@PageIndex",pageIndex);
                cmd.Parameters.AddWithValue("@PageSize", CONST_PAGE_SIZE);
                //add output parameters
                cmd.Parameters.Add("@RecordCount", SqlDbType.Int);
                cmd.Parameters["@RecordCount"].Direction = 
    ParameterDirection.Output;
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //Fill the result set
                adapter.Fill(dsProducts);
                //get the output value
                rowCount = (int)cmd.Parameters["@RecordCount"].Value;
            }
        }
        return dsProducts;
    }

    When you click on any of the page buttons, PageChangeEventHandler event is fired. Here depending upon the command argument, we decide if the user clicked on “First”, “next”, “previous” or “last” and process accordingly. Everytime page is refreshed, we got to enable/disable page buttons depending upon the page index.

    protected void PageChangeEventHandler(object sender, CommandEventArgs e)
    {
        switch (e.CommandArgument.ToString())
        {
            case "First":
                pageIndex = 1;
                break;
            case "Prev":
                pageIndex = pageIndex - 1;
                break;
            case "Next":
                pageIndex = pageIndex + 1;
                break;
            case "Last":
                pageIndex = pageCount;
                break;
        }
        //set the latest page index back to view state
        ViewState["PageIndex"] = pageIndex;
        //fetch the latest batch of products
        BindData();
        //enable/disable page buttons based on current page index
        RefreshPageButtons();
    }
    private void RefreshPageButtons()
    {
        btnFirst.Enabled = true;
        btnPrevious.Enabled = true;
        btnNext.Enabled = true;
        btnLast.Enabled = true;
        //if this is the first page, disable previous page
        if (pageIndex == 1)
        {
            btnPrevious.Enabled = false;
            btnFirst.Enabled = false;
            //if the page count is more than 1, enable next button                
            if (pageCount <= 0)
                btnNext.Enabled = false;                
        }
        else
        {                
            if (pageIndex == pageCount)
            {
                btnNext.Enabled = false;
                btnLast.Enabled = false;
            }               
        }
    }
    

    SQL Statements:

    I have created a tblProduct table and loaded it with some test data. If you would like to use the same table, you could use below scripts.

    CREATE TABLE [dbo].[tblProduct](
     [ProductID] [int] IDENTITY(1,1) NOT NULL,
     [Name] [varchar](255) NOT NULL,
     [ProductNumber] [varchar](255) NOT NULL,
     [ListPrice] [numeric](9, 2) NOT NULL,
     CONSTRAINT [PK_tblProduct_ProductID] PRIMARY KEY CLUSTERED 
    (
     [ProductID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    DECLARE @COUNTER INT, @productname VARCHAR(255), 
     @productnumber VARCHAR(255), @listprice DECIMAL
    SET @COUNTER = 0
    SET @productname = 'product name '
    SET @productnumber = 'product number '
    SET @listprice = 500
    WHILE(@COUNTER <= 20)
    BEGIN
     SET @listprice= @listprice + 100
     INSERT INTO [dbo].[tblProduct]
         VALUES
               (@productname + convert(VARCHAR, @COUNTER)
               ,@productnumber + convert(VARCHAR, @COUNTER)
               ,@listprice)
     SET @COUNTER = @COUNTER + 1           
    END

    Screenshots:

    When you run the application, it would take you the default page.

    Now if you click on “Next” link, it would take you to the next page. On the next page, you would see next 5 products

    When you click on the “Last” link, it would take you to the last page.

    Now if you click on “Previous” page, it would take you to page 4.

    If you click on the “First” link it would take you to the first page.

    Be Sociable, Share!
      Post Tagged with , ,

    Written by

    Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.

    View all articles by

    Email : [email protected]

    One Responseso far.

    1. Mike says:

      I’m not seeing where you are every using the rowcount variable for anything.

    Leave a Reply