DataReader record count

[David C]

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]

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

-- bruce (sqlwork.com)

 

[David C]

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...

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

 

[Gregory A. Beamer]

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]

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]

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,

 

[David C]

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,

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

 

[Gregory A. Beamer]

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,