[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Pageing in a data reader

Posted on 2013-12-04
6
Medium Priority
?
249 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
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!

 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 1000 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 1000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

649 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