DataReader record count

Discussion in 'ASP .Net' started by David C, Sep 30, 2009.

  1. David C

    David C Guest

    I have the code below that I use to fill a DropDownList control. I would
    like to do something different when it returns more than 1 record. Can I do
    this here or back at the code where I call this class function? Thanks.

    David
    Public Shared Function GetStaffPrograms(ByVal intStaffID As Int32) As
    SqlDataReader
    Dim conData As SqlConnection = New
    SqlConnection(ConfigurationManager.ConnectionStrings("CoreConnectionString").ConnectionString)
    conData.Open()

    Dim strSQL As String

    strSQL = "SELECT ProgramID, ProgramName" & _
    " FROM dbo.vw_StaffPrograms" & _
    " WHERE StaffID = " & intStaffID.ToString

    Dim cmdSel As SqlCommand = New SqlCommand(strSQL, conData)
    Dim dtr As SqlDataReader = cmdSel.ExecuteReader()
    Return dtr
    End Function
    David C, Sep 30, 2009
    #1
    1. Advertising

  2. David C

    bruce barker Guest

    because datareaders do a "firehose" read, the record count is not known
    until all rows are read.

    -- bruce (sqlwork.com)

    David C wrote:
    > I have the code below that I use to fill a DropDownList control. I would
    > like to do something different when it returns more than 1 record. Can I do
    > this here or back at the code where I call this class function? Thanks.
    >
    > David
    > Public Shared Function GetStaffPrograms(ByVal intStaffID As Int32) As
    > SqlDataReader
    > Dim conData As SqlConnection = New
    > SqlConnection(ConfigurationManager.ConnectionStrings("CoreConnectionString").ConnectionString)
    > conData.Open()
    >
    > Dim strSQL As String
    >
    > strSQL = "SELECT ProgramID, ProgramName" & _
    > " FROM dbo.vw_StaffPrograms" & _
    > " WHERE StaffID = " & intStaffID.ToString
    >
    > Dim cmdSel As SqlCommand = New SqlCommand(strSQL, conData)
    > Dim dtr As SqlDataReader = cmdSel.ExecuteReader()
    > Return dtr
    > End Function
    >
    >
    bruce barker, Sep 30, 2009
    #2
    1. Advertising

  3. David C

    David C Guest

    "Mark Rae [MVP]" <> wrote in message
    news:%...
    > "David C" <> wrote in message
    > news:...
    >
    >> I would like to do something different when it returns more than 1
    >> record. Can I do this here or back at the code where I call this class
    >> function?

    >
    > Neither. A DataReader is not the same as a DataTable. With a DataReader,
    > all you can do is read from beginning to end, and you can only do that
    > once. This makes it much more efficient in certain circumstances than a
    > DataTable, but the trade-off is the lack of support for functionality like
    > that which you want here. In fact, in early versions of .NET DataReaders
    > didn't even have a .HasRows property - essentially, you had to trap the
    > error caused by the .Read() method...
    >
    > You could, I suppose, read all of the records out of the DataReader into
    > another type of storage, but this will almost certainly be very
    > inefficient.
    >
    > I'd simply use a DataTable. Then you can examine its Rows.Count property,
    > move backwards as well as forwards through its records, read it as many
    > times as you like etc...
    >
    > DataReader vs DataTable / DataSet is an interesting debate...
    >
    >
    > --
    > Mark Rae
    > ASP.NET MVP
    > http://www.markrae.net


    What if I used something like below at the "calling" page, where ddl =
    DropDownList control?

    If ddl.Items.Count > 1 Then
    ddl.Items.Insert(0, New ListItem("", "0"))
    ddl.SelectedValue = "0"
    End If

    Thanks.
    David
    David C, Sep 30, 2009
    #3
  4. "David C" <> wrote in
    news::

    > I have the code below that I use to fill a DropDownList control. I
    > would like to do something different when it returns more than 1
    > record. Can I do this here or back at the code where I call this
    > class function? Thanks.


    You can get record count first and then run the DataReader. Or, you can
    curse through all of the records and get a record count. Or, you can use a
    DataTable instead. But you cannot get the count at the beginning of a
    DataReader, as it is a stream, not a recordset.

    Peace and Grace,

    --
    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA

    Twitter: @gbworld
    Blog: http://gregorybeamer.spaces.live.com


    *******************************************
    | Think outside the box! |
    *******************************************
    Gregory A. Beamer, Sep 30, 2009
    #4
  5. "David C" <> wrote in
    news:Ohve$:

    >
    > "Mark Rae [MVP]" <> wrote in message
    > news:%...
    >> "David C" <> wrote in message
    >> news:...
    >>
    >>> I would like to do something different when it returns more than 1
    >>> record. Can I do this here or back at the code where I call this
    >>> class function?

    >>
    >> Neither. A DataReader is not the same as a DataTable. With a
    >> DataReader, all you can do is read from beginning to end, and you can
    >> only do that once. This makes it much more efficient in certain
    >> circumstances than a DataTable, but the trade-off is the lack of
    >> support for functionality like that which you want here. In fact, in
    >> early versions of .NET DataReaders didn't even have a .HasRows
    >> property - essentially, you had to trap the error caused by the
    >> .Read() method...
    >>
    >> You could, I suppose, read all of the records out of the DataReader
    >> into another type of storage, but this will almost certainly be very
    >> inefficient.
    >>
    >> I'd simply use a DataTable. Then you can examine its Rows.Count
    >> property, move backwards as well as forwards through its records,
    >> read it as many times as you like etc...
    >>
    >> DataReader vs DataTable / DataSet is an interesting debate...
    >>
    >>
    >> --
    >> Mark Rae
    >> ASP.NET MVP
    >> http://www.markrae.net

    >
    > What if I used something like below at the "calling" page, where ddl =
    > DropDownList control?
    >
    > If ddl.Items.Count > 1 Then
    > ddl.Items.Insert(0, New ListItem("", "0"))
    > ddl.SelectedValue = "0"
    > End If
    >


    Yes, that would be another option, leaving:

    1. Use a DataTable
    2. Select the Count before instantiating the reader
    3. Run through a reader twice
    4. Insert value in DropDownList if count is greater than 1

    But you are solving the "real" problem, which is not what you stated the
    problem was in your question. It is still a useful exercise, as asking
    led you to the real problem and removed the confusion of mixing the
    problem with a proposed solution.

    peace and grace,



    --
    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA

    Twitter: @gbworld
    Blog: http://gregorybeamer.spaces.live.com

    *******************************************
    | Think outside the box! |
    *******************************************
    Gregory A. Beamer, Sep 30, 2009
    #5
  6. "Mark Rae [MVP]" <> wrote in news:uW0ZUTeQKHA.4028
    @TK2MSFTNGP05.phx.gbl:

    >
    >> Or, you can curse through all of the records and get a record count.

    >
    > Yes, but then the DataReader itself is no longer of any use...


    Actually, I was trying to point out that you had to build it twice to get a
    count, which is a waste, but I guess I got a big FAIL on illustrating that.
    ;-)

    Peace and Grace,

    --
    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA

    Twitter: @gbworld
    Blog: http://gregorybeamer.spaces.live.com

    *******************************************
    | Think outside the box! |
    *******************************************
    Gregory A. Beamer, Sep 30, 2009
    #6
  7. David C

    David C Guest

    "Gregory A. Beamer" <> wrote in message
    news:Xns9C966953C8595gbworld@207.46.248.16...
    > "David C" <> wrote in
    > news::
    >
    >> I have the code below that I use to fill a DropDownList control. I
    >> would like to do something different when it returns more than 1
    >> record. Can I do this here or back at the code where I call this
    >> class function? Thanks.

    >
    > You can get record count first and then run the DataReader. Or, you can
    > curse through all of the records and get a record count. Or, you can use a
    > DataTable instead. But you cannot get the count at the beginning of a
    > DataReader, as it is a stream, not a recordset.
    >
    > Peace and Grace,
    >
    > --
    > Gregory A. Beamer
    > MVP; MCP: +I, SE, SD, DBA
    >
    > Twitter: @gbworld
    > Blog: http://gregorybeamer.spaces.live.com
    >
    >
    > *******************************************
    > | Think outside the box! |
    > *******************************************


    Yes, I am sorry for the confusion. I will need both situations in the web
    project. Sometimes I need to do something at the (App_Code) class level and
    sometimes at the page code-behind due to different circumstances. I would
    guess that using a DataTable would allow me to do what I need at either
    location. Am I correct? Thank you all.

    David
    David C, Sep 30, 2009
    #7
  8. "David C" <> wrote in
    news::

    > Yes, I am sorry for the confusion. I will need both situations in the
    > web project. Sometimes I need to do something at the (App_Code) class
    > level and sometimes at the page code-behind due to different
    > circumstances. I would guess that using a DataTable would allow me to
    > do what I need at either location. Am I correct? Thank you all.


    If you want a count attached at all times, you need to move to a
    construct that keeps the count. A DataTable works, if you are using
    DataSets. You can also choose to use LINQ to SQL, which can defer the
    actual execution. Entity Framework is another choice.

    The end story is that the DataReader is simply a stream, or to use a
    database term, a firehose cursor. It is opened up and you pull items
    from the stream much like a queue. Once you have pulled everything, you
    can count what you pulled. Until then, the system is unaware of what is
    there.

    Underneath the hood, a DataSet's DataTable is filled using a DataReader,
    which kind of gives you a hint at the relationship.

    Peace and Grace,


    --
    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA

    Twitter: @gbworld
    Blog: http://gregorybeamer.spaces.live.com

    *******************************************
    | Think outside the box! |
    *******************************************
    Gregory A. Beamer, Sep 30, 2009
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Derek LaZard

    Re: Abt Datareader Count

    Derek LaZard, Jul 8, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    1,737
    Derek LaZard
    Jul 8, 2003
  2. Replies:
    1
    Views:
    1,522
  3. Replies:
    0
    Views:
    436
  4. Replies:
    0
    Views:
    418
  5. Kevin
    Replies:
    2
    Views:
    9,174
    Kevin
    Dec 18, 2003
Loading...

Share This Page