SqlDataReader returns blank instead of null for an int field

Question

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:

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

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

Answer 1

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.