Re: Foreign key constraints from DB at runtime
- From: "Francois Bonin [C# MVP]" <thecois@xxxxxxxxx>
- Date: Thu, 13 Jul 2006 14:42:08 -0000
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
"Shelah" <hshelah@xxxxxxxxx> wrote in message
news:1152737056.951451.90510@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
.
- References:
- Foreign key constraints from DB at runtime
- From: Shelah
- Foreign key constraints from DB at runtime
- Prev by Date: Re: How to Send XML to client
- Next by Date: Re: Proper resource deallocation through Dispose()
- Previous by thread: Foreign key constraints from DB at runtime
- Next by thread: How to Send XML to client
- Index(es):
Relevant Pages
|
