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?

Hackbut

You could modify the row properties definition in the DataSet's .Designer file for the table, but it seems that it is re-created every time the DataSet is modified through the VS designer, even if you dont touch the specific table. – Furlani

Answer 1

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.

Riotous

Ive been using this, and it helps, but it is not strongly typed. Im looking for strongly typed datatables that allow nullable field types (for value types) – Hackbut

I agree. I also agree that it would be very nice if they included something for it in an upcoming Visual Studio release. This is the closest I got using the current tools. I've been looking into T4 templating but that looked like too much work for what it's worth (to me). – Riotous

yes, difficult to justify rebuilding the entire dataset generator tools just to add one feature. (yes, I've looked into it as well ;) – Hackbut

Just came across this...handy extension, and how is this not strongly typed? The final method (using the strongly typed column param) avoids magic strings completely as far as I can tell. – Anastasio

Like boomhauer and M-Peror, I too was tempted to roll my own dataset generator, but was also put off by the cost-benefit ratio. Using the final method presented above seems to be making the best of a bad situation for me. – Dentiform

can you see doing an extension method to encapsulate this? Such that MyRow.MyField could be directly nullable (and internally encapsulate MyRow.Field)? – Hackbut

C# doesn't support methods returning l-value references so MyRow.Field("MyField") = myVar; gives me a compiler error. Do you mean MyRow.SetField("MyField", myVar) instead? – Rwanda

Answer 2

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.

Kyrakyriako

The problem is, a "0" is a legit value.. what I really need (or want) is null value = null, and the data type be int? instead of int. – Hackbut

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.

Murphree

-1 is a valid value in the db, so cant use that. But keep im mind im also talking about all value types. DateTime, doubles, etc. – Hackbut

You could also, by standard, use the maximum value of the value type to represent null. For a byte it is 255 and for a int32 it is 2,147,483,647 (int32.MaxValue). Note that the maximum value for a date value will be different than what you can expect from the database. Date.MaxValue does not equal the largest SQL DateTime or SmallDateTime. In general, the maximum value of a value type is never used in practice, while in the case of a byte, the minimum value (0) is used often. – Murphree

Carter, there are a number of workarounds, none of which are acceptable in the age of nullable types. ;) – Hackbut

Answer 4

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(); 

Disquieting

Answer 5

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.

Emprise

Answer 6

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.

Booker

Allen, the problem is the verbosity of a real world scenario: var x = MyDataTable.IsMyfieldNull()?(int?)null: (int?)MyDataTable.MyField; Now repeat that 5 times for 20 tables with 20 columns in each table. Not fun ;) Oh and don't forget the code needed for assignment as well.. – Hackbut

Answer 7

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.

Geisha

I didnt ask for a sermon on the evils of nulls. Heard it 1000 times. I'm looking for an easy way to make nullables work with strongly typed datasets. Perhaps an extension method or similar. And, nullables were introduced in .net 2, not 3. – Hackbut

How rude. OK I got the framework version wrong but that hardly matters, what I said still stands. I'm not exactly a noob, I've been .netting since the 1.0 beta. Pity the sermons didn't take. – Geisha

Keith, not trying to be rude, just that you answered a question I didn't ask. – Hackbut

Umm I am pretty sure he answered it. There are nullable value types in C#. He linked the reference and gave you an example. So use the nullable types to retrieve your data and maybe build an extension method or a static function to handle converting between the nullable and non-nullable value types so you can pass it to other modules you may not control. – Midcourse

Anton- I know what nullable types are. My question is about forcing strongly typed datasets to support them via an extension method, or some equally "clever" method of workaing around their lack of support for them.. ie, without writing a hand-coded wrapper around every field on every datatable etc. Make sense? – Hackbut

nullable types are not accepted as types for DataColumns, sermon or not. – Bauman