Microsoft: Please allow Null values to be returned from strongly typed datasets!
(1 votes, average: 5 out of 5)
Loading ...March 18, 2008 by mike
Recently while working on an application created in Visual Studio 2005 (also applies to 2008) on the .NET 2.0 framework I noticed a strange error when running with live customer data. The error was, “Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints”:
And if you’re debugging with all errors thrown, you’ll see something like this:
Â
StrongTypingException was unhandled
The value for column ‘FolderParent_ID’ in table ‘Folders’ is DBNull
This error is caused because the constraints of the strongly typed dataset are such that one or more columns contains an invalid value. In this case a customer table had a null value in a foreign-key column which links it with a customer_type / category table. The system had fallen over when calling the TableAdapter Fill method for the customer datatable.
Although the underlying database allows nulls in this column, Visual Studio’s much promoted dataset designer (still in 2008) doesn’t allow the developer to allow null values in any other data type than a System.String.
For example on a product I am currently working on which uses Visual Studio 2008, .NET 2.0 and targets a local Access .accdb data store I have a table called “Folders”. This table is meant to allow the user to customise a Treeview to display a set of hierarchical information. The table has a column “FolderParent_ID” which is a foreign key integer value to the ID of the same table and every “folder” optionally is a child / has a parent. So what do we do with folders that don’t have parents? Leave the FolderParent_ID as null. Only because of this bug in the Dataset Designer, we are forced to throw an exception each time a null is encountered. Result? One very frustrated developer and countless confusing error messages.
The Dataset Designer
Column properties shown (see NullValue on the right)
The table definition in Access 2007
Note: “Required” == “Do not allow nulls” == No == Allow Nulls
Null values were incorporated using the System.Generics namespace in the .NET 2.0 framework for all datatypes, but someone appeared to have forgotten to tell the Dataset designer team, because when you try to change the NullValue property to anything other than (Throw Exception) you get a Properties Window warning and the property returns to (Throw Exception):
Property value is not valid
The value entered isnot valid for the current data type
NullValue
(Throw exception)
If your DataType is a System.String you can use the return: (Empty) or return: (Nothing) value in the designer. But this won’t do, since we’re using strongly typed datasets so that our types and rules are preserved. It seems that adding a table to the Dataset Designer that uses non-string/character columns which allow nulls confuses the DataSet Designer and causes it to assign unwanted exception behaviour to your columns rather than simply returning the nulls. It’s not just Access either. I’ve tested this with SQL Server 2005 & SQL Server Express with the same results. There is absolutely no reason for this and Microsoft have had since November 2005 (release of .NET 2.0) to fix this problem and implement nullable non System.String datatypes in the Dataset Designer.
Workaround
The first workaround I tried was to edit the code that the Dataset Designer creates in order to check for, and handle null values. This only works until you open the Dataset Designer again. Once you open the Dataset in the Dataset Designer you lose all your hard earned customised code as the Dataset Designer will overwrite your changes each time it is used. Nice one, Microsoft.
You could create your own partial class but this opens up a whole new can of worms. Before you waste your time, it won’t work. Try it and see.
And yet there is hope; open your DataSet .XSD file manually (you can use the Visual Studio XML editor to do this) and find your data column expressed in XML as an element. Add the values: msprop:nullValue=“-1” nillable=“true“. This will cause your column (in this example, integer) to return “-1″ in place of the null value and not throw an exception. Amending the XSD directly appears to persist (i.e. not lose the amendment) and seems to do the job ok.
Example
<xs:element name=“FolderParent_ID” msprop:nullValue=“-1” nillable=“true” msprop:Generator_UserColumnName=“FolderParent_ID” msprop:Generator_ColumnVarNameInTable=“columnFolderParent_ID” msprop:Generator_ColumnPropNameInRow=“FolderParent_ID” msprop:Generator_ColumnPropNameInTable=“FolderParent_IDColumn” type=“xs:int” minOccurs=“0” />
Comments Please
If you’re a software developer you have probably found this page by ferociously Googling for the answer to your problem. Please leave me a comment. I’d be interested to find out if you’ve got a better solution for handling nulls or if you can improve on my workaround above. I’d like to be able to return a correct System.DBNull value rather than a hacked “-1″, so if you know how this is done please drop me a comment.
Mate just go dataset.EnforceConstraints = false
Worked for me.
But that turns off the dataset constraints, a bit like doing a “On Error Resume Next” in VB6, it can cause quite a lot more problems than it solves.
Thank you very much for this, I thought we were doing something wrong with the dataset designer, turns out it is a ‘feature’… ><
Reassuringly, Microsoft have been aware of this since VS2005, and have blogged on it:
http://blogs.msdn.com/smartclientdata/archive/2005/08/13/NullValueForTypedDataSet.aspx
I can’t believe (well I can, but you know what I mean) that this hasn’t been fixed, good database design surely relies on the correct use of null for optional FK relationships. Our DBs use them everywhere as the alternative (creating zero value rows) is terrible (and wrong IMO).
We tried this and it didn’t work for us in VS2008 - the property was correctly changed in the designer ‘NullValue’ combo, but the field (column) we had adjusted in the xsd was no longer available as an intellisense option and the code wouldn’t compile.
However, we then discovered that if you declare a data table row object, and have set the ‘AllowDBNull’ to true in the designer (without having changed the xsd), then you will find that VS2008 automatically generates a IsNameOfFieldNull field (where NameOfField is whatever the column with the nulls in is called).
You can then just use this field to test for nulls and handle appropriately, and most importantly this won’t throw an exception, even though the behaviour is still set to ‘throw exception’ in the null values column.
I’m not sure this will help in your scenario, but it worked for ours (we were looping the datatable in code and building a dynamic webpage menu)
Hope that helps someone.
Hello Tom,
Thank you for your kind words, and especially for bringing up the .IsXNull property.
I’ve noticed that property too, on strongly typed datasets. But in my particular case I was using databound controls.
Unfortunately the binding fires before you can check for .IsXNull, so where you might have null values in foreign key relationships and you’re using databinding, you are effectively screwed.
Microsoft provide data binding and go to the extreme lengths to provide masses of support for this rapid application development feature, but then screw us all with the error handling and event order. In actual fact the only real solution is to trap this error and clear it. But then it makes the project a pain to debug properly because these dataset errors keep being thrown when you don’t want to know about them.
I really hope Microsoft fix this. It’s absolutely and clearly not a feature.
Thanks again, Tom.