Email: Password:       Forgot Password 
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.

SQL Paging Implementation in ASP.NET

Viewed:  1376 
Posted On:  29/01/2015 23:04:53 
What is SQL Paging? How we can implement SQL Paging? What is the advantage of using SQL Paging? 

In this article I am going to explain how we can how we can implement SQL Paging in c#. Question is why we need SQL Paging when we have already built in paging functionality in Grid View. Some time it's not a good habit to load all data over the network, just transfer only required data to improve performance so that we have SQL paging. Pass only Page Index and Page Size to load required data.

Below is my Data Table structure from which I am fetching data:


Image 1.

Data In My Table:


Image 2.

To achieve this I created a Stored Procedure:


Image 3.

My Stored Procedure is:

ALTER PROCEDURE [dbo].[GetStudentData]
      @PageIndex INT = 1,
      @PageSize INT = 10,
      @RecordCount INT OUTPUT
         ORDER BY StudentID ASC
                     )AS RowNumber
                     ,Class,EnrollYear,City  INTO #Results FROM Student
      SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
      SELECT @RecordCount = COUNT(*) FROM #Results
      DROP TABLE #Results

Now aspx is:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SQLPagingInGridView.aspx.cs" Inherits="SQLPaging.SQLPagingInGridView" %>
<!DOCTYPE html>
<html xmlns="">
<head runat="server">
    <title>SQL Paging Implementation in ASP.NET</title>
    <form id="form1" runat="server">
            <table style="border: solid 15px blue; width: 100%; vertical-align: central;">
                    <td style="padding-left: 50px; padding-top: 20px; padding-bottom: 20px; background-color: skyblue; font-size: 20pt; color: orangered;">SQL
Paging Implementation in ASP.NET C#
                    <td style="text-align: left; padding-left: 50px; border: solid 1px red;">
                        <asp:GridView ID="GridViewStudent" runat="server" AutoGenerateColumns="False" Width="70%"
                            BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4" GridLines="Both">
                                <asp:BoundField DataField="Name" HeaderText="Student Name" />
                                <asp:BoundField DataField="Class" HeaderText="Class" />
                                <asp:BoundField DataField="EnrollYear" HeaderText="Enroll Year" />
                                <asp:BoundField DataField="City" HeaderText="City" />
                            <FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
                            <HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
                            <PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
                            <RowStyle BackColor="White" ForeColor="#003399" />
                            <SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
                            <SortedAscendingCellStyle BackColor="#EDF6F6" />
                            <SortedAscendingHeaderStyle BackColor="#0D4AC4" />
                            <SortedDescendingCellStyle BackColor="#D6DFDF" />
                            <SortedDescendingHeaderStyle BackColor="#002876" />
                        <br />
                        <asp:Repeater ID="rptPager" runat="server">
                                <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
                                    Enabled='<%# Eval("Enabled") %>' OnClick="lnkbtn_PageIndexChanged"></asp:LinkButton>

My ASPX.CS code is:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace SQLPaging
    public partial class SQLPagingInGridView : System.Web.UI.Page
        int PageSize = 10;
        protected void Page_Load(object sender, EventArgs e)
            if (!Page.IsPostBack)
                GetStudentData(1, PageSize);
        SqlDataAdapter da;
        DataSet ds = new DataSet();
        public void GetStudentData(int PageIndex, int PageSize)
            using (SqlConnection con = new SqlConnection(@"Data Source=MYPC\SqlServer2k8; Initial Catalog=SchoolManagement; Integrated Security=true;"))
                using (SqlCommand cmd = new SqlCommand("[GetStudentData]", con))
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@PageIndex", PageIndex);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
                    cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
                    da = new SqlDataAdapter(cmd);
                    if (ds.Tables[0].Rows.Count > 0)
                        GridViewStudent.DataSource = ds.Tables[0];
                    int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
                    this.PopulatePager(recordCount, PageIndex);
        private void PopulatePager(int recordCount, int currentPage)
            double dblPageCount = (double)((decimal)recordCount / (PageSize));
            int pageCount = (int)Math.Ceiling(dblPageCount);
            List<ListItem> pages = new List<ListItem>();
            if (pageCount > 0)
                pages.Add(new ListItem("FIRST >> ", "1", currentPage > 1));
                for (int i = 1; i <= pageCount; i++)
                    pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
                pages.Add(new ListItem(" << LAST", pageCount.ToString(), currentPage < pageCount));
            rptPager.DataSource = pages;
        protected void lnkbtn_PageIndexChanged(object sender, EventArgs e)
            int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
            GetStudentData(pageIndex, PageSize);

Now Run the Application:


Image 4.


Image 5.


Image 6.

         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us