ADO.NET: Accessing DB table relationships from

[Conceptor]

Subject : "ADO.NET: Accessing DB table relationships from
VB.Net"

Hi!

I need to *automatically* access ODBC database table
relationships from VB.Net, preferably using a native
object like ADO.Net, to navigate into the tables using
their relationships. I need this to be automatic,
meaning that I want to open a connection to a database
and automatically get a relations collection of some sort.
In a book I have titled "Upgrading to .Net", page 419,
it is stated that:
"IMPORTANT: Any relationships between DataTables need to
be specified by the programmer. The current version of
ADO.NET does not support propagating preexisting
relationships from the data source to the Dataset.
Therefore, any relationships that are defined in your SQL
database (or other relational databases) need to be
redefined in your dataset."
Since the databases my application will be accessing
are never the same, and that I need a functionality to
view in the GUI the tables and their relationships, I
wonder how I can get those relationships...

Any help would be greatly appreciated!

Thanks in advance,
Conceptor

 

[Rajesh Tiwari]

thanks for this post as it clarified my long pending query.i was also not
able to access foreign key relationships defined on a table in database
using dataset etc..now i know it is not possible to do so.A Bug in ADO.NET
perhaps!!!!!!!!

 

[Conceptor]

Thanks for the reply!

Still, I need a way to access these relationship
definitions. If it is impossible from ADO.Net to get
them, is there a way to get to them from another
technology? Perhaps older Ado, Dao or Rdo technologies...

I would apreciate any pointers you could give me (pun
intended!)

Thanks again,
C

 

[Kris]

it's possible with SQL but it depends on what DBMS you are using, oracle for
example has some system tables that contain this information (constraints).
It sure looks like a good project to automate and standardize this (MS :
TIP)...


grtz

Kris

 

[Roy Osherove]

You *can* get the schema information for the relationships of the different
tables.
To do this, you need to have an OleDbConnectionObject.
It has a method name 'GetOleDbSchemaTable()' which returns a data table with
all the info you need, and you can filter the kind of data to be returned
(only foreign keys for a certain table, for example).
I've implemented a very simple class to demonstrate how you would use it to
create Data Relations automatically given a connection, but it can be
further improved(a lot! I made it in like 10 mins. and had to get back to
work).

See here for the code:
Automatically Create DataRelations Based On OleDB Schema

And here's an article which explain the use of this method(It also shows
using it with SqlConnection object):
HOW TO: Retrieve Schema Information by Using GetOleDbSchemaTable and Visual
Basic .NET

good luck.
--
Regards,

Roy Osherove
http://www.iserializable.com
---------------------------------------------

 

[Conceptor]

Can. That's the word I wanted to hear. Thanks a lot
man, you just made my day!

C.