Post
Ask Question

Efficient Paging Using Stored Procedure In SQL Server

It's a really big deal how we retrieve data from a database. In some cases, we need to deliver the result in a very efficient way with less bandwidth. I was developing a recharge portal where clients are using reports constantly with lots of data. In that case, my server went down.I had to find a better solution with the same result, in a very efficient manner. Below are the simplest methods of getting data from stored procedure with efficient paging.

I am assuming you have table MENUMASTER with ItemID and ItemName columns.

The following is the stored procedure 1.

  1. CREATE PROCEDURE[dbo].[GETDATA_WITHPAGING]  
  2. @STARTROWINDEX INT, @MAXIMUMROWS INT, @GETTOTAL BIT, @TOTALRECORDS INT OUTPUT  
  3. AS  
  4. BEGIN  
  5. DECLARE @FIRSTID BIGINT  
  6. set @TOTALRECORDS = 0  
  7. IF(@GETTOTAL = 1)  
  8. BEGIN  
  9. SELECT @TOTALRECORDS = COUNT(ITEMID) FROM MenuMaster  
  10. END  
  11. SET @STARTROWINDEX = (CASE WHEN @STARTROWINDEX = 0 THEN 1 ELSE @STARTROWINDEX END)  
  12. SET ROWCOUNT @STARTROWINDEX  
  13. SELECT @FIRSTID = ITEMID FROM MENUMASTER ORDER BY ITEMID ASC  
  14. SET ROWCOUNT @MAXIMUMROWS  
  15. SELECT ITEMID, ITEMNAME FROM MENUMASTER WHERE ITEMID >= @FIRSTID ORDER BY ITEMID ASC  
  16. SET ROWCOUNT 0  
  17. RETURN  
  18. END  
The following is the stored procedure 2.
  1. CREATE PROCEDURE GET_DATAWITHPAGING2  
  2. @STARTROWINDEX INT, @MAXIMUMROWS INT, @GETTOTAL BIT, @TOTALRECORDS INT OUTPUT  
  3. AS  
  4. BEGIN  
  5. SET NOCOUNT ON;  
  6. SET @TOTALRECORDS = 0  
  7. IF(@GETTOTAL = 1)  
  8. BEGIN  
  9. SELECT @TOTALRECORDS = COUNT(ITEMID) FROM MenuMaster  
  10. END  
  11. SELECT ITEMID, ITEMNAME FROM MENUMASTER ORDER BY ITEMID ASC OFFSET @STARTROWINDEX ROWS FETCH NEXT @MAXIMUMROWS ROWS ONLY  
  12. RETURN  
  13. END  
Parameter Explaination
  • @STARTROWINDEX - Index of row from where the data will be fetched.
  • @MAXIMUMROWS - Number of records to be retrieved.
  • @GETTOTAL - Whether count total or not. Mostly I prefer it to calling on search  / the first time we bind paging.
  • @TOTALRECORDS OUTPUT - Gets total records

Code Explanation

We know MS SQL Server does not support dynamic value with TOP keyword except dynamic SQL. But, there is another method which is parallel to it, that is, SET ROWCOUNT. By using SET ROWCOUNT, we can limit the number of rows to be returned from the DML. In the same way, SET ROWCOUNT @STARTROWINDEX will get the very first id based on @STARTROWINDEX parameter. Next thing is getting maximum records, provided in the parameters. 

SET ROWCOUNT @MAXIMUMROWS

The above line will limit the SQL statement to get only required records.

MS SQL Server has introduced OFFSET and ROWS FETCH NEXT ------ ROWS ONLY.

It really is interesting. OFFSET will start fetching records from the given parameter and ROWS FETCH NEXT will fetch the number of rows from the given parameter.

These functionalities make the dynamic paging really really easy. Now, we are set up with the Stored Procedure. ' time to call it. Given below is the part of my ASPX page.

  1. <!DOCTYPE html>  
  2. <html xmlns="http://www.w3.org/1999/xhtml">  
  3.   
  4. <head runat="server">  
  5.     <title></title>  
  6.     <style>  
  7.         .link_enabled {  
  8.             display: block;  
  9.             float: left;  
  10.             margin: 2px;  
  11.             padding: 2px;  
  12.             background-color: gray;  
  13.             color: #fff;  
  14.         }  
  15.           
  16.         .link_disabled {  
  17.             display: block;  
  18.             float: left;  
  19.             margin: 2px;  
  20.             padding: 2px;  
  21.             background-color: red;  
  22.             color: #fff;  
  23.         }  
  24.     </style>  
  25. </head>  
  26.   
  27. <body>  
  28.     <form id="form1" runat="server"> PageSize  
  29.         <asp:DropDownList ID="ddPageSize" runat="server">  
  30.             <asp:ListItem Value="10">10</asp:ListItem>  
  31.             <asp:ListItem Value="20">20</asp:ListItem>  
  32.             <asp:ListItem Value="30">30</asp:ListItem>  
  33.             <asp:ListItem Value="40">40</asp:ListItem>  
  34.             <asp:ListItem Value="50">50</asp:ListItem>  
  35.         </asp:DropDownList>  
  36.         <asp:Button ID="BtnSearch" runat="server" Text="Display" OnClick="BtnSearch_Click" /><br />  
  37.         <asp:Repeater ID="RpData" runat="server">  
  38.             <HeaderTemplate>  
  39.                 <table style="border: 1px solid red;" border="0">  
  40.                     <tr>  
  41.                         <td style="width: 200px; border-right: 1px solid gray;"> <label>ID</label></td>  
  42.                         <td style="width: 250px;"> <label>Name</label></td>  
  43.                     </tr>  
  44.                 </table>  
  45.             </HeaderTemplate>  
  46.             <ItemTemplate>  
  47.                 <table style="border: 1px solid red;" border="0">  
  48.                     <tr>  
  49.                         <td style="width: 200px; border-right: 1px solid gray;"> <label><%# Eval("ItemID") %></label></td>  
  50.                         <td style="width: 250px;"> <label><%# Eval("ItemName") %></label></td>  
  51.                     </tr>  
  52.                 </table>  
  53.             </ItemTemplate>  
  54.         </asp:Repeater>  
  55.         <asp:Repeater ID="RpPaging" runat="server">  
  56.             <ItemTemplate>  
  57.                 <asp:LinkButton ID="Lnk" Text='<%# Eval("Text") %>' CommandArgument='<%# Eval("Value") %>' CssClass='<%# Convert.ToBoolean(Eval("Enabled")) ? "link_enabled" : "link_disabled" %>' OnClientClick='<%# Convert.ToBoolean(Eval("Enabled")) ? "" : "return false;" %>' runat="server" OnClick="Page_Changing">LinkButton</asp:LinkButton>  
  58.             </ItemTemplate>  
  59.         </asp:Repeater>  
  60.     </form>  
  61. </body>  
  62.   
  63. </html>  
