ADO.NET GetOleDbSchemaTable

Question

I'm trying to fill DataRelation collection by information, read from database.

DataTable schemaTable =
    oleconnection.GetOleDbSchemaTable(
        OleDbSchemaGuid.Foreign_Keys,
        new object[] { null, null, tablename });

But this info doesn't say anything about is it real foreign key constraint or just relation and, because of this, I don't know what value to set for the fourth parameter of DataRelation constructor (createConstraints).

public DataRelation(
    string relationName,
    DataColumn[] parentColumns,
    DataColumn[] childColumns,
    bool createConstraints
)

Particularly, in MS Access one could connect two tables with relation but not enforce data integrity check. Some additional research showed that such unenforced relations appear in OleDbSchemaGuid.Referential_Constraints schema table too.

I am wondering where can I get required information. Please show me the way.

for which particular database you required he information. Do you require only PK-FK relationship or the check constraints. — Romil Kumar Jain, Jun 14 '12 at 10:06
For MS Access. I need PK-FK relationship list (and for each of item information about is it enforced - in MS Access one can create PK-FK relationship without enforcement). — Ruben, Jun 15 '12 at 05:28
If I provide you the list of column names, table names used in relation and true/false to identify relation is enforced or not. Will it work or you need explicitly relation object. — Romil Kumar Jain, Jun 15 '12 at 07:23
One more query, which version of access i.e 2003, 2007 , 2010 you are using. — Romil Kumar Jain, Jun 15 '12 at 07:36
MS Access 2003. Yes, enforcement flag field (true/false) for each relation is sufficient for me. I tried to find such fields, but nothing of found was really what I want (even combinations of several fields wasn't). — Ruben, Jun 15 '12 at 19:14
I'm interested in retrieving this information through ADO .NET interface. — Ruben, Jun 15 '12 at 19:21

score 4 · Answer 1 · edited Jan 23 '13 at 18:14

It's not possible to fetch the additional attributes related to a relation in MSAccess using Ado.Net. It was possible only in VBA using DAO.

All the relations and their additional attributes are stored in MSysRelationships table in every version of ms-access. The field grbit has values for all possible attributes of a relation i.e. Enforce Referential Intigrity, Cascade Delete, Cascadde Update. Other fields of this table are self explanatory and those are same with the fields returned by GetOleDbSchemaTable method.

Some useful calculation for Grbit:

It means if there is one to one relationship and Enforce Referential Intigrity is on then Grbit will be 0.
It means if there is one to one relationship and Enforce Referential Intigrity is OFF then Grbit will be 3.
It means if there is one to many relationship and Enforce Referential Intigrity is OFF then Grbit will be 2.
It means if there is one to many relationship and Enforce Referential Intigrity is ON then Grbit will be 0.

Sample code to access the table in c#:

OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\mydb.mdb;");
con.Open();
OleDbDataAdapter da = new OleDbDataAdapter("Select * From MSysRelationships", con);
DataSet ds = new DataSet();
da.Fill(ds);

See also:

Note: It's not possible to fetch the extra information related to relation in MS-Access through GetOleDbSchemaTable. Oledb is an wrapper only on the database and Access is not exposing ths information to outer world, so it's not possible to fetch the information with ADO.net in direct maner.

So for workaround, you need to access and manipulate only grbit column information of MSysRelationships table.

Sorry, I'm interested only in answer that shows how to retrieve this information through ADO .NET interface. — Ruben, Jun 16 '12 at 12:42
@Ruben, There is no other way to get this information other than fetching records from MSysRelationships. You get the same information from this table that is returned by GetOleDbSchemaTable. — Romil Kumar Jain, Jun 16 '12 at 15:06

score 0 · Answer 2 · answered Jan 23 '13 at 18:09

0

Check this: http://support.microsoft.com/kb/309681 Seems easy but I got also problem in dealing with DataTable :(

answered Jan 23 '13 at 18:09

Stacked

6,892
7
57
73

ADO.NET GetOleDbSchemaTable

2 Answers2