ado.net CommandBehavior.KeyInfo / getSchema method

Work on C# asp.net vs2008. with Reader = Command.ExecuteReader(CommandBehavior.KeyInfo) i try to get foreign keys , is there a way to get it ? if i write

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

Then on isKey=true s开发者_StackOverflow社区et for primary key.I want to get foreign key from this command.Is there any way to get it. If have any query plz ask.Thanks in advance.

If you have the administrative Priviliges. You can get the result from the below query.

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

You can use the below code in your code behind

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())
            {

            }

ADO.NET does not currently return foreign key information from a data source. CommandBehavior.KeyInfo refers to primary key information only.

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

Use this::

**select

fk.CONSTRAINT_NAME,fk.UNIQUE_CONSTRAINT_NAME, cn.TABLE_NAME,cn.COLUMN_NAME

from

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as fk

join INFORMATION_SCHEMA.TABLE_CONSTRAINTS as pk

on

pk.CONSTRAINT_NAME=fk.UNIQUE_CONSTRAINT_NAME

join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as cn

on cn.CONSTRAINT_NAME=fk.CONSTRAINT_NAME

where cn.TABLE_NAME='tablename'**