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.

For example:

string[] GetPrimaryKeys(SqlConnection connection, string tableName)
{
    using(SqlDataAdapter adapter = new SqlDataAdapter("select * from " + tableName, connection))
    using(DataTable table = new DataTable(tableName))
    {
        return adapter
            .FillSchema(table, SchemaType.Mapped)
            .PrimayKey
            .Select(c => c.ColumnName)
            .ToArray();
    }
}

Answer 2

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;