AllowDBNull

[Strannik]

Did anybody deal with such ADO behavior ?
I have MSSQL table with some NOT NULL columns. When I retrieve AllowDBNull property for these columns each column has AllowDBNull = true.
Any ideas ?

 

[obislavu]

The AllowDBNull property is used to indicate whether null values are allowed in this column for rows belonging to the table (DataTable object) and not to data source table.
When you create a DataTable object , the default value of AllowDBNull is true and it is not related with the NOT NULL of the data source.
You should set it manually, to false for that columns.
In the DataTable object, database null e.g. the constant representing a database column absent of data is Convert.DBNull.
DataTable myTable = new DataTable();
myTable.AllowDBNull = false; // to be sure that what you will modify or add in DataTable object is not set to null database.
myTable["column"]=Convert.DBNull; // set null
-obislavu-

 

[Strannik]

OK

Then how to determine that specific column cannot be NULL(using ADO.NET) ?

 

[obislavu]

Query the data source for the columns you are interested to know e.g. send a query like that:

select COLUMNPROPERTY(OBJECT_ID('_tSummary'), 'firstname','AllowsNull')as b1, COLUMNPROPERTY(OBJECT_ID('_tSummary'), 'amount','AllowsNull')as b2 from _tSummary

In the above example returns b1 and b2 as boolean :
b1 b2
-- --
0 1
0 1
0 1
because the firstname does not allows null but amount allows null.
You can retrieve that info about the all tables and columns you want in one single query.
-obislavu-