casting problem from SqlDataReader

Question

Suppose i have this sql statement and I have executed a sql command to get a datareader:

"select 1 union select 2"
//.....
var rdr = cmd.ExecuteReader();

and now i want to read the value in the first column of the first row:

var myInt = (int)rdr.GetValue(0); //great this works
var myLong = (long)rdr.GetValue(0); //throws cast exception, even though you can cast int to long

So it appears the type you cast to in C# needs to match exactly the SQL type. I.E. If the sql type is bigint, you can only cast to long. If the sql type is int, you can only cast to int. No mix and match...

I just want to get something that works regardless of the type of integer c# asks for and sql returns, as long as you could theoretically cast one to the other. So if SQL Server gives me a floating point type, and I'm asking for an int, I want the truncated int you get from doing that cast.

My goal is to make this work with generics, so I can have this function work when the generic parameter doesn't exactly match the datatype in sql server:

List<T> GetFirstColumn<T>(string sql) where T : struct
{
    //get connection, execute reader
    // loop: 
    //    lst.Add(  (T) rdr.GetValue(0));
}

I'd like this to work for both statments:

var sql1 = "Select 1"; //sql int
var sql2 = "Select cast(1 as bigint)"; //sql equivalent of a long
var lst1 = GetFirstColumn<int>(sql1);
var lst2 = GetFirstColumn<int>(sql2);

Does anyone have a relatively painless way of doing this?

Answer 1

Like Fredrik says, the value from SqlDataReader is boxed. You can convert a boxed value to an int with Convert.ToInt32, like:

int i = Convert.ToInt32(read[0]);

This will try to convert even if SQL Server returns a bigint or a decimal.

Answer 2

System.Convert will take care of the conversion.

T GetValue<T>(SqlDataReader rdr)
{
    var dbVal = rdr.GetValue(0);
    var csVal = (T)System.Convert.ChangeType(dbVal, typeof(T));
}

Caveat: if T == Nullable<S>, you need to do some extra work with reflection to get the underlying type and call ChangeType with typeof(S) as the type parameter. Apparently, MS didn't update the ChangeType function with .NET 2.0 and the introduction of nullables. And if it's a nullable, and dbVal is DBNull, you can just return null.

object dbValue = 5;

//this throws
Convert.ChangeType(dbValue, typeof(int?));

//this works
if(dbValue == DBNull.Value || dbValue == null) 
{
  if(typeof(int?).IsNullable) //or is a class, like string
  {return null;}

  dbValue = null;
}

var type = GetUnderlyingType<int?>(); //== typeof(int)
Convert.ChangeType(dbValue, type);