SQL Nulls and Strongly Typed DataSets

[Robert Gaston]

I've seen posts on this, but never a confirmation that anyone's suggestions
actually worked...

I have a sql stored proc that returns multiple result sets. I used the VS
dataset designer, and just drug the stored proc onto the designer to build
the strongly typed dataset.

One of the resultsets includes nulls in some columns. When I attempt to
fill this dataset using adapter.fill, I get type casting errors on every one
of these null fields during the fill if the type is a value type ( decimal,
double, float, datetime ).

I have edited the xsd, adding the nillable=true attribute to these columns,
and regenerated the dataset, but this does not fix anything.

I can turn off the constraint checking, and perform the fill, but when I
turn constraint checking back on, I get the same exceptions of course.

It seems to me that when the strongly typed dataset class is generated, the
nillable attribute is ignored, although it does generate the IsMyFieldNull
and SetMyFieldNull methods.

Any thoughts on this would be greatly appreciated!
Robert Gaston

 

[Robert Gaston]

The solution to this APPEARS to be as follows -
http://msdn.microsoft.com/library/d...tml/cpconUsingAnnotationsWithTypedDataSet.asp

Since a value type cannot hold a null, you must make some provision for a
default value. You can annotate your xsd field elements using the syntax
codegen:nullValue="x" where x is an actual value, or _throw, _null, or
_empty. _empty will use a value created using the default constructor for
the type, but only for strings, sadly. For value types, you pretty much
have to spell out a value. So your null integer becomes zero, your null
date becomes 01/01/0001, etc.

This is most annoying; I hope someone can post a better solution, besides
maybe "never allow nulls in your database" or "always expose your fields as
objects", or "don't use constraint checking"