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()
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)
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
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
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()
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.
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!