none
Get WRONG primary key when i use DbDataAdapter.FillSchema or DbDataReader.GetSchemaTable

    Question

  • Hi All:

    I have one table at below:

    Table Name=T_APP

    Columns:

    APP_ID IDENTITY(1,1) NOT NULL,

    NAME nvarchar,

    LOWERED_APP_NAME nvarchar

    The table has one pk :

    CONSTRAINT [C_IC_APP_PK] PRIMARY KEY NONCLUSTERED
    (
     [APP_ID] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],

    it also has one uk: 

    CONSTRAINT [C_IC_APP_UK1] UNIQUE CLUSTERED
    (
     [LOWERED_APP_NAME] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

     

    But when i use DbDataAdapter.FillSchema or DbDataReader.GetSchemaTable, they return LOWERED_APP_NAME as primary key to me NOT APP_ID.

    But when i try to make C_IC_APP_UK1 to non-Unique, others still remain, the two methods return me correct column.

     

    Is it a bug? After all, APP_ID is the pk not LOWERED_APP_NAME.

     

    Hope someone can help me.

     

    Thanks and Regards

    Carol
     

    Friday, November 17, 2006 11:00 AM

All replies

  • Sorry, forget to tell what the database is.

    The database i test is Sql server 2000 and 2005

     

    Thanks

    Friday, November 17, 2006 11:03 AM
  • Have you tried to use native provider (SqlClient namespace) to get this information instead of using base class? It is quite possible that because DbDataAdapter is not database specific, it does not handle all the information the way you are expecting
    Friday, November 17, 2006 11:31 AM
  • Hi VMazur:

    Thanks your help. But I still did not get the correct column even i use the objects in SqlClient. The following code is i used:

                using(SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[selectedDatabase].ConnectionString))
                {
                    conn.Open();

                    SqlCommand command = new SqlCommand(string.Format("Select * from {0}", tableName),conn);

                    using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
                    {
                        DataTable dt = reader.GetSchemaTable();
                        Console.WriteLine("--------- Schema ------------");
                        ShowTableDetails(dt);
                    }
                }

    Is my code not correct?

    Regards

    Carol

    Monday, November 20, 2006 2:37 AM
  • Look at the MSDN docs (http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getschematable.aspx) and you're gonna have an idea about why it behaves that way:

    IsKey

    true: The column is one of a set of columns in the rowset that, taken together, uniquely identify the row. The set of columns with IsKey set to true must uniquely identify a row in the rowset. There is no requirement that this set of columns is a minimal set of columns. This set of columns may be generated from a base table primary key, a unique constraint or a unique index.

     

    Essentially, IsKey can be true for primarykey, OR unique key.

    Tuesday, November 21, 2006 3:47 AM
  • But

    1. why is IsKey of App_ID false?

    2.why  is PrimaryKey of table that is returned by DbDataAdapter.FillSchema  LOWERED_APP_NAME?

     

     

    Wednesday, November 22, 2006 1:35 AM
  • the msdn docs has explicitly convey the idea that the generated set with IsKey=true MAY be a primary key, or a unique key. So App_ID happens to be out of the set in your case. Futhurmore, the set was generated by the server, not the Client.

    FillSchema uses the same property the server returned.

    Wednesday, November 22, 2006 5:35 AM
  • Hi Bill:

    Thank u very much for your answer.

    I can accept the explanation about IsKey.

    But I still can not accept the explanation about FillSchema. After all, when I use FillSchema, PrimaryKey should return PK to me, but it does not.

    Regards

    Carol

    Friday, November 24, 2006 1:39 AM
  •  

    Hi Carol

     

    Thought I'd let you know that you are not mad: I get the same problem where I have a Primary Key AND another column specified as being a unique index - seems that the SQL provider doesn't pick up the primary key at all - in our case we have some failsafe code that is supposed to work it out from the GetSchemaTable on a DataReader - which has IsKey on the unique index, but doesn't notice that I also have a primary key on the table.

     

    Good luck.  I've had to take the unique index off to get my software to work.

     

    Andy

    Friday, November 30, 2007 3:24 PM
  • Hi folks,

     

    This looks like a known issue we are investigating with our provider this week, I will post back once I hear something.

    Friday, November 30, 2007 7:05 PM
  • Hi Andy:

    When I knew the FillSchema cannow give the PK, I also use another way to work it out. My solution is to retrieve info in system tables.

     

    And Thank for Matt's help

    Carol

    Monday, December 10, 2007 3:19 AM
  • Sorry folks, this one totally slipped my radar and I forgot to follow up.

    Here is the story:

     

    Our code to fetch the "primary key" relies on server BMM (Browse Mode Metadata).  What is BMM you ask?  We'll let me try to explain.

     

    Normally to fetch metadata from SQL Server you have to make calls to system stored procedures or look at system tables, etc... This causes your client code to make extra expensive round trips to the server.  To improve performance in certain common metadata scenarios, we created browse mode in the underlying TDS (Tabular Data Stream) protocol.  When you are in browse mode, the server will append extra metadata about results each time it sends them back.  We don't leave browse mode on all the time because in most cases the client only needs a small amount of metadata about the result (like column name and data type).

     

    BM includes things like what is the base table name for each column and addition things like is the column part of a primary key, etc...  All of this is called BMM.

     

    Now in BMM mode, a unique clustered index is considered a better candidate for a primary key than another index that is marked as a primary key.  This is just how it always worked and can lead to some confusion IF you have a unique clustered index AND a primary key on the same table.   This choice is smarter because a unique clustered index is actually more efficient than a non-clustered primary key.

     

    So just something to remember, this behavior is by design and we're not going to change it.

     

    Thursday, September 18, 2008 3:21 PM
  • Greetings,

    I'm having an issue similar to this and I'm wondering if you could help me with it. 

    We are doing a Database Refactor project where we are redesigning an existing table and creating a view that looks exactly the same as the old table, so that legacy code doesn't break.  We found a problem with exposing the base tables' browse-mode metadata, so we used the VIEW_METADATA option on the said view.  However, we are now finding that the SQL code generators in our legacy apps are using this BMM to generate UPDATE statements, and the problem it's having is that it can't find a good primary key to generate the UPDATE statement.  So, instead of using the PK to build the WHERE clause of the UPDATE, it uses ALL the columns of the table in the WHERE clause to try to make up for the fact that it can't find a PK.  This results in a messy UPDATE statement and is actually causing bugs in some areas.

    I imagine that it can't find a PK because it only has access to the View's metadata, and Views don't have PKs.  Is there any way we can get around this problem?  Is there a way to 'mark' columns of a view as the key columns (other than by doing an indexed view)?  Is there a setting somewhere that controls this other than VIEW_METADATA?

    Thanks,

    SB
    Wednesday, September 09, 2009 8:17 PM