Dataset allowing Null values even when AllowDBNull = False?

Question

I have designed a dataset using VS2008 dataset designer. In one of the datatables, I have set "AllowDBNull" property of most of the columns to be False. However, still if I create a DataRow containing null values for these columns, this datatable accepts this row, without any error.

Am I not understanding something here? Please advice. Thank you.

Edit Mike Spross' excellent explanation however, brings forth another question. How do we check text fields if they are System.DBNull? It is surprising that DataSets are not considering a string "" as System.DBNull and throwing an exception. Or is it not?

Edit I think I have found the problem and reason. I am initializing a new row of the DataTable, before filling in the values to that row. While initializing the row, default value for string, ie, "" might be being filled in that column. I think that's it? Any ideas about this?

Answer 1

The short answer is:

System.DBNull.Value != null

The longer answer is:

In C#, the concept of a NULL value in SQL is represented by the Value property of the System.DBNull class. When dealing with a database, the more familiar C# null doesn't actually mean "null value."

When you set a database column to null, ADO.NET will initialize the column to whatever the default value is for that column (for example, an int column would be initialized to 0). That is, using null can actually cause a non-null value to end up in the database, and therefore you won't get an error.

If you instead set a database column to System.DBNull.Value, the column will actually be set to NULL. This is the situation that AllowDBNulls == false will prevent you from doing.

Answer 2

Regarding your "bonus" ;-) question: NULL (no string) and "" (empty string) are two different things. So it's perfectly reasonable to treat them differently. It's the distinction between null and DBNull that is messing things up. If nullable types had been available at the time of designing ADO.NET, things probably would be a lot easier. But before .NET 2.0, there was no way to represent e.g. a "null integer".

Answer 3

Are you exactly assigning NULL values or an empty string to those columns? If you don't assign any value to a column, it will default to NULL (if a DEFAULT constraint is not imposed). Else you can assign a NULL value by doing -

ds.Tables[0].Rows[0]["Col"] = null;

If you are assigning an Empty string to those columns, it's not equal to NULL.

And if you have a NULL value in a column which has been marked as NOT NULLABLE, it will throw an error -

Column 'Col1' does not allow nulls.

EDIT:

By NOT NULLABLE, I mean AllowDBNull = false.

Your code seems correct. Can you try trimming the text?

Here's the whole code -

DataTable dt = new DataTable();

DataColumn column = new DataColumn("Col1");
column.AllowDBNull = false;
dt.Columns.Add(column);

DataRow dr = dt.NewRow();
dr["Col1"] = null;

dt.Rows.Add(dr);