Nannette Thacker ShiningStar.net

ASP.net Web Application Development

Sponsors

News

See all Blog Posts by Nannette.

Nannette Thacker, consultant and owner of Shining Star Services LLC, specializes in development of custom dynamic database driven web applications utilizing ASP.net technologies. Nannette has been developing ASP sites since 1997. Nannette has written numerous articles on web development techniques and tutorials.

Nannette is the owner and developer of ChristianSinglesDating.com.

 Subscribe in a reader





View Nannette  Thacker's profile on LinkedIn

Retrieving Values from a DataReader

By Nannette Thacker

There are several ways to retrieve values from a DataReader.

The first method is by using the index and looping through the fields in order as retrieved within the Select statement. Starting with the 0 index, you may use the GetValue() Function.

The following code snippets are in VB. A C# snippet is provided at the bottom.

reader = objCommand.ExecuteReader()
While reader.Read()

If Not reader.GetValue(0) Is DBNull.Value Then _
    lastname = reader.GetValue(0)
If Not reader.GetValue(1) Is DBNull.Value Then _
    firstname = reader.GetValue(1)

End While
reader.Close()
However, this makes the code harder to read, and if a field is added or removed from the query, it could break your code. So let's look at how to retrieve the values by field names.

If your reader is using an OleDbDataReader, we use the reader.Item function.

If Not reader.Item("lastname") Is DBNull.Value Then _
    lastname = reader.Item("lastname")
If Not reader.Item("firstname") Is DBNull.Value Then _
    firstname = reader.Item("firstname")
If your reader is using a SqlDataReader, we use the reader.GetItem function.

If Not reader.GetItem("lastname") Is DBNull.Value Then _
    lastname = reader.GetItem("lastname")
If Not reader.GetItem("firstname") Is DBNull.Value Then _
    firstname = reader.GetItem("firstname")
Here is a C# example:

if (reader["lastname"] != DBNull.Value)
    lastname = reader["lastname"].ToString();
A reader of my column, Zac, suggests that reader("lastname") instead of reader.Item("lastname") will also work.

He is correct. For the DataReader class implementation, Item is the default property and may be left out. You may access an indexer by using the () operator on the base class object or you may access an indexer by using the Item property.

Another reader of my column, Marneus, pointed out the GetOrdinal option as well. He said "there is a performance hit, each time you use the reader("lastname"), it has to check the meta datas to get the column number."

Readers may refer to this document on the GetOrdinal()

http://msdn2.microsoft.com/en-us/library/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
Another reader, AndrewSeven, pointed out the cost for hitting the reader twice for each field. He suggests:

object val = reader["lastname"];
if (val != DBNull.Value)
{
   lastname = val;
}
And also comments "If your concern is readability, you should probably encapsulate the null test so you can write:

lastname = GetValueFromReader(reader,"lastname");"
Travis responded with this suggestion: "Just do this, it handles nulls nicely unlike .ToString():"

lastname = Convert.ToString(reader["lastname"]);
So I tested his theory by creating columns that would not allow null values, and imported columns with null values. In VB it is written like so:

lastname = Convert.ToString(reader("lastname"))
firstname = Convert.ToString(reader("firstname"))
That is much cleaner! Thanks everyone for your great input!

May your dreams be in ASP.net!

Nannette Thacker

Comments

Zak said:

Is there a performance hit or some other concern with using reader("lastname") in VB.NET?  I know it compiles just fine...

# February 14, 2008 1:18 PM

Marneus said:

An other option is to use the GetOrdinal(fieldName) method of the datareader. It gives you back an int.

So on top of you method, you initialize a bunch of ints named lastNameCol, firstNameCol and so on, and then you use the GetValue feeding it with the ints you initialized before.

With this you get both the readability of the GetItem and the perfs of the GetValue.

# February 14, 2008 2:51 PM

Marneus said:

An other way to go is to use the GetOrdinal(fieldName) of the datareader to initialize a bunch of int variables with the numbers of the columns, and then use these (conveniently named) variables as parameters of the GetValue.

This way, you get both the readability and the perf.

# February 14, 2008 2:54 PM

nannette said:

Zac,

You are correct. I have added this to my last paragraph for clarification:

...reader("lastname") instead of reader.Item("lastname") will also work. For the DataReader class implementation, Item is the default property and may be left out. You may access an indexer by using the () operator on the base class object or you may access an indexer by using the Item property.

Thanks for pointing that out as well!

# February 14, 2008 2:56 PM

Marneus said:

To respond to Zak, yes, there is a performance hit, each time you use the reader("lastname"), it has to check the meta datas to get the column number.

# February 14, 2008 2:58 PM

AndrewSeven said:

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");

# February 14, 2008 3:16 PM

nannette said:

AndrewSeven,

Good points! Thanks!

# February 14, 2008 3:34 PM

nannette said:

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!

# February 14, 2008 3:51 PM

Travis said:

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"]);

# February 15, 2008 1:24 PM

nannette said:

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

# February 18, 2008 2:50 PM

fdf said:

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

# July 27, 2008 5:55 AM

Axel said:

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.

# August 6, 2008 11:28 AM

JamesNT said:

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

# October 30, 2008 12:44 PM

shima said:

i have dynamic table that i don't know it's field names.if i want to use a variable as a field name that the variable is string and use it in reader, the code wouldn't answer me. what can i do?

# September 7, 2010 8:46 AM

Lstewart said:

My 2 cents... lol works

lastName = dr.Item(dr.GetOrdinal("lastName")).ToString

# November 15, 2010 11:38 AM

abc said:

hi all!!!!!!!!thanks for the useful info

# November 17, 2011 5:59 AM
Leave a Comment

(required) 

(required) 

(optional)

(required)