Library tutorials & articles
DataGrid/GridView Paging and Sorting Using A DataReader
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 deskto...
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.
Related articles
Related discussion
-
An Introduction to VB.NET and Database Programming
by yen (12 replies)
-
Very slow inserts using SqlCommand.ExecuteNonQuery()
by porchelvi (1 replies)
-
Compatibility Issue on Firefox to display on Cursor Location
by ansari.wajid (0 replies)
-
Windows Services in .NET 2.0
by anand.lv (2 replies)
-
VB.NET Type 'SqlDatabaseException' not defined
by Mulish Mehdi (1 replies)
Related podcasts
-
CodeCast Episode 4: State of .NET, IE8, ASP.NET MVC, and O'Reilly Media
CodeCast Episode 4: State of .NET, IE8, ASP.NET MVC, and O'Reilly MediaHosts Ken Levy and Markus Egger discuss the new State of .NET events, IE8, ASP.NET MVC, followed by an interview from PDC with two editors from O'Reilly Media. More on ASP.NET MVC can be found at http://asp.net/mvc. Interview...
Related jobs
-
Microsoft .Net Architect
in AMSTERDAM (€50K-€90K per annum)
Events coming up
-
Dec
6
Developing AJAX Web Applications with Castle Monorail
London, United Kingdom
Monorail is the model-view-controller engine of the Castle Project, bringing many of the best ideas of Ruby on Rails to the .NET world. In this talk, David De Florinier and Gojko Adzic show how Monorail makes it easy to develop .NET based AJAX applications, and how to use the Castle Project to build Web 2.0 applications effectively. Come to this session if you are a .NET web developer. Everyone is welcome!
Hi all,
What about displaying, sorting etc' data in a DataGird/GridView with a paging mechanism using a scrollbar (not a Next/Previous buttons)??
It seems like no one has ever mention this issue.
Can anyone please help me solve this problem.
Regards ido
this site is really very good. i m really a big fan of this site thanks to all
developerfusion team
jitendra negi
Dimitrios thanks for the informative article. I have picked your code apart and tried out a few variants of your sample code which I am looking to implement into some of my web apps.
I tried a code sample in a web page that used “Data Grid Paging Using a Data Reader Bound Array List” – no caching or dynamic sorting.
This method still reads all the data even though a small fraction is only displayed on the page. This works well but when I blew the table up to 100,000 records (I was using MS Access in this experiment) it slowed down somewhat. Admittedly it was substantially faster than a similar sample of code using a dataset on the same database (as expected).
My question is;
Is there any way that straight forward data reader + data grid paging can be implemented in such a way that it will only get the records from the database that the webapp actually displays on the page? Any suggestions, directions or web links would be appreciated.
I'm working on an app that implements a structure and stores a recordset in an ArrayList based upon the example outlined here. I used the same to bind to a DataGrid and it worked out so well, I thought it might be a better solution that moving back and forth through a DataSet to perform some simple though numerous calculations.
In order to perform these calcs, I have to compare elements of the array checking for duplicates on some columns and then just comparing other elements to strings utilizing IndexOf and LastIndexOf. Unfortunately I haven't been able to get it to work. On another site I read that those 2 methods use Object.Equals and as the array contains structure objects, I need to override the Equals function. That route has been just as unsuccessful.
The code below generates an that the index is out of range on the line that assigns the array item to 'last' because the LastIndexOf search returns -1. Thing is the stored procedure I use to retrieve the recordset guarantees that there would be a match on first.account. Can anyone point me in the right direction?
Public Overrides Function Equals(ByVal obj As Object) As Boolean If obj Is Nothing Or Not Me.GetType() Is obj.GetType() Then Return False End If Dim d As duplicates = CType(obj, duplicates) Return Me.account = d.account End Function'Errant code below
conn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader() If reader.HasRows Then 'Create array to hold entries for accounts tracked more than once per LOB Dim arrResults As New ArrayList Dim dupEntries As New duplicates While reader.Read With arrResults.Add(
New duplicates(reader.GetValue(0), reader.GetValue(1), reader.GetValue(2), _reader.GetValue(3), reader.GetValue(4), reader.GetValue(5), reader.GetValue(6), _
reader.GetValue(7), reader.GetValue(8), reader.GetValue(9), reader.GetValue(10), _
reader.GetValue(11), reader.GetValue(12), reader.GetValue(13), reader.GetValue(14)))
End With End Whilereader.Close()
Dim first As duplicates Dim last As duplicates Dim i As Integer For i = 0 To arrResults.Count - 1first =
CType(arrResults.Item(i), duplicates)Trace.Warn(arrResults.LastIndexOf(first.account))
last = arrResults.Item(arrResults.LastIndexOf(first.account))
Trace.Warn(
"first date" & first.tDate)Trace.Warn(
"last date " & last.tDate) Next End IfI've been searching around, but haven't been able to figure out how to access the individual values. For example, how would I determine CompanyName for a particular row?
Yeah thanks. No, it does absolutely work, regardless, and even though the "AllowCustomPaging" property isn't part of the Gridview, it does not affect it, as I just copied the code from the article and it ran perfectly. The Gridview simply ignores it, whether set to "True" or "False"
I would check and make sure it's not something else you may have modified.
Hope this helps
Good article overall. But the Gridview version cannot work. One problem is it shows the following property:
AllowCustomPaging="False"
that is not available in Gridview.
Hello...
Can anybody plis post the C# version?
Thx in advance...
Sup everyone...
I have a datagrid and I want to implement a button that will simulate the datagrid scroll down click button. What events do I need to handle? How to make my datagrid rows to scroll once my button is pressed?
Hello
The DataReaders read one-way data As I undesrtand you dynamically bind your Oracle Database to your Datagird. Try to convert oracleDataReader to DataView. DataViews can be red 2 way and they are Compatible with AllowPaging=true
I have recently sent a Control to Asp.Net I think it is in the proccessing stage that Control converts OdbcDataReader to DataView below is the open code for you to give you an idea. You must apply the same logic to your Oracle Reader
Good Luck,
Baris ERGUN
www.thecoreopsis.com
public static DataView ConvertToDataView(OdbcDataReader setToCheck, string tableName)
{
DataTable dataReaderTable = new DataTable(tableName);
try
{
for(int h=0;h<setToCheck.FieldCount;h++)
{
DataColumn temp = new DataColumn(setToCheck.GetName(h),setToCheck.GetFieldType(h));
dataReaderTable.Columns.Add(temp);
}
while(setToCheck.Read())
{
DataRow dr = dataReaderTable.NewRow();
for(int g=0;g<setToCheck.FieldCount;g++)
{
dr![Gift [G]](/emoticons/emotion-51.gif)
= setToCheck.GetValue(setToCheck.GetOrdinal(setToCheck.GetName(g)));
}
dataReaderTable.Rows.Add(dr);
}
return dataReaderTable.DefaultView;
}
catch
{
return null;
}
}
This thread is for discussions of DataGrid/GridView Paging and Sorting Using A DataReader .