Visual Studio Dataset Designer Null

Question

Visual Studio 2008 has a bug in that you cannot use the dataset designer to set an int type field to be nullable. (There are error reports going back to Visual Studio 2005 but it seems this has never been addressed.)

The only selectable behaviour is to emit code that raises an exception if an null value is passed back from the database, rather than set the value to null, which would require the data type to be "int?" instead of "int". A real world example case is when a foreign key is null because the dependent object has not yet be assigned.

Are there any sensible workarounds other than 1, not using Visual Studio datasets, 2. changing the datatype to string (which allows null but negates the point of strong typing and forces numerous casting), or 3. changing the emitted code (which will be overwritten if any changes are made to the dataset via the designer).

Answer 1

Try this link.

I suspect the author jumped through a lot of the same hoops you've had to, including updating by hand the DataSet designer code (which just gets regenerated) and the use of partial classes.

What worked for him in VS2008 was to edit the DataSet .xsd file manually to add the following values for the columns that he needed to fix:

msprop:nullValue="-1" 
nillable="true"

What this does is return -1 in place of null with no errors.

The author gives an example of a complete column edited like this:

<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” />

These updates are not lost when the DataSet is edited and saved.

Fair warning: I've never tried this code. Someone commented to the author's blog by saying this did not work for him in VS2008 (look for the second comment posted by "Tom"). Tom does present a fix though.

I hope it works for you.