.NET Framework - DataReader record count

Asked By David C on 30-Sep-09 09:10 AM
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" & _

Dim cmdSel As SqlCommand = New SqlCommand(strSQL, conData)
Dim dtr As SqlDataReader = cmdSel.ExecuteReader()
Return dtr
End Function




bruce barker replied on 30-Sep-09 09:30 AM
because datareaders do a "firehose" read, the record count is not known
until all rows are read.

-- bruce (sqlwork.com)
Mark Rae [MVP] replied on 30-Sep-09 09:30 AM
--
Mark Rae
ASP.NET MVP
http://www.markrae.net
Mark Rae [MVP] replied on 30-Sep-09 09:37 AM
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 did not 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
David C replied on 30-Sep-09 10:15 AM
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
Mark Rae [MVP] replied on 30-Sep-09 10:26 AM
Yes, that would be fine but, with respect, that is not what you originally
asked (see title of thread)...

By the time you come to count the Items collection of the DropDownList
webcontrol, the DataReader used to populate that Items collection through
databinding is no longer useable...


--
Mark Rae
ASP.NET MVP
http://www.markrae.net
Gregory A. Beamer replied on 30-Sep-09 11:23 AM
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 replied on 30-Sep-09 11:35 AM
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!             |
*******************************************
Mark Rae [MVP] replied on 30-Sep-09 11:43 AM
Yes, but then the DataReader itself is no longer of any use...


--
Mark Rae
ASP.NET MVP
http://www.markrae.net
Gregory A. Beamer replied on 30-Sep-09 12:09 PM
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!             |
*******************************************
David C replied on 30-Sep-09 03:28 PM
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
Mark Rae [MVP] replied on 30-Sep-09 03:35 PM
Yes, based on what you have said so far...


--
Mark Rae
ASP.NET MVP
http://www.markrae.net
Gregory A. Beamer replied on 30-Sep-09 04:55 PM
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!             |
*******************************************