SQLDataReader how to check for null column values?

Question

I am using a SQLDataReader to insert column data into a string[].

However, some of the column values are null. I am a little unsure how to check for this because when I do reader[column_pos] it says the value is "n/a".

What check can I perform so that I could do something like:

for(int i=0; i<myarray.Length;i++){
    if(something){
        myarray[i] = reader[i];
    }
    else
    {
        myarray[i] = "";
    }
}

and insert a blank sting whenver there is a null in the column.

Answer 1

You can use IsDBNull

for(int i=0; i<myarray.Length;i++){
    if(reader.IsDBNull(i)){
        myarray[i] = "";
    }
    else
    {
        myarray[i] = reader[i];

    }
}

Answer 2

You can also use if (reader[0] == DBNull.Value). If you have control over the queries run, you can alternatively use the SQL ISNULL(field, default_value) function to ensure the column returns a value.

For C# you may also use the if/else shorthand:

myArray[i] = (reader[i] == DBNull.Value) ? "" : reader[0];

Which translates to: If the reader value is DBNULL, use "", else use the reader value.

Answer 3

SqlDataReader class has the IsDBNull property.

You can write code as follow:

for(int i=0; i<myarray.Length;i++){
    if(myReader.IsDBNull(i)){
        myarray[i] = "";
    }
    else
    {
        myarray[i] = reader[i];

    } }

or better yet:

for(int i=0; i<myarray.Length;i++){
      myarray[i] = reader.IsDBNull(i) ? "" : reader[i];
}

Answer 4

myarray[i] = reader.IsDBNull(i) || reader[i] == DBNull.Value ? string.Empty : reader[i];