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.

Answer 1

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:

• Refer possible values of GrBit field and their meaning
• How GrBit is calculated

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.

Answer 2

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