SqlDataReader returns a null value instead of zero for an int field

Question

I am using SqlDataReader to retrieve 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 field intfrom the database, it is reader["Column1Name"]empty, so the code throws an InvalidCastException at run time.

I tried

reader.GetInt32(reader.GetOrdinal("Column1Name"))

but this raises a System.Data.SqlTypes.SqlNullValueException.

I also tried

reader.GetSqlInt32(reader.GetOrdinal("Column1Name"))

which returns null, but the type SqlInt32, and not the int?way I want.

I finished work

if (!reader.IsDBNull(reader.GetOrdinal("Column1Name"))) 
    int? var1 = (int?)reader["Column1Name"];

which is working.

Questions:

• Is there an easier way than calling a method IsDBNull?

• Why does it reader["Column1Name"]return blank instead nullif db is NULL and the field is int?

Answer 1

reader["Column1Name"] , null, db null, - int?

, reader["Column1Name"] DBNull.Value, NULL. ( Visual Studio , DBNull.ToString() .) DBNull.Value int? .

IsDBNull?

, as :

int? var1 = reader["Column1Name"] as int?;

DBNull.Value int, as null.

Answer 2

# int? (int?). - , 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);
}

, , null .

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);
}