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!