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.