How to handle all DBNulls without checking for them every time (with a generated DataSet)?

Question

My original question was, as @nicholas correctly pointed, a little misleading, because this case is a little specific.

So here's the real problem:

I created Windows Form Application with DataGridView control and a DataSource generated from a table in my production database.

The goal of my program is to view the table, optionally edit comment columns and to export the table data to a XML file, which schema is strictly defined both with XSD and other independent constraints I cannot change.

It is especially important that I must not insert some fields into my XML output file if some values are not defined.

It's explicitly said I must not insert empty tags for those fields. That's the reason I need null values. To insert XML tags only where the value is given. That's why I need DBNull values, not any other default values.

The generated DataSet contains getters which throw an exception on accessing DBNull values. I cannot change generated code, it's a rule, you don't change generated code, especially the code which could (and would) be overwritten.

I solved this problem by surrounding each assignment of a nullable field with a "try/catch" block. If the generated getter throws an exception, my nullable field of my XML object will keep it's default null value, which is my expected behavior.

Later - my XML constructor will skip those fields while generating XML output.

But one problem remains: why does it have to be so ugly? What if I had a hundred nullable fields? Would I have to write a hundred "try/catch" blocks? It seems like a bug in the whole Microsoft mechanism to me. Or maybe there's a simple and elegant solution to this?

-- Here's the original content, to understand where the first answers came from:

Please, I do know I could check each field for DBNull, but there must be a automatic way for programmers. It would be the most stupid thing in whole .NET/C# - having to write "if" or "try/catch" statement for each column of my huge table! I've tried to use "foreach" on my table row type, but it didn't work. The exceptions are thrown at the very beginning of "foreach" body, it seems if I even try to touch DBNull, the exception is thrown. If I put my whole code block inside "try/catch" - it won't work because it will skip all fields behind first DBNull.

There must be a way to do it without putting a couple of dozens fugly checks in my code. So, what is the magic trick for the rows with DBNulls?

Write a function to extract the value, or a default value if DBNull, and use it when you need to read a column — asawyer, Aug 26 '12 at 13:12
[this answer](http://stackoverflow.com/a/3050744/1106367) might help. — Adam, Aug 26 '12 at 13:12
One line of code; see here: http://stackoverflow.com/questions/2433155/handle-dbnull-in-c-sharp — Bob Horn, Aug 26 '12 at 13:13
I forgot to mention: there is a generated getter which throws the exception - there's no way to touch DBNull value. And there's no way to change that getter - of course I can change it (for every NULL column) but it will be overwritten with each change to related objects. Dear Microsoft, why did you do such thing? I thought NULL is a normal value in TSQL, not the "always throwing a stupid pointless exception" one. I don't need a default value! — Harry, Aug 26 '12 at 16:25

Chris Gessler · Answer 1 · 2012-08-26T13:58:33.307

Write an extension:

public static T GetValue<T>(this IDataRecord @this, string name, T defaultValue = default(T))
{
    int ordinal = @this.GetOrdinal(name));
    return @this.GetValue<T>(ordinal, defaultValue);
} 

public static T GetValue<T>(this IDataRecord @this, int ordinal, T defaultValue = default(T))
{
    return @this.IsDBNull(ordinal) ? defaultValue : (T)@this.GetValue(ordinal);
}

Use it like so:

connstr = @"Data Source=.\sqlexpress;Integrated Security=SSPI;Initial Catalog=Test;";

string sql = "select WidgetId, WidgetName, WidgetValue, CreatedDt, ModifiedDt from dbo.widgets";

using (connection = new SqlConnection(connstr))
{
    connection.Open();
    using (command = new SqlCommand(sql, connection))
    using (IDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.Write(reader.GetValue<int>("WidgetId").ToString());
            Console.Write("\t");
            Console.Write(reader.GetValue<string>("WidgetName"));
            Console.Write("\t");
            Console.Write(reader.GetValue<int>("WidgetValue"));
            Console.Write("\t");
            Console.Write(reader.GetValue<DateTime>("CreatedDt"));
            Console.Write("\t");
            Console.Write(reader.GetValue<DateTime>("ModifiedDt"));
            Console.Write("\n");
        }
    }
}

Results:

1       Widget 1        0       7/17/2012 6:13:26 AM    1/1/0001 12:00:00 AM
2       Widget 2        0       7/17/2012 6:13:26 AM    1/1/0001 12:00:00 AM
3       Widget 3        0       7/17/2012 6:13:26 AM    1/1/0001 12:00:00 AM

score 0 · Answer 2 · answered Aug 26 '12 at 15:12

0

Remove the DbNull values prior to retrieving them from the server by modifying your SQL select query to call IsNull (or in the case of an Access back-end Nz). Definition here.

string sql = "select WidgetId, IsNull(WidgetName,""), " +
             "IsNull(WidgetValue,0), CreatedDt, ModifiedDt from dbo.widgets";

Assuming you have some kind of appropriate default value or use a sentinel value for catching the nulls later on.

answered Aug 26 '12 at 15:12

nicholas

2,969
20
39

I need null values. Null means I don't put a tag into output XML, "" and 0 and similar mean I need to put an empty tag or tag with "0" content. The XML schema and rules are not defined by me, so I would have to make a very dirty workaround for this. Nulls are just necessary for the whole thing to work. – Harry Aug 26 '12 at 16:51
@Harry, I'd recommend revising your original question to reflect these additional constraints / desired results of your program (including your comment on the 'generated getter') – nicholas Aug 26 '12 at 19:12
you're definitely right. I think my question is a little bit misleading. – Harry Aug 28 '12 at 06:35

How to handle all DBNulls without checking for them every time (with a generated DataSet)?

2 Answers2