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

If your column is a nullable int, but you are trying to assign to an int, with a default value of 0 :

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

If your column is a nullable int, and you want to assign to a nullable int :

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