ASP.NET DataGrid has built-in functionality of paging. This
is called Default Paging. However, it has one disadvantage.
Even though you are displaying only a small part of entire
DataSet, you need to populate the DataSet with whole data.
This works well when your DataSet is small but certainly not
with huge amount of data. To overcome this problem DataGrid
also allows custom paging. In this article we are going to
see how to do paging in DataGrid using both these methods.
At the end of this article we are also going to see the advantages
and disadvantages of this two paging in DataGrid.
Default Paging
For default paging, we need to set AllowPaging property to
True and use PageSize property to set the number of items
to display in a page. If we set this two property, then default
paging is enabled and navigation controls( Prev-next or Page
Numbers ) will come automatically in DataGrid. We can use
PagerStyle-Mode property to set the style of Paging. When
we press any navigation controls, then PageIndexChanged event
is raised. In this event handlers, we need to write code to
set that current page index and then we need to bind the DataGrid
again. Code for this handler will be like this,
Private Sub DataGrid1_PageIndexChanged(ByVal source As Object,
ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs)
Handles DataGrid1.PageIndexChanged
In this code sample, first
thing we are doing is setting the page index. We can get the
current page index from the DataGridPageChangedEventargs.
It has a property called NewPageIndex which will give us the
current page index. Then we need to bind the DataGrid again.
Custom Paging
For custom paging, we need to set AllowPaging, AllowCustomPaging
properties to True. Then as in default paging we need to use
PageSize property to set the number of items to display in
a page. Other than these properties we need to set VirtualItemCount
property. This property is used to set the virtual number
of items in DataGrid. This property is required in Custom
paging if we want to use the navigation controls provided
by DataGrid. For navigation controls to work, DataGrid has
to know the total number of rows that DataSource will contain.
Since in Custom Paging we will fetch only required rows for
showing in that current page, we need to set VirtualItemCount
property. So that DataGrid will show navigation controls depending
upon this value. When we press any navigation controls, then
PageIndexChanged event is raised. In this event handlers,
we need to write code to set that current page index and then
we need to bind the DataGrid again. Before binding DataGrid
, we need to fetch the data from database for this new page.
There are various option available for paging, here we will
see one of the efficient option for doing the same.
Paging Solution
In the PageIndexChanged Event Handler, we know which page we
want to show in DataGrid. Since we know the Current Page Index,
we can find out starting row position and ending row position
for that page. For example, if the Current Page Index is 1 and
Page size is 10. Then we need to fetch 11th to 20th row from
database.
Start Position ? (Current Page Index * Page
Size ) + 1
End Position ? (Current Page Index * PageSize) + PageSize
Since we know start position and end position in ASPX, we
can pass these values to SP. SP can fetch the rows from Start
Position to End Position from table and it can return to ASPX.
Then we can bind this DataSet to DataGrid. Code for PageIndexChanged
Event Handler is,
Private Sub DataGrid1_PageIndexChanged(ByVal source As Object,
ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs)
Handles DataGrid1.PageIndexChanged
‘
This code is to fetch the required values from database by
passing starting value and ending
‘ value and then it will bind
that dataset to datagrid.
Dim Con As SqlConnection
Dim sqlAda As SqlDataAdapter
Dim connStr As String
connStr = "server=perfdb;uid=tluser;pwd=password;initial
catalog=pubs"
Con = New SqlConnection(connStr)
sqlAda = New SqlDataAdapter()
sqlAda.SelectCommand = New SqlCommand()
sqlAda.SelectCommand.Connection =
Con
sqlAda.SelectCommand.CommandText =
"sprocGetAuthors"
sqlAda.SelectCommand.CommandType =
CommandType.StoredProcedure
----
Limit upper bound to the @StopRow parameter
Set RowCount @StopRow
----
Fill in the table variable
insert @t_table
(
[au_lname] , [au_fname] , [phone] ,[address]
,[city],[state] ,[zip] ,[Contract]
)
SELECT [au_lname], [au_fname], [phone],
[address], [city],[state], [zip], [Contract]
FROM
authors
WHERE au_lname like '%' + @au_lname
+ '%'
ORDER BY au_lname
----
Return the proper rows to the application
SELECT * FROM @t_table WHERE rownum
>= @StartRow
ORDER BY rownum
GO
In the Page_Load event, you
need to bind DataGrid with DataSet with rows starting from 0
and 10(PageSize) . Before binding the DataSet you need to set
the VirtualItemCount property of that DataGrid. For setting
this property, you need to find out how many rows are there
in the DataSource (Table). For this you need to hit the database
to get that value (This operation should happen only once that
too in Page_Load). Code for Page_Load will be,
Private Sub Page_Load(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles MyBase.Load
If
Not IsPostBack Then
Dim Con As
SqlConnection
Dim sqlAda
As SqlDataAdapter
Dim connStr
As String
connStr =
"server=perfdb;uid=tluser;pwd=password;initial catalog=pubs"
Con = New
SqlConnection(connStr)
Dim comm As
New SqlCommand()
comm.CommandText
= "sprocGetAuthorsCount"
comm.Connection
= Con
comm.CommandType
= CommandType.StoredProcedure
Dim DReader
As SqlDataReader
Con.Open()
DReader =
comm.ExecuteReader()
While (DReader.Read())
DataGrid1.VirtualItemCount
= DReader.GetInt32(0)
End While
Con.Close()
sqlAda = New
SqlDataAdapter()
sqlAda.SelectCommand
= New SqlCommand()
sqlAda.SelectCommand.Connection
= Con
sqlAda.SelectCommand.CommandText
= "sprocGetAuthors"
sqlAda.SelectCommand.CommandType
= CommandType.StoredProcedure
