Members

Technology Zones

IBM Learning Center

Articles

Hosted By

MaximumASP

Info
Rated
Read 107,354 times
5 comments

Contents

Related Categories

DataGrid/GridView Paging and Sorting Using A DataReader - ASP.NET 2.0 GridView Version

DMarko1

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 desktop applications. Till now Jimmy has authored nearly two dozen .NET articles, published on Dot Net Junkies, 4 Guys From Rolla, Sitepoint, MSDN Academic Alliance, Developers.NET, The Official Microsoft ASP.NET Site, and here on Developer Fusion, covering various unique and advanced techniques on .NET.

Comments

Read Comments | Post Comment