It's easy to understand. Let's move to the C# code part.

Calling Stored procedure

  1. public class DynamicPagingVJ   
  2. {  
  3.     public int TotalRecords = 0;  
  4.     string CON = @ "Data Source=.\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True";  
  5.     public DataTable GetData(int PageIndex, int PageSize, bool GetTotal)   
  6.     {  
  7.         DataTable Dset = new DataTable();  
  8.         using(SqlConnection Con = new SqlConnection(CON)) {  
  9.             SqlCommand Cmd = new SqlCommand("GETDATA_WITHPAGING", Con);  
  10.             Cmd.Parameters.AddRange(new SqlParameter[] {  
  11.                 new SqlParameter("@STARTROWINDEX", PageIndex), new SqlParameter("@MAXIMUMROWS", PageSize), new SqlParameter("@TOTALRECORDS", SqlDbType.Int),  
  12.                     new SqlParameter("GETTOTAL", (GetTotal ? 1 : 0))  
  13.             });  
  14.             Cmd.Parameters["@TOTALRECORDS"].Direction = ParameterDirection.Output;  
  15.             Cmd.CommandType = CommandType.StoredProcedure;  
  16.             SqlDataAdapter Da = new SqlDataAdapter(Cmd);  
  17.             Da.Fill(Dset);  
  18.             TotalRecords = int.Parse(Cmd.Parameters["@TOTALRECORDS"].Value.ToString());  
  19.         }  
  20.         return Dset;  
  21.     }  
  22. }  
The class, above, will call the stored procedure and return the TotalRecords. 

Let's integrate it in the webpage.

Variable Declaration

  1. DynamicPagingVJ pa;   

Page Load

  1. if (!IsPostBack) {  
  2.     PageSize = int.Parse(ddPageSize.SelectedValue);  
  3. }  
Private Properties
  1. int TotalRecords  
  2. {  
  3.     get {  
  4.         if (ViewState["TR"] == null) ViewState["TR"] = "0";  
  5.         return int.Parse(ViewState["TR"].ToString());  
  6.     }  
  7.     set {  
  8.         ViewState["TR"] = value;  
  9.     }  
  10. }  
  11. int CurrentRecordID {  
  12.     get {  
  13.         if (ViewState["GPID"] == null) ViewState["GPID"] = "0";  
  14.         return int.Parse(ViewState["GPID"].ToString());  
  15.     }  
  16.     set {  
  17.         ViewState["GPID"] = value;  
  18.     }  
  19. }  
  20. int PageSize {  
  21.     get {  
  22.         if (ViewState["PS"] == null) ViewState["PS"] = "10";  
  23.         return int.Parse(ViewState["PS"].ToString());  
  24.     }  
  25.     set {  
  26.         ViewState["PS"] = value;  
  27.     }  
  28. }  
No big deal! Names explain the function themselves.  

Bind Paging

  1. double PgCount = Convert.ToDouble(Total) / Convert.ToDouble(PageSize);  
  2. int TotalPages = (int) Math.Ceiling(PgCount);  
  3. List < ListItem > Li = new List < ListItem > ();  
  4. if (PgCount > 0)  
  5.     for (int i = 0; i < TotalPages; i++)   
  6.     {  
  7.         Li.Add(new ListItem((i + 1).ToString(), i.ToString(), i != CurrentRecordID));  
  8.     }  
  9. RpPaging.DataSource = Li;  
  10. RpPaging.DataBind();  
Button Click Event
  1. pa = new DynamicPagingVJ();  
  2. PageSize = int.Parse(ddPageSize.SelectedValue);  
  3. CurrentRecordID = 0;  
  4. RpData.DataSource = pa.GetData(1, PageSize, true);  
  5. RpData.DataBind();  
  6. TotalRecords = pa.TotalRecords;  
  7. BindPaging(pa.TotalRecords);  
Page_Changing Event
  1. LinkButton lnk = sender as LinkButton;  
  2. CurrentRecordID = int.Parse(lnk.CommandArgument);  
  3. pa = new DynamicPagingVJ();  
  4. RpData.DataSource = pa.GetData((int.Parse(lnk.CommandArgument) * PageSize), PageSize, false);  
  5. RpData.DataBind();  
  6. BindPaging(TotalRecords);  
That's it. We are done with the coding part. This will reduce the unnecessary data and work really fast. I hope it helps someone.
Next Recommended Reading SET NOCOUNT ON And SET NOCOUNT OFF Using Stored Procedure In SQL Server