SQL Paging in ASP.NET Using SQL Server, ASP.NET and C#

In this article, you will learn how to implement SQL paging in ASP.Net. I have used an ASP.Net Repeater control but the same concept can be used for an ASP.Net GridView control too.

Required software's are:

• Visual Studio 2008/2010
• SQL Server 2005/2008

I am using Visual Studio 2010 and SQL Server 2008.

Implementation Steps

Step 1

Download the attached file to get more information about it. The images and CSS files are also in the attached file.

Step 2

Before creating a web site, run the database script. In the SQL query analyzer create a table and a stored procedure.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employee](
      [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
      [FirstName] [varchar](100) NULL,
      [MiddleName] [varchar](100) NULL,
      [LastName] [varchar](100) NULL,
      [Address1] [varchar](max) NULL,
      [City] [varchar](50) NULL,
      [Country] [varchar](50) NULL,
      [Age] [int] NULL,
      [DOB] [datetime] NULL
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DisplayDataPaging]               
(           
@PageNumber    INT,            
@PageSize      INT=''   
)             
AS               
SET NOCOUNT ON;                       

 CREATE TABLE #Employee       
 (       
   EmployeeID int
  ,FullName Varchar(500)       
  ,Address1 Varchar(max)   
  ,City Varchar(50)   
  ,Country Varchar(100)
  ,Age int      
  ,DOB DateTime       
  ,RowNumber Int IDENTITY(1,1)       
 )                      
 DECLARE @intTotal INT             
 DECLARE @SQL varchar(MAX)       
      SET @intTotal = @PageSize * @PageNumber                     
      Select COUNT(EmployeeID) As TotalRecord        
      FROM Employee            
 INSERT INTO #Employee       
 SELECT EmployeeID
   ,(FirstName + ' ' + LastName) As FullName               
   ,Address1           
   ,City
   ,Country
   ,Age
   ,DOB
 FROM Employee      
   Select         
   EmployeeID           
   ,FullName      
   ,Address1           
   ,City
   ,Country
   ,Age
   ,DOB
   ,RowNumber           
  From #Employee           
  WHERE  RowNumber between ((@PageNumber * @PageSize)-(@PageSize- 1)) AND (@PageNumber * @PageSize)                  
 Declare @TotalRecords Integer             
 Declare @TotalPage Integer                         
 SELECT @TotalRecords=MAX(RowNumber) from #Employee              
 if(@TotalRecords is not NULL)             
 begin             
  if(@TotalRecords%@PageSize = 0)             
  begin             
   SET @TotalPage = @TotalRecords/@PageSize             
  end             
  else              
  begin             
   SET @TotalPage = @TotalRecords/@PageSize + 1             
  end             
 end             
 else             
 begin             
  set @TotalPage = 1             
 end              
 Select @TotalPage [TotalPages], @TotalRecords [TotalRecords]                       
 DROP Table #Employee             

GO

Insert some records into Employee table.

Step 3

Create a new web site called <PagingSite> in Visual Studio 2010.

Step 4

Change the connection string in the web.config file according to your database server credentials.

<connectionStrings>
    <add name="connectionStringKey" connectionString ="Data Source=server1;Initial Catalog=TestDatabase;uid=user1;pwd=password1"/>
  </connectionStrings>

Step 5

Create 2 classes called clsDatabase.cs and JobServices.cs as:

And write the following code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

/// <summary>
/// Summary description for clsDatabase
/// </summary>
public class clsDatabase
{
       public clsDatabase()
       {
              //
              // TODO: Add constructor logic here
              //
       }
    private SqlConnection objConnection = null;
    private string connectionString = string.Empty;

    public string ConnectionString
    {
        get { return this.connectionString; }
        set { this.ConnectionString = value; }
    }
    /// <summary>
    /// Open the connection.
    /// </summary>
    private SqlConnection funOpenConnection()
    {
        try
        {
            string connStr = "";
            objConnection = new SqlConnection();
            this.connectionString = ConfigurationManager.ConnectionStrings["connectionStringKey"].ToString();
            //this.connectionString = ConfigurationSettings.AppSettings["connectionStringKey"];
            objConnection = new SqlConnection(connectionString);
            if (objConnection.State != ConnectionState.Open)
                objConnection.Open();
        }
        catch (Exception objException)
        {
            return null;
        }
        return objConnection;
    }
    /// <summary>
    /// Close the connection.
    /// </summary>
    public void CloseConnection()
    {
        if (objConnection != null)
        {
            if (objConnection.State == ConnectionState.Open)
                objConnection.Close();
        }
    }
    /// <summary>
    /// Close the connection.
    /// </summary>
    public void funCloseConnection()
    {
        if (objConnection != null)
            objConnection.Close();
    }

    /// <summary>
    /// Release resources.
    /// </summary>
    public void Dispose()
    {
        // make sure connection is closed
        if (objConnection != null)
        {
            objConnection.Dispose();
            objConnection = null;
        }
    }
    public void RunProcedure(string sProcName, SqlParameter[] objaPrams, out DataSet objDataSet)
    {
        try
        {
            SqlDataAdapter objDataAdapter = new SqlDataAdapter();
            SqlCommand objCommand = funCreateCommand(funOpenConnection(), sProcName, objaPrams);
            objDataAdapter.SelectCommand = objCommand;
            objDataSet = new DataSet();
            objDataAdapter.Fill(objDataSet);
        }
        catch (Exception ex) { objDataSet = null; }
        finally { this.CloseConnection(); }
    }
    private SqlCommand funCreateCommand(SqlConnection objConnection, string sProcName, SqlParameter[] objaPrams)
    {
        SqlCommand objCommand = new SqlCommand(sProcName, objConnection);
        objCommand.CommandType = CommandType.StoredProcedure;

        // add proc parameters
        if (objaPrams != null)
        {
            foreach (SqlParameter objParameter in objaPrams)
                objCommand.Parameters.Add(objParameter);
        }
        // return objaPrams
        objCommand.Parameters.Add(
            new SqlParameter("ReturnValue", SqlDbType.Int, 4,
            ParameterDirection.ReturnValue, false, 0, 0,
            string.Empty, DataRowVersion.Default, null));

        return objCommand;
    }
    public SqlParameter MakeInParameter(string sParamName, SqlDbType objDbType, int iSize, object objValue)
    {
        return MakeParameter(sParamName, objDbType, iSize, ParameterDirection.Input, objValue);
    }

    /// <summary>
    /// Make input param.
    /// </summary>
    /// <param name="sParamName">Name of param.</param>
    /// <param name="objDbType">Param type.</param>
    /// <param name="iSize">Param size.</param>
    /// <returns>New parameter.</returns>
    public SqlParameter MakeOutParameter(string sParamName, SqlDbType objDbType, int iSize)
    {
        return MakeParameter(sParamName, objDbType, iSize, ParameterDirection.Output, null);
    }
 
    /// <summary>
    /// Make stored procedure param.
    /// </summary>
    /// <param name="sParamName">Name of param.</param>
    /// <param name="objDbType">Param type.</param>
    /// <param name="iSize">Param size.</param>
    /// <param name="objDirection">Parm direction.</param>
    /// <param name="objValue">Param value.</param>
    /// <returns>New parameter.</returns>
    public SqlParameter MakeParameter(string sParamName, SqlDbType objDbType, Int32 iSize, ParameterDirection objDirection, object objValue)
    {
        SqlParameter objParameter;
 
        if (iSize > 0)
            objParameter = new SqlParameter(sParamName, objDbType, iSize);
        else
            objParameter = new SqlParameter(sParamName, objDbType);
 
        objParameter.Direction = objDirection;
        if (!(objDirection == ParameterDirection.Output && objValue == null))
            objParameter.Value = objValue;

        return objParameter;
    }
}

Create another class called JobServices.cs

and write the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
 
/// <summary>
/// Summary description for JobServices
/// </summary>
public class JobServices
{
    SqlParameter[] objParam;
    clsDatabase objDatabase;
    DataSet objDataSet;
       public JobServices()
       {
              //
              // TODO: Add constructor logic here
              //
       }
    public DataSet GetAllJobs(int PageNumber, int PageSize)
    {
        objDatabase = new clsDatabase();
        DataSet ds = new DataSet();
        SqlParameter[] objParameter = new SqlParameter[2];
        objParameter[0] = objDatabase.MakeInParameter("@PageNumber", SqlDbType.Int, 4, PageNumber);
        objParameter[1] = objDatabase.MakeInParameter("@PageSize", SqlDbType.Int, 4, PageSize);
        objDatabase.RunProcedure("DisplayDataPaging", objParameter, out ds);
        return ds;
    }
}

Complete design of Default.aspx is

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeFile="Default.aspx.cs" Inherits="_Default" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
    <link href="StyleSheet.css" rel="stylesheet" type="text/css" />
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <h2>
       Implementation of SQL paging in ASP.Net using SQL Server, ASP.Net and C#
    </h2>

    <asp:ScriptManager ID="ScriptManagerID1" runat="server">
    </asp:ScriptManager>
    <asp:UpdatePanel ID="updatePanelDefaultPage" runat="server" ChildrenAsTriggers="true">
        <ContentTemplate>
            <div class="questionDetail">
                <div class="noDataFound">
                    <asp:Label ID="LabelMessage" runat="server" Visible="false" Text="No Records Found!"
                        ForeColor="Red"></asp:Label>
                </div>
                <ul>
                    <asp:Repeater ID="repeaterLatestJobs" runat="server" OnItemDataBound="repeaterLatestJobs_ItemDataBound">
                        <ItemTemplate>
                            <li>
                                <div style="width: 600px; float: left;">
                                    <a class="LinkNormalGray" href='<%# DataBinder.Eval(Container.DataItem,"FullName")%>'>
                                        <%#Server.HtmlDecode(DataBinder.Eval(Container.DataItem,"FullName").ToString())%></a>
                                    | <span><a class="LinkNormalBlue" href='<%# DataBinder.Eval(Container.DataItem,"Address1")%>'>
                                        <%# DataBinder.Eval(Container.DataItem, "Address1")%>
                                    </a></span>| <span><a href='<%# DataBinder.Eval(Container.DataItem,"CITY")%>' class="LinkRed">
                                        <%# DataBinder.Eval(Container.DataItem, "CITY")%></a></span> | <span><a href='<%# DataBinder.Eval(Container.DataItem,"Country")%>'
                                            class="LinkNormalBlue">
                                            <%# DataBinder.Eval(Container.DataItem, "Country")%></a> </span>| <span class="LinkNormalGrayLight">
                                                <%#DateTime.Parse(Eval("DOB").ToString()).ToString("MMM dd, yyyy")%></span>
                                </div>
                            </li>
                        </ItemTemplate>
                    </asp:Repeater>
                </ul>
            </div>
            <div id="PagingRow" runat="server">
                <ul class="paging">
                    <li>
                        <asp:LinkButton ID="LinkButtonFirst" runat="server" Enabled="False" Font-Bold="True"
                            CssClass="pagingSpritIcons first" OnClick="LinkButtonFirst_Click"></asp:LinkButton>
                    </li>
                    <li>
                        <asp:LinkButton ID="LinkButtonPrevious" runat="server" Enabled="False" Font-Bold="True"
                            CssClass="pagingSpritIcons previous" OnClick="LinkButtonPrevious_Click"></asp:LinkButton>
                    </li>
                    <li>
                        <asp:LinkButton ID="LinkButtonNext" runat="server" Enabled="False" Font-Bold="True"
                            CssClass="pagingSpritIcons next" EnableViewState="true" OnClick="LinkButtonNext_Click"></asp:LinkButton>
                    </li>
                    <li>
                        <asp:LinkButton ID="LinkButtonLast" runat="server" Enabled="False" Font-Bold="True"
                            CssClass="pagingSpritIcons last" OnClick="LinkButtonLast_Click"></asp:LinkButton>
                    </li>
                </ul>
            </div>
        </ContentTemplate>
    </asp:UpdatePanel>
</asp:Content>

Step 6

The complete code of the Default.aspx.cs is:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.HtmlControls;
using System.Text;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{
    int PageSize = int.Parse(ConfigurationManager.AppSettings["RecentJobsSize"].ToString());

    protected void Page_Load(object sender, EventArgs e)
    {
        LabelMessage.Visible = false;
        if (PagingRow.Visible == false)
        {
            PagingRow.Visible = true;
        }
        if (!IsPostBack)
        {
            funBindGrid();

        }
    }

    public int CurrentPage
    {
        get
        {
            // look for current page in ViewState
            object current = this.ViewState["CurrentPage"];
            if (current == null)
                return 1; // default page index of 0
            else
                return (int)current;
        }
        set
        {
            this.ViewState["CurrentPage"] = value;
        }
    }

    protected void LinkButtonPrevious_Click(object sender, EventArgs e)
    {
        CurrentPage -= 1;
        funBindGrid();

    }

    protected void LinkButtonNext_Click(object sender, EventArgs e)
    {
        CurrentPage += 1;
        funBindGrid();
    }

    protected void LinkButtonFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;
        funBindGrid();
    }

    protected void LinkButtonLast_Click(object sender, EventArgs e)
    {
        CurrentPage = int.Parse(ViewState["TotalPages"].ToString());
        funBindGrid();
    }

    protected void LinkButton1_Click(object sender, EventArgs e)
    {
        LinkButton lnkbtn = (LinkButton)sender;
        CurrentPage = (int.Parse(lnkbtn.Text));
        funBindGrid();
    }

    private void ShowPagingLinks()
    {
        if (CurrentPage == int.Parse(ViewState["TotalPages"].ToString()))
        {

            LinkButtonNext.Enabled = false;
            LinkButtonLast.Enabled = false;
        }
        else
        {
           LinkButtonNext.Enabled = true;
            LinkButtonLast.Enabled = true;

        }

        if (CurrentPage == 1)
        {
            LinkButtonPrevious.Enabled = false;
            LinkButtonFirst.Enabled = false;
        }
        else
        {

            LinkButtonPrevious.Enabled = true;
            LinkButtonFirst.Enabled = true;
        }
    }

    private void funBindGrid()
    {
        try
        {
            JobServices objAllJobs = new JobServices();
            DataSet objDataSet = new DataSet();
            objDataSet = objAllJobs.GetAllJobs(CurrentPage, PageSize);

            if (objDataSet.Tables[1].Rows.Count > 0)
            {
                DataView jobs = objDataSet.Tables[1].DefaultView;
                repeaterLatestJobs.DataSource = jobs;
                repeaterLatestJobs.DataBind();
                int totalRecords = int.Parse(objDataSet.Tables[2].Rows[0]["TotalRecords"].ToString());

                if (totalRecords % PageSize > 0)
                {
                    PagingRow.Visible = true;
                    ViewState["TotalPages"] = objDataSet.Tables[2].Rows[0]["TotalPages"].ToString();
                    ShowPagingLinks();
                }
                else
                {
                    PagingRow.Visible = false;
                    ViewState["TotalPages"] = (totalRecords % PageSize) - 1;
                }
            }
            else
            {
                ShowError();
            }

           
        }
        catch (Exception objExecption)
        {
         
        }
    }
 
    private void ShowError()
    {
        LabelMessage.Visible = true;
        repeaterLatestJobs.DataSource = null;
        repeaterLatestJobs.DataBind();
        PagingRow.Visible = false;
    }

    protected void ButtonSort_Click(object sender, EventArgs e)
    {
        funBindGrid();
    }

    protected void repeaterLatestJobs_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {

    }

}

Step 7

Compile and run the application. By default, records from 1 to 10 are displayed and the result looks like this:

Now click on next, records from 11 to 20 are displayed and so on.

 

It means only 10 records comes from the database as a result of the front end and so it is very quick in terms of loading the page for the next set of records.

It is very useful and fast to access the data from the huge database records.

Happy coding.