Retrieve table relationships from MS Access DB using C#

Question

I am using C# with MS Access 2010. I need to retrieve the table relationships from the DB in order to determine the relationships between entities and use them in my C# code. I need the same functionality for SQL Server database also.

Is there a way to do this using C# and .NET 3.0/ 3.5/ 4.0?

Appreciate your time.

Thanks, Mahesh

Olivier Jacot-Descombes · Accepted Answer · 2016-03-13T21:32:46.407

This is the code I used to retrieve the foreign key constraints (the relationships, if you prefer). TableSchema, ForeignKey and ForeignKeyColumn are my own classes, where I store the result. The point is to use the GetOleDbSchemaTable method of the OleDbConnection:

private static void RetrieveForeignKeyInfo(OleDbConnection cnn, TableSchema tableSchema, Func<string, string> prepareColumnNameForMapping)
{
    string[] fkRestrictions = new string[] { null, null, null, null, null, tableSchema.TableName };
    using (DataTable dtForeignKeys = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, fkRestrictions)) {
        ForeignKey foreignKey = null;
        string constraintName = "";
        foreach (DataRow row in dtForeignKeys.Rows) {
            string newConstraintName = (string)row["FK_NAME"];
            if (newConstraintName != constraintName) {
                constraintName = newConstraintName;
                foreignKey = new ForeignKey();
                foreignKey.MasterTableName = (string)row["PK_TABLE_NAME"];
                tableSchema.ForeignKeys.Add(foreignKey);
            }
            var foreignKeyColumn = new ForeignKeyColumn();
            foreignKeyColumn.DetailColumnName = (string)row["FK_COLUMN_NAME"];
            foreignKeyColumn.MasterColumnName = (string)row["PK_COLUMN_NAME"];
            foreignKeyColumn.DetailColumnNameForMapping = prepareColumnNameForMapping(foreignKeyColumn.DetailColumnName);
            foreignKeyColumn.MasterColumnNameForMapping = prepareColumnNameForMapping(foreignKeyColumn.MasterColumnName);
            foreignKey.Columns.Add(foreignKeyColumn);
        }
    }
}

Do you mean to store the result of some SQL Query in the TableSchema class? If so, I do not have a specific chunk of data for which I need the foreign key relationships. I need to retrieve all the foreign key relationships present in the DB Schema. — OnlyMahesh, Nov 17 '11 at 22:37
My example retrieves the foreign keys for one table. The table name is specified in fkRestrictions. You don't have a TableSchema class. TableSchema and ForeignKey are classes that I have made for my purpose. You will get a DataTable from the cnn.GetOleDbSchemaTable() call. You can try to pass null for the table name, or you can use a schema table that returns all the tables available. Have a look a the OleDbSchemaGuid class. There are a lot of schema tables available, each of them returning valuable information. — Olivier Jacot-Descombes, Nov 18 '11 at 13:38

score 2 · Answer 2 · answered Nov 11 '11 at 21:24

2

I would use DAO, in which case the relationships are in a collection that you can retrieve from the Relationships property of the Database object.

In ADO.NET, you use the Relations property of the DataSet class.

answered Nov 11 '11 at 21:24

phoog

42,068
6
79
117

I used DAO for Access DB. But what do I do for SQL Server DB? DataSet.Relations will not help, because I want all the foreign key relationships. I do not have a specific DataSet for which I need to find out the relationships. – OnlyMahesh Nov 17 '11 at 22:32
Can't you get a SqlDataAdapter and call FillSchema to get the schema in the DataSet? – phoog Nov 17 '11 at 22:57
Again, I need a query for the SqlDataAdapter. There are nearly 80 tables in my DB for which I need to retrieve foreign key relationships without actual data. – OnlyMahesh Nov 17 '11 at 23:49
1

Try this http://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server-2005/483266 – phoog Nov 18 '11 at 00:02

Retrieve table relationships from MS Access DB using C#

2 Answers2