Subscribe
Filter by APMLCustom 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.
<< Previous Download source code here
