GridView Custom Paging(Pagination) Using ObjectDataSource
Have you ever wondered how to present some large data (more than 10,000 rows) in the GridView? That’s where the GridView Paging comes to be helpful. Actually Paging enables the user to show data page by page. Tough GridView and normal DataTable Binding allows paging, natively DataTable binding will not allow fetching page by page out of the box.
How Custom Paging(Pagination) can be enabled in GridView
As I said earlier the Custom Paging cannot be enabled out of the box, but there are other ways to implement it. ObjectDataSource can be used to fetch data page by page directly from DB. This ObjectDataSource contains various properties and methods to invoke a custom TableAdapter there we can actually get the inputs from the GridView and fetch the rows respectively.
Basic differences between Native Paging And Custom Paging
You will know the difference when you watch the requested SQL queries through MS SQL profiler if the db is MS SQL Server. The native GridView Paging will fetch all the rows while paginating, if the data is not cached in ViewState or any other equivalent.
But for the Custom Paging will fetch only the number of rows allowed per page while paginating. So the pagination will be responsive and user experience will be better.
ObjectDataSource and Custom Paging(Pagination)
Here ObjectDataSource is the DataSource for the grid. And it will connect the Custom Table Adapter to fetch rows page by page. To achieve this ObjectDataSource will transfer all user requests to the TableAdapter. Find the following reference which will be helpful for Custom Paging.
EnablePaging
This property is to enable paging for the ObjectDataSource. This has to be True. Otherwise the paging will not be enabled. Basically this setting will be passed through the respective GridView’s allow paging property.
TypeName
This property is to specify which object is the TableAdapter to fetch the data from Database. This has to be a fully qualified along with the namespace. Otherwise an error “The type specified in the TypeName property of ObjectDataSource 'PagingSourceGridView1' could not be found” will be thrown. I faced this error first because I only specified the class name and after specifying fully qualified name it went successfully.
SelectMethod
ObjectDataSource knows the Class to find the TableAdapter. But still the ObjectDataSource does not know which function returns the DataTable for the current page. So this Property is the one we should configure for function responsible for fetching rows. Remember the function should be able to fetch the rows page by page respective to the client’s pagination request.
SelectCountMethod
When you see the pagination bar at the bottom or in top of the GridView, the page numbers presented with number of pages available. This will be computed with the help of total row count and Maximum number of rows per page. This SelectCountMethod property will be useful to specify ObjectDataSource to look for the function in the Custom Table Adapter to fetch the total numbers rows.
StartRowIndexParameterName
When paginating the actual result set rendered on the screen is fetched using two parameters namely StartRowIndexParameterName, and MaximumRowsParameterName. Based on these two parameters only the SQL query will be formed. As the name implies, it is actually a parameter name which specifies Start Row of the current page request. This is actually the parameter of the function which is specified in SelectMethod.
MaximumRowsParameterName
This is another parameter name of the function given in the SelectMethod property. This property is used to specify the maximum number of rows allowed per page.
EnableViewState
Using this property the ViewState of the current object can be disabled so that caching will not be done.
Custom Paging Table Adapter
Till now we have seen how to communicate the GridView Paging requests using the ObjectDataSource to enable Pagination. Actual data fetching operation happens at the Custom Paging Table Adapter. Basically this class is responsible for fetching the rows based on the ObjectDataSource’s requirements, and provides the total row count.
RowCount Function
As per this sample, the RowCount function returns the actual number of rows available
GetPagingData Function
As per this sample, the GetPagingData function returns the rows based on the GridView’s paging request for Pagination. Whenever the GridView paginates, the StartRowIndex, will be changed respectively. Normally the MaximumRows will not be changed between paginations, but if required and if it is changed the number of rows per page will be changed respectively.
Source Code
Code Behind(ASPX.CS file)
Imports System.Data.SqlClient
Partial Public Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
PagedBind(GridView1)
End Sub
Private Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging
GridView1.PageIndex = e.NewPageIndex
GridView1.DataBind()
End Sub
Public Sub PagedBind(ByVal PagingGrid As GridView)
Dim PagingSource As New ObjectDataSource()
With PagingSource
.ID = "PagingSource" & PagingGrid.ID
.EnablePaging = PagingGrid.AllowPaging
.TypeName = "CustomPaging.PagingAdapter"
.SelectMethod = "GetPagingData"
.SelectCountMethod = "RowCount"
.StartRowIndexParameterName = "StartRowIndex"
.MaximumRowsParameterName = "MaximumRows"
.EnableViewState = False
End With
PagingGrid.DataSource = PagingSource
PagingGrid.DataBind()
End Sub
End Class
Custom Paging Adapter Class(.CS)
Imports System.Data.SqlClient
Friend Class PagingAdapter
Private ReadOnly Property ConnectionString() As String
Get
Return "Server=.\SQLEXPRESS;Database=NorthWind;Trusted_Connection=True"
End Get
End Property
Private ReadOnly Property Connection() As SqlConnection
Get
Dim ConnectionToFetch As New SqlConnection(ConnectionString)
ConnectionToFetch.Open()
Return ConnectionToFetch
End Get
End Property
Public Sub New()
End Sub
Public Function RowCount() As Integer
Try
Dim CountQry = "Select Count(1) from Orders"
Dim PagingCommand As New SqlCommand(CountQry, Connection)
Dim PagingReader = PagingCommand.ExecuteReader()
If (PagingReader.Read()) Then
RowCount = PagingReader.GetInt32(0)
PagingReader.Close()
End If
Catch ex As Exception
Throw
End Try
End Function
Public Function GetPagingData(ByVal StartRowIndex As Integer, _
ByVal MaximumRows As Integer) As DataTable
Dim SelectQry = "select * from (" _
& "SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowIndex,CustomerID,ShipName " _
& " FROM Orders ) as Rowed where RowIndex >= " & StartRowIndex _
& " and RowIndex <= " & StartRowIndex + MaximumRows
Dim PagingSource As New DataSet
Try
Dim PagingDataAdapter As New SqlDataAdapter(SelectQry, ConnectionString)
PagingDataAdapter.Fill(PagingSource)
Catch ex As Exception
Throw ex
End Try
Return PagingSource.Tables(0)
End Function
End Class