A much needed SchemaHelper class
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:
(Notice that I had to break lines a lot to keep it all readable…)
public class SchemaHelper
{
public enum InformationType
{
TablesAndView,
Everything
}
public static readonly string FK_COL_NAME = "FK_COLUMN_NAME";
public static readonly string PK_COL_NAME = "PK_COLUMN_NAME";
public static readonly string FK_TABLE_NAME = "FK_TABLE_NAME";
public static readonly string PK_TABLE_NAME= "PK_TABLE_NAME";
private SchemaHelper(){}
private static object[] CreateArrayForSchemaQuery(
string TableCatalog,
string TableSchema,
string TableName,
string TableType)
{
return new object[]{TableCatalog,TableSchema,
TableName,TableType};
}
public static string GetRestrictionName(InformationType restriction)
{
switch(restriction)
{
case InformationType.TablesAndView:
return "TABLE";
case InformationType.Everything:
return null;
default:
return null;
}
}
public static DataTable GetForeignKeysSchemaTable(
OleDbConnection connection,
string tableName)
{
DataTable schema =
connection.GetOleDbSchemaTable(
OleDbSchemaGuid.Foreign_Keys,
CreateArrayForSchemaQuery(
null,
null,
tableName,
GetRestrictionName(
InformationType.Everything)));
return schema;
}
public static DataTable GetForeignKeysSchemaTable(OleDbConnection connection)
{
return GetForeignKeysSchemaTable(connection,null);
}
private static void CreateTableIf(DataSet ds,string tableName,
OleDbConnection connection,bool create)
{
if(create)
{
OleDbDataAdapter adp = new OleDbDataAdapter(string.Empty,
connection);
CreateAndFillSchemaIfMissing(ds,adp,tableName);
}
}
private static void CreateRelationsForAllSchemaRows(DataTable schemaTable,
OleDbConnection connection,
DataSet ds,
string tableName,
bool createOriginal,
bool CreateRelated)
{
foreach(DataRow row in schemaTable.Rows)
{
string sPKCol = row[SchemaHelper.PK_COL_NAME].ToString();
string sFKCol = row[SchemaHelper.FK_COL_NAME].ToString();
string sPKTable = row[SchemaHelper.PK_TABLE_NAME].ToString();
string sFKTable = row[SchemaHelper.FK_TABLE_NAME].ToString();
string sRelName = sPKTable + "_" + sPKCol +
"_" + sFKTable + "_" + sFKCol;
bool bCreatePKForThisRow= (createOriginal && tableName ==null);
CreateTableIf(ds,sPKTable,connection,bCreatePKForThisRow);
CreateTableIf(ds,sFKTable,connection,CreateRelated);
if(ds.Tables.Contains(sFKTable)&& ds.Tables.Contains(sPKTable))
}
CreateDataRelation(ds,sRelName, sPKTable,
sPKCol,sFKTable,sFKCol);
}
}
}
public static void InferDataRelations(OleDbConnection connection,
DataSet ds,bool createOriginal,
bool createRelated)
{
InferDataRelations(connection,ds,null,createOriginal,createRelated);
}
public static void InferDataRelations(OleDbConnection connection,
DataSet ds,string tableName,
bool createMasterTableIfMissing,
bool createChildTableIfMissing)
{
CreateTableIf(ds,tableName,connection,
(createMasterTableIfMissing && tableName !=null));
DataTable schema = GetForeignKeysSchemaTable(connection,tableName);
CreateRelationsForAllSchemaRows(schema,connection,ds,
tableName,createMasterTableIfMissing,
createChildTableIfMissing);
}
public static DataRelation CreateDataRelation(DataSet ds,
string RelationName,
string PrimaryTableName,
string PrimaryColumnName,
string ChildTableName,
string ChildColumnName)
{
DataColumn PKDataCol =
ds.Tables[PrimaryTableName].Columns[PrimaryColumnName];
DataColumn FKDataCol =
ds.Tables[ChildTableName].Columns[ChildColumnName];
DataRelation dr =
ds.Relations.Add(RelationName,FKDataCol,PKDataCol);
return dr;
}
public static void CreateAndFillSchemaIfMissing(DataSet ds,
OleDbDataAdapter adp,
string TableName)
{
if(!ds.Tables.Contains(TableName))
{
adp.SelectCommand.CommandText = "SELECT * FROM " + TableName;
adp.FillSchema(ds, SchemaType.Source, TableName);
}
}
}