CODEWITHSUNDEEP
c#sql-server-2012
Ivan-Mark Debono
Ivan-Mark Debono

Reputation: 16310

Casting from int to long throws exception

I have a table with 2 columns (string & int). The current value for the int column is -1.

Using a SQLDataReader I'm reading rows from this table:

public class MyObject
{
    public string Name;
    public long Number;
}

using (SqlDataReader dataReader = command.ExecuteReader())
{
    MyObject o = new MyObject()
    while (dataReader.Read())
    {
        o.Name= dataReader.GetString(0);
        o.Number = dataReader.GetInt64(1);
    }
}

However, I'm getting an InvalidCastException when reading the Number column. What am I missing here?

Upvotes: 2

Views: 5202

Answers (5)

Valeriy
Valeriy

Reputation: 1

If you want to unbox SQLDataReader object from (object{int}) to long, you should know, what there is no direct way. For example this code

long _result = (long)(sqlDR["IntTypeField"] ?? 0L);

will generate "System.InvalidCastException". You should use more comprehensive function, like this

public static T NvlO<T>(object a, T b)
{
    if (a == null)
        return b;
    else
    {
        var lSrcType = a.GetType();
        var lDestType = typeof(T);

        if (lDestType.IsValueType && lDestType.IsAssignableFrom(lSrcType))
            return (T)a;
        var lDestTC = TypeDescriptor.GetConverter(typeof(T));
        if (lDestTC.CanConvertFrom(lSrcType))
            return (T)lDestTC.ConvertFrom(a);
        else
        {
            var lSrcTC = TypeDescriptor.GetConverter(lSrcType);
            String lTmp = lSrcTC.ConvertToInvariantString(a);
            return (T)lDestTC.ConvertFromInvariantString(lTmp);
        }
    }
}

Upvotes: 0

Harald Coppoolse
Harald Coppoolse

Reputation: 30464

The remarks section of SqlDataReader.GetInt64 states:

No conversions are performed; therefore, the data retrieved must already be a 64-bit signed integer.

By the way: don't forget to call IsDBNUll if your value is nullable

Are you sure that the retrieved data is a bigint in your database? if it is an int, or a nchar / nvarchar, you should use the corresponding function and cast or parse it to a System.Int64

Some advises to improve robustness of your code

  • use SqlDataReader[columnName] instead of GetInt64. This will give you the object in the original type, which is most likely to be convertible to your long.
  • Using column names instead of column numbers increases the stability of your code. It will still work if columns are reordered in your query, or if a different column is added or removed from the query
  • If you really want to stick to the GetInt... functions, use SqlDataReader.GetOrdinal(column name) to get your column number.
  • Before assigning use SqlDataReader.GetFieldType to check the type of the column and use the corresponding GetInt.. function

Upvotes: 6

MajkeloDev
MajkeloDev

Reputation: 1661

Ok to make correct conclusion. Your columns DataType is int not bigint so You can't call it via .GetInt64(), because it's being unboxed and

You can't unbox Int32 -> Int64

As @Adam Houldsworth mentioned -

"Read what is given, not what You want"

To resolve Your problem just switch GetInt64() to GetInt32(). See below:

using (SqlDataReader dataReader = command.ExecuteReader())
{
    MyObject o = new MyObject()
    while (dataReader.Read())
    {
        o.Name= dataReader.GetString(0);
        o.Number = dataReader.GetInt32(1); // safe implicitly cast no need to make it manually
    }
}

If You don't know exact underlying DataType You can always do it other way. Please see code below:

using (SqlDataReader dataReader = command.ExecuteReader())
{
    MyObject o = new MyObject()
    while (dataReader.Read())
    {
        o.Name = dataReader.GetString(0);
        var number = dataReader.GetValue(1);

        try
        {
            o.Number = Convert.ToInt64(number);
        }
        catch(InvalidCastException)
        {
            Throw new Exception("Underlying DataType is not convertable to int 64.");
        }
    }
}

Upvotes: 0

Adam Houldsworth
Adam Houldsworth

Reputation: 64487

You need to read it as an int via GetInt32. You don't need to cast it to long because that can be done implicitly with safety by the runtime.

The runtime won't let you un-box from object to another data-type without first un-boxing object to the correct internal data type of whatever the object is, then casting to the next one. This quirk isn't anything to do with data readers:

static void Main(string[] args)
{
    int i = 1;

    object o = i;

    long l = (long)o; // invalid cast, trying to unbox to something other than the original type

    l = (long)(int)o; // No invalid cast, un-boxed to int then cast to long.
}

Specifically with SqlDataReader, because your underlying type is not Int64, it internally tries to un-box directly to the wrong type:

internal Int64 Int64 {
    get {
        ThrowIfNull();

        if (StorageType.Int64 == _type) {
            return _value._int64;
        }
        return (Int64)this.Value; // anything else we haven't thought of goes through boxing.
    }
    // set excluded for brevity
}

Taken from http://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlBuffer.cs,1b180f5ee49fc7ee

TL;DR; Do what the documentation says (as advised by HaraldDutch's answer) and read the correct expected type.

Upvotes: 2

Akshay
Akshay

Reputation: 1

Make sure that the value you are trying to get is bigint in database. BIGINT is the equivalent of Int64 (which is long in C#).If its other than bigint try using Convert.ToInt64()

Upvotes: 0

Related Questions