Foreign key constraints from DB at runtime

12 Jul 2006 8:44 PM

Shelah

I'm trying to figure out how to discover foreign key constraints at
runtime by asking the database.

All the code samples I see in .NET have you manually create the foreign
key constraints. I'm trying to detect them from the database's schema.
I'm working with MySQL4.1.

When I query the database, I get my UniqueConstraints but I don't get
my ForeignKeyConstraints, and wondered if anyone knew how to do it.

In my program, the business objects are associated in the database with
a main table, and collections within this object are associated by
foreign key constraints to other tables.

So say I have an object, User. It's associated with the table "user",
which is identified by a unique integer key, ObjectId. It contains no
foreign key constraints.

A User can belong to any number of Groups, and a Group can contain any
number of Users, so I have a "groups" table keyed by its own integer
ObjectId. It also contains no foreign key constraints.

Connecting the two is a "usergroups" table, also keyed by integer
ObjectId, and all it contains is two foreign keys. "UserId" refers to
"user.ObjectId" and "GroupId" refers to "groups.ObjectId".

My code looks like this:

            StringBuilder sb = new StringBuilder();
            sb.Append( "SELECT * FROM " );
            sb.Append( strTableName );
            using ( OdbcCommand cmd = new OdbcCommand( sb.ToString(),
connection ) )
            {
                using ( OdbcDataAdapter da = new OdbcDataAdapter() )
                {
                    da.SelectCommand = cmd;
                    // Get the schema for this table and add
                    // the schema for any foreign key tables to the
                    // dataset
                    DataTable table = new DataTable( strTableName );
                    da.FillSchema( table, SchemaType.Source );
                    ds.Tables.Add( table );
                    foreach ( Constraint constraint in
table.Constraints )
                    {
                        if ( constraint is ForeignKeyConstraint )
                        {
                           ... do stuff...
                        }
                    }
                }
            }

When you run this code on the "usergroups" table, which has two foreign
key constraints, no ForeignKeyConstraints exist. I have my
UniqueConstraint on UserId but that's it. If I look in the debugger,
table.Constraints.Non-Public members.fLoadForeignKeyConstraint is
false, which looks like the culpret. Do I need to set this flag to
give me the foreign key constraint? If I do, how do I do it?

I see there's the DataTable.ParentRelations and ChildRelations
properties but I haven't tried using them because I think they just
access the ForeignKeyConstraints. I figured if there are no
ForeignKeyConstraints in the collection, I'm going to get the same
results if I rip out my code and rewrite it to use DataRelations.

Does anyone know how to do this?

Thanking you for in advance for your time and thoughts.

Shelah

13 Jul 2006 2:42 PM

Francois Bonin [C# MVP]

Given that you are working with a MySQL db, you can connect using an
OleDbConnection object, and then use the GetOleDbSchemaTable() method to get
your schema information

OleDbConnection conn;
//
conn.Open()
Dim schemaTable As DataTable =
conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, New Object()
{Nothing, Nothing, Nothing, Nothing, Nothing})
conn.Close()

This will get you all the foreign key definitions inside the schemaTable
DataTable.
You can get more info by looking at the documentation for
OleDbConnection.GetOleDbSchemaTable()

HTH
Cois

Show quote

"Shelah" <hshe***@yahoo.com> wrote in message
news:1152737056.951451.90510@s13g2000cwa.googlegroups.com...
> I'm trying to figure out how to discover foreign key constraints at
> runtime by asking the database.
>
> All the code samples I see in .NET have you manually create the foreign
> key constraints. I'm trying to detect them from the database's schema.
> I'm working with MySQL4.1.
>
> When I query the database, I get my UniqueConstraints but I don't get
> my ForeignKeyConstraints, and wondered if anyone knew how to do it.
>
> In my program, the business objects are associated in the database with
> a main table, and collections within this object are associated by
> foreign key constraints to other tables.
>
> So say I have an object, User. It's associated with the table "user",
> which is identified by a unique integer key, ObjectId. It contains no
> foreign key constraints.
>
> A User can belong to any number of Groups, and a Group can contain any
> number of Users, so I have a "groups" table keyed by its own integer
> ObjectId. It also contains no foreign key constraints.
>
> Connecting the two is a "usergroups" table, also keyed by integer
> ObjectId, and all it contains is two foreign keys. "UserId" refers to
> "user.ObjectId" and "GroupId" refers to "groups.ObjectId".
>
> My code looks like this:
>
>            StringBuilder sb = new StringBuilder();
>            sb.Append( "SELECT * FROM " );
>            sb.Append( strTableName );
>            using ( OdbcCommand cmd = new OdbcCommand( sb.ToString(),
> connection ) )
>            {
>                using ( OdbcDataAdapter da = new OdbcDataAdapter() )
>                {
>                    da.SelectCommand = cmd;
>                    // Get the schema for this table and add
>                    // the schema for any foreign key tables to the
>                    // dataset
>                    DataTable table = new DataTable( strTableName );
>                    da.FillSchema( table, SchemaType.Source );
>                    ds.Tables.Add( table );
>                    foreach ( Constraint constraint in
> table.Constraints )
>                    {
>                        if ( constraint is ForeignKeyConstraint )
>                        {
>                           ... do stuff...
>                        }
>                    }
>                }
>            }
>
> When you run this code on the "usergroups" table, which has two foreign
> key constraints, no ForeignKeyConstraints exist. I have my
> UniqueConstraint on UserId but that's it. If I look in the debugger,
> table.Constraints.Non-Public members.fLoadForeignKeyConstraint is
> false, which looks like the culpret. Do I need to set this flag to
> give me the foreign key constraint? If I do, how do I do it?
>
> I see there's the DataTable.ParentRelations and ChildRelations
> properties but I haven't tried using them because I think they just
> access the ForeignKeyConstraints. I figured if there are no
> ForeignKeyConstraints in the collection, I'm going to get the same
> results if I rip out my code and rewrite it to use DataRelations.
>
> Does anyone know how to do this?
>
> Thanking you for in advance for your time and thoughts.
>
> Shelah
>