How can I include DBNull as a value in my strongly typed dataset?

Question

I've created a strongly typed dataset (MyDataSet) in my .NET app. For the sake of simplicity, we'll say it has one DataTable (MyDataTable), with one column (MyCol). MyCol has its DataType property set to "System.Int32", and its AllowDBNull property set to "true".

I'd like to manually create a new row, and add it to this dataset. I create the row without a problem, with something like:

MyDataSet.MyDataTableRow myRow = MySimpleDataSet.MyDataTable.NewItemRow();

Fine. However, when I try to set the value to DBNull:

myRow.MyCol = DBNull.Value;

I'm told that I can't do it...that it can't cast that to an int. This makes sense, in a way, since I've defined it to be an int...but then how can I get DBNull in there? Am I not supposed to be able to have DBNull in there? Isn't that what the AllowDBNull property is for?

I'm obviously missing something fundemental. Can someone help explain what it is?

EDIT: I also tried entering "int?" as the DataType, but Visual Studio throws an error when I enter it, saying that "Column requires a valid DataType."

Answer 1

int does not accept null, but int? (the INullable version) does. I think you need that datatype instead.

Answer 2

you have in MyDataTableRow class a generated method named: SetMyColNull().

myRow.SetMyColNull();

you can also do :

myRow["MyCol"] = DBNull.Value;

because myRow["MyCol"] is of type object

EDIT (added by Question OP):

There is also a counterpart method generated for reading this value back out, IsMyColNull().

Answer 3

There are two things.

Allowing DBNull is a separate setting from being able to set the value to DBNull.

I've only used the DataSet XSD interface to set this... but I had to not only set "AllowDBNull = true", but I also had to set "NullValue = (null)". Originally "NullValue" was set to "(Exception)". This meant that the dataset would accept bieng .Fill()ed with null, but would not allow you to manually set the value to null.

Not sure if this is your problem, but it sounds similar.

Answer 4

just go to designer mode in dataset and right click on field that you want to be null enabled, and select properties , in properties window , set AllowDBNull to True and NullValue to (NULL) ; it works for me right ! best regards !