How can I find the primary key columns of a table using ado.net?

Is there a pure .net way to do this reliably? The solutions I keep finding either require guessing or they have a solution that is specific to a database provider. Usually querying some internal system table to get this information.

From stackoverflow

• Each DataTable object has a PrimaryKey property wich is an array of DataColumns that represent the table's primary key

  Kevin Gale : Interesting, I'm not using a datatable since this involves sql commands but it does look like I could create a datatable to get the primary key information.

  Igor Zelaya : How about calling FillSchema()? That works!

  Charles Bretana : Yes Igor, you are correct, sorry... Here's the link... http://msdn.microsoft.com/en-us/library/152bda9x.aspx

• Just to add to Igor's answer. You don't actually need to query an entire subset of data, you can just use adapter.FillSchema(table, SchemaType.Mapped) and then get the primary key(s) from the data table.

  Linkage

  (If you're using SQL though, you can get them from the system view though, but I'm assuming you already know that.)

• The only way [wrong] As Igor mentioned in comment, you can call FillSchema() Here's a link... FillSchema()

  is to query the Database's schema information, which will be dependant on the database vendor...

  This is SQL Server specific, for example...

  select kcu.TABLE_SCHEMA, kcu.TABLE_NAME, 
    kcu.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION
  from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
    join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
      on kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
     and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
     and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
     and kcu.TABLE_NAME = tc.TABLE_NAME
   where tc.CONSTRAINT_TYPE in ( 'PRIMARY KEY', 'UNIQUE' )
   order by kcu.TABLE_SCHEMA, kcu.TABLE_NAME, 
        tc.CONSTRAINT_TYPE, kcu.CONSTRAINT_NAME, kcu.ORDINAL_POSITION;
  

• just tagging onto this query. is there a way in ADO.Net to get a generic (i.e. database vendor independent) schema extracted from a selection of tables. i'm thinking along the lines of not only column names, types and primary keys (which i know is fairly easy to get) but also the foreign keys collections. i can do all of this stuff fairly easily using sqlserver2005 specific logic, but would love to see an object model approach to it that was database agnostic.

  i'll be watching this space :)

  good luck gurus...

  Kevin Gale : You may want to add this as a separate question. I'd like to know the answer.