Library tutorials & articles
DataGrid/GridView Paging and Sorting Using A DataReader
By Dimitrios Markatos, published on 26 Jan 2006
ASP.NET 2.0 GridView Version
As an added bonus, I've included the updated .NET 2.0 GridView code below. It contains both the ArrayList and DataTable versions in binding a GridView from a DataReader. Enjoy!
<%@ 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">
'ArrayList setup
Dim dgCache, bkResults As ArrayList
'DataTable setup
'Dim dgCache As DataView
Sub Page_Load (Source As Object, E As EventArgs)
If Not Page.IsPostBack Then
'Reset GridView Page to the top
MyGridView.PageIndex = 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) 'ArrayList Version
'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 all connections
objDataReader.Close()
MyCommand.Dispose() : MyCommand = Nothing
MyConnection.Close() : MyConnection = Nothing
'Bind GridView from ArrayList
MyGridView.DataSource = bkResults
Else
'Bind GridView from Cached ArrayList
MyGridView.DataSource = dgCache
End If
MyGridView.DataBind()
'Clear ArrayList
bkResults = Nothing
End Sub
'Sub GetDataReader (ColumnOrder As String) 'DataTable Version
' 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;"
' Using MyConnection As New SQLConnection (strConn)
'
' 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
'
' '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
' MyGridView.DataSource = Source
'
' 'Close all connections
' End Using
' Else
'
' 'Assign Cached DataView new sort order
' dgCache.Sort = ViewState("SortOrder").ToString()
'
' 'Bind DataGrid from Cached DataView
' MyGridView.DataSource = dgCache
'
' End If
'
' MyGridView.DataBind()
'
'End Sub
Sub MyGridView_Page(sender As Object, e As GridViewPageEventArgs)
MyGridView.PageIndex = e.NewPageIndex
GetDataReader (ViewState("SortOrder").ToString())
End Sub
Sub MyGridView_Sort(sender As Object, e As GridViewSortEventArgs)
MyGridView.PageIndex = 0
GetDataReader(SortOrder(e.SortExpression.ToString()))
End Sub
</script>
<br><br>
<b>Dynamic GridView Paging and Sorting Using A DataReader Bound ArrayList</b>
<br>
<form runat="server">
<%= "Page: " & MyGridView.PageIndex+1 & " of " & (MyGridView.PageCount) %>
<br><br>
<ASP:GridView id="MyGridView" 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"
OnSorting="MyGridView_Sort"
OnPageIndexChanging="MyGridView_Page">
<Columns>
<asp:BoundField DataField="SupplierID" SortExpression="SupplierID asc" HeaderText="SupplierID"/>
<asp:BoundField DataField="CompanyName" SortExpression="CompanyName asc"
HeaderText="CompanyName"/>
<asp:BoundField DataField="ContactName" HeaderText="ContactName"
SortExpression="ContactName asc"/>
<asp:BoundField DataField="Country" HeaderText="Country"
SortExpression="Country asc"/>
</Columns>
</asp:GridView>
</form>
</body>
</html>
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 .