How to (efficiently) convert (cast?) a SqlDataReader field to its corresponding c# type?

Question

First, let me explain the current situation: I'm reading records from a database and putting them in an object for later use; today a question about the database type to C# type conversion (casting?) arose.

Let's see an example:

namespace Test {     using System;     using System.Data;     using System.Data.SqlClient;      public enum MyEnum     {         FirstValue = 1,         SecondValue = 2     }      public class MyObject     {         private String field_a;         private Byte field_b;         private MyEnum field_c;          public MyObject(Int32 object_id)         {             using (SqlConnection connection = new SqlConnection("connection_string"))             {                 connection.Open();                  using (SqlCommand command = connection.CreateCommand())                 {                     command.CommandText = "sql_query";                      using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow))                     {                         reader.Read();                          this.field_a = reader["field_a"];                         this.field_b = reader["field_b"];                         this.field_c = reader["field_c"];                     }                 }             }         }     } } 

This is (obviously) failing because the three this.field_x = reader["field_x"]; calls are throwing the Cannot implicitly convert type 'object' to 'xxx'. An explicit conversion exists (are you missing a cast?). compiler error.

To correct this I currently know of two ways (let's use the field_b example): number one is this.field_b = (Byte) reader["field_b"]; and number two is this.field_b = Convert.ToByte(reader["field_b"]);.

The problem with option number one is that DBNull fields are throwing exceptions as the cast is failing (even with nullable types as String), ant the problem with number two is that it's not preserving null values (the Convert.ToString(DBNull) yields a String.Empty), and I can't use them with enums too.

So, after a couple of lookups on the internet and here at StackOverflow, what I came up with is:

public static class Utilities {     public static T FromDatabase<T>(Object value) where T: IConvertible     {         if (typeof(T).IsEnum == false)         {             if (value == null || Convert.IsDBNull(value) == true)             {                 return default(T);             }             else             {                 return (T) Convert.ChangeType(value, typeof(T));             }         }         else         {             if (Enum.IsDefined(typeof(T), value) == false)             {                 throw new ArgumentOutOfRangeException();             }              return (T) Enum.ToObject(typeof(T), value);         }     } } 

This way I should handle every case.

Question is: Am I missing something? Am I doing a WOMBAT (Waste Of Money, Brain And Time) as there's a quicker and cleaner way to do it? It's all correct? Profit?

Answer 1

If a field allows nulls, don't use regular 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 a ? to any non-nullable C# type makes it "nullable". Using the as keyword will attempt to cast an object to the specified type. If the cast fails (like it would if the type is DBNull), then the operator returns null.

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

Answer 2

don't you want to use the reader.Get* methods ? The only annoying thing is that they take column numbers so you have to wrap the accessor in a call 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 }