Q: SqlDataReader returns blank instead of null for an int field I am using SqlDataReader to fetch data from a SQL Server 2012 database:
SqlConnection connection = (SqlConnection)_db.Database.GetDbConnection();
await connection.OpenAsync();
SqlCommand command = new SqlCommand("dbo.[sp_MyStoredPrc] @InputId=1", connection);
var reader = await command.ExecuteReaderAsync();

if (reader.HasRows)
{
    while (reader.Read())
    {
        int? var1 = (int?)reader["Column1Name"];
    }
}

When reading a NULL int field from the database, 
reader["Column1Name"] is blank so the code throws an InvalidCastException at runtime.
I have tried
reader.GetInt32(reader.GetOrdinal("Column1Name"))

but this throws System.Data.SqlTypes.SqlNullValueException.
I have also tried
reader.GetSqlInt32(reader.GetOrdinal("Column1Name"))

which returns null, but the type is SqlInt32 and not int? like I want.
I ended up doing
if (!reader.IsDBNull(reader.GetOrdinal("Column1Name"))) 
    int? var1 = (int?)reader["Column1Name"];

which works.
Questions:


*

*Isn't there a simpler way than calling the IsDBNull method?

*Why does reader["Column1Name"] return blank instead of null if the db value is NULL and the field is an int?

A: 
Why does reader["Column1Name"] return blank instead of null if the db value is null and the field is an int?

Actually, reader["Column1Name"] returns DBNull.Value if the database value is NULL. (In the Visual Studio debugger, the value appears to be blank because DBNull.ToString() returns an empty string.) You cannot cast DBNull.Value directly to int? using the cast operator.

Isn't there a simpler way then calling the IsDBNull method?

Yes, use the as operator instead of a cast:
int? var1 = reader["Column1Name"] as int?;

Because DBNull.Value isn't an int, the as operator returns null.

A: C# cannot cast a string to a int? using (int?). The safest way is to use the conditional operator to check for null, then manually set the value to null.
//Get the column index to prevent calling GetOrdinal twice
int COLUMNNAME1 = reader.GetOrdinal("ColumnName1");

while (reader.Read())
{
    int? var1 = reader.IsDBNull(COLUMNNAME1) ? null : reader.GetInt32(COLUMNNAME1);
}

Similarly, if you have non null data types, you can check them for null and set them to their default values.
int STRINGCOLUMN = reader.GetOrdinal("StringColumn");
int INTCOLUMN = reader.GetOrdinal("IntColumn");
int DATECOLUMN = reader.GetOrdinal("DateColumn");
int BOOLCOLUMN = reader.GetOrdinal("BoolColumn");

while (reader.Read())
{
    string var1 = reader.IsDBNull(STRINGCOLUMN) ? "" : reader.GetString(STRINGCOLUMN);
    int var2 = reader.IsDBNull(INTCOLUMN) ? 0 : reader.GetInt32(INTCOLUMN);
    DateTime var3 = reader.IsDBNull(DATECOLUMN) ? DateTime.MinValue : reader.GetDateTime(DATECOLUMN);
    bool var4 = reader.IsDBNull(BOOLCOLUMN) ? false : reader.GetBoolean(BOOLCOLUMN);
}

