Problem getting schema information: AllowDBNull vs. IsNullable
Hello,
I'm trying to gather the schema information for a tables columns using ADO.Net with OleDb (an Access Database) and Sql (a SQL Server database) and found some problems:
For Access .mdb:
method 1 to get the MetaData Collections
OleDbConnection currentconnection = ...;
DataTable schema = currentConnection.GetSchema("COLUMNS");
method 2 to get the schema for the commands result:
OleDbCommand cmd = ...;
OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
DataTable schema = reader.GetSchemaTable();
Both give me the correct list of columns, but the values for IsNullable (from method 1) and AllowDBNull (method 2) differ!?
I'm using the Northwind database for example purposes and for one table (Order Details) I get IsNullable=true for the first foreign key and IsNullable=false for the second foreign key.
I also get IsNullable=false for the other three columns that have a Column_Default set.
Method 2 seems to give the correct values: AllowDBNull=false for the two foreign keys and AllowDBNull=true for the default-value-columns.
In another table (Categories) method 1 returns IsNullable=false for a VarChar column and method 2 returns AllowDBNull=true for the same column, while this time I think method 1 gives the correct answer because SQL Server says IsNullable=false and AllowDBNull=false for this column.
How comes that? Am I missing a difference in this two values? Because I saw many websites using them as synonyms, e.g. http://www.koders.com/csharp/fid74A751F1A6F8ACEB258469216B7C263679DAD88A.aspx
The other problem is when using the SQL Server (again using northwind database). This time both methods return identical values, if IsNullable=false then AllowDBNull=false too.
But: It returns AllowDBNull=false for default-value-columns too? How can i then do an Insert without setting these fields for using the default values?
Sorry for this long text, I'm looking forward for help,
regards J.Schumann
Did no one got this problem before?
Can somebody help me please!
Thank you in advance,
J.Schumann
In general the keyinfo returned by a reader and the values returned by GetSchema() are describing two slightly different things: The reader is returning the metadata of the query, while GetSchema is returning the metadata of the database objects. So there can easily be differences in the fine print.
What is the command text you are using? If it's simple SELECT * FROM TABLE, then I'd suspect a subtle bug somewhere in the product. Can you post more complete scenario code?
Hello!
Thanks for your answer.
I use the following for what i called "method 1" with currentConnection as an opened OleDbConnection:
string[] restriction = new string[4];
restriction[2] = "Example";
DataTable columns = currentConnection.GetSchema("Columns", restriction );
The code I use to get the readers schema looks like this:
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Example] WHERE 0=1", currentConnection);
OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
DataTable schema = reader.GetSchemaTable();
I copied the results together to show the differences
Table Categories [Northwind Database]
COLUMNNAME ISNULLABLE ALLOWDBNULL DATATYPECategoryID False False Int32, autoincrementCategoryName False True String[15]Description True True String, memo typePicture True True Byte[], memo typeTable Order Details [Northwind Database]
COLUMNNAME ISNULLABLE ALLOWDBNULL DATATYPEOrderID True False Int32, foreign key ProductID False False Int32, foreign key UnitPrice False True Decimal, default value: 0Quantity False True Int16, default value: 1Discount False True Single, default value: 0Other differences:
COLUMNNAME ISNULLABLE ALLOWDBNULL DATATYPECompanyName False True String[40] (table Customers)ContactName True True String[30] (table Customers)
Freight True True Decimal, default value: 0 (table Orders)Using CommandBehavior.SchemaOnly, CommandBehavior.Default doesn't help.
Are IsNullable and AllowDBNull supposed to return different Values?
If no: Why are they different then? Using SQL Server they are always equal
If yes: What is the difference? Why is AllowDBNull==False for an autoincrement column but True for a default value-column? Why is IsNullable==true for one default value-column but false for another? Why is AllowDBNull==true when IsNullable==False indicates that this field must be set?
I'm stuck here, any help is appreciated!
Thanks, J.Schumann
Ok, I forgot to give some version infomation:
OleDbConnectionStringBuilder.Provider = Microsoft.Jet.OLEDB.4.0
querying Northwind database as downloaded from http://office.microsoft.com/downloads/2000/Nwind2K.aspx
Thanks, J.Schumann
I took a look at what is causing this behavior, and have determined that this is due to how the Jet OLE DB provider reports nullability.
In native OLE DB terms, when you call GetSchema, System.Data.OleDb tries to call IDBSchemaRowset::GetRowset for the metadata rowset you requested ("Columns", in this case). Whatever we get back is what we report. Specifically, the columns rowset contains a column called IS_NULLABLE, and that maps directly to the IS_NULLABLE column in the resulting DataTable. GetSchema is not tied to any particular query, but operates against metadata for objects in the database.
When you call GetSchemaTable, we use the native OLE DB API IColumnsRowset::GetColumnsRowset. In that case, one of the columns in the output is DBCOLUMN_FLAGS. The flags are a bitmask of several different properties that the column may have, including nullability. There are two flags that can be used to determine nullability -- DBCOLUMNFLAGS_MAYBENULL and DBCOLUMNFLAGS_ISNULLABLE. In some cases, System.Data.OleDb uses one, and some it uses the other. Now, this scenario is a little different from GetSchema in that we don't always directly report whatever the provider gave us for the metadata. In this case, we also make an additional schema call internally in order to get the primary key information for the table being used in the query. Even if the provider reports that a column is nullable, we'll report it as not nullable if it's a key.
The problem here is that the Jet OLE DB provider apparently reports DBCOLUMNFLAGS_ISNULLABLE and DBCOLUMNFLAGS_MAYBENULL as true for all columns in a rowset, regardless of the "Required" setting on the column in the database. I verified this outside of System.Data.OleDb, and I also found some information internally that this is by design for Jet and the OLE DB provider. This is actually allowed by the OLE DB spec, which says the following for each of these properties:
DBCOLUMNFLAGS_ISNULLABLE: "Set if consumer can set the column NULL or if the provider cannot determine whether or not the consumer can set the column to NULL."
DBCOLUMNFLAGS_MAYBENULL: "Set if the column can contain null values or if the provider cannot guarantee that the column cannot contain null values."
If the provider can't determine the nullability, it is free to set these properties anyway. However, if it doesn't set these properties, that would indicate for sure that the column is not nullable. With System.Data.OleDb, the problem is that we have no way to independently determine if a column is nullable or not, so we have to rely on what the provider tells us. We couldn't just use the GetSchema information either, because a column that is not normally nullable in the database could end up containing nulls in a query (e.g. outer join).
The bottom line is that System.Data.OleDb can only be as accurate as what we get from the native OLE DB provider, and in this case, it looks like the Jet provider is not very accurate when it comes to nullability.
Thanks,
Sarah
Please Mark as Answer if this answers your question, or Unmark as Answer if it is marked and you feel it is not answered.
