Check if it's the last record in sqldatareader

Question

Is there a way to check if I'm on the last record ? thanks

Just curious, why do you need that? – codingbiz Jul 28 '12 at 17:46 — codingbiz, Jul 28 '12 at 17:46
I want to do some exceptions if I'm on the last records !! – wben Jul 28 '12 at 17:54 — wben, Jul 28 '12 at 17:54

score 21 · Accepted Answer · edited Jul 28 '12 at 19:25

21

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

edited Jul 28 '12 at 19:25

Arjan Einbu

13,543
2
56
59

answered Jul 28 '12 at 18:04

Raman Zhylich

3,537
25
23

4

Just we have to replace if(reader.Read()) with if(reader.HasRow) – wben Jul 29 '12 at 18:27
2

@wben In this case you won't be able to read values at the beginning of the first iteration. – Raman Zhylich Jul 30 '12 at 08:58
4

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. – Thorsten Dittmar Mar 16 '15 at 13:57
2

@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. – user2966445 Feb 24 '17 at 21:36
You are right, but the code sample is very misleading the way it is formatted now. – Thorsten Dittmar Feb 25 '17 at 07:35

score 5 · Answer 2 · answered Jul 28 '12 at 17:47

5

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.

answered Jul 28 '12 at 17:47

Marc Gravell

1,026,079
266
2,566
2,900

Yeah , but I don't want to pass on two loops !! – wben Jul 28 '12 at 17:59
@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 Jul 28 '12 at 18:48
@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. – Marc Gravell Jul 28 '12 at 18:50

Arjan Einbu · Answer 3 · 2012-07-28T19:20:37.697

1

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.

edited Jul 28 '12 at 19:20

answered Jul 28 '12 at 17:59

Arjan Einbu

13,543
2
56
59

reader isn't an enumerator it doesn't have MoveNext(). You need to request enumerator first, i.e. reader.GetEnumerator() – Raman Zhylich Jul 28 '12 at 18:16
@RamanZhylich: You're right. It should be reader.Read(). Fixed! (edited the post) – Arjan Einbu Jul 28 '12 at 19:22

score 1 · Answer 4 · answered Jul 28 '12 at 17:59

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

score 0 · Answer 5 · answered Jul 28 '12 at 17:54

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:
- http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/dc198199-be24-473c-9973-746fe14d65ba/

score 0 · Answer 6 · answered Jul 28 '12 at 18:09

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

Check if it's the last record in sqldatareader

6 Answers6

Linked