MySQLDataReader retrieving Null value problem in c#

Question

I am currently working on a C# project that will export MySQL Data. The export is for any database within the server so I am not going to know what fields and the data types that are in the table and I am not going to know if a field in the table allows null values or not.

During testing, I have found that the export is working fine but if the field allows null when the mysql data reader goes gets to the row which is null it displays an error SqlNullValueException, data is null.

I have tried doing if (reader.getString(field) == null) {} but it is still displaying the error.

How do I cope with Null values in the database.

Thanks for any help you can provide.

score 22 · Accepted Answer · answered Aug 09 '11 at 15:45

22

You need to test for null explicitly in a reader so:

if (!reader.IsDbNull(field)) {
    var value = reader.GetString(field);
    // ... do stuff here ...
}

answered Aug 09 '11 at 15:45

Deleted

4,368
1
18
17

1

Shouldn't that be `.IsDbNull(reader["field"])` ? Or am I wrong? – Hossein Nov 18 '12 at 18:28
2

Depends on the reader implementation/helper methods. You would be right that the base interface IDataReader only takes integer column indexes. MySqlDataReader however has overloads to take a string field name, which just does a reader.GetOrdinal lookup. – Nicholi Mar 14 '13 at 01:42

score 4 · Answer 2 · edited May 23 '17 at 12:18

In this blog Post there is an good extension method for the reader

SQL Data Reader - handling Null column values

I Changed it to the IDataReader Interface

public static string GetStringSafe(this IDataReader reader, int colIndex)
    {
        return GetStringSafe(reader, colIndex, string.Empty);
    }

    public static string GetStringSafe(this IDataReader reader, int colIndex, string defaultValue)
    {
        if (!reader.IsDBNull(colIndex))
            return reader.GetString(colIndex);
        else
            return defaultValue;
    }

    public static string GetStringSafe(this IDataReader reader, string indexName)
    {
        return GetStringSafe(reader, reader.GetOrdinal(indexName));
    }

    public static string GetStringSafe(this IDataReader reader, string indexName, string defaultValue)
    {
        return GetStringSafe(reader, reader.GetOrdinal(indexName), defaultValue);
    }

you're right is much better an extension instead that do the same thing all code — MrAlex6204, Feb 27 '15 at 01:32

score 2 · Answer 3 · answered Aug 09 '11 at 15:55

.Net does not use a null literal to distinguish database nulls. I can only speculate, but I suspect the reason for this is that many common database column types (int, float, etc) are value types, and comparing a value type to null won't work at all how you expect.

Instead, check for DBNull.Value or use the .IsDbNull() function.

score 1 · Answer 4 · answered Feb 27 '15 at 00:44

1

You could always use the c# conditional operator '?' like so...

string val = (reader.IsDBNull(columnIndex)) ? "" : reader.GetString(columnIndex);

answered Feb 27 '15 at 00:44

Oscar

11
1

score 1 · Answer 5 · answered Aug 09 '11 at 17:29

1

I have had problems with using the GetString() method on fields that allow null values. I worked around this by doing something like:

reader[0].ToString()

answered Aug 09 '11 at 17:29

sithius92

136
4

Illuminator · Answer 6 · 2013-01-11T10:42:13.133

0

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

edited Jan 11 '13 at 10:42

answered Jan 11 '13 at 10:33

Illuminator

446
6
12

MySQLDataReader retrieving Null value problem in c#

6 Answers6

Linked