How is System.Data.DataTable.Load() determining whether a column is a primary key?

R

Reece Laurie

IBM's ADO.NET provider extends the MS System classes and, while overriding
some of the implementation, in a lot of cases relies on the MS System
class's implementation. In the case of DataTable.Load(), IBM's provider
relies completely on the implementation provided in the Microsoft's
System.Data.DataTable. What we'd like to find out are the schema's column
attributes that are being checked by the logic in the Load() to determine
whether that column is a part of the primary key, and, therefore, needs to
be added to the DataTable's PrimaryKey property. The description of the
Load(IDataReader) method on MSDN states that in the case when DataTable has
no schema one is inferred from the IDataReader's result set. Is the
mechanism based on IDataReader.GetSchemaTable() method? If so, what column
attributes should be set in order for the PrimaryKey to get set?

The reason for the question is that we encountered a problem where the
PrimaryKey property of the DataTable is not being set for the tables with
defined nullable PK, even though the "IsKey" column attribute is set to
"True" in the schema for the columns that are part of the PK. If the table
is defined with a non-nullable PK, then the DataTable's PrimaryKey property
is set correctly. However, if I understand the docs correctly, column
nullability should not be a deciding factor in cases when "IsKey" is set to
True. Is that correct?
 
C

Colbert Zhou [MSFT]

Hello Reece,

Thanks for contacting the Newsgroup Support! As I mentioned in my previous
reply in another thread you raised in newsgroup. We can know the
DataTable.Load() behavior through Reflected codes or debugging .NET sources
codes.

To know how to debug into .NET Framework source codes, please read this
blog article,
http://blogs.msdn.com/sburke/archive/2008/01/16/configuring-visual-studio-to
-debug-net-framework-source-code.aspx

When I investigate in my side and I can see,

1.Yes, just like you mention, when the target table schema is null, it
calls IDataReader's GetSchemaTable, and then calls into
GetSchemaTableFromDataTable method to retrieve the schema.
----------------------------------------------------------
override public DataTable GetSchemaTable(){
ValidateOpen("GetSchemaTable");
ValidateReader();

// each time, we just get schema table of current table for once, no need
to recreate each time, if schema is changed, reader is already
// is invalid
if (schemaTable == null)
schemaTable = GetSchemaTableFromDataTable(currentDataTable);

return schemaTable;
}
----------------------------------------------------------

2.After that, the DataTable.Load() calls into LoadAdapter.FillFromReader to
fill the table. I paste the call stack below.
-------------------------------------------------------------

System.Data.dll!System.Data.ProviderBase.SchemaMapping.SetupSchemaWithKeyInf
o(System.Data.MissingMappingAction mappingAction = Passthrough,
System.Data.MissingSchemaAction schemaAction = AddWithKey, bool
gettingData, System.Data.DataColumn parentChapterColumn, object
chapterValue) Line 686 C#

System.Data.dll!System.Data.ProviderBase.SchemaMapping.SchemaMapping(System.
Data.Common.DataAdapter adapter, System.Data.DataSet dataset,
System.Data.DataTable datatable,
System.Data.ProviderBase.DataReaderContainer dataReader, bool keyInfo,
System.Data.SchemaType schemaType, string sourceTableName, bool
gettingData, System.Data.DataColumn parentChapterColumn, object
parentChapterValue) Line 151 + 0x16 bytes C#

System.Data.dll!System.Data.Common.DataAdapter.FillMappingInternal(System.Da
ta.DataSet dataset, System.Data.DataTable datatable, string srcTable,
System.Data.ProviderBase.DataReaderContainer dataReader, int schemaCount,
System.Data.DataColumn parentChapterColumn, object parentChapterValue) Line
621 + 0x2b bytes C#

System.Data.dll!System.Data.Common.DataAdapter.FillMapping(System.Data.DataS
et dataset, System.Data.DataTable datatable, string srcTable,
System.Data.ProviderBase.DataReaderContainer dataReader, int schemaCount,
System.Data.DataColumn parentChapterColumn, object parentChapterValue) Line
626 + 0x19 bytes C#

