Retrieving a single row from C#

[Solarion]

Retrieving a single row from SQL [ C#]

I have these two options. Both work the same but which one is correct when retrieving a single row paying attention to the "while" and the "if"



Edit: I can't post code anymore (php or code tags) so it is an image sorry.

 

[scudsucker]

Looks to me that the first one would give you the last row of the query result.

In

while (sdr.Read())

you are resetting the Id, JobCardDate etc to the current record, as that will loop through all records until the last.

The second will return the first record.

If the query has only 1 row the end result will be the same.

Note: you may want to check if

sdr.HasRows()

before you try to read it.

 

[Kosmik]

Looks to me that the first one would give you the last row of the query in "while(sdr.read)" you are resetting the Id, JobCardDate etc to the current record, as that will loop through all records until the last.

The second will return the first record.

Note: you may want to check if "sdr.HasRows()" before you try to read it.

No. The first one will create a item for each iteration and add it to the list.

The second will only work with a single record retrieved. Agree about the HasRows check though.

*Edit* The second will ensure that you only save the first record but to be neat, you should be ensuring you only receive one within your sql statement, otherwise you are just adding additional database,network and server overhead.

 

[scudsucker]

No. The first one will create a item for each iteration and add it to the list.

Perhaps I should have paid more attention to the line that adds an item to a list.... DOH!

 

[Kosmik]

Perhaps I should have paid more attention to the line that adds an item to a list.... DOH!

hehe , inline constructors, depends if old school or not

 

[Solarion]

Thanks gentlemen that has cleared this confusion for me

 

[Solarion]

When I was just using .HasRows I was getting an error telling me no records found. You have to include the while(reader.read) as that statement fetches the first row.

Straight off the bat I'd say readability. We all know that Read() will return False if there are no more rows to fetch, but Reader.HasRows is much more telling as to what it does than Read().

Under the surface though, they're doing quite different things. HasRows is simply telling you whether or not there are any rows to read, whereas Read() advances the internal cursor to the next row in the data, and incidentally returns False if there are no more rows to read, or True if there was another row available.

Also, you've probably just picked a simple example but with HasRows, you'd have to use Read to advance the cursor to the first row. You'd generally use HasRows to determine whether or not you need to enter a loop which reads all of the data from your data source, like so:

If reader.HasRows then
while reader.Read()
txtCR_Subject.text = reader("subject")
end while
end if

sqldatareader: Hasrows vs Read()