Visual Studio Dataset Designer Null

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).

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.

There was a workaround suggested on the connect.microsoft.com topic (Nullable types for typed dataset by Danila Korablin) that illustrates a hack that provides a usable alternative to fixing the dataset.

To put it succinctly, it uses the partial classes, in the myDataSet.cs module, as a repository for adding nullable properties to the row class. You can access these properties in your code but not in the designer. This manner of adding properties also cannot replace the original broken ones.

The Data Sources window also will not show properties you add to dataset classes in this manner. If you try to repair the MS-generated data table class by adding columns of the correct nullable types, the Data Sources visualizer doesn't understand them, invalidating the dataset, so it displays nothing. This means you are prevented from using Visual Studio's built-in designer to access columns of the correct types.

This deficiency makes an operation as simple as copying a row that contains a nullable column impossible.

I can verify nullable columns are unsupported in Visual Studio 2005's dataset designer. Microsoft has repeatedly made it clear they will not address this problem.