Datareader does not work but Dataset does

[Rnes]

Has anyone run into a problem where a datareader does not return any
data but the same code with a dataset does return data ?

I.E.

This code DOES NOT return any results

Dim dr As SqlDataReader
dr = SqlHelper.ExecuteReader(_connectionStringRead,
CommandType.StoredProcedure, "GetTopDocumentStatus")

This code DOES return data to the dataset
Dim ds As New DataSet
ds = SqlHelper.ExecuteDataset(_connectionStringRead,
CommandType.StoredProcedure, "GetTopDocumentStatus")

I would like to use the datareader instead of the dataset, but for
some reason no results are comming back. Any ideas?

 

[Jon Skeet [C# MVP]]

Has anyone run into a problem where a datareader does not return any
data but the same code with a dataset does return data ?

This code DOES NOT return any results

Dim dr As SqlDataReader
dr = SqlHelper.ExecuteReader(_connectionStringRead,
CommandType.StoredProcedure, "GetTopDocumentStatus")

What exactly is SqlHelper in this case, and how are you determining
that it isn't returning any data?

Jon

 

[Rnes]

What exactly is SqlHelper in this case, and how are you determining
that it isn't returning any data?

Jon

SqlHelper is from Microsolft.ApplicationBlocks.Data

There is no data in the datadreader after the command is executed ...

In the "Command Window" in debug mode I look at the results here is
what it looks like when I try and read "DR" ...
? dr.read()
False

and

? dr
{System.Data.SqlClient.SqlDataReader}
Depth: 0
FieldCount: 8
HasRows: True
IsClosed: False
Item: <cannot view indexed property>
RecordsAffected: -1

But if I look in the Dataset (DS) ...

? ds.tables(0).rows(0).itemarray
{Length=8}
(0): {System.Guid}
(1): 900 {Integer}
(2): "Waiting"
(3): "0033000"
(4): "EDI837I_0033000_3mg.txt"
(5): #9/20/2007 2:46:38 PM#
(6): #9/20/2007 2:46:11 PM#
(7): False {Boolean}

 

[sloan]

To use the datareader, you gotta loop on it, and do something with it.

IDataReader doesn't have all the info, until after you start looping.

I have a downloadable example here:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry


DataSet is "in memory" meaning, you can see all the tables/rows at once.

IDataReader... you gotta call the .Read() method before you start seeing the
data.

Get my downloadable example code, and you'll see it in action in a few
minutes.

The example uses the SqlHelper (DAAB 2.0) as well.

 

[sloan]

//quote
{System.Data.SqlClient.SqlDataReader}
Depth: 0
FieldCount: 8
HasRows: True
IsClosed: False
Item: <cannot view indexed property>
RecordsAffected: -1
// end quote


Exactly. This is what a IDataReader is.

You have rows (HasRows = true)
RecordsAffected -1 means you have to start looping.

You need to research more about what an IDataReader is.
You will NEVER all have the rows in memory at once.

This is the PRECISE reason the IDataReader is a lighter object. Because you
only have 1 row at a time.

.........

Get my sample, it shows this completely. How to use an IDataReader, even
how to have multiple ResultSet's in 1 datareader.

 

[Rnes]

To use the datareader, you gotta loop on it, and do something with it.

IDataReader doesn't have all the info, until after you start looping.

I have a downloadable example here:http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry

DataSet is "in memory" meaning, you can see all the tables/rows at once.

IDataReader... you gotta call the .Read() method before you start seeing the
data.

Get my downloadable example code, and you'll see it in action in a few
minutes.

The example uses the SqlHelper (DAAB 2.0) as well.









- Show quoted text -

When I use the Read() method, there is no data. Please check out my
reply to Jon, I must have posted that reply just as you posted yours.

I looked at the link you provided. I do not see any examples of using
a Datareader. The title of the article that the link takes me to is
"Custom Objects and Tiered Development II // 2.0". Is this the
correct link?

Whats a little strange is that this code was working before, now its
not. I have made a lot of changes to my application, but none related
to the data access code.

Thanks for you time with this.

 

[Rnes]

//quote
{System.Data.SqlClient.SqlDataReader}
Depth: 0
FieldCount: 8
HasRows: True
IsClosed: False
Item: <cannot view indexed property>
RecordsAffected: -1
// end quote

Exactly. This is what a IDataReader is.

You have rows (HasRows = true)
RecordsAffected -1 means you have to start looping.

You need to research more about what an IDataReader is.
You will NEVER all have the rows in memory at once.

This is the PRECISE reason the IDataReader is a lighter object. Because you
only have 1 row at a time.

........

Get my sample, it shows this completely. How to use an IDataReader, even
how to have multiple ResultSet's in 1 datareader.















- Show quoted text -

I under stand that. I inherited this code several years ago, its been
working just fine until recently. Here is a better example of whats
happening...

Program A...

Dim dbAccess As DataAccess.Status = New DataAccess.Status
Dim dbReader As SqlDataReader

DO
dbReader = dbAccess.RetrieveWaiting()

If dbReader Is Nothing Then <-- This fails (like it
should) and goes to the Else stmt.
Exit Do
Else
If (dbReader.Read()) Then <-- This is always false

.... Process data here ...

Else
Exit Do ' No more records in Waiting status
End If
End If
LOOP


Public Class Status
Public Function RetrieveWaiting() As SqlDataReader
Dim dr As SqlDataReader
dr = SqlHelper.ExecuteReader(_connectionStringRead,
CommandType.StoredProcedure, "GetTopDocumentStatus")
Return dr
End Function

Thanks for your time.

 

[sloan]

It's there. Either do a global search for "IDataReader" or go here

namespace GranadaCoder.Applications.TieredObjectEx.BusinessLayer.Controllers
..
CustomerController (class)
SerializeCustomers (method)


there is 1 of 2 things going on here.

either you're using the datareader incorrectly
Or
you're query doesn't bring back any results.

if you're absolutely sure you using the same query/stored procedure as the
dataset one, (AND using the same parameters), then you're left with the
first thing.

Also, look in your stored procedure code (if you're using a stored
procedure)

Make sure you don't have multiple resultsets.

Something as simple as

Select @MyVariable

is considered a resultset.

This is not considered a resultset

Select @MyVariable = count(*) from dbo.MyTable

...

The best way to "see" the Results sets is to turn on "Results to Grid"
(inside of Query Analyser or Studio Management).
If it comes back in a Grid, then it is a ResultSet.


.......................
If I run this code in Query Analyser

declare @MyVar int

select @MyVar = count(*) from Person.Contact

select * from Person.Contact

select @MyVar As C



........

There are 2 resultsets from that code.

 

[sloan]

Give this code a shot:


Private Function TestAnIDataReader(ByVal dataReader As IDataReader)
As ArrayList
Dim coll As ArrayList = New ArrayList()
Try
While dataReader.Read()
If Not (dataReader.IsDBNull(0)) Then


If Not (dataReader.IsDBNull(0)) Then
Dim o1 As Object = dataReader.GetValue(0)
End If

If Not (dataReader.IsDBNull(1)) Then
Dim o2 As Object = dataReader.GetValue(0)
End If

If Not (dataReader.IsDBNull(2)) Then
Dim o3 As Object = dataReader.GetValue(2)
End If

coll.Add(o1)
End If
End While


End Try
'no catch here... see
http://blogs.msdn.com/brada/archive/2004/12/03/274718.aspx
Finally
{
If Not ((dataReader = Nothing)) Then
Try
dataReader.Close()
End Try
End If
}
End Function


this is not production type code, esp where I have "dim o1 as object".
This is debugging code. The GetValue returns an object, which is good for
debugging.
Put a watch on o1, o2 and o3. You should see something.


Read my other post about the "Select @MyVar", esp if the code has been
working, and is not working now.
Someone may have changed the stored procedure on you.

 

[Rnes]

Give this code a shot:

Private Function TestAnIDataReader(ByVal dataReader As IDataReader)
As ArrayList
Dim coll As ArrayList = New ArrayList()
Try
While dataReader.Read()
If Not (dataReader.IsDBNull(0)) Then

If Not (dataReader.IsDBNull(0)) Then
Dim o1 As Object = dataReader.GetValue(0)
End If

If Not (dataReader.IsDBNull(1)) Then
Dim o2 As Object = dataReader.GetValue(0)
End If

If Not (dataReader.IsDBNull(2)) Then
Dim o3 As Object = dataReader.GetValue(2)
End If

coll.Add(o1)
End If
End While

End Try
'no catch here... seehttp://blogs.msdn.com/brada/archive/2004/12/03/274718.aspx
Finally
{
If Not ((dataReader = Nothing)) Then
Try
dataReader.Close()
End Try
End If
}
End Function

this is not production type code, esp where I have "dim o1 as object".
This is debugging code. The GetValue returns an object, which is good for
debugging.
Put a watch on o1, o2 and o3. You should see something.

Read my other post about the "Select @MyVar", esp if the code has been
working, and is not working now.
Someone may have changed the stored procedure on you.

























- Show quoted text -

OK, I found the problem.

Stupid error on my part. I had a dr.read() in my watch window while
debugging. What this did was to read the data from the datareader, so
now the data is gone out of the datareader. That is why it was empty
when I was trying to process the data from the datareader. I took out
the dr.read() in the debugging watch window and now its working just
fine.

Thanks for the link about the try catch finally. Makes for
interesting reading. Looks like I need to go thru some of my try
catch bocks. What I have noticed is that sometimes in my application
we have our try catch blocks just like you outlined, but not in all
places.

Thanks again for your time.

 

[sloan]

Glad you figured it out.

Yeah, if you have 1 row, then .Read() only works one time. This (again) is
what makes an IDataReader very lightweight.


I don't understand that DO thing in your vb.net code either. I'd try to
make the code look more like mine.
Clean that stuff up while you're in there.


........

If you only have 1 row, and 1 value you're trying to find...
ExecuteScalar is a better option.

Like, if you wanted to know how many employees there are in the db.

uspEmployeeGetTotalCount
select count(*) as EmpCount from dbo.Employee

Notice, its ONE single value.

That is what ExecuteScalar is all about.

..........

 

[Rnes]

Glad you figured it out.

Yeah, if you have 1 row, then .Read() only works one time. This (again) is
what makes an IDataReader very lightweight.

I don't understand that DO thing in your vb.net code either. I'd try to
make the code look more like mine.
Clean that stuff up while you're in there.

.......

If you only have 1 row, and 1 value you're trying to find...
ExecuteScalar is a better option.

Like, if you wanted to know how many employees there are in the db.

uspEmployeeGetTotalCount
select count(*) as EmpCount from dbo.Employee

Notice, its ONE single value.

That is what ExecuteScalar is all about.

.........










- Show quoted text -

The SProc will bring back only one record. There could be more
records on the table, but most of the time there is only one. The
"DO" loop makes sure that there are not any more records that meet the
criteria. This is a batch process (that runs once a minute), and
while I am processing one record another record could have been added
to the database, so it does another read to see if there are anymore.

Thanks for the "ExecuteScalar" idea. I will look into it. I have
been trying to figure out if I should keep it the way it was written,
or change it to bring back all records that meet the criteria into a
dataset then process the dataset.

 

[Cor Ligthert[MVP]]

Sloan,

What do you mean with lightweight, I never had a class on a balance.

That is has less code than a DataAdapter is of course for sure, because a
DataAdapter is in fact nothing more than a class that does all the work for
you and uses a datareader to get the resultset.

If you only want one value then you can better use the execute.scalar, that
is you terms even more lightweight then the datareader.

Cor

 

[sloan]

http://aspnet.4guysfromrolla.com/articles/050405-1.aspx

It consumes less memory than the DataSet, and depending upon the number of
rows, can have signficant performance implications.

//Quote from URL
According to A Speed Freak's Guide to Retrieving Data in ADO.NET, the
DataReader is roughly thirty times more performant than the DataSet. For
large amounts of data being brought back - several hundred or several
thousand records - the absolute time differences between accessing data with
these two objects can be quite pronounced.
//End Quote

My 3:39 EST post also mentions the ExecuteScalar option.