如何使用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.

是否有一种纯粹的.net方式可靠地完成这项工作?我一直发现的解决方案要么需要猜测,要么他们有一个特定于数据库提供商的解决方案。通常查询一些内部系统表以获取此信息。

5 个解决方案

#1

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

每个DataTable对象都有一个PrimaryKey属性,它是一个DataColumns数组,表示表的主键。

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();
    }
}

#2

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

唯一的方法[错]正如伊戈尔在评论中提到的,你可以调用FillSchema()这里是一个链接... FillSchema()

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

是查询数据库的架构信息,这将取决于数据库供应商...

This is SQL Server specific, for example...

这是SQL Server特定的,例如......

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;

#3

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.

只是为了增加伊戈尔的答案。您实际上不需要查询整个数据子集,只需使用adapter.FillSchema(table,SchemaType.Mapped),然后从数据表中获取主键。

Linkage

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

(如果你正在使用SQL,你可以从系统视图中获取它们,但我假设你已经知道了。)

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

只是标记到此查询。 ADO.Net中是否有一种方法可以从一组表中提取一个通用(即独立于数据库供应商)的模式。我正在考虑的不仅是列名,类型和主键(我知道这很容易获得),还有外键集合。我可以使用sqlserver2005特定逻辑轻松地完成所有这些工作,但是很想看到一个与数据库无关的对象模型方法。

i'll be watching this space :)

我会看这个空间:)

good luck gurus...

祝你好运大师......

#5

There's a library, Kailua - The forgotten methods in the ADO.NET API. , that does provide this and additional metadata for the top 5 vendors. This info is vendor specific.

有一个库,Kailua - ADO.NET API中被遗忘的方法。 ,这确实为前5个供应商提供了这个和额外的元数据。此信息是特定于供应商的。

#1