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
Solved

Pageing in a data reader

Posted on 2013-12-04
6
244 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
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:…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

856 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