DataGrid/GridView Paging and Sorting Using A DataReader

ASP.NET 2.0 GridView Version

 As an added bonus, I've included the updated .NET 2.0 GridView code below. It contains both the ArrayList and DataTable versions in binding a GridView from a DataReader. Enjoy!
<%@ Page Language="VB" Debug="False" Strict="True"
 Explicit="True" Buffer="True" Trace="False" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<html>
<head></head>
<body bgcolor="#FFFFFF" topmargin="0" marginheight="0">

<script language="VB" runat="server">
    'ArrayList setup
Dim dgCache, bkResults As ArrayList
'DataTable setup
'Dim dgCache As DataView


Sub Page_Load (Source As Object, E As EventArgs)
If Not Page.IsPostBack Then
'Reset GridView Page to the top
MyGridView.PageIndex = 0
'Check if ViewSate is already populated
If IsNothing(ViewState("SortOrder")) then
'Assign default column sort order
GetDataReader ("SupplierID asc")
Else
'Else retrieve sort order from ViewState
GetDataReader (ViewState("SortOrder").ToString())
End If
End If
End Sub


Public Structure DBInfo 'Lightweight Class to hold our data results

Private _SupplierID As Object
Private _CompanyName As String
Private _ContactName As String
Private _Country As String

'Constructor
Public Sub New (ByVal SupplierID As Object, ByVal CompanyName As String, _
ByVal ContactName As String, ByVal Country As String)

_SupplierID = SupplierID
_CompanyName = CompanyName
_ContactName = ContactName
_Country = Country

End Sub


'All relevant public properties based on database columns
Public Property SupplierID() As Object
Get
Return _SupplierID
End Get
Set (ByVal Value As Object)
_SupplierID = Value
End Set
End Property

Public Property CompanyName() As String
Get
Return _CompanyName
End Get
Set (ByVal Value As String)
_CompanyName = Value
End Set
End Property

Public Property ContactName() As String
Get
Return _ContactName
End Get
Set (ByVal Value As String)
_ContactName = Value
End Set
End Property

Public Property Country() As String
Get
Return _Country
End Get
Set (ByVal Value As String)
_Country = Value
End Set
End Property

End Structure 'DBinfo Structure


Function SortOrder (Field As String) As String

If Field = ViewState("SortOrder").ToString() Then
SortOrder = Replace (Field,"asc","desc")
Else
SortOrder = Replace (Field,"desc","asc")
End If

End Function


Sub GetDataReader(ColumnOrder As String) 'ArrayList Version

'Assign ColumnOrder to ViewState
ViewState("SortOrder") = ColumnOrder

'Set up Cache Object and determine if it exists
dgCache = CType(Cache.Get("dgCache" & ColumnOrder), ArrayList)

If (dgCache Is Nothing) Then

Dim sqlStr As String = "SELECT SupplierID, CompanyName, "_
& "ContactName, Country FROM Suppliers Order by " & ColumnOrder
Dim strConn As String = "server=(local);uid=sa;pwd=;database=Northwind;"
Dim MyConnection As New SQLConnection (strConn)

MyConnection.Open()

Dim MyCommand As New SQLCommand(sqlStr, MyConnection)
Dim objDataReader As SQLDataReader = MyCommand.ExecuteReader (CommandBehavior.CloseConnection)
'Create instances of the class,
Dim bkResults as New ArrayList()

'Loop through DataReader
While objDataReader.Read()

With bkResults

'and then add the instances to the ArrayList
.Add (New DBInfo (objDataReader.GetInt32(0), _
objDataReader.GetString(1), _
objDataReader.GetString(2), _
objDataReader.GetString(3)))
End With
End While

'Insert ArrayList into Cache Object with unique identifier
Cache.Insert ("dgCache" & ColumnOrder, bkResults)
'Close all connections
objDataReader.Close()
MyCommand.Dispose() : MyCommand = Nothing
MyConnection.Close() : MyConnection = Nothing
'Bind GridView from ArrayList
MyGridView.DataSource = bkResults
Else
'Bind GridView from Cached ArrayList
MyGridView.DataSource = dgCache
End If

MyGridView.DataBind()

