Solved

Pageing in a data reader

Posted on 2013-12-04
6
241 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now