Get Sql Table Relationships using c#

Question

I'm trying to get all the relationships of a table using ADO.Net. I have the following code below, but am unable to get Data.Relations or DataSet.Tables[0].ChildRelations/ParentRelations

string query = string.Format("SELECT TOP 0 * FROM {0}", tableName);

var ds = new DataSet(tableName);
SqlCommand sqlCmd = new SqlCommand(query, connection);
SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);

sda.FillSchema(ds, SchemaType.Source, tableName);
sda.Fill(ds, tableName);

sda.Dispose();

Thanks!

Answer 1

Getting the results from a DbCommand doesn't return any information about the relationship of the tables that were used to form the projection of the values that are returned (they're not really needed beyond the query).

That said, you can call the GetSchema method to help get the information about the relationships.

When called without parameters, it will give you a list of all the metadata collection types available through that collection. In the case of a SqlConnection one of those collections is "ForeignKeys".

You can then make a call like this:

DataTable foreignKeys = connection.GetSchema("ForeignKeys");

Which will then give you a listing of all the foreign keys that exist in that database.

That said, each call to GetSchema is specific for the provider; there's no guarantee that GetSchema will return the same data collections or the same format across different providers. The result you get for Oracle, ODBC, OLE-DB, MySql, etc, can potentially all be different, and you have to account for those differences yourself.