SqlDataReader - more readable code

Question

If you know a SqlDataReader contains one row then you can do this:

If objDR.HasRows() Then
objDR.read
.........
End If

Instead of this:

Do While objDR.Read
..........
loop

If you use a DataTable then it appears you have to do this:

For Each row as DataRow In objDT.Rows
...................
Next

Is there anything you can do with SqlDataReader if there is only one row? I am asking as I believe it makes code more readable.

I don't quite understand what you are looking for. You have that first code sample - what exactly is wrong with it? — Oded
– Oded, Commented Sep 12, 2013 at 18:47
Another developer more senior than I looked at some code and criticized the approach of enumerating a datatable because only one row would ever be returned (guaranteed) by the underlying query. I don't understand what the alternative is (must use a DataTable). — w0051977
– w0051977, Commented Sep 12, 2013 at 18:49
IMO the other dev's opinion is entirely subjective. Arguably,not relying on the query only ever returning one row is better/more defensive programming than assuming it always will return precisely one. ETA I see DavidW simultaneously made much the same point in his 2nd para — peterG
– peterG, Commented Sep 12, 2013 at 19:46
FYI SQLreader (and others maybe) have SingleRow command for the reader: msdn.microsoft.com/en-us/library/… — rheitzman
– rheitzman, Commented Sep 12, 2013 at 22:51

LarsTech · Accepted Answer · 2013-09-12 18:47:37Z

2

You can just do:

If objDR.Read() Then
  .........
End If

answered Sep 12, 2013 at 18:47

LarsTech

81.9k14 gold badges161 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

w0051977 Over a year ago

Thanks. I know you can do this. I can't use a datareader because they don't outlive the connection. Is there similar syntax for a DataTable.

LarsTech Over a year ago

@w0051977 Your question was about DataReaders. You might want to update your question to DataTables then. You don't read DataTables like DataReaders, you access them, so you would just do dataTable.Rows(0) to access the first (or only) row.

w0051977 Over a year ago

I think dataTable.Rows(0) was what I was looking for. Thanks. +1.

Karl Anderson · Accepted Answer · 2013-09-12 19:32:55Z

0

You can do this:

If objDT IsNot Nothing AndAlso objDT.Rows.Count > 0 Then
    ' We have one or more rows
    For Each row as DataRow In objDT.Rows
        ' Do something with row here
    Next
End If

answered Sep 12, 2013 at 19:32

Karl Anderson

34.9k12 gold badges69 silver badges83 bronze badges

Comments

David W · Accepted Answer · 2013-09-12 19:46:34Z

I've encountered situations like this, where your code will be guaranteed to generate exactly one single result row, and I tend to agree that instantiation of an enumerator for that row isn't ideal. In fact, I've seen a few apps where single-row (or even single-column) result sets were in use, and the authors wrote some very simple wrapper class methods that were designed to take an arbitrary single-result query and return it in a single call, packaging up the overhead of the call, parameters, etc. in a neat, clean single method.

In that case, I actually think its a good case to check the Count property for the rows, and if its non-one, throw an exception. If you've got a query that's forever and always supposed to return exactly one value, but more show up, something's wrong.

if (objDT != null && objDT.Rows.Count != 1)
{
     DataRow x = objDT.Rows[0];
     var value1 = x["column1"]; // and so on
     //..do whatever
}
else
{
    if (objDT==null)
    {
         throw new InvalidOperationException("No data returned");
    }

    if (objDT.Rows.Count != 1)
    {
         throw new InvalidOperationException("Multiple values returned where only one expected."); 
    }
}

Collectives™ on Stack Overflow

SqlDataReader - more readable code

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related