SQL DataReader how to show null-values from query

Question

I have a DataReader and a StringBuilder (C#.NET) used in the following way;

                while (reader.Read())
                {
                    sb.AppendFormat("{0},{1},{2},",reader["Col1"], reader["Col2"], reader["Col3"]);
                }

Which works great for my use, but when a row is null I need it to return "null", instead of just "". What would be a good way of accomplishing that?

Suggestions are very appreciated

Answer 1

reader["Col1"] == DBNull.Value ? "null" : Convert.ToString(reader["Col1"])

Answer 2

Try:

Convert.IsDBNull(reader["Col1"]) ? "null" : reader["Col1"]

Alternatively, if you're going to be using this repeatedly, this is probably an ideal candidate for a tightly scoped Extension Method, for example:

public static class ExtensionMethods
{
    public static object GetValueOrNull(this SqlDataReader reader, string key)
    {
        return Convert.IsDBNull(reader[key]) ? (object)"null" : reader[key];
    }
}

So you could then write:

var valueOfReader = reader.GetValueOrNull("Col1");

Which would definately make things tidier if you needed to use this logic multiple times inside one StringBuilder.AppendFormat call:

while (reader.Read())
{
    sb.AppendFormat("{0},{1},{2},",reader.GetValueOrNull("Col1"), reader.GetValueOrNull("Col2"), reader.GetvalueOrNull("Col3"));
}

Answer 3

reader.IsDBNull(indexOfColumn) ? "null" : reader[indexOfColumn].ToString();

Answer 4

(string)reader["Col1"] ?? "null"