ado.net CommandBehavior.KeyInfo / getSchema 方法

Question

在 C# asp.net vs2008 上工作。使用 Reader = Command.ExecuteReader(CommandBehavior.KeyInfo) 我尝试获取外键，有没有办法获取它？ 如果我写

     string sql = string.Format("Select * from {0}", tableName);
    conn.Open();
    SqlCommand cmd = new SqlCommand(sql, conn);
    cmd.CommandType = CommandType.Text;
    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);

    DataTable schema = reader.GetSchemaTable();

然后在 isKey=true 设置主键。我想从这个命令获取外键。有什么方法可以获取它。 如果有任何疑问请询问。提前致谢。

Answer 1

如果您有管理权限。您可以从以下查询中获取结果。

SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
Where FK.TABLE_NAME = YourtableName

您可以在后面的代码中使用以下代码

System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(@"Connection String");
            con.Open();

            string str = "SELECT ";
            str+= " K_Table = FK.TABLE_NAME,";
            str+= " FK_Column = CU.COLUMN_NAME,";
            str+= " PK_Table = PK.TABLE_NAME,";
            str+= " PK_Column = PT.COLUMN_NAME,";
            str+= " Constraint_Name = C.CONSTRAINT_NAME";
            str+= " FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C";
            str+= " INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME";
            str+= " INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME";

            str+= " INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME";
            str+= " INNER JOIN (";
            str+= " SELECT i1.TABLE_NAME, i2.COLUMN_NAME";
            str+= " FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1";
            str+= " INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME";
            str+= " WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'";
            str+= " ) PT ON PT.TABLE_NAME = PK.TABLE_NAME";
            str+= " Where FK.TABLE_NAME = 'f'";

            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(str, con);
            System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {

            }

Answer 2

ADO.NET 当前不从数据源返回外键信息。 CommandBehavior.KeyInfo 仅指主键信息。

http://support.microsoft.com/kb/310107

使用此::

**选择

fk。 CONSTRAINT_NAME、fk.UNIQUE_CONSTRAINT_NAME、cn.TABLE_NAME、cn.COLUMN_NAME

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS中的

作为 fk

join INFORMATION_SCHEMA.TABLE_CONSTRAINTS 作为 pk

上的

pk.CONSTRAINT_NAME=fk.UNIQUE_CONSTRAINT_NAME

将 INFORMATION_SCHEMA.KEY_COLUMN_USAGE 作为 cn 加入到

cn.CONSTRAINT_NAME=fk.CONSTRAINT_NAME 上

，其中 cn.TABLE_NAME='tablename'**