'Clear ArrayList
bkResults = Nothing
End Sub
'Sub GetDataReader (ColumnOrder As String) 'DataTable Version
' dgCache = CType(Cache.Get("dgCache"), DataView)
' ViewState("SortOrder") = ColumnOrder
' If (dgCache Is Nothing) Then
' Dim sqlStr As String = "SELECT SupplierID, CompanyName, ContactName, " & _
' "Country FROM Suppliers Order by " & ColumnOrder
' Dim strConn As String = "server=(local);uid=sa;pwd=;database=Northwind;"
' Using MyConnection As New SQLConnection (strConn)
'
' Dim MyCommand As New SQLCommand(sqlStr, MyConnection)
' Dim objDataReader As SQLDataReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection)
' 'Create DataTable
' Dim DataReaderTable As New DataTable()
' Dim dc1 As New DataColumn("SupplierID", GetType(Object))
' Dim dc2 As New DataColumn("CompanyName", GetType(String))
' Dim dc3 As New DataColumn("ContactName", GetType(String))
' Dim dc4 As New DataColumn("Country", GetType(String))
'
' With DataReaderTable
'
' .Columns.Add(dc1)
' .Columns.Add(dc2)
' .Columns.Add(dc3)
' .Columns.Add(dc4)
'
' End With
'
' 'Loop through Data
' While objDataReader.Read()
'
' 'Set up DataRow object
' Dim dr As DataRow = DataReaderTable.NewRow
'
' With dr
' dr(0) = objDataReader(0)
' dr(1) = objDataReader.GetString(1)
' dr(2) = objDataReader.GetString(2)
' dr(3) = objDataReader.GetString(3)
' End With
'
' 'Add rows to existing DataTable
' DataReaderTable.Rows.Add(dr)
'
' End While
'
' 'Create DataView to support our column sorting
' Dim Source As DataView = DataReaderTable.DefaultView
'
' 'Assign column sort order for DataView
' Source.Sort = ColumnOrder
'
' 'Insert DataTable into Cache object
' Cache.Insert ("dgCache", Source)
'
' 'Bind DataGrid from DataView
' MyGridView.DataSource = Source '
' 'Close all connections
' End Using
' Else
'
' 'Assign Cached DataView new sort order
' dgCache.Sort = ViewState("SortOrder").ToString()
'
' 'Bind DataGrid from Cached DataView
' MyGridView.DataSource = dgCache
'
' End If
'
' MyGridView.DataBind()
'
'End Sub
Sub MyGridView_Page(sender As Object, e As GridViewPageEventArgs)
MyGridView.PageIndex = e.NewPageIndex
GetDataReader (ViewState("SortOrder").ToString())
End Sub

Sub MyGridView_Sort(sender As Object, e As GridViewSortEventArgs)
MyGridView.PageIndex = 0
GetDataReader(SortOrder(e.SortExpression.ToString()))
End Sub

</script>
<br><br>
<b>Dynamic GridView Paging and Sorting Using A DataReader Bound ArrayList</b>
<br>
<form runat="server">

<%= "Page: " & MyGridView.PageIndex+1 & " of " & (MyGridView.PageCount) %>
<br><br>

<ASP:GridView id="MyGridView" runat="server"

AutoGenerateColumns="False"
AllowPaging="True"
AllowCustomPaging="False"
AllowSorting="True"
PageSize="10"
PagerStyle-Mode="NumericPages"
PagerStyle-HorizontalAlign="Right"
PagerStyle-NextPageText="Next"
PagerStyle-PrevPageText="Prev"
BorderColor="black"
BorderWidth="1"
GridLines="Both"
CellPadding="3"
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
AlternatingItemStyle-BackColor="#eeeeee"
Width="700px"
OnSorting="MyGridView_Sort"
OnPageIndexChanging="MyGridView_Page">
<Columns>
<asp:BoundField DataField="SupplierID" SortExpression="SupplierID asc" HeaderText="SupplierID"/>
<asp:BoundField DataField="CompanyName" SortExpression="CompanyName asc"
HeaderText="CompanyName"/>
<asp:BoundField DataField="ContactName" HeaderText="ContactName"
SortExpression="ContactName asc"/>
<asp:BoundField DataField="Country" HeaderText="Country"
SortExpression="Country asc"/>
</Columns>
</asp:GridView>
</form>
</body>
</html>

You might also like...

Comments

About the author

Dimitrios Markatos

Dimitrios Markatos United States

Dimitrios, or Jimmy as his friends call him, is a .NET developer/architect who specializes in Microsoft Technologies for creating high-performance and scalable data-driven enterprise Web and des...

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“There are only 3 numbers of interest to a computer scientist: 1, 0 and infinity”