SqlDataReader is End of File

  • Thread starter Thread starter Rajesh Patel
  • Start date Start date
R

Rajesh Patel

check recordsaffected() property which returns integer. may be that helps
you.

Rajesh Patel
 
Hello!

I'm looking for a method to determine if the record of a SqlDataReader is
the last. Basically I'm creating a hierarchical object tree with recursion.
I guess I'm looking for a method like the

With an old ADODB Recordset, you could simply use the following code:

'## RECORDSET IS END OF FILE
If (oRs.Eof) Then
Don't reference any columns ..
End If

Any ideas? I don't see any ways to do this with a SqlDataReader, except
wrapping a Read() call in a try/catch block, which isn't that great.
 
if recordsaffected returns interger, if it is the number of records the
datareader has you can keep a counter in a loop and with condition if is
counter = recordsaffected.

if this does not help, try to post your code snippet. somebody would be in
better position to help then.

Rajesh Patel
 
Hello!

Thanks for the quick reply. However, I don't think this is what I'm looking
for. Basically, I need to check the SqlDataReader, whether a Read() is
possible. This isn't possible by the RecordsAffected property you're
referring to.

Any other ideas?
 
Records affected doesn't tell you how many records are in the
datareader...there's no way to tell in advance how many rows there are in a
DataReader without iterating through it.

You can check for DataReader.NextResult . It returns a Boolean and if you
get false..there aren't any more records.

HTH,

Bill
 
yes, you are right recordsaffected is not a right choice

however, datareader.nextresult doesn't show that you have more rows or not
in the current dataset. it just returns is there any more resultset left to
read or not.

there are 2 options left. may be you can write some logic or use dataset.

if you guys have some ideas, this would be more interesting to discuss.

Rajesh Patel.
 
Rajesh:

With all due respect, I think you are mixing things up:

<<however, datareader.nextresult doesn't show that you have more rows or not
in the current dataset. it just returns is there any more resultset left to
read or not.>>

Technically, you are right b/c NextResult only applies to a DataReader and a
DataReader is a connected object, not a disconnected one. You don't need a
DataSet to use a DataReader, and normally you would never use both objects
in for the same task. They are intended for totally different purposes.

NextResult returns a boolean value, if NextResult returns false, you are at
the end (or possibly at the beginning of a DataReader that doesn't have any
rows - in which case you can use DataReader.HasRows if you have VS2003).

One of the primary reasons one would choose .NextResult vs. While dr.Read()
is to determine such a thing.

While Dr.Read would only know you hit the end After you hit it, NextResult
would return False as soon as you were at the last record.

If there aren't any results to return , then you are at the end of the set.

As far as writing logic goes, if you are using a DataReader, you Have to
iterate through it to determine if you are at the end. The only exception
would be if you didn't have any rows, but if you verify that you have rows,
then you need to walk the Reader in order to find out when you are at the
end.

Using a DataSet in many instances is totally overkill b/c of the overhead
associated with the DataAdapter (assuming you are using a DataSet/DataTable
to retrieve data).

I don't mean to parse words, but it's important to draw a distinction
between connected and disconnected objects b/c the behave Totally
differently and performance and functionality is night and day depending on
the task.

Also, you don't need a DataSet at all if you are working with one query.
You can simply use a DataTable, and check its Rows.Count property,
Rows.Count-1 would give you the last record, but there are a lot of things
(mainly performance and needed functionality) to consider before scrapping a
DataReader based approach in favor of a disconnected methodology.

HTH,

Bill
 
Hello!

Thanks for the input. Unfortunately, neither suggestions are what I'm
looking for. The NextResult() method actually looks for additional Transact
SQL SELECT statements, resulting in one or more resultsets available to the
SqlDataReader.

It doesn't tell you if there are additional rows left in the current
resultset. That's what I'm looking for, without forcing a Read() on the
SqlDataReader. Basically, it's just like the good old ADODB Recordset, as
mentioned in my original posting.

'## ADODB
oRs.EOF

'## .NET?
sqlReader.EOF?

Thanks in advance. I really need this one.
 
Hello!

I realized there was another way to get around the problem. If you're facing
recursion in your code, it's harder to check, whether a nested recursion has
advanced the SqlDataReader beyond it's last record.

So, I simply decided to check on the IsClosed property on the SqlDataReader.
You can programatically force a SqlDataReader to close, simply by calling
the method Close().

// Advance SqlDataReader to next record (if available)
if (sqlReader.Read() == false)
{
// Force SqlDataReader to close
sqlReader.Close()
}

... and then, in the parent method, you simply do the following:

if (sqlReader.IsClosed)
{
break;
}

Here's a small sample I wrote to document the idea. I'd still like to have a
property, that allows you to check for additional records.

