Custom Paging Of Large Record Set in ASP.NET
Normally we are using Gridview control for Data Binding in ASP.NET.
Below is the sample code for Data Binding in Asp.Net using Gridview Control.
Sub BindData()
Dim con As SqlConnection, sqldad As SqlDataAdapter, ds As DataSet
Dim strcon As String
Dim server As String
Dim database_name, user_name, user_password As String
Try
server = "your server name"
database_name = "database name"
user_name = "database user name"
user_password = "database password"
strcon = "SERVER=" & server & ";DATABASE=" & database_name &
";UID=" & user_name & ";PWD=" & user_password
con = New SqlConnection(strcon)
sqldad = New SqlDataAdapter("select * from Employee", strcon)
ds = New DataSet
sqldad.Fill(ds)
Gridview1.DataSource = ds.Tables(0)
Gridview1.DataBind()
Catch ex1 As SqlException
Response.Write(ex1.Message)
Catch ex As Exception
Response.Write(ex.Message)
End Try
End Sub
To enable paging for Gridview, just set AllowPaging property to True and give page size.
Assume page size is 10 and we need to implement the gridview’s PageIndexChanging method.
Protected Sub Gridview1_PageIndexChanging(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.GridViewPageEventArgs) Handles Gridview1.PageIndexChanging
Gridview1.PageIndex = e.NewPageIndex
BindData()
End Sub
That’s it, Gridview paging is working.
It is ok for small amount of data,but it is notsuitable for bulk data.
Why because, for every page it binds total records and showing only particular page records.
Take for example, if our query giving the 10,000 records and we want to show 10 records at a time. What we are doing , we are binding 10,000 records for each requet and we are showing 10 records only. i.e, we are Unneceesarly binding 9990 records. It is giving the more load on Database server for every page request and on webserver also.(because we are binding 10,000 records show just 10 records per page)
Is there any solution to avoid to binding unnecessary records.
Yes, we have a solution by using custom paging with SQL Server 2005.
Custom Paging improves the performance of default paging by retrieving only those records from the database that need to be displayed for the particular page of data requested by the user; however, custom paging involves a bit more effort to implement than default paging.
Implementing Custom Paging:
We need three variables to implement custom paging.
Those are
start row index
end row index
total records
start row index: This is the index of the first row in the page of data to display. This index can be calculated by multiplying the page index by the records to display per page and adding one. For example, when paging through records 10 at a time, for the first page (whose page index is 0), the Start Row Index is 0 * 10 + 1, or 1; for the second page (whose page index is 1), the Start Row Index is 1 * 10 + 1, or 11.
end row index: This is the index of the last row in the page of data to display. This index can be calculated by adding the page size to the start row index-1(already you got this one).For example our page size is 10 and start row index is 31(3rd page) , then our end row index is 40(31-1+10).
total records: This is total number of records being paged through. It dictates the paging interface. For example if the total records are 80 then we came to know that we casme to know that page size is 8.
We can get total records by simple query.
Select count(1) from emp1
Row_Number(): In Sql server 2005 Row_Number() keyword is giving ranking for each Returned record based on some ordering. Use primary key column for ordering.
Sample query: select * from (select *,row_number() over(Order by id) as Rank from emp1) as A
the above query returns all records and row number for each record based on column id(in emp1 table id is primary key coumn).
By using all things above we can do effective pageing.
Custom Paging:
We need to caluculate total records before doing the pageing.
Private Sub BindData(ByVal startindex As Integer, ByVal endindex As Integer)
Dim con As SqlConnection, sqldad As SqlDataAdapter, ds As DataSet
Dim strcon As String
Dim server As String
Dim database_name, user_name, user_password As String
Dim strsql As String
Try
server = "your server name"
database_name = "database name"
user_name = "database user name"
user_password = "database password"
strcon = "SERVER=" & server & ";DATABASE=" & database_name &
strcon = strcon & " ;UID=" & user_name & ";PWD=" & user_password
con = New SqlConnection(strcon)
strsql = "select * from (select *,row_number() over(Order by id) as Rank from "
strsql = strsql & " emp1) as A where Rank between " & startindex & "and " & endindex"
sqldad = New SqlDataAdapter(strsql, strcon)
ds = New DataSet
sqldad.Fill(ds)
Gridview1.DataSource = ds.Tables(0)
Gridview1.DataBind()
Catch ex1 As SqlException
Response.Write(ex1.Message)
Catch ex As Exception
Response.Write(ex.Message)
End Try
End Sub
If you observe query in above code, we put the condition as Rank between starteindex and end index. i.e, we are retrieving rows only what we required.
Download source code here
