protected override DataTable GetForeignKeyColumns(DbConnection con, DBSchemaItemRef fkref)
{
DBSchemaMetaDataCollection colmscol = this.AssertGetCollectionForType(DBMetaDataCollectionType.ForeignKeyColumns);
DataTable dtfkc = this.GetCollectionData(con, colmscol, fkref.Schema, null, fkref.Name);
return(dtfkc);
}
//
// Table Schema
//
protected override DataTable GetTableData(DbConnection con, DBSchemaItemRef tableref)
{
DBSchemaMetaDataCollection tblcol = this.AssertGetCollectionForType(DBMetaDataCollectionType.Tables);
DataTable dtTable = this.GetCollectionData(con, tblcol, tableref.Schema, tableref.Name);
return(dtTable);
}
protected override DataTable GetViewData(DbConnection con, DBSchemaItemRef vref)
{
DBSchemaMetaDataCollection vcol = this.AssertGetCollectionForType(DBMetaDataCollectionType.Views);
DataTable dtView = this.GetCollectionData(con, vcol, vref.Schema, vref.Name);
return(dtView);
}
protected override DataTable GetViewColumns(DbConnection con, DBSchemaItemRef vref)
{
DBSchemaMetaDataCollection colmscol = this.AssertGetCollectionForType(DBMetaDataCollectionType.Columns);
DataTable dtVCols = this.GetCollectionData(con, colmscol, vref.Schema, vref.Name);
return(dtVCols);
}
//
// Routines
// The MSAccess support for procedures is limited and inconsistent -
// views are procedures and procedures are views and no inspection of the
// parameters are supported however - this is how it is reported!
//
#region protected virtual void LoadRoutineRefs(DbConnection con, IList<DBSchemaItemRef> intoCollection)
/// <summary>
/// Loads all the routines (Procedures and fuctions) in this providers data connection
/// </summary>
/// <param name="con"></param>
/// <param name="intoCollection"></param>
protected override void LoadRoutineRefs(DbConnection con, IList <DBSchemaItemRef> intoCollection)
{
DBSchemaMetaDataCollection viewcollection =
this.AssertGetCollectionForType(DBMetaDataCollectionType.Procedures);
DataTable data = this.GetCollectionData(con, viewcollection);
WriteCollectionData("Procedures", new string[] { }, data);
DataColumn catalogcol = GetColumn(data, "PROCEDURE_CATALOG", false);
DataColumn schemacol = GetColumn(data, "PROCEDURE_SCHEMA", false);
DataColumn namecol = GetColumn(data, "PROCEDURE_NAME", true);
DataColumn typecol = GetColumn(data, "PROCEDURE_TYPE", true);
foreach (DataRow row in data.Rows)
{
DBSchemaItemRef iref;
string type = GetColumnStringValue(row, typecol);
string name = GetColumnStringValue(row, namecol);
//system procedures start with a tilde - remove these.
if (string.IsNullOrEmpty(name) || name.StartsWith("~"))
{
row.Delete();
}
else
{
iref = this.LoadAnItemRef(row, catalogcol, schemacol, namecol, DBSchemaTypes.StoredProcedure);
if (null != iref)
{
intoCollection.Add(iref);
}
}
}
}
//
// indexes
//
protected override void LoadIndexRefs(DbConnection con, IList <DBSchemaItemRef> intoCollection)
{
DBSchemaMetaDataCollection idxcollection =
this.AssertGetCollectionForType(DBMetaDataCollectionType.Indexes);
DataTable data = this.GetCollectionData(con, idxcollection);
DataColumn catalogcol = GetColumn(data, "catalog", false);
DataColumn schemacol = GetColumn(data, "owner", false);
DataColumn namecol = GetColumn(data, "index_name", true);
DataColumn tablecatalogcol = GetColumn(data, "table_catalog", false);
DataColumn tableschemacol = GetColumn(data, "table_owner", false);
DataColumn tablenamecol = GetColumn(data, "table_name", true);
//If there are no explicit schema columns for the containing table then use the column for the index.
if (null == tableschemacol && null != schemacol)
{
tableschemacol = schemacol;
}
if (null == tablecatalogcol && null != catalogcol)
{
tablecatalogcol = catalogcol;
}
this.LoadItemRefsWithContainer(data, intoCollection,
catalogcol, schemacol, namecol, DBSchemaTypes.Index,
tablecatalogcol, tableschemacol, tablenamecol, DBSchemaTypes.Table);
}
protected override DataTable GetIndexColumns(DbConnection con, DBSchemaItemRef idxref)
{
DBSchemaMetaDataCollection col = this.AssertGetCollectionForType(DBMetaDataCollectionType.IndexColumns);
string ownertable = idxref.Container == null ? null : idxref.Container.Name;
DataTable dt = this.GetCollectionData(con, col, idxref.Schema, idxref.Name, null, ownertable);
return(dt);
}
//
// views
//
protected override void LoadViewRefs(DbConnection con, IList <DBSchemaItemRef> intoCollection)
{
DBSchemaMetaDataCollection viewcollection = this.AssertGetCollectionForType(DBMetaDataCollectionType.Views);
DataTable data = this.GetCollectionData(con, viewcollection);
DataColumn catalogcol = GetColumn(data, "CATALOG", false);
DataColumn schemacol = GetColumn(data, "OWNER", false);
DataColumn namecol = GetColumn(data, "VIEW_NAME", true);
this.LoadItemRefs(data, intoCollection, catalogcol, schemacol, namecol, DBSchemaTypes.View);
}
protected override void LoadViewRefs(DbConnection con, IList <DBSchemaItemRef> intoCollection)
{
DBSchemaMetaDataCollection tblcollection = this.AssertGetCollectionForType(DBMetaDataCollectionType.Views);
DataTable data = this.GetCollectionData(con, tblcollection, null, null, null, "SYSTEM VIEW");
DataColumn catalogcol = GetColumn(data, "TABLE_CATALOG", false);
DataColumn schemacol = GetColumn(data, "TABLE_SCHEMA", false);
DataColumn namecol = GetColumn(data, "TABLE_NAME", true);
this.LoadItemRefs(data, intoCollection, catalogcol, schemacol, namecol, DBSchemaTypes.View);
}
protected override DataTable GetForeignKeyColumns(DbConnection con, DBSchemaItemRef fkref)
{
DBSchemaMetaDataCollection colmscol = this.AssertGetCollectionForType(DBMetaDataCollectionType.IndexColumns);
string sql = @"SELECT
CONSTRAINT_SCHEMA AS FKSchema,
CONSTRAINT_NAME AS FKConstraintName,
TABLE_SCHEMA AS FKTableSchema,
TABLE_NAME AS FKTable,
COLUMN_NAME AS FKColumn,
REFERENCED_TABLE_SCHEMA AS PKSchema,
REFERENCED_TABLE_NAME AS PKTable,
REFERENCED_COLUMN_NAME AS PKColumn
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME = {0} AND
TABLE_SCHEMA = {1} AND TABLE_NAME = {2}";
using (DbCommand cmd = this.Database.CreateCommand(con, ""))
{
DbParameter pname = this.Database.AddCommandParameter(cmd, "name", DbType.String);
//DbParameter pcatalog = this.Database.AddCommandParameter(cmd, "catalog", DbType.String);
DbParameter pschema = this.Database.AddCommandParameter(cmd, "schema", DbType.String);
DbParameter ptable = this.Database.AddCommandParameter(cmd, "table", DbType.String);
//we format here to get the native name from the parameter
cmd.CommandText = string.Format(sql, pname.ParameterName, pschema.ParameterName, ptable.ParameterName);
pname.Value = fkref.Name;
if (fkref.Container != null)
{
ptable.Value = fkref.Container.Name;
pschema.Value = fkref.Container.Schema;
}
else
{
ptable.Value = "%";
pschema.Value = "%";
}
DataSet ds = new DataSet();
DataTable dt = ds.Tables.Add("ForeignKeys");
this.Database.PopulateDataSet(ds, cmd, LoadOption.OverwriteChanges, "ForeignKeys");
ds.AcceptChanges();
try {
dt.WriteXml("C:\\SchemaOutput\\ForeignKeys" + fkref.Name + ".xml");
}catch (Exception ex) { }
return(dt);
}
}
protected override DataTable GetIndexData(DbConnection con, DBSchemaItemRef idxref)
{
DBSchemaMetaDataCollection idxcol = this.AssertGetCollectionForType(DBMetaDataCollectionType.Indexes);
string table;
if (idxref.Container != null)
{
table = idxref.Container.Name;
}
else
{
table = null;
}
return(this.GetCollectionData(con, idxcol, idxref.Schema, idxref.Name));
}
protected override void LoadStoredProcedureRefs(DbConnection con, IList <DBSchemaItemRef> intoCollection)
{
DBSchemaMetaDataCollection viewcollection =
this.AssertGetCollectionForType(DBMetaDataCollectionType.Procedures);
DataTable data = this.GetCollectionData(con, viewcollection);
DataColumn catalogcol = GetColumn(data, "CATALOG", false);
DataColumn schemacol = GetColumn(data, "OWNER", false);
DataColumn namecol = GetColumn(data, "OBJECT_NAME", true);
foreach (DataRow row in data.Rows)
{
DBSchemaItemRef iref;
iref = this.LoadAnItemRef(row, catalogcol, schemacol, namecol, DBSchemaTypes.StoredProcedure);
intoCollection.Add(iref);
}
}
/// <summary>
/// Loads all the indexes in this providers data connection
/// </summary>
/// <param name="con"></param>
/// <param name="intoCollection"></param>
protected override void LoadIndexRefs(DbConnection con, IList <DBSchemaItemRef> intoCollection)
{
DBSchemaMetaDataCollection viewcollection =
this.AssertGetCollectionForType(DBMetaDataCollectionType.Indexes);
DataTable data = this.GetCollectionData(con, viewcollection);
DataColumn catalogcol = GetColumn(data, "index_catalog", false);
DataColumn schemacol = GetColumn(data, "index_schema", false);
DataColumn namecol = GetColumn(data, "index_name", true);
DataColumn tablecatalogcol = GetColumn(data, "table_catalog", false);
DataColumn tableschemacol = GetColumn(data, "table_schema", false);
DataColumn tablenamecol = GetColumn(data, "table_name", true);
this.LoadItemRefsWithContainer(data, intoCollection,
catalogcol, schemacol, namecol, DBSchemaTypes.Index,
tablecatalogcol, tableschemacol, tablenamecol, DBSchemaTypes.Table);
}
protected override void LoadForeignKeyRefs(DbConnection con, IList <DBSchemaItemRef> intoCollection, DBSchemaItemRef fortable)
{
DBSchemaMetaDataCollection viewcollection =
this.AssertGetCollectionForType(DBMetaDataCollectionType.ForeignKeys);
DataTable data = this.GetCollectionData(con, viewcollection, fortable.Schema, fortable.Name, null);
DataColumn catalogcol = GetColumn(data, "foreign_key_catalog", false);
DataColumn schemacol = GetColumn(data, "foreign_key_owner", false);
DataColumn namecol = GetColumn(data, "foreign_key_constraint_name", true);
DataColumn containercatalogcol = GetColumn(data, "foreign_key_table_catalog", false);
DataColumn containerschemacol = GetColumn(data, "foreign_key_table_owner", false);
DataColumn containernamecol = GetColumn(data, "foreign_key_table_name", true);
this.LoadItemRefsWithContainer(data, intoCollection,
catalogcol, schemacol, namecol, DBSchemaTypes.ForeignKey,
containercatalogcol, containerschemacol, containernamecol, DBSchemaTypes.Table);
}
protected override DataTable GetTableColumns(DbConnection con, DBSchemaItemRef tableref)
{
DBSchemaMetaDataCollection colmscol = this.AssertGetCollectionForType(DBMetaDataCollectionType.Columns);
DataTable dt = this.GetCollectionData(con, colmscol, tableref.Schema, tableref.Name);
DBSchemaMetaDataCollection pks = this.AssertGetCollectionForName("PrimaryKeys");
DataTable pkcols = this.GetCollectionData(con, pks, tableref.Schema, tableref.Name);
if (pkcols.Rows.Count > 0)
{
string owner = pkcols.Rows[0]["OWNER"].ToString();
string indexname = pkcols.Rows[0]["INDEX_NAME"].ToString();
DBSchemaMetaDataCollection ix = this.AssertGetCollectionForType(DBMetaDataCollectionType.IndexColumns);
pkcols = this.GetCollectionData(con, ix, owner, indexname);
FillPrimaryKeys(dt, pkcols);
}
return(dt);
}
/// <summary>
/// Loads all the indexes in this providers data connection for the specified table
/// </summary>
/// <param name="con"></param>
/// <param name="fortable"></param>
/// <param name="intoCollection"></param>
protected override void LoadIndexRefs(DbConnection con, IList <DBSchemaItemRef> intoCollection, DBSchemaItemRef fortable)
{
DBSchemaMetaDataCollection idxcollection =
this.AssertGetCollectionForType(DBMetaDataCollectionType.Indexes);
DataTable data = this.GetCollectionData(con, idxcollection, fortable.Schema, fortable.Name, string.Empty);
int i = data.Rows.Count;
DataColumn catalogcol = GetColumn(data, "catalog", false);
DataColumn schemacol = GetColumn(data, "owner", false);
DataColumn namecol = GetColumn(data, "index_name", true);
DataColumn tablecatalogcol = GetColumn(data, "table_catalog", false);
DataColumn tableschemacol = GetColumn(data, "table_owner", false);
DataColumn tablenamecol = GetColumn(data, "table_name", true);
this.LoadItemRefsWithContainer(data, intoCollection,
catalogcol, schemacol, namecol, DBSchemaTypes.Index,
tablecatalogcol, tableschemacol, tablenamecol, DBSchemaTypes.Table);
}
/// <summary>
/// Overriden because default behaviour for the Get Tables collection returns views too.
/// </summary>
/// <param name="con"></param>
/// <param name="intoCollection"></param>
protected override void LoadTableRefs(DbConnection con, IList <DBSchemaItemRef> intoCollection)
{
DBSchemaMetaDataCollection tblcollection = this.AssertGetCollectionForType(DBMetaDataCollectionType.Tables);
DataTable data = this.GetCollectionData(con, tblcollection);
DataColumn catalogcol = GetColumn(data, "TABLE_CATALOG", true);
DataColumn schemacol = GetColumn(data, "TABLE_SCHEMA", true);
DataColumn namecol = GetColumn(data, "TABLE_NAME", true);
DataColumn typecol = GetColumn(data, "TABLE_TYPE", true);
foreach (DataRow row in data.Rows)
{
if (GetColumnStringValue(row, typecol).Equals("BASE TABLE", StringComparison.OrdinalIgnoreCase))
{
DBSchemaItemRef tbl = this.LoadAnItemRef(row, catalogcol, schemacol, namecol, DBSchemaTypes.Table);
if (null != tbl)
{
intoCollection.Add(tbl);
}
}
}
}
protected override DataTable GetForeignKeyData(DbConnection con, DBSchemaItemRef fkref)
{
string table = null;
if (fkref.Container != null)
{
table = fkref.Container.Name;
}
DBSchemaMetaDataCollection fkcol = this.AssertGetCollectionForType(DBMetaDataCollectionType.ForeignKeys);
DataTable dtFK = this.GetCollectionData(con, fkcol, fkref.Catalog, fkref.Schema, table, null);
for (int i = dtFK.Rows.Count - 1; i >= 0; i--)
{
DataRow row = dtFK.Rows[i];
if (row["constraint_name"].ToString().Equals(fkref.Name, StringComparison.OrdinalIgnoreCase) == false)
{
row.Delete();
}
}
dtFK.AcceptChanges();
return(dtFK);
}
//
// Indexes
//
#region protected virtual void LoadIndexRefs(DbConnection con, IList<DBSchemaItemRef> intoCollection) + 1 overload
/// <summary>
/// Loads all the indexes in this providers data connection
/// </summary>
/// <param name="con"></param>
/// <param name="intoCollection"></param>
protected override void LoadIndexRefs(DbConnection con, IList <DBSchemaItemRef> intoCollection)
{
DBSchemaMetaDataCollection viewcollection =
this.AssertGetCollectionForType(DBMetaDataCollectionType.Indexes);
DataTable data = this.GetCollectionData(con, viewcollection);
DataColumn catalogcol = GetColumn(data, "index_catalog", false);
DataColumn schemacol = GetColumn(data, "index_schema", false);
DataColumn namecol = GetColumn(data, "index_name", true);
DataColumn tablecatalogcol = GetColumn(data, "table_catalog", false);
DataColumn tableschemacol = GetColumn(data, "table_schema", false);
DataColumn tablenamecol = GetColumn(data, "table_name", true);
//we need to shring this to take account of the fact that multiple entries are returned
//for a single index on multiple columns
List <DBSchemaItemRef> all = new List <DBSchemaItemRef>();
this.LoadItemRefsWithContainer(data, all,
catalogcol, schemacol, namecol, DBSchemaTypes.Index,
tablecatalogcol, tableschemacol, tablenamecol, DBSchemaTypes.Table);
Dictionary <string, DBSchemaItemRef> unique = new Dictionary <string, DBSchemaItemRef>();
foreach (DBSchemaItemRef idx in all)
{
string key = idx.ToString();
if (!unique.ContainsKey(key))
{
unique.Add(key, idx);
}
}
foreach (DBSchemaItemRef iref in unique.Values)
{
intoCollection.Add(iref);
}
}
protected override DataTable GetRoutineParams(DbConnection con, DBSchemaItemRef routineref)
{
DBSchemaMetaDataCollection routinecol = this.AssertGetCollectionForType(DBMetaDataCollectionType.ProcedureParameters);
return(this.GetCollectionData(con, routinecol, routineref.Schema, routineref.Name));
}
