Phew. I've made a fixed version of my SchemaHelper class (this time in C#).

This class solves the problem of not being able to automatically find Data Relations while filling a Dataset object with tables. Although DataAdapter will fill the primary key of the columns and other properties if you use it with FillSchema() or set its MissingSchemaAction to AddWithKey, DataRelations will not be added automatically.

 

Enter SchemaHelper.

 

This class lets you automatically find and create DataRelations for the tables in your Dataset(or it can create them for you if they are missing) according to the foreign key schema information that can be retrieved using an OleDBConnection object. This functionality does exist for SqlConection, but I'll add that functionality later(or you can add it yourself).

 

You'll need to pass it an open instance of a connection, a dataset, and two flags.

The first flag tells it whether to create master tables which do not exist in your dataset but can be found in the foreign key schema.

The second flag tells it whether to automatically create related tables if they do not exist for your primary table in your dataset.

That last flag is handy if you only have a table in your dataset and you want to discover and add to the dataset all the data tables that are related to it using a foreign key, and create the data relations between them automatically as well.

 You can also use it to fill relations of one particular table, by passing in the table name, in which case it will only try to retrieve and create the table you pass in, and the related tables for it (given the right flags).

 

The syntax to use is pretty straight forward:

DataSet ds = new DataSet();

cn.Open();

 

Console.WriteLine("table count is " + ds.Tables.Count);

Console.WriteLine("relation count is " + ds.Relations.Count);

SchemaHelper.InferDataRelations(cn,ds,"Users",true,true);

 

cn.Close();

 

After this operation(supposing you have a foreign key between Users.userID and orders.UserID) you'll end up with two DataTables and on relation between them.

If you use this syntax, however:

 

SchemaHelper.InferDataRelations(cn,ds,false,false);

 

You'll end up with only new Data relations between already existing tables in your dataset(which should be a faster operation).

 

Here's the code for the entire class – as always – I welcome any comment or suggestion:

Here's the full story & code