[ACCEPTED]-How to (efficiently) convert (cast?) a SqlDataReader field to its corresponding c# type?-sqldatareader

If a field allows nulls, don't use regular 14 primitive types. Use the C# nullable type and the as keyword.

int? field_a = reader["field_a"] as int?;
string field_b = reader["field_a"] as string;

Adding 13 a ? to any non-nullable C# type makes it 12 "nullable". Using the as keyword 11 will attempt to cast an object to the specified 10 type. If the cast fails (like it would if 9 the type is DBNull), then the operator returns 8 null.

Note: Another small benefit of using as is that 7 it is slightly faster than normal casting. Since it can 6 also have some downsides, such as making 5 it harder to track bugs if you try to cast 4 as the wrong type, this shouldn't be considered 3 a reason for always using as over traditional 2 casting. Regular casting is already a fairly 1 cheap operation.

don't you want to use the reader.Get* methods ? The only annoying 3 thing is that they take column numbers so 2 you have to wrap the accessor in a call 1 to GetOrdinal()

using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow))
{
    reader.Read();

    this.field_a = reader.GetString(reader.GetOrdinal("field_a"));
    this.field_a = reader.GetDouble(reader.GetOrdinal("field_b"));
    //etc
}

This is how I've dealt with it in the past:

    public Nullable<T> GetNullableField<T>(this SqlDataReader reader, Int32 ordinal) where T : struct
    {
        var item = reader[ordinal];

        if (item == null)
        {
            return null;
        }

        if (item == DBNull.Value)
        {
            return null;
        }

        try
        {
            return (T)item;
        }
        catch (InvalidCastException ice)
        {
            throw new InvalidCastException("Data type of Database field does not match the IndexEntry type.", ice);
        }
    }

Usage:

int? myInt = reader.GetNullableField<int>(reader.GetOrdinal("myIntField"));

0

You can make a set of extension methods, one 15 pair per data type:

    public static int? GetNullableInt32(this IDataRecord dr, string fieldName)
    {
        return GetNullableInt32(dr, dr.GetOrdinal(fieldName));
    }

    public static int? GetNullableInt32(this IDataRecord dr, int ordinal)
    {
        return dr.IsDBNull(ordinal) ? null : (int?)dr.GetInt32(ordinal);
    }

This gets a bit tedious 14 to implement, but it's pretty efficient. In 13 System.Data.DataSetExtensions.dll, Microsoft 12 solved the same problem for DataSets with 11 a Field<T> method, which generically handles multiple data 10 types, and can turn DBNull into a Nullable.

As 9 an experiment, I once implemented an equivalent 8 method for DataReaders, but I ended up using 7 Reflector to borrow an internal class from 6 DataSetExtensions (UnboxT) to do the actual 5 type conversions efficiently. I'm not sure 4 about the legality of distributing that 3 borrowed class, so I probably shouldn't 2 share the code, but it's pretty easy to 1 look up for oneself.

The generic hanlding code posted here is 13 cool, but since the question title includes 12 the word 'efficiently' I will post my less 11 generic but (I hope) more efficient answer.

I 10 suggest you use the getXXX methods that 9 others have mentioned. To deal with the 8 column number problem that bebop talks about, I 7 use an enum, like this:

enum ReaderFields { Id, Name, PhoneNumber, ... }
int id = sqlDataReader.getInt32((int)readerFields.Id)

It's a little extra 6 typing, but then you don't need to call 5 GetOrdinal to find the index for each column. And, instead 4 of worrying about column names, you worry 3 about column positions.

To deal with nullable 2 columns, you need to check for DBNull, and 1 perhaps provide a default value:

string phoneNumber;
if (Convert.IsDBNull(sqlDataReader[(int)readerFields.PhoneNumber]) {
  phoneNumber = string.Empty;
}
else {
  phoneNumber = sqlDataReader.getString((int)readerFields.PhoneNumber);
}