Casting Problem from SqlDataReader

Question

Suppose I have this sql statement and I executed the sql command to get the 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 looks like the type you are pointing to in C # should exactly match the SQL type. I.E. If the sql type is bigint, you can only use long ones. If the sql type is int, you can use only int. No mixing and matching ...

I just want to get something that works regardless of the C # integer type, and returns sql if you can theoretically drop one on top of the other. Therefore, if SQL Server gives me a floating point type, and I ask for an int, I want the truncated int you get from this cast.

My goal is to make this work with generics, so I can work with this function when the general parameter does not exactly match the data type in the SQL server:

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

I would like this to work for both entries:

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 to do this?

Answer 1

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

Caution: if T == Nullable<S>, you need to do extra work with reflection to get the base type and call ChangeType with typeof(S)as the type parameter. Apparently, MS did not update the ChangeType function with .NET 2.0 and introduced nullables. And if it is 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);

Answer 2

, , GetValue object. , int int object. long, int:

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

, . , ; , - , . ; SqlDataReader , GetInt32, GetInt64 .., .

Answer 3

, SqlDataReader . int Convert.ToInt32, :

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

, SQL Server bigint .