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