void Recurse(SqlDataReader sqlReader)
{
if (sqlReader.Read() == false)
{
sqlReader.Close();
return;
}

int m_Right = (int) sqlReader["Right"];

while (while((int) sqlReader["Right"] < m_Right))
{
Recurse(sqlReader);

if (sqlReader.IsClosed)
{
break;
}
}
}
 
Anders,

Your solution looks fine. When Microsoft designed ADO.Net they wanted to
make the reading of records in the datareader even faster than with ADO2.x,
so they combined the NextRecord and EOF methods into one method, Read().
This moves onto the next (or first) record and tells you whether EOF is
true, all in one call.

Whilst you may feel I'm stating the obvious, for your needs, all you need is
to remember the value of the Read() call and use this as your EOF, as below:


bool eof;
// Advance reader to next record
eof = sqlReader.Read();

then when you want to check whether you are at EOF,

if (eof)
{
// All records processed
}

The noise about RecordsAffected (which always returns -1 or selects) and
NextResult is not relevant here, Rajesh is correct about what NextResult
does, and Bill is way off!

Regards,

Neil.

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Neil McKechnie
Microlink Associates Ltd
(e-mail address removed)
Anders Borum said:
Hello!

I realized there was another way to get around the problem. If you're facing
recursion in your code, it's harder to check, whether a nested recursion has
advanced the SqlDataReader beyond it's last record.

So, I simply decided to check on the IsClosed property on the SqlDataReader.
You can programatically force a SqlDataReader to close, simply by calling
the method Close().

// Advance SqlDataReader to next record (if available)
if (sqlReader.Read() == false)
{
// Force SqlDataReader to close
sqlReader.Close()
}

.. and then, in the parent method, you simply do the following:

if (sqlReader.IsClosed)
{
break;
}

Here's a small sample I wrote to document the idea. I'd still like to have a
property, that allows you to check for additional records.

void Recurse(SqlDataReader sqlReader)
{
if (sqlReader.Read() == false)
{
sqlReader.Close();
return;
}

int m_Right = (int) sqlReader["Right"];

while (while((int) sqlReader["Right"] < m_Right))
{
Recurse(sqlReader);

if (sqlReader.IsClosed)
{
break;
}
}
}


--
venlig hilsen / with regards
anders borum
--
William Ryan said:
Rajesh:

With all due respect, I think you are mixing things up:

<<however, datareader.nextresult doesn't show that you have more rows or not
in the current dataset. it just returns is there any more resultset
Click to expand...
left
to
read or not.>>

Technically, you are right b/c NextResult only applies to a DataReader
Click to expand...
and
a
DataReader is a connected object, not a disconnected one. You don't
Click to expand...
need
a
DataSet to use a DataReader, and normally you would never use both objects
in for the same task. They are intended for totally different purposes.

NextResult returns a boolean value, if NextResult returns false, you are at
the end (or possibly at the beginning of a DataReader that doesn't have any
rows - in which case you can use DataReader.HasRows if you have VS2003).

One of the primary reasons one would choose .NextResult vs. While dr.Read()
is to determine such a thing.

While Dr.Read would only know you hit the end After you hit it, NextResult
would return False as soon as you were at the last record.

If there aren't any results to return , then you are at the end of the set.

As far as writing logic goes, if you are using a DataReader, you Have to
iterate through it to determine if you are at the end. The only exception
would be if you didn't have any rows, but if you verify that you have rows,
then you need to walk the Reader in order to find out when you are at the
end.

Using a DataSet in many instances is totally overkill b/c of the overhead
associated with the DataAdapter (assuming you are using a DataSet/DataTable
to retrieve data).

I don't mean to parse words, but it's important to draw a distinction
between connected and disconnected objects b/c the behave Totally
differently and performance and functionality is night and day depending on
the task.

Also, you don't need a DataSet at all if you are working with one query.
You can simply use a DataTable, and check its Rows.Count property,
Rows.Count-1 would give you the last record, but there are a lot of things
(mainly performance and needed functionality) to consider before
Click to expand...
scrapping
a
DataReader based approach in favor of a disconnected methodology.

HTH,

Bill

left
to are if
you records
the if would
be Read()
Click to expand...
Click to expand...
 
Hello Neil!

Yes, I follow Microsoft here, but they should (in my oppinion) expand the
API with the EOF property. I've seen several postings regarding this, and
I'm sure a few people have learned from this thread (with the Close()
approach).

Regarding your code, please note that I'm supposed to check the state of the
SqlDataReader in a recursion, and might not always want to pass a "ref"
variable when recursing, just to check the state when the recursion returns
to the caller.

void (ref int readerState, SqlDataReader sqlReader)
{
readerState = sqlReader.Read();

// Recursion here
}

venlig hilsen / with regards
anders borum
--
 
Back
Top