Nullable Int Column in DataSet

Question

I'm working with .NET strongly-typed datasets and have a table with a nullable int column (and a nullable DateTime column as well).

Apparently there is a bug with the dataset designer that prevents having nullable columns on these data types. The designer only allows "throw exception" as the default behavior for null values. Unfortunately, when using a nullable data type in the database, a null value IS a legitimate value but results in a thrown exception when attempting to retrieve this value from a data row.

I've seen several newsgroup postings about this problem but have yet to see any decent workarounds to this issue.

I'd love to hear how others have dealt with this problem.

Thanks.

Herrle

CLARIFICATION: The dataset is using DBNull (the default value), not null. – Herrle

Answer 1

DBNull was basically brought in to play to deal with non nullable value types, pre .NET 2.0. Due to the design of ADO.NET, there's no way you can avoid DBNull, unless you chose a more direct approach. DBNull is built-in the core of ADO.NET, so you'll have to learn to live with that, if you want to keep using it.

If you provide your own data transport objects instead of relying on the generic System.Data namespace, you can check (while reading in the results with a data reader) if the value is null, but you'll need some way to generate strongly typed objects and mappings because that's really tedious work.

To the extent of my knowledge, DBNull is built in to the design of ADO.NET and the best way to build your apps if you use that, is to coalesce (normalize) DBNull and null. Basically, provide your own DbConvert class which intercepts DBNull and returns an actual null reference if the value is DBNull. This is a minimal requirement, but as soon as that's done you'll have less DBNull values floating around to worry about.

Albertoalberts

Answer 2

I think this post on ASP.NET forum will be some help for the question: Strongly-Typed DataSet/Nullable column issue

The only way to set such properties to null is to use the helper methods that the dataset generator also creates. The methods are named after your column name, so in your case, you should have methods on the data row object called IsApprovingUserNull() and SetApprovingUserNull().

Dayak

Answer 3

It's been a while since I used typed DataSets, but I see in my old code that use codegen:nullValue attribute. I don't think it's supported by the designer, at least not in VS2005 (which I used for that project), so you'd have to open your xsd file in the xml editor and do it by hand.

The resulting xml would look something like this:

<xs:sequence>
    <xs:element 
        name="MyIntColumn" 
        codegen:nullValue="0" 
        type="xs:int" 
        minOccurs="0" />
    <xs:element 
        name="MyBoolColumn" 
        codegen:nullValue="false" 
        type="xs:boolean" 
        minOccurs="0" />
    <xs:element 
        name="MyDateColumn" 
        codegen:nullValue="1900-01-01" 
        type="xs:dateTime" 
        minOccurs="0" />
</xs:sequence>

Seclusion

Unfortunately this introduces the concept of "magic numbers", something I'd like to avoid if possible. (That is, "Null" is very clearly null, while "0" is not) – Micropathology