Is there a way to check if I'm on the last record ? thanks
2
-
2Just curious, why do you need that?codingbiz– codingbiz2012-07-28 17:46:27 +00:00Commented Jul 28, 2012 at 17:46
-
2I want to do some exceptions if I'm on the last records !!wben– wben2012-07-28 17:54:53 +00:00Commented Jul 28, 2012 at 17:54
Add a comment
|
6 Answers
Use this pattern to identify and process the last row in result:
if (reader.Read())
{
var loop = true;
while (loop)
{
//1. Here retrive values you need e.g. var myvar = reader.GetBoolean(0);
loop = reader.Read();
if (!loop)
{
//You are on the last record. Use values read in 1.
//Do some exceptions
}
else {
//You are not on the last record.
//Process values read in 1., e.g. myvar
}
}
}
5 Comments
wben
Just we have to replace if(reader.Read()) with if(reader.HasRow)
Raman Zhylich
@wben In this case you won't be able to read values at the beginning of the first iteration.
Thorsten Dittmar
Wrong. As your code is now, the first row returned is even being skipped. You're setting the reader to the first record in
if (reader.Read()) and then you set loop = reader.Read() which directly sets the reader to the second record. Still your solutions provides a proper pattern to how you the problem can be solved.
user2966445
@ThorstenDittmar If you actually read the code comments, he's not skipping the first record. He advances to the first record in the if statement, then reads the values, then sets loop = reader.Read() which is the second record.
Thorsten Dittmar
You are right, but the code sample is very misleading the way it is formatted now.
Other than "there isn't another one afterwards", no. This may mean you need to buffer the row, try to read, then look at the buffered data if it turned out to be the last.
In many cases, when the data is moderate and you are building an object model of some kind, you can just looks at the constructed data, i.e. make a List<T> of all the rows when using the reader, then after that, look at the last row in the list.
3 Comments
wben
Yeah , but I don't want to pass on two loops !!
Marc Gravell
@wben if it is in a list, looping is insanely cheap - or just access by indexer:
list[list.Count-1]. Of course, buffering the previous row is even cheaper, but more complex.Marc Gravell
@wben to make things clearer, in the accepted answer, where is says " //1. Here retrive values you need e.g. var myvar = reader.GetBoolean(0);", that is what I/we mean by "buffer the row, try to read, then look at the buffered data if it turned out to be the last". The code example makes it clearer, of course.
Another possibility would be to put the information into the data set.
You are reading the data using a query. Let's call it q. Use this query instead:
select row_number() over (order by (select NULL)) as seqnum,
count(*) over (partition by null) as numrecs
q.*,
from (<q>) q
order by seqnum
Now the last record has seqnum = numrecs, which you can test for in your application.
By the way, this assumes that you are retrieving the data in an unordered way. If you want ordering, put the criteria in the "order by" clause for row_number().
Comments
To iterate through the SqlDataReader:
SqlDataReader reader = ...;
while(reader.Read())
{
// the reader will contain the current row
}
There's nothing in the reader telling you it's the last row. If you still need to get on the last row, perhaps storing the row's data in a seperate variable you can get that data after exiting the loop.
Other alternatives would be:
- to get the data into a DataSet/DataTable
- to store the data in a list/array
- retrieve the data in reverse order (rewrite your query), so that the first row is the one you want.
2 Comments
Raman Zhylich
reader isn't an enumerator it doesn't have MoveNext(). You need to request enumerator first, i.e. reader.GetEnumerator()
Arjan Einbu
@RamanZhylich: You're right. It should be reader.Read(). Fixed! (edited the post)
As you know, a SQLDataReader doesn't have a "row count" property. You just read until EOF.
However, a DataSet does. Perhaps you can substitute a DataAdapter and some kind of data set or table:
Comments
You can't do that without repeating yourself i.e. fetching the record twice. Here is a shorter method
System.Data.DataTable dt = new System.Data.DataTable();
dt.Load(myDataReader);
int recordCount = dt.Rows.Count;
if(recordCount > 0)
{
if(dt.Rows[recordCount-1]["username"] == "wrong value")
throw new Exception("wrong value");
}
I wrote this off-hand so it's not tested but that's the approach
