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:

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?

I'm not sure but try to use Convert.ToInt32(reader["Column1Name"]); — SergeyAn, Jan 21 '16 at 17:13
Are you sure that db value is null? It can be empty but it doesn't mean it is null. Otherwise there is no easy way to deal with it than to check for empty value. — SergeyAn, Jan 21 '16 at 17:20
I am sure it is null I can do a SQL profiler trace to see ti returns a null. Also reader.GetSqlInt32(reader.GetOrdinal("Column1Name")) returns null — dfmetro, Jan 21 '16 at 17:26
Thanks for clarification. Also, Column1Name and ColumnName1 are different fields? — SergeyAn, Jan 21 '16 at 17:33
It's a good question! On the other side I personally use the approach described in [the answer](http://stackoverflow.com/a/34764698/315935). It shows how to read generic information returned from reader to `List`. In the same way one can save the results in `List>`. As the result one can read the data and one have no problem with reading `null` because you read data by ordinal number. — Oleg, Jan 21 '16 at 18:12

score 5 · Accepted Answer · answered Jan 21 '16 at 18:10

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.

score 0 · Answer 2 · answered Jan 21 '16 at 18:01

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

SqlDataReader returns blank instead of null for an int field

2 Answers2