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)); }