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://msdn.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

15 Comments

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

  • 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.

  • 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.

  • 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!

  • 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.

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

  • AndrewSeven,

    Good points! Thanks!

  • 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!

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

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

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

  • 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.

  • 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

  • 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?

  • My 2 cents... lol works

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

Comments have been disabled for this content.