Efficient Paging Using Stored Procedure In SQL Server

Vishal Jadav
8y
23.7k
0
5
25
Blog

DynamicPaging.rar

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.

CREATE PROCEDURE[dbo].[GETDATA_WITHPAGING]
@STARTROWINDEX INT, @MAXIMUMROWS INT, @GETTOTAL BIT, @TOTALRECORDS INT OUTPUT
AS
BEGIN
DECLARE @FIRSTID BIGINT
set @TOTALRECORDS = 0
IF(@GETTOTAL = 1)
BEGIN
SELECT @TOTALRECORDS = COUNT(ITEMID) FROM MenuMaster
END
SET @STARTROWINDEX = (CASE WHEN @STARTROWINDEX = 0 THEN 1 ELSE @STARTROWINDEX END)
SET ROWCOUNT @STARTROWINDEX
SELECT @FIRSTID = ITEMID FROM MENUMASTER ORDER BY ITEMID ASC
SET ROWCOUNT @MAXIMUMROWS
SELECT ITEMID, ITEMNAME FROM MENUMASTER WHERE ITEMID >= @FIRSTID ORDER BY ITEMID ASC
SET ROWCOUNT 0
RETURN
END

The following is the stored procedure 2.

CREATE PROCEDURE GET_DATAWITHPAGING2

@STARTROWINDEX INT, @MAXIMUMROWS INT, @GETTOTAL BIT, @TOTALRECORDS INT OUTPUT

AS

BEGIN

SET NOCOUNT ON;

SET @TOTALRECORDS = 0

IF(@GETTOTAL = 1)

BEGIN

SELECT @TOTALRECORDS = COUNT(ITEMID) FROM MenuMaster

END

SELECT ITEMID, ITEMNAME FROM MENUMASTER ORDER BY ITEMID ASC OFFSET @STARTROWINDEX ROWS FETCH NEXT @MAXIMUMROWS ROWS ONLY

RETURN

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.

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style>
.link_enabled {
display: block;
float: left;
margin: 2px;
padding: 2px;
background-color: gray;
color: #fff;
}
.link_disabled {
display: block;
float: left;
margin: 2px;
padding: 2px;
background-color: red;
color: #fff;
}
</style>
</head>
<body>
<form id="form1" runat="server"> PageSize
<asp:DropDownList ID="ddPageSize" runat="server">
<asp:ListItem Value="10">10</asp:ListItem>
<asp:ListItem Value="20">20</asp:ListItem>
<asp:ListItem Value="30">30</asp:ListItem>
<asp:ListItem Value="40">40</asp:ListItem>
<asp:ListItem Value="50">50</asp:ListItem>
</asp:DropDownList>
<asp:Button ID="BtnSearch" runat="server" Text="Display" OnClick="BtnSearch_Click" /><br />
<asp:Repeater ID="RpData" runat="server">
<HeaderTemplate>
<table style="border: 1px solid red;" border="0">
<tr>
<td style="width: 200px; border-right: 1px solid gray;"> <label>ID</label></td>
<td style="width: 250px;"> <label>Name</label></td>
</tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table style="border: 1px solid red;" border="0">
<tr>
<td style="width: 200px; border-right: 1px solid gray;"> <label><%# Eval("ItemID") %></label></td>
<td style="width: 250px;"> <label><%# Eval("ItemName") %></label></td>
</tr>
</table>
</ItemTemplate>
</asp:Repeater>
<asp:Repeater ID="RpPaging" runat="server">
<ItemTemplate>
<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>
</ItemTemplate>
</asp:Repeater>
</form>
</body>
</html>

It's easy to understand. Let's move to the C# code part.

Calling Stored procedure

public class DynamicPagingVJ
{
public int TotalRecords = 0;
string CON = @ "Data Source=.\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True";
public DataTable GetData(int PageIndex, int PageSize, bool GetTotal)
{
DataTable Dset = new DataTable();
using(SqlConnection Con = new SqlConnection(CON)) {
SqlCommand Cmd = new SqlCommand("GETDATA_WITHPAGING", Con);
Cmd.Parameters.AddRange(new SqlParameter[] {
new SqlParameter("@STARTROWINDEX", PageIndex), new SqlParameter("@MAXIMUMROWS", PageSize), new SqlParameter("@TOTALRECORDS", SqlDbType.Int),
new SqlParameter("GETTOTAL", (GetTotal ? 1 : 0))
});
Cmd.Parameters["@TOTALRECORDS"].Direction = ParameterDirection.Output;
Cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter Da = new SqlDataAdapter(Cmd);
Da.Fill(Dset);
TotalRecords = int.Parse(Cmd.Parameters["@TOTALRECORDS"].Value.ToString());
}
return Dset;
}
}

The class, above, will call the stored procedure and return the TotalRecords.

Let's integrate it in the webpage.

Variable Declaration

DynamicPagingVJ pa;

Page Load

if (!IsPostBack) {
PageSize = int.Parse(ddPageSize.SelectedValue);
}

Private Properties

int TotalRecords
{
get {
if (ViewState["TR"] == null) ViewState["TR"] = "0";
return int.Parse(ViewState["TR"].ToString());
}
set {
ViewState["TR"] = value;
}
}
int CurrentRecordID {
get {
if (ViewState["GPID"] == null) ViewState["GPID"] = "0";
return int.Parse(ViewState["GPID"].ToString());
}
set {
ViewState["GPID"] = value;
}
}
int PageSize {
get {
if (ViewState["PS"] == null) ViewState["PS"] = "10";
return int.Parse(ViewState["PS"].ToString());
}
set {
ViewState["PS"] = value;
}
}

No big deal! Names explain the function themselves.

Bind Paging

double PgCount = Convert.ToDouble(Total) / Convert.ToDouble(PageSize);
int TotalPages = (int) Math.Ceiling(PgCount);
List < ListItem > Li = new List < ListItem > ();
if (PgCount > 0)
for (int i = 0; i < TotalPages; i++)
{
Li.Add(new ListItem((i + 1).ToString(), i.ToString(), i != CurrentRecordID));
}
RpPaging.DataSource = Li;
RpPaging.DataBind();

Button Click Event

pa = new DynamicPagingVJ();
PageSize = int.Parse(ddPageSize.SelectedValue);
CurrentRecordID = 0;
RpData.DataSource = pa.GetData(1, PageSize, true);
RpData.DataBind();
TotalRecords = pa.TotalRecords;
BindPaging(pa.TotalRecords);

Page_Changing Event

LinkButton lnk = sender as LinkButton;
CurrentRecordID = int.Parse(lnk.CommandArgument);
pa = new DynamicPagingVJ();
RpData.DataSource = pa.GetData((int.Parse(lnk.CommandArgument) * PageSize), PageSize, false);
RpData.DataBind();
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.