Wednesday, November 11, 2009

Efficient Paging in Asp.net

Already we know the concept of paging in Asp.net, it is easier to do and faster in development.
But with built in gridview paging we have one problem that is each and every page load the it retrives entire records from the database. so it is burden on the server to get entire records each and every time from the database.
Inbuilt paging good for upto 100 records but if we have more than 100 record it is not good to impliment. so we need to develop a custom pagination for that.
Using the custom pagination we will get only the records which were displayed on that page only. It is good to give the load to Database server than our asp.net application. That is we need to write a Query using rownum with which we will get only the record in between certain rownumber, which solves our problem.

Steps to Impliment custom paging:

1. Get the total number of records from the database and devide the number of records by using our page size and use ceiling function to get next integer which gives our number of pages.

Example:


public void Gv_Custompaging()
{
SqlCommand cmd_count = new SqlCommand("select count(*) from tablename", con);
con.Open();
int i = Convert.ToInt16(cmd_count.ExecuteScalar());
con.Close();
i = Convert.ToInt16(Math.Ceiling(Convert.ToDouble(i/3)));
for (int c = 1; c <= i; c++)
{
LinkButton lb = new LinkButton();
lb.ID = "link" + c;
lb.Text = c.ToString();
lb.Command += new CommandEventHandler(lb_Command);
PlaceHolder1.Controls.Add(lb);
PlaceHolder1.Controls.Add(new LiteralControl(" "));
}
 getBind("1");
}



Here the code gets number of records into Number_Of_Recs, and which will be devided by our page size (10) gives number of pages after ceiling the fraction and which will be stored in Number_Of_Pages.
And that loop iterates for Number_Of_pages and creates a link button for each iteration and that link button is added to the Placeholder1 displays like following

1 2 3 4 5 and more..

here each link click calls a function lb_cmd() where we need to get data from database.


void lb_Command(object sender, CommandEventArgs e)

{
LinkButton link = (LinkButton)sender;
getBind(link.Text);
}


protected void getBind(string page)
{
SqlDataAdapter da = new SqlDataAdapter("Sp_GetPage", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.AddWithValue("@page", page);
DataTable dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}

Here the lb_cmd() calls the method getBind() which accepts a parameter page which is the page number we are going to display. getBind() method calls a procedure " Sp_GetPage " which takes a parameter @page.
and the stored procedure is...

CREATE PROCEDURE Sp_GetPage

@page int
as begin
set @page =@page-1
select rownum,field1,field2 from (select field1,field2,ROW_NUMBER() over (order by sno desc) as rownum from tablename f )
as newtablename where rownum between (@page*10)+1 and (@page+1)*10
end

This was the procedure and code used for Gridview custom paging

No comments:

Post a Comment