Retrieving Values from a DataReader

re: Retrieving Values from a DataReader

JamesNT — Thu, 30 Oct 2008 16:44:57 GMT

While I have found this post to be most informative, it may be worth mentioning that lines such as:

lastname = reader.item("lastname")

will generate an error if OPTION STRICT is on.

You will either have to turn off OPTION STRICT (not recommended) or have code like:

with reader

if not .item("birthdate") is dbnull.value then

birthdate = .getdatetime(.getordinal("birthdate"))

end if

end with

You may also need to put in additional checks for the datatype of the fields in the datareader if you are NOT gauranteed to know what they are. We are, of course, assuming that the database the reader got its information from has the right datatypes in the right fields (i.e. that the birthdate field is of type datetime).

Please correct me if I'm wrong.

JamesNT

re: Retrieving Values from a DataReader

Dee — Wed, 01 Oct 2008 04:54:49 GMT

When using

lastname = GetValueFromReader(reader,"lastname");

I get an error:

Compiler Error Message: CS0103: The name 'GetValueFromReader' does not exist in the current context

When looking up "GetValueFromReader" on Microsoft (msdn.microsoft.com/.../system.data.oledb.oledbdatareader_methods.aspx) it does not list this as a method of the datareader class.

re: Retrieving Values from a DataReader

Axel — Wed, 06 Aug 2008 15:28:04 GMT

You also forgot about the possibility to do it like this:

SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())

{

lastName = dr.GetString(dr.GetOrdinal("LastName"));

firstName = dr.GetString(dr.GetOrdinal("FirstName"));

age = dr.GetInt32(dr.GetOrdinal("Age"));

amountOwed = dr.GetDouble(dr.GetOrdinal("AmountOwed"));

}

etc. etc.

re: Retrieving Values from a DataReader

fdf — Sun, 27 Jul 2008 09:55:47 GMT

PhoneNumber += rdr.GetValue(rdr.GetString(PhoneNumber))+"\n";

re: Retrieving Values from a DataReader

nannette — Mon, 18 Feb 2008 19:50:52 GMT

Great suggestion Travis! Thanks! I have also added that to my post.

re: Retrieving Values from a DataReader

Travis — Fri, 15 Feb 2008 18:24:58 GMT

if (reader["lastname"] != DBNull.Value)

{

lastname = reader["lastname"].ToString();

}

Why all the code? Just do this, it handles nulls nicely unlike .ToString():

lastname = Convert.ToString(reader["lastname"]);

re: Retrieving Values from a DataReader

nannette — Thu, 14 Feb 2008 20:51:27 GMT

Thanks everyone for your feedback and valuable input. I have included your comments at the end of my article so they won't be missed.

Thanks!

re: Retrieving Values from a DataReader

nannette — Thu, 14 Feb 2008 20:34:24 GMT

AndrewSeven,

Good points! Thanks!

re: Retrieving Values from a DataReader

AndrewSeven — Thu, 14 Feb 2008 20:16:39 GMT

Isn't there a small cost because you are hitting the reader twice for each field?

if (reader["lastname"] != DBNull.Value)

{

lastname = reader["lastname"].ToString();

}

vs.

object val = reader["lastname"];

if (val != DBNull.Value)

{

lastname = val;

}

If your concern is readability, you should probably encapsulate the null test so you can write: lastname = GetValueFromReader(reader,"lastname");

re: Retrieving Values from a DataReader

nannette — Thu, 14 Feb 2008 20:02:43 GMT

Marneus,

You are correct. Readers may refer to this document on the GetOrdinal()

msdn2.microsoft.com/.../system.data.sqlclient.sqldatareader.getordinal.aspx

Please note this comment on efficiency:

"Because ordinal-based lookups are more efficient than named lookups, it is inefficient to call GetOrdinal within a loop. Save time by calling GetOrdinal once and assigning the results to an integer variable for use within the loop."

From the article, here is the usage:

        ' Call GetOrdinal and assign value to variable.
        Dim customerID As Integer = reader.GetOrdinal("CustomerID")

        ' Use variable with GetString inside of loop.
        While reader.Read()
            Console.WriteLine("CustomerID={0}", reader.GetString(customerID))
        End While