CODEWITHSUNDEEP
c#sqlstringbuilderdatareader
cc0
cc0

Reputation: 1950

SQL DataReader how to show null-values from query

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

Upvotes: 1

Views: 914

Answers (4)

Rob
Rob

Reputation: 45771

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

Upvotes: 4

Anthony Faull
Anthony Faull

Reputation: 17957

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

Upvotes: 1

David
David

Reputation: 15360

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

Upvotes: 1

dcp
dcp

Reputation: 55444

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

Upvotes: 4

Related Questions