How to check for NULL in MySqlDataReader by the column's name?

Question

How can I check for a NULL value in an open MySqlDataReader?

The following doesn't work; it's always hitting the else:

if (rdr.GetString("timeOut") == null)
{
    queryResult.Egresstime = "Logged in";
}
else
{
    queryResult.Egresstime = rdr.GetString("timeOut");
}

rdr.IsDbNull(int i) only accepts a column number, not name.

Solution 1

var ordinal = rdr.GetOrdinal("timeOut");
if(rdr.IsDBNull(ordinal)) {
  queryResult.Egresstime = "Logged in";
} else {
  queryResult.Egresstime = rdr.GetString(ordinal);
}//if

or

if(Convert.IsDBNull(rdr["timeOut"])) {
  queryResult.Egresstime = "Logged in";
} else {
  queryResult.Egresstime = rdr.GetString("timeOut");
}//if

Solution 2

if(rdr.GetString("timeOut") == DBNull.Value)

null is not the same as DBNull

I am sorry, wrong answer, Sam B is right. I mistook this for DataRow stuff.

SqlDataReader does have strongly typed GetString() and provides IsDBNull(int column) for this case.

Solution 3

You must call rdr.IsDBNull(column) to determine if the value is DbNull.

Solution 4

You can compare the object that retrive from NULL field with DBNull.Value.

Solution 5

    private T GetNullableValue<T>(MySqlDataReader rdr, string parameterName)
    {
        object value = rdr[parameterName];
        if (value is DBNull)
            return default;

        return (T)value;
    }

And the usage for example:

string message = GetNullableValue<string>(rdr, "Message");
bool flag = GetNullableValue<bool>(rdr, "Flag");
DateTime startTime = GetNullableValue<DateTime>(rdr, "StartTime");

View more solutions

Comments

[VoodooChild]

I think it should be DBNull.Value

[rd42]

Heres what I get Error 1:Operator '==' cannot be applied to operands of type 'string' and 'System.DBNull'

[rd42]

Has the same effect as (rdr.GetString("timeOut") == null)

[rd42]

Heres what I get Error 1:Operator '==' cannot be applied to operands of type 'string' and 'System.DBNull' –

[rd42]

You are the Winner 0f your very own green check. I used the second one. Thanks!

[VoodooChild]

@rd42: so what is the actual value in that field when you debug? just curious?

[rd42]

The value is: 12/7/2010 10:16:46 AM Thanks for your help.

[4mla1fn]

small typo in the in second example: change "IsDbNull" to "IsDBNull"

[ed22]

This is crazy long for a null check.