Automatically Create DataRelations Based On OleDB Schema

2 Comments

• why not write it in c#
  
  
  ;)
  

• public static void InferRelations( DataSet ds, System.Data.OleDb.OleDbConnection cn )
  
  {
  
  System.Data.OleDb.OleDbDataAdapter adp = new System.Data.OleDb.OleDbDataAdapter("", cn);
  
  //we can put restrictions on the data we want to recieve
  
  Object[] restrictions = new Object[] {null, null, null, null};
  
  //this is the Foreign key data we need to create relations
  
  DataTable schemaTable = cn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Foreign_Keys, restrictions);
  
  
  
  //go through all the foreign keys displayed
  
  
  
  foreach(DataRow row in schemaTable.Rows)
  
  {
  
  string strTable = row["PK_TABLE_NAME"].ToString();
  
  string strChild = row["FK_TABLE_NAME"].ToString();
  
  string strParentColName = row["PK_COLUMN_NAME"].ToString();
  
  string strChildColName = row["FK_COLUMN_NAME"].ToString();
  
  
  
  // the relation name that will be created.
  
  string strRelationName = strTable + "_" + strParentColName + "_" + strChild + "_" + strChildColName;
  
  
  
  // create any non existing tables along with key information
  
  CreateAndfFillSchema(ds, adp, strTable);
  
  CreateAndfFillSchema(ds, adp, strChild);
  
  try
  
  {
  
  DataColumn PrimaryColumn = ds.Tables[strTable].Columns[strParentColName];
  
  DataColumn ChildColumn = ds.Tables[strChild].Columns[strChildColName];
  
  ds.Relations.Add(strRelationName, PrimaryColumn, ChildColumn);
  
  }
  
  catch( Exception )
  
  {
  
  //well get here if there's already such as relation, but we want to continue anyway.
  
  }
  
  }
  
  }
  
  
  
  private static void CreateAndfFillSchema( System.Data.DataSet ds, System.Data.OleDb.OleDbDataAdapter adp, string TableName)
  
  {
  
  if( ! ds.Tables.Contains(TableName) )
  
  {
  
  adp.SelectCommand.CommandText = "SELECT * FROM " + TableName;
  
  adp.FillSchema(ds, SchemaType.Source, TableName);
  
  }
  
  }