Introduction
In a previous article I had written on Dynamic
Column Sorting and Paging in ASP.NET, I demonstrated one of nine ways
one could page and bi-directionally sort a DataGrid. I opted for the old hidden
field method. At any rate, this method, among the eight others works fine, and
it all depends on how you want to approach this based on any given factors in
your app. Having said that, the problems we'll be discussing here lay not with
either the paging or sorting aspects of it, but rather in implementing the most
efficient manner in retrieving data and presenting it to the client.
No one wants to bog down a server with unnecessary network traffic
by displaying hundreds or even thousands of rows or data, nor in furthermore
weighing down server resources by creating any unnecessary memory demanding
objects, when you can easily conserve resources and time by utilizing more lightweight
data objects to display data with.
As you may or may not realize, there are numerous ways to bind
a DataGrid. The most common way is the typical DataAdapter/DataSet combo. Although,
this method has its proper setting and time to be used, you'll need to realize
that aside from data modification or management, employing this strategy will
prove in hindering performance, as the DataSet is definitely slower that the
lightning fast read-only, forward-only DataReader. Some performance statistics
worth mentioning can be found here - Performance
Comparison: Data Access Techniques.
Now, the Datareader is forward-only right? Hmm, but what about
paging and sorting? Can we natively implement a DataReader to provide paging
and sorting as one would typically with a DataSet? The answer unfortunately
is no, and if you try doing this you receive this error message:
| AllowCustomPaging must be true and VirtualItemCount
must be set for a DataGrid with ID DataGridID when AllowPaging is set to
true and the selected datasource does not implement ICollection. |
It is after all a foward-only cursor type object. However, there
are ways around this and in this article I will demonstrate how you could not
only use the DataReader to bind a DataGrid, but cache it, page it and persistently
and bi-directionally sort it!
Ways to bind a DataGrid
Now most web apps simply need quick data retrieval presented to the client
and again it's now good to discover that the DataGrid is not locked in with
the DataSet, as we'll momentarily see.
There are some cool ways to bind a DataGrid that most
people may not know about. I'll discuss two ways one could efficiently bind
a DataGrid. The first really cool way to bind a DataGrid with is using a DataReader
bound ArrayList,
and the other way is using a standalone DataTable
in conjunction with a DataView.
Both will use the DataReader
in getting our data, and both will have the cool paging and sorting features
I mentioned as well. And, by the way, they're both cacheable, to further increase
performance and scalability!
For all intents and purposes, the two methods that are
going to be illustrated will be nearly identical, expect for the soon evident
data creation/storage method, which is the aptly named GetDataReader
subroutine listed below. The Arraylist in our example
draws on the DataReader, but again in this instance to circumvent the error
message above, I employ the DataReader and ArrayList to instance a new lightweight
Structure Class (struct in C#) as opposed to a full fledged Class, to store
our data in its properties, and this is what makes the difference in allowing
you to bind a DataGrid with all it's features, instead of trying to natively
bind a Datareader to a DataGrid. You can find more on Structures and Classes
here - The
Quick and Dirty .NET Guide to C#/VB Object-Oriented Programming.
Additionally, the DataTable technique will also use the Datareader to create
a new DataTable and a DataView to sort the data, and it's your typical run of
the mill setup.
Notwithstanding, for brevity in this article I will discuss
in detail the two aforementioned techniques solely and will not be discussing
how to set up paging and sorting. For that have a look at Dynamic
Column Sorting and Paging in ASP.NET for more detail. And also glance
at .NET
Data Caching to find out more insight on the caching methodologies discussed
here.
So before we examine what the code does, I'll just list the
entire code for you to have a good OnPreRender view of it, and break it down
afterwards :-) We'll start off now with the ArrayList method code:
DataGrid Paging and Sorting using a DataReader and Structure
Class/ArrayList
<%@ 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">
'Set up the variables for our ArrayLists
Dim dgCache, bkResults As ArrayList
Sub Page_Load (Source As Object, E As EventArgs)
If Not Page.IsPostBack Then
'Reset DataGrid Page to the top
MyDataGrid.CurrentPageIndex = 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)
'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 DataReader Connection
objDataReader.Close()
'Bind DataGrid from ArrayList
MyDataGrid.DataSource = bkResults
Else
'Bind DataGrid from Cached ArrayList
MyDataGrid.DataSource = dgCache
End If
MyDataGrid.DataBind()
'Clear ArrayList
bkResults = Nothing
End Sub
Sub MyDataGrid_Page(sender As Object, e As DataGridPageChangedEventArgs)
MyDataGrid.CurrentPageIndex = e.NewPageIndex
GetDataReader (ViewState("SortOrder").ToString())
End Sub
Sub MyDataGrid_Sort(sender As Object, e As DataGridSortCommandEventArgs)
MyDataGrid.CurrentPageIndex = 0
GetDataReader(SortOrder(e.SortExpression.ToString()))
End Sub
</script>
<br><br>
<b>Dynamic DataGrid Paging and Sorting Using A DataReader Bound
ArrayList</b>
<br>
<form runat="server">
<%= "Page: " & MyDataGrid.CurrentPageIndex+1 & "
of " & (MyDataGrid.PageCount)%>
<br><br>
<ASP:DataGrid id="MyDataGrid" 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"
OnPageIndexChanged="MyDataGrid_Page"
OnSortCommand="MyDataGrid_Sort" >
<Columns>
<asp:BoundColumn DataField="SupplierID" SortExpression="SupplierID
asc" HeaderText="SupplierID"/>
<asp:BoundColumn DataField="CompanyName" SortExpression="CompanyName
asc"
HeaderText="CompanyName"/>
<asp:BoundColumn DataField="ContactName" HeaderText="ContactName"
SortExpression="ContactName asc"/>
<asp:BoundColumn DataField="Country" HeaderText="Country"
SortExpression="Country asc"/>
</Columns>
</asp:DataGrid>
</form>
</body>
</html> |
What's doing above?
OK, the main thing we'll focus on in the above code is
the Structure Class and the DataReader/ArrayList method. The reason I chose
to use a Structure
Class instead of a typical Class
is primarily for its lightweight memory footprint. As for our Collection Class,
I opted for an ArrayList as this has the most flexibility I found as compared
to other System.Collections
such as Stack, Queue, Array, SortedList or HashTable.
Now, at the top you'll notice I have the Structure Class
DBInfo. Within in it, I've set up its private variables and public properties,
and our New Constructor that will be instanced to in turn hold our data results
enabling our DataGrid to be bound from it.
Then after we've set up our Structure to hold the necessary
data, we move onto the main data retrieving DataReader method, which is aptly
named GetDataReader().
Nonetheless, you'll notice it accepts one parameter which is for sorting. Then
you'll notice the ViewState
and Caching setup for our added DataGrid enhancements. Next you'll observe the
typical ADO.NET connection setup, SQL query string, and how we open our connection
and query the database and finally execute the Datareader to get our data.
To digress for a moment, one note regarding the Cache setup. You'll notice
in light of the ArrayList example that I have added a variable to the cache
name. Due to the ArrayList's inherent nature, when inserting an ArrayList into
the cache as is without a unique identifier, it would result in not only not
allowing any sorting but moreover, would relegate all subsequent sorting for
all other columns to be based on the initial sorted column! Thus in adding a
unique identifier, the cache could distinguish which column in the ArrayList
it needs to sort, and in what order. You, of course can do away with caching
and it'll work just fine. To find out more about this technique read - Precise
.NET Server Content Caching.
In addition, for anyone concerned with constantly holding the cache object
in memory for too long, you can simply add to your Page_Load method the following
code:
| If Not Page.IsPostBack Then
MyDataGrid.CurrentPageIndex = 0
Cache.Remove("dgCache")
'Or for the ArrayList
Cache.Remove("dgCache" & ViewState("SortOrder").ToString())
End If |
This will insure that every page hit will result in a freshly cached DataGrid,
sorted from the top. What's more, the cache manager is good at discarding the
cached object if it feels the server needs the memory. Aside from this, there
are also some issues regarding caching with an attached variable for uniqueness.
There's also another point to be made for when and why to use the Session API
in lieu of the Cache API Object. Both do indeed have their advantages as well
as their disadvantages, so to make some sense of this have a look at my article
- Drilldown
DataGrid Searching with ASP.NET where I discuss this topic in greater
detail.
Returning now to the fun part. Now as soon as the Datareader
has been executed, we construct a new ArrayList called bkResults.
| Dim bkResults as New ArrayList() |
Next, as we're looping through the data we add to our Arraylist a new instance
of our DBInfo Structure Class, each representing
a row a data.
'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 |
We also insert our ArrayList into the Cache Object.
| Cache.Insert ("dgCache" & ColumnOrder, bkResults) |
Finally, we close out DataReader, and bind it to our DataGrid. And that's it.
'Close DataReader Connection
objDataReader.Close()
'Bind DataGrid from ArrayList
MyDataGrid.DataSource = bkResults |
We now have a DataGrid bound from a Datareader, where its DataSource
is a very lightweight and efficient ArrayList bound Structure Class, that's
cached, and also includes paging and bi-directional sorting. Pretty cool!
Now let's examine the same procedure, but this time implementing
our DataTable and DataView instead of an ArrayList.
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 |
In this instance
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!
Conclusion
In conclusion, we have established two very resource and memory friendly approaches
in binding a DataGrid, without sacrificing some of its more common features.
Both methods discussed are quick, fast, and not to mention agile, and in the
end, when judiciously used, you'll end up with some very scalable apps.
Until next time. Happy .NETing! </>