DataGrid/GridView Paging and Sorting Using A DataReader

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.

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.

“A computer is a stupid machine with the ability to do incredibly smart things, while computer programmers are smart people with the ability to do incredibly stupid things. They are, in short, a perfect match” - Bill Bryson