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.

Viacheslav Ivanov · Accepted Answer · 2016-08-03T05:27:50.827

34

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

edited Aug 03 '16 at 05:27

answered Jan 19 '11 at 19:39

Viacheslav Ivanov

1,535
13
22

You are the Winner 0f your very own green check. I used the second one. Thanks! – rd42 Jan 19 '11 at 19:42
small typo in the in second example: change "IsDbNull" to "IsDBNull" – 4mla1fn Aug 02 '16 at 19:09
This is crazy long for a null check. – ed22 Mar 30 '19 at 01:29

Axarydax · Answer 2 · 2011-01-19T19:41:22.917

14

~~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.

edited Jan 19 '11 at 19:41

answered Jan 19 '11 at 19:20

Axarydax

16,353
21
92
151

2

I think it should be `DBNull.Value` – VoodooChild Jan 19 '11 at 19:25
Heres what I get Error 1:Operator '==' cannot be applied to operands of type 'string' and 'System.DBNull' – rd42 Jan 19 '11 at 19:33

score 4 · Answer 3 · answered Jan 19 '11 at 19:35

4

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

answered Jan 19 '11 at 19:35

Sam B

2,441
2
18
20

score 2 · Answer 4 · answered Jan 11 '13 at 10:40

2

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

answered Jan 11 '13 at 10:40

Illuminator

545
6
13

score 1 · Answer 5 · answered Nov 25 '13 at 21:14

1

Here's one I like:

var MyString = rdr["column"] is DBNull ? "It's null!" : rdr.GetString("column");

E.g. (for the original requirement):

queryResult.Egresstime = rdr["timeOut"] is DBNull ? "Logged in" : rdr.GetString("timeOut");

answered Nov 25 '13 at 21:14

Danny Beckett

20,529
24
107
134

score 1 · Answer 6 · answered Jan 19 '11 at 19:21

1

Change null to DBNull.Value.

answered Jan 19 '11 at 19:21

Tyler Treat

14,640
15
80
115

Heres what I get Error 1:Operator '==' cannot be applied to operands of type 'string' and 'System.DBNull' – – rd42 Jan 19 '11 at 19:35

score 1 · Answer 7 · answered Jan 19 '11 at 19:23

1

You can also do:

If (string.IsNullOrEmpty(rdr.GetString("timeOut"))

answered Jan 19 '11 at 19:23

VoodooChild

9,776
8
66
99

Has the same effect as (rdr.GetString("timeOut") == null) – rd42 Jan 19 '11 at 19:35
1

@rd42: so what is the actual value in that field when you debug? just curious? – VoodooChild Jan 19 '11 at 19:42
The value is: 12/7/2010 10:16:46 AM Thanks for your help. – rd42 Jan 19 '11 at 20:21

Udi Y · Answer 8 · 2021-03-14T14:24:19.227

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

score 0 · Answer 9 · answered Aug 08 '18 at 02:11

Here is a method that I created to read DBNull and return a default(T) incase:

   private T GetNullable<T>(MySqlDataReader reader, int ordinal, Func<int, T> getValue)
        {
            if (reader.IsDBNull(ordinal))
            {
                return default(T);
            }
            return getValue(ordinal);
        }

It can be used like this:

   if (reader.Read())
            {
                account = new Account();
                account.Id = reader.GetInt32(0);
                account.Name = reader.GetString(1);
                account.MailVerifiedAt = GetNullable(reader, 2, reader.GetDateTime);
                account.MailToken = GetNullable(reader, 3, reader.GetString);
            }

The generic type T will be resolved based on the return value of the reader.- method. If it returns a string you will receive a null incase of DBNull. If it is an int it will return 0, etc.

Note: for integer values it might not be desired to get a 0 so be careful.

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

9 Answers9

Linked