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

Discussion in 'Microsoft ADO .NET' started by Reece Laurie, Nov 17, 2009.

  1. Reece Laurie

    Reece Laurie Guest

    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?
    Reece Laurie, Nov 17, 2009
    1. Advertisements

  2. 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

    To know how to debug into .NET Framework source codes, please read this
    blog article,

    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(){

    // 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.

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

    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#

    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#

    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#

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

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

    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#
    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
    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"));
    // 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");
    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
    Colbert Zhou [MSFT], Nov 18, 2009
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. KJ

    Setting the Primary key of a DataTable

    KJ, Nov 12, 2003, in forum: Microsoft ADO .NET
    Cowboy \(Gregory A. Beamer\)
    Nov 12, 2003
  2. Andy Sjostrom

    Populating DataTable with Primary Key Constraints

    Andy Sjostrom, Feb 23, 2004, in forum: Microsoft ADO .NET
    Andy Sjostrom
    Feb 23, 2004
  3. localhost

    DataTable Primary Key Not Remove?

    localhost, Dec 15, 2004, in forum: Microsoft ADO .NET
    Scott M.
    Dec 15, 2004
  4. Bernie Yaeger

    adding a primary key to a dataset/datatable

    Bernie Yaeger, Feb 22, 2005, in forum: Microsoft ADO .NET
    Bernie Yaeger
    Feb 24, 2005
  5. deko
    Mar 14, 2006
  6. Nathan Sokalski
    Nathan Sokalski
    May 21, 2006
  7. Replies:
  8. Reece Laurie
    Reece Laurie
    Oct 15, 2009