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

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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;

Answer 4

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