Solved

Pageing in a data reader

Posted on 2013-12-04
6
247 Views
Last Modified: 2014-01-12
Hello i have a dynamic table that i created in my VB.net web application and the data reader works but the data is massive. i wanted to know if there is a way that i can page my data  so maybe 300 records on each page number

CODE EXAMPLE
    ' builds the list for finaid
    ' builds the refund codes for the drop list
    Public Sub Build_FinAid()

        ' clears the table after postback
        _control(3).Controls.Clear()
        '''''''''''''' Add itesm to SQl server for faster runtime preformance
        Dim sql As New SqlCommand
        Dim conn As SqlConnection
        Dim dr As SqlDataReader
        Dim ORC As New Connect
        ''''''''''''''' Varables for detail codes''''''''''
        Dim DetailCode As String
        Dim Valid As String
        Dim ControlDy As New Dynamic
        Dim Sid As Integer
        ''''''''''''''' End varabe chunk''''''''''''''''''

        ' call the first sql setup
        conn = ORC.NewSQLConnectionWebAppT()
        sql.Connection = conn


        ''''''''''
        ''''''' ' Update the database and diaplay results to the ened user
        '''''''''
        t = New Table
        t.Width = Unit.Percentage(100)

        ' creates the table for the diplay 
        r = New TableRow
        r.VerticalAlign = VerticalAlign.Top
        r.CssClass = "StandardRowHeader"
        c = New TableHeaderCell
        c.Text = "Fin-Aid Code"
        r.Controls.Add(c)
        c = New TableHeaderCell
        c.Text = "Valid"
        r.Controls.Add(c)
        c = New TableHeaderCell
        c.Text = "Delete"
        r.Controls.Add(c)
        t.Controls.Add(r)

        ' *****
        ' ***** Get currebt drop list entry that was ran by and administrator
        ' *****

        sql.CommandText = "SELECT * FROM SDL_DetailCodes WHERE CodeType = 'FINAIDCodesExcludes'"
        dr = sql.ExecuteReader

        While dr.Read()
            DetailCode = dr.Item("Code")
            Valid = dr.Item("Valid")
            Sid = dr.Item("ID")
            r = New TableRow
            ct += 1
            If ct Mod 2 = 0 Then
                r.CssClass = "StandardRowEven"
            Else
                r.CssClass = "StandardRowOdd"
            End If

            Call CellAdd(r, DetailCode.ToString)
            Call CellAdd(r, Valid.ToString)
            Call Build_Controls.WebCellButtonNew(r, "RU" & Sid & "", "" & "Delete", AddressOf Delete_FinAid_Click)
            t.Controls.Add(r)
        End While

        ' *****
        ' ***** Include the "Add" row
        ' *****



        r = New TableRow
        If ct Mod 2 = 0 Then
            r.CssClass = "StandardRowEven"
        Else
            r.CssClass = "StandardRowOdd"
        End If
        Call ControlDy.WebCellTextBoxNew(r, "AddFinAid", "", , 10)
        Call ControlDy.WebCellButtonNew(r, _
                              "Addf", _
                              "Add Fin-Aid Code", _
                              AddressOf AddFinAid_Click, _
                              , _
                              0)


        t.Controls.Add(r)
        _control(4).Controls.Add(t)

        ' *****
        ' ***** Close Database
        ' *****
        dr.Close()
        sql.Dispose()
        conn.Close()
    End Sub

Open in new window

0
Comment
Question by:Chris Jones
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 39695586
As far as I know, there is no paging for Datareader. Why don't you use DataSet instead?
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 39695604
This is an older application a my company so not sure what all would be involved in converting my data readers to data sets. would i get ageing capabilities with the data sets ?
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 39695610
oh and my data reader i have some costume stuff that i am calling to the data such as link driven data add buttons for my table.
0
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 250 total points
ID: 39696224
Actually I just found couple of examples of implementing paging on DataReader, even though originally DataReader does not support paging:

http://www.moretechtips.net/2008/12/paging-in-aspnetvbnet-using-datareader.html

Datasets support paging but in your case it might be a better idea to customize paging for datareader
0
 
LVL 11

Accepted Solution

by:
SAMIR BHOGAYTA earned 250 total points
ID: 39700317
hi.. try this example

' Paging Params (1-based)
Dim PageSize As Integer = 10
Dim PageIndex As Integer = 3
' Create+Open Conn on SQL Server 2005 Express
Dim Conn As New SqlConnection("Data Source=127.0.0.1\sqlexpress,1433;Initial Catalog=DB;Persist Security Info=False;User ID=user;Password=pass;Network Library=dbmssocn")
Conn.Open()
'Create SQL Command
Dim Cmd As New SqlCommand()
Cmd.Connection = Conn
'Execute Count Query To calculate PageCount
Cmd.CommandText = "SELECT COUNT(*) FROM users where status=1"
Dim Count As Integer = Cmd.ExecuteScalar()
Dim PageCount As Integer = Math.Ceiling(Count / PageSize)
'Query Records
'If you forget to add 'Top PageIndex * PageSize', Performance degrades badly in large tables
Cmd.CommandText = "SELECT Top " & (PageIndex * PageSize) & " * FROM users where status=1"
Dim rdr As SqlDataReader = Cmd.ExecuteReader()

'Move Reader to start record
For I As Integer = 0 To ((PageIndex - 1) * PageSize) - 1
  If Not rdr.Read Then Exit For
Next
'Loop the desired Records
'if you didn't Add Top clause you will notice that all records in table are fetched!!
While rdr.Read()
  'Do Something
End While
rdr.Close()
rdr = Nothing
Conn.Close()
Conn = Nothing
Cmd = Nothing
0
 
LVL 1

Author Closing Comment

by:Chris Jones
ID: 39774819
thanks that worked quite well
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question