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
  19. SELECT * FROM #Results
  20. WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
  22. SELECT @RecordCount = COUNT(*) FROM #Results
  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;
  10. namespace SQLPaging
  11. {
  12. public partial class SQLPagingInGridView : System.Web.UI.Page
  13. {
  14. int PageSize = 10;
  16. protected void Page_Load(object sender, EventArgs e)
  17. {
  18. if (!Page.IsPostBack)
  19. GetStudentData(1, PageSize);
  20. }
  22. SqlDataAdapter da;
  23. DataSet ds = new DataSet();
  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();
  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. }
  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. }
  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.