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


Answers

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
}
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 , 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?




I just want to add that the context of how many records you are expecting plays a big role because if you are returning a single row then performance difference between those two would not be significant. However if you are looping over many rows then using typed accessor is better for performance since it's optimized. So in that case if you need to get best performance by using a column name then call GetOrdinal once, put it into a variable and then use typed accessor with the column ordinal in your loop. This would yield the best performance.

if you are curious about the performance difference check out my blog post




Why use the GetOrdinal() Method of the SqlDataReader

I think that the reason to use GetOrdinal() is so that you can cache the result and re-use it multiple times for performance.

E.g.

Dim reader As SqlClient.SqlDataReader
int valueOrdinal = reader.GetOrdinal("value");
while ( ... )
{
    var value = reader.GetString(valueOrdinal);
}



C# System.InvalidCastException: Specified Cast is not valid

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

You can't just cast SqlDataReader fields to their corresponding value types because of the possibility of nulls. You can use nullable types but it's likely your Account object isn't setup to take nullable types.

One way you can try to handle this is to add null checking:

 ReturnValue = new Account(Reader["UserUID"] == DBNull.Value ? 0 : (int)Reader["UserUID"] ,
                           Reader["Pw"] == DBNull.Value ? "" : Reader["Pw"].ToString(),
                           Reader["Admin"] == DBNull.Value ? false : (bool)Reader["Admin"],
                           Reader["Staff"] == DBNull.Value ? false : (bool)Reader["Staff"],
                           Reader["Status"] == DBNull.Value ? (short) 0 : (short)Reader["Status"],
                           Reader["Point"] == DBNull.Value ? 0 :  (int)Reader["Point"],
                           Reader["DaemonPoints"] == DBNull.Value ? 0 : (int)Reader["DaemonPoints"]);



You were missing reader.IsDBNull(3) there, so it could be trying to cast DbNull to int, which is invalid.

If you're sure there are no nulls in that column, then make sure that what you're getting from the DB is an int, you can do that by either getting contents as string; or leave it as object, set a break point, and using immediate window in your IDE call value.GetType() which will tell you what exactly you're dealing with.

If you're storing enums as strings in db, then you should use Enum.Parse()

Also this is similar: Exception: Specified cast is not valid







Tags