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

I just had to find a work-around for this. I needed to change some old code for a web page, written for ASP.NET 2.0. The page uses Telerik RadMenu, a menu control. This component requires the root elements to have proper DBNull-values (for parentIDs). So, when I compiled the old code, the RadMenu component gave me lots of problems. First exceptions regarding Constraints, then it did not understand which elements were root elements and the whole thing looked horrible.

But I solved it and this is what worked for me.

In the Properties page of the ParentID column in the Table Adapter designer, I used: - AllowDBNull: true - DefaultValue: -1 (-1 is a value that does not occur normally for that column) THe NullValue-property stayed at "Throw Exception" as it was impossible to change for me.

And in the code using the values from the table adapter, I used this construct (VB.NET code, not C#, as this question is tagged):

Dim MenuBarTable As DAL.Page.MenuBarDataTable 'The Table Adapter
MenuBarTable = PageObj.GetMenuBar()  'The generated Get function 
MenuBarTable.ParentIDColumn.AllowDBNull = True 

    For Each row As Page.MenuBarRow In MenuBarTable.Rows
        If row.IsParentIDNull() Then 
            row.SetParentIDNull() 
        End If
    Next

The code generated for the table adapters generates two functions for each column that should allow DBNULLs. They are meant be used when dealing with NULLs, but it is a clumsy solution by Microsoft. What happens behind the scenes is that the the table adapter will output the column's DefaultValue instead of NULL from the Get-function. I call that a "simulated NULL" or a "fake NULL".

The function IsParentIDNull() will actually check if the row contains this "fake NULL", e.g. the column's DefaultValue, and when it does, I'm inserting a proper DBNull using the SetParentIDNull() function.

This solution works for me, but is not very elegant, nor is it very efficient, but it could be of help to someone else, I hope.

Answer 2

You could do this: set AllowDbNull to true, if it is not set; DefaultValue remains on ; NullValue remains on (Throw exception). Then from code when you want to set to null a column you may use built in method Set_Column_Null(). Look my example:

if (entry.PosX.HasValue)
    newRow.PosX = entry.PosX.Value;
else
    newRow.SetPosXNull(); 

Answer 3

If you are using .Net 3.5 or higher, may be these extensions might be of use to you: http://msdn.microsoft.com/en-us/library/system.data.datarowextensions.field.aspx

According to the documentation, it fully supports nullable types. It allows you to use constructs like

MyRow.Field<int?>("MyField") 

and if you assign it to or from an existing variable of the correct type, the compiler can infer the type automatically and you can leave out the type specifier, making it as short as

int? myVar = MyRow.Field("MyField");
MyRow.Field("MyField") = myVar;

Still not perfect, but a lot more legible than having to use IsMyFieldNull() etc everywhere.

Oh and if you want to be more on the safe side regarding misspelling column names, you can use things like

MyRow.Field(MyTable.MyFieldColumn)

Don't forget to add a reference to System.Data.DataSetExtensions.

Answer 4

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.

Answer 5

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 6

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 7

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.