Post
Ask Question

Implementing SQL Paging in ASP.Net

The question is, why do we need SQL Paging when we already have built-in paging functionality in Grid View. Sometimes it's not a good habit to load all the data over the network, but instead to transfer only the data required, to improve performance. For that we have SQL paging. Pass only a Page Index and Page Size to load the required data.

The following is my data table structure from which I am fetching the data:

table design
                                       Image 1.

Data in my table:

My Table
                                                                          Image 2.

To do this, I created the following Stored Procedure:

Stored Procedure
                                                                          Image 3.

My Stored Procedure is:

  1. ALTER PROCEDURE [dbo].[GetStudentData]  
  2. (         
  3.       @PageIndex INT = 1,  
  4.       @PageSize INT = 10,  
  5.       @RecordCount INT OUTPUT  
  6. )  
  7. AS  
  8. BEGIN  
  9.       SET NOCOUNT ON;  
  10.       SELECT ROW_NUMBER() OVER  
  11.       (  
  12.          ORDER BY StudentID ASC  
  13.           )AS RowNumber  
  14.           ,StudentID  
  15.           ,Name  
  16.           ,Email  
  17.           ,Class,EnrollYear,City  INTO #Results FROM Student  
  18.             
  19.       SELECT * FROM #Results  
  20.       WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1  
  21.       
  22.       SELECT @RecordCount = COUNT(*) FROM #Results  
  23.         
  24.       DROP TABLE #Results  
  25. END  
Now the aspx is: 
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SQLPagingInGridView.aspx.cs" Inherits="SQLPaging.SQLPagingInGridView" %>  
  2. <!DOCTYPE html>  
  3. <html xmlns="http://www.w3.org/1999/xhtml">  
  4. <head runat="server">  
  5.     <title>SQL Paging Implementation in ASP.NET</title>  
  6. </head>  
  7. <body>  
  8.     <form id="form1" runat="server">  
  9.         <div>  
  10.             <table style="border: solid 15px blue; width: 100%; vertical-align: central;">  
  11.                 <tr>  
  12.                     <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#  
  13.                     </td>  
  14.                 </tr>  
  15.                 <tr>  
  16.                     <td style="text-align: left; padding-left: 50px; border: solid 1px red;">  
  17.                         <asp:GridView ID="GridViewStudent" runat="server" AutoGenerateColumns="False" Width="70%"  
  18.                             BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4" GridLines="Both">  
  19.                             <Columns>  
  20.                                 <asp:BoundField DataField="Name" HeaderText="Student Name" />  
  21.                                 <asp:BoundField DataField="Class" HeaderText="Class" />  
  22.                                 <asp:BoundField DataField="EnrollYear" HeaderText="Enroll Year" />  
  23.                                 <asp:BoundField DataField="City" HeaderText="City" />  
  24.                             </Columns>  
  25.                             <FooterStyle BackColor="#99CCCC" ForeColor="#003399" />  
  26.                             <HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />  
  27.                             <PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />  
  28.                             <RowStyle BackColor="White" ForeColor="#003399" />  
  29.                             <SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />  
  30.                             <SortedAscendingCellStyle BackColor="#EDF6F6" />  
  31.                             <SortedAscendingHeaderStyle BackColor="#0D4AC4" />  
  32.                             <SortedDescendingCellStyle BackColor="#D6DFDF" />  
  33.                             <SortedDescendingHeaderStyle BackColor="#002876" />  
  34.                         </asp:GridView>  
  35.                         <br />  
  36.                         <asp:Repeater ID="rptPager" runat="server">  
  37.                             <ItemTemplate>  
  38.                                 <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'  
  39.                                     Enabled='<%# Eval("Enabled") %>' OnClick="lnkbtn_PageIndexChanged"></asp:LinkButton>  
  40.                             </ItemTemplate>  
  41.                         </asp:Repeater>  
  42.                     </td>  
  43.                 </tr>  
  44.             </table>  
  45.         </div>  
  46.     </form>  
  47. </body>  
  48. </html>  
My ASPX.CS code is:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4. using System.Data.SqlClient;  
  5. using System.Linq;  
  6. using System.Web;  
  7. using System.Web.UI;  
  8. using System.Web.UI.WebControls;  
  9.   
  10. namespace SQLPaging  
  11. {  
  12.     public partial class SQLPagingInGridView : System.Web.UI.Page  
  13.     {  
  14.         int PageSize = 10;  
  15.   
  16.         protected void Page_Load(object sender, EventArgs e)  
  17.         {  
  18.             if (!Page.IsPostBack)  
  19.                 GetStudentData(1, PageSize);  
  20.         }  
  21.   
  22.         SqlDataAdapter da;  
  23.         DataSet ds = new DataSet();  
  24.   
  25.         public void GetStudentData(int PageIndex, int PageSize)  
  26.         {  
  27.             using (SqlConnection con = new SqlConnection(@"Data Source=MYPC\SqlServer2k8; Initial Catalog=SchoolManagement; Integrated Security=true;"))  
  28.             {  
  29.                 using (SqlCommand cmd = new SqlCommand("[GetStudentData]", con))  
  30.                 {  
  31.                     cmd.CommandType = CommandType.StoredProcedure;  
  32.                     cmd.Parameters.AddWithValue("@PageIndex", PageIndex);  
  33.                     cmd.Parameters.AddWithValue("@PageSize", PageSize);  
  34.                     cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);  
  35.                     cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;  
  36.                     da = new SqlDataAdapter(cmd);  
  37.                     da.Fill(ds);  
  38.                     con.Open();  
  39.                     cmd.ExecuteNonQuery();  
  40.   
  41.                     if (ds.Tables[0].Rows.Count > 0)  
  42.                     {  
  43.                         GridViewStudent.DataSource = ds.Tables[0];  
  44.                         GridViewStudent.DataBind();  
  45.                     }  
  46.                     int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);  
  47.                     this.PopulatePager(recordCount, PageIndex);  
  48.                 }  
  49.             }  
  50.         }  
  51.   
  52.         private void PopulatePager(int recordCount, int currentPage)  
  53.         {  
  54.             double dblPageCount = (double)((decimal)recordCount / (PageSize));  
  55.             int pageCount = (int)Math.Ceiling(dblPageCount);  
  56.             List<ListItem> pages = new List<ListItem>();  
  57.             if (pageCount > 0)  
  58.             {  
  59.                 pages.Add(new ListItem("FIRST >> ", "1", currentPage > 1));  
  60.                 for (int i = 1; i <= pageCount; i++)  
  61.                 {  
  62.                     pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));  
  63.                 }  
  64.                 pages.Add(new ListItem(" << LAST", pageCount.ToString(), currentPage < pageCount));  
  65.             }  
  66.             rptPager.DataSource = pages;  
  67.             rptPager.DataBind();  
  68.         }  
  69.   
  70.         protected void lnkbtn_PageIndexChanged(object sender, EventArgs e)  
  71.         {  
  72.             int pageIndex = int.Parse((sender as LinkButton).CommandArgument);  
  73.             GetStudentData(pageIndex, PageSize);  
  74.         }  
  75.     }  
  76. }  
Now run the application.

sql paging
                                                                        Image 4.

Application
                                                                        Image 5.

first last
                                                                     Image 6. 


Similar Articles