none
IDataReader.GetSchemaTable() returns false for a primary key column RRS feed

  • Question


  • Hi all,

    I have this situation:

    SQL Server 2008 R2
    AdventureWorks database

    cmd.CommandText = "SELECT * FROM [Production].[BillOfMaterials]";
    myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
    DataTable. schemaTable = myReader.GetSchemaTable();

    Result:
          BillOfMaterialsID:             IsKey => false
          ProductAssemblyID:         IsKey => true
          ComponentID:                 IsKey => true
          UnitMeasureCode:            IsKey => true

    Note:    BillOfMaterialsID is the primary key, while the others are foreign keys.

    Why is the primary key returns false?

    Thanks in advance.

    • Moved by eryang Friday, April 22, 2011 2:56 AM Move to ADO.NET forum for better support. (From:.NET Base Class Library)
    Thursday, April 21, 2011 3:17 AM

Answers

  • Hi sobo1,

    I repro your scenario, the primary key is false to me. But I research for a long time, and find it seem we should use GetOleDbSchemaTable method to get the primark key:

     OleDbConnection cn = new OleDbConnection();
          cn.ConnectionString = @"Provider=SQLOLEDB;Data Source=.\sqlexpress;Integrated Security=SSPI;Initial Catalog=AdventureWorks";
          cn.Open();
          object[] objArrRestrict;
          objArrRestrict = new object[] { "AdventureWorks", "Production", "[BillOfMaterials]" };
          DataTable schemaTbl;
          schemaTbl = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, objArrRestrict);      
          foreach (DataRow row in schemaTbl.Rows)
          {
            Console.WriteLine(row["COLUMN_NAME"]);
          }
    
    Have a nice day.
    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by sobo1 Sunday, May 8, 2011 3:00 PM
    Monday, May 2, 2011 9:09 AM
    Moderator
  •  

    Hi sobo1,

    Welcome!

    According to your description and code, I try to repro your scenario, but unfortunately I couldn't reproduce, and the primary key works ok on my computer, I think I may miss something.

    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.

    false : The column is not required to uniquely identify the row.

    using (SqlConnection con = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=EFTest;Integrated Security=True"))
          {
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = " select * from Vacation";
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
            DataTable dt = reader.GetSchemaTable();
            var test = dt.Columns["IsKey"];   //  test.Table.Rows.Count
          }
    
    
    T-SQL:
    CREATE TABLE [dbo].[Vacation](
    	[Id] [int] NOT NULL,
    	[Days] [int] NOT NULL,
    	[EId] [int] NOT NULL,
     CONSTRAINT [PK__Vacation__3214EC0720C1E124] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC,
    	[Days] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[Vacation] WITH CHECK ADD CONSTRAINT [FK] FOREIGN KEY([EId])
    REFERENCES [dbo].[Employees] ([EmployeeId])
    GO
    
    ALTER TABLE [dbo].[Vacation] CHECK CONSTRAINT [FK]
    GO
    
    

     

    Would you please give us more information? Thanks for understanding!

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, April 22, 2011 5:57 AM
    Moderator

All replies

  •  

    Hi sobo1,

    Welcome!

    According to your description and code, I try to repro your scenario, but unfortunately I couldn't reproduce, and the primary key works ok on my computer, I think I may miss something.

    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.

    false : The column is not required to uniquely identify the row.

    using (SqlConnection con = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=EFTest;Integrated Security=True"))
          {
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = " select * from Vacation";
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
            DataTable dt = reader.GetSchemaTable();
            var test = dt.Columns["IsKey"];   //  test.Table.Rows.Count
          }
    
    
    T-SQL:
    CREATE TABLE [dbo].[Vacation](
    	[Id] [int] NOT NULL,
    	[Days] [int] NOT NULL,
    	[EId] [int] NOT NULL,
     CONSTRAINT [PK__Vacation__3214EC0720C1E124] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC,
    	[Days] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[Vacation] WITH CHECK ADD CONSTRAINT [FK] FOREIGN KEY([EId])
    REFERENCES [dbo].[Employees] ([EmployeeId])
    GO
    
    ALTER TABLE [dbo].[Vacation] CHECK CONSTRAINT [FK]
    GO
    
    

     

    Would you please give us more information? Thanks for understanding!

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, April 22, 2011 5:57 AM
    Moderator
  • Hi sobo1,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 
     
    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, April 25, 2011 2:35 AM
    Moderator
  • Thanks Alan.

    It works on some tables, but not all.
    Please test it on AdventureWorks database (installed on Sql Server 2008 R2) as I did:
    "SELECT * FROM [Production].[BillOfMaterials]";
    I am also using OleDb Provider, not Sql Provider.
    Can you try to test that with OleDb Provider, on the same database and table as I did?

    Thanks,
    sobo1

    Monday, April 25, 2011 11:30 AM
  • Hi Alan,

    Do you have an update on this issue?

    Thanks

    Wednesday, April 27, 2011 2:07 AM
  • Hi sobo1,

    I repro your scenario, the primary key is false to me. But I research for a long time, and find it seem we should use GetOleDbSchemaTable method to get the primark key:

     OleDbConnection cn = new OleDbConnection();
          cn.ConnectionString = @"Provider=SQLOLEDB;Data Source=.\sqlexpress;Integrated Security=SSPI;Initial Catalog=AdventureWorks";
          cn.Open();
          object[] objArrRestrict;
          objArrRestrict = new object[] { "AdventureWorks", "Production", "[BillOfMaterials]" };
          DataTable schemaTbl;
          schemaTbl = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, objArrRestrict);      
          foreach (DataRow row in schemaTbl.Rows)
          {
            Console.WriteLine(row["COLUMN_NAME"]);
          }
    
    Have a nice day.
    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by sobo1 Sunday, May 8, 2011 3:00 PM
    Monday, May 2, 2011 9:09 AM
    Moderator