Solved

Pageing in a data reader

Posted on 2013-12-04
6
245 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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Adding items to a C# list incrementally 5 58
Why use this lambda? 12 60
Filtering a datagrid view 8 34
Include MS Database in Winforms Project using Intellilock 5 21
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 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