Checking if a record in datareader is NULL

Question

I have browsed thru other postings on S/O, but I can't find a solution that works for me.

I have a datareader that might return a null value, and if so, I want to value to equal blank

txtMiddleName.Text = rdrGetUserInfo.GetString(1) ?? "";

The string above does not work. When I walk thru the code, the code jumps to my error trapping block;

Any ideas?

Answer 1

It's because the rdrGetUserInfo is returning DBNull which is a class.

Before getting the string do a check for dbnull it's something like (been a while since I used one!):

if (!rdrGetUserInfo.IsDBNull("someField"))
 txtMiddleName.Text = rdrGetUserInfo.GetString(1);

Answer 2

You should check the column for DbNull before calling GetString.

Answer 3

Try txtMiddleName.Text = rdrGetUserInfo.IsDBNull(1) ? "" : rdrGetUserInfo.GetString(1);

Answer 4

You can use the IsDBNull method on the DataReader.

if (!rdrGetUserInfo.IsDBNull(1))
{
    txtMiddleName.Text = rdrGetUserInfo.GetString(1);
}

Answer 5

If(rdr("Field") == DBNull.Value) 
  'assign String.Empty 
myString = string.Empty;
else
  'assign value
   myString = rdr("Field");

Or to shorten this up you could do this:

txtMiddleName.Text = rdrGetUserInfo.IsDBNull(1) ? String.Empty : rdrGetUserInfo.GetString(1);

Answer 6

IsDbNull(int) is usually much slower that using methods like GetSqlInt32 and then comparing to DBNull.Value or using it's own .IsNull Like:

    public static int Int32(this SqlDataReader r, int ord)
    {
        var t = r.GetSqlInt32(ord);
        return t.IsNull ? default(int) : t.Value;
    }

Tried a few template solutions but to no avail so far. The problem is that all Sql-types (SqlInt32 here) types are actually structs and while they all have .Value property C# doesn't have real templates to handle that. Also they have their own INullable interface which has only .IsNull and is not conpatible with Nyllable<>.

I suspect that one would need full set of Sql-types as C# templates or to add ICOnvertible to them in order to be able to have just one or two templated methods.

If someone has maybe an idea with a functional trick or two speak up :-)