Datareader does not work but Dataset does

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?

Re: Datareader does not work but Dataset does

On Oct 8, 4:57 pm, Rnes wrote:
> 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

Re: Datareader does not work but Dataset does

On Oct 8, 8:59 am, "Jon Skeet [C# MVP]" wrote:
> On Oct 8, 4:57 pm, Rnes wrote:
>
> > 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

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:
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}

Re: Datareader does not work but Dataset does

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!1 40.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.





"Rnes" wrote in message
news:1191859066.608602.39710@50g2000hsm.googlegroups.com...
> 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?
>

Re: Datareader does not work but Dataset does

//quote
{System.Data.SqlClient.SqlDataReader}
Depth: 0
FieldCount: 8
HasRows: True
IsClosed: False
Item:
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.





"sloan" wrote in message
news:%23HJ3wZcCIHA.464@TK2MSFTNGP02.phx.gbl...
>
> 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!1 40.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.
>
>
>
>
>
> "Rnes" wrote in message
> news:1191859066.608602.39710@50g2000hsm.googlegroups.com...
>> 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?
>>
>
>

Re: Datareader does not work but Dataset does

On Oct 8, 9:15 am, "sloan" wrote:
> 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!A68482B9628A8 42A!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.
>
> "Rnes" wrote in message
>
> news:1191859066.608602.39710@50g2000hsm.googlegroups.com...
>
>
>
> > 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?- Hide quoted text -
>
> - 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.

Re: Datareader does not work but Dataset does

On Oct 8, 9:54 am, "sloan" wrote:
> //quote
> {System.Data.SqlClient.SqlDataReader}
> Depth: 0
> FieldCount: 8
> HasRows: True
> IsClosed: False
> Item:
> 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.
>
> "sloan" wrote in message
>
> news:%23HJ3wZcCIHA.464@TK2MSFTNGP02.phx.gbl...
>
>
>
>
>
> > 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.
>
> > "Rnes" wrote in message
> >news:1191859066.608602.39710@50g2000hsm.googlegroups.com...
> >> 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?- Hide quoted text -
>
> - 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.

Re: Datareader does not work but Dataset does

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

namespace GranadaCoder.Applications.TieredObjectEx.BusinessLayer.Contr ollers
..
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.


"Rnes" wrote in message
news:1191862833.715525.324950@o3g2000hsb.googlegroups.com...
> On Oct 8, 9:15 am, "sloan" wrote:
>> 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!A68482B9628A8 42A!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.
>>
>> "Rnes" wrote in message
>>
>> news:1191859066.608602.39710@50g2000hsm.googlegroups.com...
>>
>>
>>
>> > 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?- Hide quoted
>> > text -
>>
>> - 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.
>
>
>

Re: Datareader does not work but Dataset does

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.







"sloan" wrote in message
news:ehSq$AdCIHA.5980@TK2MSFTNGP04.phx.gbl...
>
> It's there. Either do a global search for "IDataReader" or go here
>
> namespace
> GranadaCoder.Applications.TieredObjectEx.BusinessLayer.Contr ollers .
> 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.
>
>
> "Rnes" wrote in message
> news:1191862833.715525.324950@o3g2000hsb.googlegroups.com...
>> On Oct 8, 9:15 am, "sloan" wrote:
>>> 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!A68482B9628A8 42A!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.
>>>
>>> "Rnes" wrote in message
>>>
>>> news:1191859066.608602.39710@50g2000hsm.googlegroups.com...
>>>
>>>
>>>
>>> > 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?- Hide quoted
>>> > text -
>>>
>>> - 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.
>>
>>
>>
>
>

Re: Datareader does not work but Dataset does

On Oct 8, 11:21 am, "sloan" wrote:
> 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.asp x
> 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.
>
> "sloan" wrote in message
>
> news:ehSq$AdCIHA.5980@TK2MSFTNGP04.phx.gbl...
>
>
>
>
>
> > It's there. Either do a global search for "IDataReader" or go here
>
> > namespace
> > GranadaCoder.Applications.TieredObjectEx.BusinessLayer.Contr ollers .
> > 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.
>
> > "Rnes" wrote in message
> >news:1191862833.715525.324950@o3g2000hsb.googlegroups.com.. .
> >> On Oct 8, 9:15 am, "sloan" wrote:
> >>> 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!A68482B9628A8 42A!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.
>
> >>> "Rnes" wrote in message
>
> >>>news:1191859066.608602.39710@50g2000hsm.googlegroups.com. ..
>
> >>> > 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?- Hide quoted
> >>> > text -
>
> >>> - 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.- Hide quoted text -
>
> - 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.

Re: Datareader does not work but Dataset does

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" wrote in message
news:1191871790.157548.119080@19g2000hsx.googlegroups.com...
> On Oct 8, 11:21 am, "sloan" wrote:
>> 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.
>>
>> "sloan" wrote in message
>>
>> news:ehSq$AdCIHA.5980@TK2MSFTNGP04.phx.gbl...
>>
>>
>>
>>
>>
>> > It's there. Either do a global search for "IDataReader" or go here
>>
>> > namespace
>> > GranadaCoder.Applications.TieredObjectEx.BusinessLayer.Contr ollers .
>> > 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.
>>
>> > "Rnes" wrote in message
>> >news:1191862833.715525.324950@o3g2000hsb.googlegroups.com.. .
>> >> On Oct 8, 9:15 am, "sloan" wrote:
>> >>> 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!A68482B9628A8 42A!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.
>>
>> >>> "Rnes" wrote in message
>>
>> >>>news:1191859066.608602.39710@50g2000hsm.googlegroups.com. ..
>>
>> >>> > 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?- Hide quoted
>> >>> > text -
>>
>> >>> - 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.- Hide quoted text -
>>
>> - 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.
>

Re: Datareader does not work but Dataset does

On Oct 8, 12:39 pm, "sloan" wrote:
> 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" wrote in message
>
> news:1191871790.157548.119080@19g2000hsx.googlegroups.com...
>
>
>
> > On Oct 8, 11:21 am, "sloan" wrote:
> >> 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.
>
> >> "sloan" wrote in message
>
> >>news:ehSq$AdCIHA.5980@TK2MSFTNGP04.phx.gbl...
>
> >> > It's there. Either do a global search for "IDataReader" or go here
>
> >> > namespace
> >> > GranadaCoder.Applications.TieredObjectEx.BusinessLayer.Contr ollers .
> >> > 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.
>
> >> > "Rnes" wrote in message
> >> >news:1191862833.715525.324950@o3g2000hsb.googlegroups.com.. .
> >> >> On Oct 8, 9:15 am, "sloan" wrote:
> >> >>> 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!A68482B9628A8 42A!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.
>
> >> >>> "Rnes" wrote in message
>
> >> >>>news:1191859066.608602.39710@50g2000hsm.googlegroups.com. ..
>
> >> >>> > 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?- Hide quoted
> >> >>> > text -
>
> >> >>> - 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.- Hide quoted text -
>
> >> - 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.- Hide quoted text -
>
> - 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.

Re: Datareader does not work but Dataset does

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

Re: Datareader does not work but Dataset does

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.





"Cor Ligthert[MVP]" wrote in message
news:A560EA9B-0288-4ECD-975E-56EB9164DA56@microsoft.com...
> 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