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

SQL Paging Implementation in ASP.NET

Article:
Viewed:  1320 
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 asp.net 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:

1.png

Image 1.

Data In My Table:

2.png

Image 2.

To achieve this I created a Stored Procedure:

3.png

Image 3.

My Stored Procedure is:

ALTER PROCEDURE [dbo].[GetStudentData]
(      
      @PageIndex INT = 1,
      @PageSize INT = 10,
      @RecordCount INT OUTPUT
)
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
         ORDER BY StudentID ASC
                     )AS RowNumber
                     ,StudentID
                     ,Name
                     ,Email
                     ,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
END

Now aspx is:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SQLPagingInGridView.aspx.cs" Inherits="SQLPaging.SQLPagingInGridView" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>SQL Paging Implementation in ASP.NET</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <table style="border: solid 15px blue; width: 100%; vertical-align: central;">
                <tr>
                    <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>
                </tr>
                <tr>
                    <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">
                            <Columns>
                                <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" />
                            </Columns>
                            <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" />
                        </asp:GridView>
                        <br />
                        <asp:Repeater ID="rptPager" runat="server">
                            <ItemTemplate>
                                <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
                                    Enabled='<%# Eval("Enabled") %>' OnClick="lnkbtn_PageIndexChanged"></asp:LinkButton>
                            </ItemTemplate>
                        </asp:Repeater>
                    </td>
                </tr>
            </table>
        </div>
    </form>
</body>
</html>


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);
                    da.Fill(ds);
                    con.Open();
                    cmd.ExecuteNonQuery();
 
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        GridViewStudent.DataSource = ds.Tables[0];
                        GridViewStudent.DataBind();
                    }
                    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;
            rptPager.DataBind();
        }
 
        protected void lnkbtn_PageIndexChanged(object sender, EventArgs e)
        {
            int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
            GetStudentData(pageIndex, PageSize);
        }
    }
}

Now Run the Application:

4.png

Image 4.

5.png

Image 5.

6.png

Image 6.

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