DataGrid/GridView Paging and Sorting Using A DataReader

DataGrid Paging and Sorting using a DataReader with a DataTable/DataView

Ok here is this code, except the only thing that I will list here is the DataReader method, because the rest of the code is pretty much the same. All you need to do is replace the ArrayList GetReader method with this one and remove the Structure Class. And also modify the Cache object for the unique identifier and cast the dgCache and bkResults variables at the top of the page as DataViews instead:

Sub GetDataReader (ColumnOrder As String)
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;"
Dim MyConnection As New SQLConnection (strConn)

MyConnection.Open()

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

'Close all connections
objDataReader.Close()
MyCommand.Dispose() : MyCommand = Nothing
MyConnection.Dispose() : MyConnection = Nothing

'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
MyDataGrid.DataSource = Source

Else

'Assign Cached DataView new sort order
dgCache.Sort = ViewState("SortOrder").ToString()

'Bind DataGrid from Cached DataView
MyDataGrid.DataSource = dgCache

End If

MyDataGrid.DataBind()

End Sub

Initially off the top in our GetDataReader method, the one noticeable difference is with our cache object. In our last example I had to cast the Cache object as an ArrayList to accommodate that setup. Here it's properly cast as a DataView. Next are the same data connections as mentioned earlier, except we now are setting up our standalone DataTable, instead of working with an ArrayList or a DataSet (eeks!). After the DataReader get executed, I create a new instance of the DataTable class using DataReaderTable that I initialized, and I add to it the columns I'll be working with.

Now again as the DataReader is being looped through I simultaneously add the data results each time into a new DataRow, in turn subsequently adding the culminated full resultset into my existing DataTable rows using the Rows.Add member of the DataTable Class.

Next, I set up a DataView on my DataTable, and assign its Sort property a sorting order, and finally bind this to my DataGrid. Now upon each initial sorting this value will be updated based on the sort expression passed into it and any sort order modifications I've done in determining which direction to sort in, utilizing the SortOrder function, listed in the first example and below:

   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

The rest is the same as before. After all's been said and done, the DataGrid is bound from the DataTable and is pageable and two-way sortable. Nice!

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.

“An expert is a man who has made all the mistakes that can be made in a very narrow field” - Niels Bohr