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

I think your problem is that GetValue returns an object. This means that in the case of an int, you will get an int boxed in an object. Then you cannot directly cast it to a long but must first unpack it as an int:

var myLong = (long)(int)rdr.GetValue(0);

This will be quite tricky using generics, I would say. Well, you could make generic methods with two type arguments; one specifying what type the field is, and one specifying the type you want. But I don't really see the need; SqlDataReader already has methods for the various data types, such as GetInt32, GetInt64 and so on, so the generic method would not really give any added value in that case.

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.

//this throws
Convert.ChangeType(5, typeof(int?));
//this doesn't good
var type = GetUnderlyingType<int?>(); //== typeof(int)
Convert.ChangeType(5, type);

Answer 3

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.