Nullable types in strongly-typed datatables/datasets - workarounds?

Question

Strongly-typed DataTables support "nullable" field types, except that the designer will not allow you change the setting to "allow nulls" for any value type fields. (ie: String types allow nullable, but int's do not).

The workaround is to call IsMyFieldNull() any time you want to get Myfield. If you access MyField when it does contain a null, it throws an eception.

This is a massive headache, in addition to causing many runtime bugs when a null showing up can cause your app to crash. I've complained to microsoft for years about this, yet every new release of visual studio still does not allow nullable value types to be used.

My question: Anyone know of a fancy extension method(s) that could be used to work around this major shortcoming?

Answer 1

In VS 2008 you can just enter a '0' in the nullvalue property.
If you are using vs2005 you must do it with an XML editor. You must add msprop:nullValue="0" as attribute to the column.

Answer 2

Not sure I totally go your question, but did you try alternatives like using "int?" instead of "int"?

Answer 3

I agree that it would a nice feature to allow nullable types.

If you set the "NullValue" property on the column to "-1" instead of "(Throw exception)" the property will return a -1 when the column is null instead of throwing an exception. You still have to set the column to allow nulls.

Alternatively, you can always set the datatype to "System.Object", and allow nulls=true. You can access the value of the column without having to use the "IsMyFieldNull()" method. The value of the column will be "System.DbNull.Value" when the column is null. If you don't like using "System.DbNull.Value", you can set the "NullValue" property to "(Nothing)" instead of "(Throw exception)" and then compare the object to a null reference.

Answer 4

Nullable types were introduced in .net 3.0, these can be used with data sets. You declare a nullable int like this int? myNullableInt = null Here is a link to the MSDN article: Nullable Types C#

Personally, I'd steer clear of nulls in databases in the first place (if you have the luxry of doing this). NULL is really there to allow an "Undefined" or "Unknown" status. It is rare to have this problem, for example a String Field containing a Surname will often be set to nullable, whereas defaulting to "" would be a much better option. Putting nulls into databases makes things unnecessarily hard Null Values In Databases, additionally it propagates nulls into code and you have to work to avoid null reference exceptions.

Unfortunately there is a lot of bad stuff written on the Internet about DBs (such as the over-use of null as being OK). These viewpoints are normally by people who really don't understand the theory behind them, another classic example being a DB without relations "because it is more flexible/quicker to handle these in code". This means the dev has to re-write existing [database-layer] functionality that the database inevitability does handle more efficiently and with much greater reliability. I say inevitability as, of course, the dev doing the re-write is re-implementing stuff that Oracle/Microsoft/Whoever have had large teams spending a great deal of time optimising etc. Yet every so often, you see someone advocating this as a design. This guy really understands databases, DBDebunkings he spent a lot of time trying to debunk a lot of nonsense arugments that take relational databases away from their theoretical roots.

Answer 5

I'm not sure why var x = !IsMyFieldNull() ? MyField : null (or similar) is such a headache.

I suppose you could write a wrapper around SqlDataReader to trap those null values somehow when you read the data into your DataTable, or you could write the equivalent of .TryParse() for your queries: something nice and encapsulated like:

var x = myDataTable.TryParse(myField);

where .TryParse is an extension method looking something like:

public static TryParse(DataRow myField)
{
    if(!myField == DbNull) //Or similar
       return myField.Value;
}

This is, as you can see, basically pseudo-code rough, but you get the idea. Write your code once, then call it as an extension method.