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
?
Why does
reader["Column1Name"]
return blank instead of null if the db value is null and the field is anint
?
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
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With