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





database name (9)


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"]);

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?




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.




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
}



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"));



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);
}



Change the type of the command to stored procedure, and remove the "EXEC" from the command text so the command text is just the stored procedure name:

using (var connection = new SqlConnection(_sqlstring))
{
    using (var command = new SqlCommand("GetAllEncodedMedia", connection))
    {
        try
        {
            connection.Open();
            command.CommandType = CommandType.StoredProcedure;
            var reader = command.ExecuteReader();
            while (reader.Read())
            {
                if (reader.HasRows)
                {
                    var assetItem = new MediaServices.EncodedAssets
                    {
                        Id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0),
                        PublishedName = reader.IsDBNull(1) ? null : reader.GetString(1),
                        PublishUri = reader.IsDBNull(2) ? new Uri("http://www.null.com") : new Uri(reader.GetString(2)),
                        EncodePreset = reader.IsDBNull(3) ? 0 : (MediaServices.EncodePresetsForSmoothStreaming)reader.GetInt32(3),
                        AssetId = reader.IsDBNull(4) ? null : reader.GetString(4),
                        EncoderJobId = reader.IsDBNull(5) ? 0 : reader.GetInt32(5),
                        //EncoderState = reader.IsDBNull(6) ? 0 : (JobState)reader.GetInt32(6),
                        //AssetState = reader.IsDBNull(7) ? 0 : (MediaServices.InternalAssetState)reader.GetInt32(7),
                        GroupId = reader.IsDBNull(8) ? 0 : reader.GetInt32(8),
                        Published = !reader.IsDBNull(2)
                    };
                    listReturn.Add(assetItem);
                }
            }
        }
        catch (Exception ex)
        {
            //error
        }
    }
}



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




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




Properties are used to expose field. They use accessors(set, get) through which the values of the private fields can be read, written or manipulated.

Properties do not name the storage locations. Instead, they have accessors that read, write, or compute their values.

Using properties we can set validation on the type of data that is set on a field.

For example we have private integer field age on that we should allow positive values since age cannot be negative.

We can do this in two ways using getter and setters and using property.

 Using Getter and Setter

    // field
    private int _age;

    // setter
    public void set(int age){
      if (age <=0)
       throw new Exception();

      this._age = age;
    }

    // getter
    public int get (){
      return this._age;
    }

 Now using property we can do the same thing. In the value is a key word

    private int _age;

    public int Age{
    get{
        return this._age;
    }

    set{
       if (value <= 0)
         throw new Exception()
       }
    }

Auto Implemented property if we don't logic in get and set accessors we can use auto implemented property.

When use auto-implemented property compiles creates a private, anonymous field that can only be accessed through get and set accessors.

public int Age{get;set;}

Abstract Properties An abstract class may have an abstract property, which should be implemented in the derived class

public abstract class Person
   {
      public abstract string Name
      {
         get;
         set;
      }
      public abstract int Age
      {
         get;
         set;
      }
   }

// overriden something like this
// Declare a Name property of type string:
  public override string Name
  {
     get
     {
        return name;
     }
     set
     {
        name = value;
     }
  }

We can privately set a property In this we can privately set the auto property(set with in the class)

public int MyProperty
{
    get; private set;
}

You can achieve same with this code. In this property set feature is not available as we have to set value to field directly.

private int myProperty;
public int MyProperty
{
    get { return myProperty; }
}