System.Data.dll!System.Data.Common.DataAdapter.FillFromReader(System.Data.Da
taSet dataset = null, System.Data.DataTable datatable = {}, string srcTable
= null, System.Data.ProviderBase.DataReaderContainer dataReader =
{System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader
}, int startRecord = 0, int maxRecords = 0, System.Data.DataColumn
parentChapterColumn = null, object parentChapterValue = null) Line 505 +
0x1d bytes C#

System.Data.dll!System.Data.Common.DataAdapter.Fill(System.Data.DataTable[]
dataTables = {System.Data.DataTable[1]}, System.Data.IDataReader dataReader
= {System.Data.DataTableReader}, int startRecord = 0, int maxRecords = 0)
Line 476 C#

System.Data.dll!System.Data.Common.LoadAdapter.FillFromReader(System.Data.Da
taTable[] dataTables, System.Data.IDataReader dataReader, int startRecord,
int maxRecords) Line 740 + 0x11 bytes C#
System.Data.dll!System.Data.DataTable.Load(System.Data.IDataReader reader
= {System.Data.DataTableReader}, System.Data.LoadOption loadOption,
System.Data.FillErrorEventHandler errorHandler) Line 4436 C#
System.Data.dll!System.Data.DataTable.Load(System.Data.IDataReader
reader) Line 4413 C#
ConsoleApplication8.exe!ConsoleApplication8.Program.Main(string[] args =
{string[0]}) Line 15 + 0xd bytes C#
-------------------------------------------------------------

3. From the call stack, we can see, eventually, it is
System.Data.ProviderBase.SchemaMapping.SetupSchemaWithKeyInfo that fills
the caller table's schema. SetupSchemaWithKeyInfo is very long, I will not
post the whole codes here. I just post the important section for this
question. If you are interesting in this, please follow the instructions in
the above blog to debug the .NET source codes.
------------------------------------------------------------------
// The server sends us one key per table according to
these rules.
//
// 1. If the table has a primary key, the server sends
us this key.
// 2. If the table has a primary key and a unique key,
it sends us the primary key
// 3. if the table has no primary key but has a unique
key, it sends us the unique key
//
// In case 3, we will promote a unique key to a primary
key IFF all the columns that compose
// that key are not nullable since no columns in a
primary key can be null. If one or more
// of the keys is nullable, then we will add a unique
constraint.
//
if (addPrimaryKeys && schemaRow.IsKey) { //
if (keys == null) {
keys = new DataColumn[schemaRows.Length];
}
keys[keyCount++] = dataColumn;
#if DEBUG
if (AdapterSwitches.DataSchema.TraceVerbose) {
Debug.WriteLine("SetupSchema: building list of
" + ((isPrimary) ? "PrimaryKey" : "UniqueConstraint"));
}
#endif
// see case 3 above, we do want
dataColumn.AllowDBNull not schemaRow.AllowDBNull
// otherwise adding PrimaryKey will change
AllowDBNull to false
if (isPrimary && dataColumn.AllowDBNull) { //
#if DEBUG
if (AdapterSwitches.DataSchema.TraceVerbose) {
Debug.WriteLine("SetupSchema: changing
PrimaryKey into UniqueContraint");
}
#endif
isPrimary = false;
}
------------------------------------------------------------------
From the above codes, we can see, .NET Framework considers all PrimaryKeys
as NOT nullable. This is a by design behavior since most of the DataBase
requires the Primary Key cannot be null. Actually, I intend to help on this
very much but I am not a database guy so I am confused why IBM database
allow Primary Key to be nullable? So if IBM decides to support nullable
PrimaryKey, they are responsible for providing a nullable-PrimaryKey
supported ADO.NET provider.


Best regards,
Ji Zhou
Microsoft Online Support Team
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top