How to (efficiently) convert (cast?) a SqlDataReader field to its corresponding c# type?
c#, casting, sqldatareader
Solution
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.
Problem
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?