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
– SergeyAn, Commented Jan 21, 2016 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
– SergeyAn, Commented Jan 21, 2016 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
– dfmetro, Commented Jan 21, 2016 at 17:26
Thanks for clarification. Also, Column1Name and ColumnName1 are different fields? — SergeyAn
– SergeyAn, Commented Jan 21, 2016 at 17:33

Michael Liu · Accepted Answer · 2016-01-21 18:10:32Z

5

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.

answered Jan 21, 2016 at 18:10

Michael Liu

55.8k14 gold badges126 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Will Parks · Accepted Answer · 2016-01-21 18:01:20Z

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

Collectives™ on Stack Overflow

SqlDataReader returns blank instead of null for an int field

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related