Internet Applications in .NET

Creation QR-Code in .NET Internet Applications

Display a page. Sub ShowPage(ByVal pageNum As Integer) Keep the page number in valid range. pageNum = Math.Max(0, Math.Min(pageNum, dgrTitles.PageCount - 1)) Enforce the new page number and bind the control. dgrTitles.CurrentPageIndex = pageNum BindDataGrid() Update button state. btnFirst.Enabled = (pageNum > 0) btnPrevious.Enabled = (pageNum > 0) btnNext.Enabled = (pageNum < dgrTitles.PageCount - 1) btnLast.Enabled = (pageNum < dgrTitles.PageCount - 1) Display the current page number (1-based). txtPage.Text = (pageNum + 1).ToString End Sub

When you re doing default paging using either default or custom navigation buttons you must bind the DataGrid control to a DataView or DataTable object, as you d do if pag ing were disabled so that the control can use the CurrentPageIndex property to display only the requested page. The demo application reduces database activity by creating the DataSet object the first time the page is requested and storing it in a Session variable. Here s the code that does the binding and provides support for column sorting as well:

Bind the DataGrid control. Sub BindDataGrid() Retrieve the DataSet from the session variable. Dim ds As DataSet = DirectCast(Session( DataSet ), DataSet) Read data from database if this is the first time you do it. If ds Is Nothing Then Dim cn As New OleDbConnection(BiblioConnString) cn.Open() Read data from Titles table, plus the publisher s name. Dim sql As String sql = SELECT Titles.*, Publishers.Name As PubName FROM Titles _ & INNER JOIN Publishers ON Titles.PubId=Publishers.PubId" Dim da As New OleDbDataAdapter(sql, cn) ds = New DataSet() da.Fill(ds, Titles ) cn.Close() Store the DataSet in a Session variable. Session( DataSet ) = ds End If

dv.Sort = dgrTitles.Attributes( SortExpr )

dgrTitles.DataBind()

End Sub Private Sub dgrTitles_SortCommand(ByVal source As Object, _ ByVal e As DataGridSortCommandEventArgs) Handles dgrTitles.SortCommand

25:

Dim currSortExpr As String = dgrTitles.Attributes( SortExpr )

If Not (currSortExpr Is Nothing) AndAlso _ currSortExpr.ToString = e.SortExpression Then newSortExpr &= DESC" End If

ShowPage(0)

Default paging has a great shortcoming: you must load all the data in a DataTable or DataView object to let the DataGrid control select only the rows that belong to the current page. This approach might be OK when the data source contains a few hundred rows, but it won t work in real-world applications that manage thousands (or even millions) of rows. In cases like these, custom paging is the only reasonable solution. You activate custom paging by setting both the AllowPaging and the AllowCustomPag ing properties to True. You then assign the total number of rows to the VirtualItemCount property so that the control can correctly evaluate the PageCount value. You can use default buttons or provide your own. Unlike all the samples shown previously, when in custom paging mode you bind the DataGrid control to a data source that contains only the data you want to display in the current page. Fortunately, you already know how to page through a large resultset by using the DataAdapter s Fill method. Thanks to the high modularity of the code written so far, implementing custom paging is just a matter of replacing the BindDataGrid pro cedure with a new version that takes the CurrentPageIndex and PageSize properties into account. The following code also takes the current sort order into account: Notice that when doing custom paging, you implement sorting by means of an ORDER BY clause in the SQL query so that you can then extract the correct page from the sorted resultset:

Sub BindDataGrid() Open the connection. Dim cn As New OleDbConnection(BiblioConnString) cn.Open() If necessary, initialize VirtualItemCount with number of records. If Not Me.IsPostBack Then Dim cmd As New OleDbCommand( SELECT COUNT(*) FROM Titles", cn) dgrTitles.VirtualItemCount = CInt(cmd.ExecuteScalar) End If Prepare to read from Titles table plus Name of publisher. Dim sql As String = SELECT Titles.*, Publishers.Name As PubName FROM _ & Titles INNER JOIN Publishers ON Titles.PubId=Publishers.PubId" Append sort expression, if there is one.

Part VI: