Nannette Thacker ShiningStar.net

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