Library tutorials & articles
DataGrid/GridView Paging and Sorting Using A DataReader
- Introduction
- Ways to bind a DataGrid
- DataGrid Paging and Sorting using a DataReader and Structure Class/ArrayList
- What's doing above?
- DataGrid Paging and Sorting using a DataReader with a DataTable/DataView
- ASP.NET 2.0 GridView Version
- Conclusion
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!
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...
Related articles
Related discussion
-
hey developers out there
by pitsophera (0 replies)
-
High-Performance .NET Application Development & Architecture
by Manjot Bawa (0 replies)
-
An Introduction to VB.NET and Database Programming
by carlosmen (14 replies)
-
Using ADO.NET with SQL Server
by Manjot Bawa (23 replies)
-
HI All ! Request for some information on "Open Insight".
by PaulE (3 replies)
Related podcasts
-
ADO.NET Data Services in .NET 3.5 Service Pack 1 Beta1 with ASP.NET AJAX
Wally walks through using ASP.NET Podcast Show #114 - ADO.NET Data Services in .NET 3.5 Service Pack 1 Beta1 with ASP.NET AJAX.
Events coming up
-
Mar
15
DevWeek 2010
London, United Kingdom
DevWeek is Europe’s leading independent conference for software developers, database professionals and IT architects, and features expert speakers on a wide range of topics, including .NET 4.0, Silverlight 3, WCF 4, Visual Studio 2010, REST, Windows Workflow 4, Thread Synchronization, ASP.NET 4.0, SQL Server 2008 R2, LINQ, Unit Testing, CLR & C# 4.0, .NET Patterns, WPF 4, F#, Windows Azure, ADO.NET, Entity Framework, Debugging, T-SQL Tips & Tricks, and more.
The info is really helpful to the beginers. Thanks alot it saves my alot of time. -Shailu
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 IfYeah 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 .