DataRow.Field<T>(string Column) throws invalid cast exception

Question

Good day,

IDE Visual Studio 2010
.NET 3.5
Platform WinForms

The SO question " difference between getting value from DataRow " refers.

I have a database table with a column [ID] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY. When querying this table and storing the values in local variables I get an invalid cast exception; sample code:

string sQuery = @"
    SELECT [ID], [Description]
    FROM [Sources]
    ORDER BY [Description] ";

using (DataTable dtSources = SQLHelper.Fetch(sQuery))
{
    foreach (DataRow drSource in dtSources.Rows)
    {
        int iID = drSource.Field<int>("ID"); // InvalidCastException
        string sDescrption = drSource.Field<string>("Description");
    }
}

When stepping the execution and performing a "Quick Watch" on the faulty line I discovered that, by changing the line to drSource.Field<object>("ID"), the cell value type is that of short and not of int. Why would this happen when, in the table definition, this is clearly and int? Furthermore, short should be implicitly converted to int since short is smaller and should "fit" right?

Answer 1

Agree with Richard's answer just better to use,

int iID = Convert.ToInt32(drSource["ID"]);

Answer 2

According to implementation of Field extension, your field has DbNull value.

public static T Field<T>(this DataRow row, string columnName)
    {
        DataSetUtil.CheckArgumentNull<DataRow>(row, "row");
        return UnboxT<T>.Unbox(row[columnName]);
    }

UnboxT is a private class that provides methods for converting object to T. In your case ValueField converter is used:

private static class UnboxT<T>
{
    internal static readonly Converter<object, T> Unbox;

    static UnboxT()
    {
       DataRowExtensions.UnboxT<T>.Unbox =  
          new Converter<object, T>(DataRowExtensions.UnboxT<T>.ValueField);
    }

    private static T ValueField(object value)
    {
        if (DBNull.Value == value)
        {
            // You get this exception 
            throw DataSetUtil.InvalidCast(Strings.DataSetLinq_NonNullableCast(typeof(T).ToString()));
        }
        return (T) value;
    }
}

Answer 3

Another way to get your row value into an int variable is to use 'object' and convert it to Int32 like so:

int iID = Convert.ToInt32(row.Field<object>("ID"));

Answer 4

Out of curiosity, what happens if you explicitly cast it yourself from the key/value collection?

int iID = (int)drSource["ID"];