METHOD 1 :-
One disadvantage of using the DataGrid control's built-in paging support is performance. When you use the method of paging through records discussed in the preceding section, all the records must be retrieved from the data source every time you navigate to a new page. So, if you are paging through a database table with 2 million records, those 2 million records must be retrieved into memory every time you move to a new page.
The custom paging solution you examine here makes a strong assumption about the contents of the database table with which it is used. This approach works only with database tables, that have a column that uniquely indexes each database row.
CAUTION
If certain values are missing from the identity column, the DataGrid displays fewer records for some pages than others.
Listing 11.20 DataGridCustomPaging.aspx
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<Script Runat="Server">
Dim conNorthwind As SqlConnection
Dim strSelect As String
Dim intStartIndex As Integer
Dim intEndIndex As Integer
Sub Page_Load
Dim cmdSelect As SqlCommand
conNorthwind = New SqlConnection( "" )
If Not IsPostBack Then
' Get Total Pages
strSelect = "Select Count(*) From Products"
cmdSelect = New SqlCommand( strSelect, conNorthwind )
conNorthwind.Open()
dgrdProducts.VirtualItemCount = ( cmdSelect.ExecuteScalar() / dgrdProducts.PageSize )
conNorthwind.Close()
BindDataGrid // First Time
End If
End Sub
Sub BindDataGrid
Dim dadProducts As SqlDataAdapter
Dim dstProducts As DataSet
intEndIndex = intStartIndex + dgrdProducts.PageSize
strSelect = "Select * From Products Where ProductID > @startIndex " _
& "And ProductID <= @endIndex Order By ProductID"
dadProducts = New SqlDataAdapter( strSelect, conNorthwind )
dadProducts.SelectCommand.Parameters.Add( "@startIndex", intStartIndex )
dadProducts.SelectCommand.Parameters.Add( "@endIndex", intEndIndex )
dstProducts = New DataSet
dadProducts.Fill( dstProducts )
dgrdProducts.DataSource = dstProducts
dgrdProducts.DataBind()
End Sub
Sub dgrdProducts_PageIndexChanged( s As Object, e As DataGridPageChangedEventArgs )
intStartIndex = (( e.NewPageIndex – 1) * dgrdProducts.PageSize )) + 1
dgrdProducts.CurrentPageIndex = e.NewPageIndex
BindDataGrid // From next Time
End Sub
</Script>
A count of the total number of records in the Products table is retrieved in the Page_Load subroutine. This value is assigned to the VirtualItemCount property of the DataGrid. The DataGrid control uses this property when rendering the paging links.
METHOD 2 :-
In this approach we are not following the assumption we require in the first method as :
“The custom paging solution you examine here makes a strong assumption about the contents of the database table with which it is used. This approach works only with database tables, that have a column that uniquely indexes each database row”.
Listing 11.20 DataGridCustomPaging.aspx
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<Script Runat="Server">
Dim conNorthwind As SqlConnection
Dim strSelect As String
Dim intStartIndex As Integer =1
Dim intEndIndex As Integer
Sub Page_Load
Dim cmdSelect As SqlCommand
conNorthwind = New SqlConnection("")
If Not IsPostBack Then
' Get Total Pages
strSelect = "Select Count(*) From Products"
cmdSelect = New SqlCommand( strSelect, conNorthwind )
conNorthwind.Open()
dgrdProducts.VirtualItemCount = ( cmdSelect.ExecuteScalar() / dgrdProducts.PageSize )
conNorthwind.Close()
BindDataGrid(1) // First Time
End If
End Sub
private Function BindDataGrid(nStartIndex As Integer)
Dim strConn As String = "DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;"
Dim conn As SqlConnection = New SqlConnection(strConn)
' FetchEmployeesByPage is a CUSTOM Northwind stored procedure
' that returns each single record in a different resultset. This is due
' to the use of T-SQL cursors
Dim strCmd As String = "FetchEmployeesByPage"
Dim da As SqlDataAdapter = New SqlDataAdapter()
Dim cmd As SqlCommand = New SqlCommand(strCmd, conn)
cmd.CommandType = CommandType.StoredProcedure
da.SelectCommand = cmd
Dim prmStartNumber As SqlParameter = New SqlParameter"@StartIndex", SqlDbType.Int)
prmStartNumber.Direction = ParameterDirection.Input
prmStartNumber.Value = nStartIndex
cmd.Parameters.Add(prmStartNumber)
Dim prmPageSize As SqlParameter = New SqlParameter("@PageSize", SqlDbType.Int)
prmPageSize.Direction = ParameterDirection.Input
prmPageSize.Value = grid.PageSize
cmd.Parameters.Add(prmPageSize)
' Execute the command
Dim ds As DataSet = New DataSet()
da.Fill(ds)
' Merges the different resultsets(Each containing one record) in a single table
Dim dsOutputTable As DataTable = ds.Tables(0).Clone()
Dim dTable As DataTable
Dim drow As DataRow
For Each dTable In ds.Tables
For Each drow In dTable.Rows
Dim tmp As DataRow = dsOutputTable.NewRow()
tmp.ItemArray = drow.ItemArray
dsOutputTable.Rows.Add(tmp)
Next
Next
grid.DataSource = dsOutputTable
grid.DataBind()
End Function
Sub dgrdProducts_PageIndexChanged( s As Object, e As DataGridPageChangedEventArgs )
intStartIndex = (( e.NewPageIndex – 1) * dgrdProducts.PageSize )) + 1
dgrdProducts.CurrentPageIndex = e.NewPageIndex
BindDataGrid(intStartIndex) // From next Time
End Sub
</Script>
That procedure uses T-SQL cursors to return a page of records.
CREATE PROCEDURE FetchEmployeesByPage (@StartIndex smallint, @PageSize smallint)
AS
-- @StartIndex, Index of First record to fetch on each page
-- @PageSize, records that fit into one page
-- internal variables
DECLARE @MyIndex smallint
DECLARE @FirstRecPos smallint
-- sets the position of the first record to retrieve in the page
SET @FirstRecPos = @StartIndex
-- cursor associated with the query string
DECLARE employees_cursor SCROLL CURSOR FOR
SELECT employeeid,firstname,lastname FROM employees
ORDER BY lastname
-- opens the cursor
OPEN employees_cursor
-- fetches the first record in the page
FETCH ABSOLUTE @FirstRecPos FROM employees_cursor
-- loops through the cursor to pick up the other records in the page
SET @MyIndex = 1
WHILE @@FETCH_STATUS = 0 AND @MyIndex < @PageSize
BEGIN
FETCH NEXT FROM employees_cursor
SET @MyIndex = @MyIndex + 1
END
-- clean up
CLOSE employees_cursor
DEALLOCATE employees_cursor
GO
Hope this helps.
Regards,
Arun Manglick
Disclaimer: The information contained in this message may be privileged, confidential, and protected from disclosure. If you are not the intended recipient, or an employee, or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer.
