public Table() { Columns = new ColumnCollection(); PrimaryKey = new PrimaryKey(); ForeignKeys = new ForeignKeyCollection(); Dependencies = new ForeignKeyCollection(); }
public Table() { Columns = new ColumnCollection(); ForeignKeys = new ForeignKeyCollection(); Indexes = new IndexCollection(); //Triggers = new TriggerCollection<O>(); }
private static ForeignKeyCollection CreateForeignKeys(DataTable data, ColumnCollection columns) { var keys = new ForeignKeyCollection(); for (var i = 0; i < data.Rows.Count; i++) { var row = data.Rows[i]; var schema = (string)row["schema_name"]; var table = GetCleanTableName((string)row["referenced_table"]); var column = (string)row["referenced_column"]; var to = columns.FindByName(schema, table, column); schema = (string)row["schema_name"]; table = GetCleanTableName((string)row["table"]); column = (string)row["column"]; var from = columns.FindByName(schema, table, column); var key = new ForeignKey(from, to) { Name = (string)row["FK_NAME"] }; keys.Add(key); } return(keys); }
private static void LoadKeys(TableCollection tables, ForeignKeyCollection keys) { foreach (var table in tables) { foreach (var key in keys) { foreach (var column in table.Columns) { if (key.From.Equals(column)) { // If FK = PK then assume a one-to-one relationship. if (table.PrimaryKey.Columns.Count == 1 && table.PrimaryKey.Columns[0] == column) { key.Relationship = RelationshipType.OneToOne; } } if (key.From.Equals(column)) { table.ForeignKeys.Add(key); } if (key.To.Equals(column)) { table.Dependencies.Add(key); } } } } }
public Table(DataRow itemToLoad) : base(itemToLoad) { Columns = new ColumnCollection(); ForeignKeys = new ForeignKeyCollection(); Indexes = new IndexCollection(); //Triggers = new TriggerCollection<O>(); }
public void CanNotAddMultipleForeignKeysToTheSameTable() { var collection = new ForeignKeyCollection(); var key1 = new ForeignKey(new ObjectName("", "DetailTable1"), new[] { "Field1" }, new ObjectName("", "MasterTable1"), new[] { "Field1" }); var key2 = new ForeignKey(new ObjectName("", "DetailTable1"), new[] { "Field2" }, new ObjectName("", "MasterTable1"), new[] { "Field2" }); collection.Add(key1); Assert.Throws <ArgumentException>(() => collection.Add(key2)); }
public void CanAddSingleKey() { var collection = new ForeignKeyCollection(); var key = new ForeignKey(new ObjectName("", "Table1"), new[] {"Field1"}, new ObjectName("", "Table2"), new[] {"Field2"}); collection.Add(key); Assert.That(collection, Contains.Item(key)); }
public void CanNotAddMultipleForeignKeysToTheSameTable() { var collection = new ForeignKeyCollection(); var key1 = new ForeignKey(new ObjectName("", "DetailTable1"), new[] { "Field1" }, new ObjectName("", "MasterTable1"),new[] { "Field1" }); var key2 = new ForeignKey(new ObjectName("", "DetailTable1"), new[] { "Field2" }, new ObjectName("", "MasterTable1"),new[] { "Field2" }); collection.Add(key1); Assert.Throws<ArgumentException>(() => collection.Add(key2)); }
public void CanAddSingleKey() { var collection = new ForeignKeyCollection(); var key = new ForeignKey(new ObjectName("", "Table1"), new[] { "Field1" }, new ObjectName("", "Table2"), new[] { "Field2" }); collection.Add(key); Assert.That(collection, Contains.Item(key)); }
public static IEnumerable <ForeignKey> ForeignKeys(this ForeignKeyCollection fkc) { Collection <ForeignKey> colFkey = new Collection <ForeignKey>(); foreach (ForeignKey fk in fkc) { colFkey.Add(fk); } return(colFkey); }
public Table() { // this._Database = null; this.Name = ""; this.Description = ""; this.Properties = new TablePropertyCollection(); this.Columns = new ColumnCollection(); // this._PrimaryKey = null; this.OutgoingForeignKeys = new ForeignKeyCollection(); this.IncomingForeignKeys = new ForeignKeyCollection(); }
public void CanAddMultipleForeignKeys() { var collection = new ForeignKeyCollection(); var key1 = new ForeignKey(new ObjectName("", "DetailTable1"), new[] { "Field1" }, new ObjectName("", "MasterTable1"), new[] { "Field1" }); var key2 = new ForeignKey(new ObjectName("", "DetailTable1"), new[] { "Field2" }, new ObjectName("", "MasterTable2"), new[] { "Field2" }); collection.Add(key1); collection.Add(key2); Assert.That(collection, Contains.Item(key1)); Assert.That(collection, Contains.Item(key2)); }
public void GetRelatedTable(ForeignKeyCollection fkc, CustomColumn clmn) { foreach (ForeignKey relation in fkc) { foreach (ForeignKeyColumn c in relation.Columns) { if (c.Name == clmn.dbObject.Name) { clmn.ReferanceTable = GetTable(relation.ReferencedTable); } } } }
public void CanAddMultipleForeignKeys() { var collection = new ForeignKeyCollection(); var key1 = new ForeignKey(new ObjectName("", "DetailTable1"), new[] { "Field1" }, new ObjectName("", "MasterTable1"),new[] { "Field1" }); var key2 = new ForeignKey(new ObjectName("", "DetailTable1"), new[] { "Field2" }, new ObjectName("", "MasterTable2"),new[] { "Field2" }); collection.Add(key1); collection.Add(key2); Assert.That(collection,Contains.Item(key1)); Assert.That(collection,Contains.Item(key2)); }
/// <summary> /// Process foreign keys. /// </summary> /// <param name="foreignKeys">Object to process.</param> /// <param name="tableName">Name of table foreign keys.</param> public void Process(ForeignKeyCollection foreignKeys, string tableName) { new { foreignKeys }.AsArg().Must().NotBeNull(); new { tableName }.AsArg().Must().NotBeNullNorWhiteSpace(); if (foreignKeys.Count > 0) { this.documentGenerator.AddEntry("Foreign Keys for table - " + tableName, 12, true); } this.documentGenerator.Indent(); foreach (ForeignKey foreignKey in foreignKeys) { this.databaseDocumenter.Document(foreignKey); ScriptAndWriteToFile(foreignKey, this.tablePath, FileExtensionForeignKey); } this.documentGenerator.Undent(); }
//Copie les métata data d'une BDD sur un serveur vers un autre public static void copyMetaData(String sourceConnection = "Connection1", String destinationConnection = "Connection2") { //Obtenion de la DataBase de la premiere Connection--------------------------------------------------------------- ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings[sourceConnection]; SqlConnection Connection = new SqlConnection(connectionString.ToString()); //SMO Server object setup with SQLConnection. Server server = new Server(new ServerConnection(Connection)); string[] opt = connectionString.ToString().Split(';'); string dbName = ""; for (int i = 0; i < opt.Length; i++) { if (opt[i].Contains("Initial Catalog")) { dbName += opt[i].Split('=')[1]; } } //Set Database to the database Database db = server.Databases[dbName]; //---------------------------------------------------------------------------------------------------------------- //Obtenion de la DataBase de la seconde Connection--------------------------------------------------------------- ConnectionStringSettings connectionString2 = ConfigurationManager.ConnectionStrings[destinationConnection]; SqlConnection Connection2 = new SqlConnection(connectionString2.ToString()); //---------------------------------------------------------------------------------------------------------------- /*Option pour la creation des tables * inclus les cles primaires, les contraintes nonclustered et * if not exist pour ne pas creer une table qui existe deja*/ ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.DriPrimaryKey = true; scriptOptions.IncludeIfNotExists = true; scriptOptions.DriNonClustered = true; /*Option pour les foreign key de chaque table, * préposé de leur schéma*/ ScriptingOptions scrOpt = new ScriptingOptions(); scrOpt.SchemaQualifyForeignKeysReferences = true; scrOpt.DriForeignKeys = true; Console.WriteLine("Obtention metadonnees tables et clefs"); StringCollection schemaCollection = new StringCollection(); StringCollection tableCol = new StringCollection(); StringCollection foreignKeyCol = new StringCollection(); foreach (Table myTable in db.Tables) { //Si c'est un nouveau schéma on retient son nom if (!schemaCollection.Contains("[" + myTable.Schema + "]")) { schemaCollection.Add("[" + myTable.Schema + "]"); } //On joute le script de la table à tableCol StringCollection tableScripts = myTable.Script(scriptOptions); string[] tmp = new string[tableScripts.Count]; tableScripts.CopyTo(tmp, 0); tableCol.AddRange(tmp); //On ajoute le script des foreign keys à foreignKeyCol ForeignKeyCollection fk = myTable.ForeignKeys; foreach (ForeignKey myFk in fk) { StringCollection stmp = myFk.Script(scrOpt); string[] tmp2 = new string[stmp.Count]; stmp.CopyTo(tmp2, 0); foreignKeyCol.AddRange(tmp2); } } //Eneleve le schéma par défault schemaCollection.Remove("[dbo]"); Console.WriteLine("Obtention des Procédures stockées"); ScriptingOptions scrOpt2 = new ScriptingOptions() { IncludeIfNotExists = true }; StringCollection prostoCol = new StringCollection(); foreach (StoredProcedure sp in db.StoredProcedures) { if (!sp.Schema.Equals("sys") && !sp.IsSystemObject) { StringCollection scsp = sp.Script(); string[] tmp2 = new string[scsp.Count]; scsp.CopyTo(tmp2, 0); prostoCol.AddRange(tmp2); } } Console.WriteLine("Obtention Metadonees schemas"); SchemaCollection sc = db.Schemas; StringCollection schemaCol = new StringCollection(); foreach (Schema schem in sc) { if (schemaCollection.Contains(schem.ToString())) { StringCollection tableScripts = schem.Script(new ScriptingOptions() { IncludeIfNotExists = true }); string[] tmp = new string[tableScripts.Count]; tableScripts.CopyTo(tmp, 0); string[] strtmp = { "AUTHORIZATION" }; schemaCol.Add(tmp[0].Split(strtmp, new StringSplitOptions())[0] + "AUTHORIZATION [dbo]'"); } } Connection.Close(); //Execution sur la nouvelle base des scripts récupérés Connection2.Open(); using (SqlCommand cmd = Connection2.CreateCommand()) { Console.WriteLine("Création Schemas"); foreach (string str in schemaCol) { cmd.CommandText = str; cmd.ExecuteNonQuery(); } Console.WriteLine("Création Tables"); foreach (string str in tableCol) { cmd.CommandText = str; cmd.ExecuteNonQuery(); } Console.WriteLine("Création Clefs etrangeres"); foreach (string str in foreignKeyCol) { cmd.CommandText = str; cmd.ExecuteNonQuery(); } Console.WriteLine("Création Procedures Stockées"); foreach (string str in prostoCol) { try { cmd.CommandText = str; cmd.ExecuteNonQuery(); } catch (Exception e) { Console.WriteLine("Suite à une erreur la procédure suivante n'a pas pu etre copiée"); Console.WriteLine(str); Console.WriteLine(e.Message); } } } Connection2.Close(); }
/// <summary> /// Copie les métata data (schema, tables, PK et FK, ProcStock d'une BDD dans un tableau à 4 entrées: /// <list type="dd">TABLE</list> /// <list type="dd">SCHEMA</list> /// <list type="dd">FK</list> /// <list type="dd">PROCSTOC</list> /// </summary> /// <param name="ListMode">rien ou vide pour ne pas tenir compte de la liste , toutes les tables de la source sont concernées / "exclude" pour donner une liste de tables à exclure / "include" pour donner le nom des tables à inclure</param> /// <param name="ListTableName">la liste des noms des tables à considerer dns un mode exclude ou include </param> public override DataSet GetMetatDataDBScripts(ListeMode mode = ListeMode.Aucune, List <DatabaseTable> ListTableName = null) { // nom de la base de source string dbName = Connection.Database.ToString(); // structure de retour contenant les scripts DataSet ds = new DataSet("SCRIPTS"); DataTable tableDT = new DataTable("TABLE"); tableDT.Columns.Add("sql"); ds.Tables.Add(tableDT); DataTable fkDT = new DataTable("FK"); fkDT.Columns.Add("sql"); ds.Tables.Add(fkDT); DataTable procstocDT = new DataTable("PROCSTOC"); procstocDT.Columns.Add("sql"); ds.Tables.Add(procstocDT); DataTable schemaDT = new DataTable("SCHEMA"); schemaDT.Columns.Add("sql"); ds.Tables.Add(schemaDT); //SMO Server object setup with SQLConnection. Server server = new Server(new ServerConnection((SqlConnection)Connection)); //Set Database to the database Database db = server.Databases[dbName]; //---------------------------------------------------------------------------------------------------------------- /*Option pour la creation des tables * inclus les cles primaires, les contraintes nonclustered et * if not exist pour ne pas creer une table qui existe deja*/ ScriptingOptions tablesScriptOptions = new ScriptingOptions(); tablesScriptOptions.DriPrimaryKey = true; tablesScriptOptions.IncludeIfNotExists = true; tablesScriptOptions.DriNonClustered = true; /*Option pour les foreign key de chaque table, * préposé de leur schéma*/ ScriptingOptions fkScriptOptions = new ScriptingOptions(); fkScriptOptions.SchemaQualifyForeignKeysReferences = true; fkScriptOptions.DriForeignKeys = true; InfoLogger.Debug("Obtention metadonnees tables et clefs"); StringCollection schemaCollection = new StringCollection(); foreach (Table myTable in db.Tables) { // si il y a une liste if (mode.Equals(ListeMode.Include)) { if (!ListTableName.Contains(myTable.Name, myTable.Schema)) { continue; } } else if (mode.Equals(ListeMode.Exclude)) { if (ListTableName.Contains(myTable.Name, myTable.Schema)) { continue; } } //Si c'est un nouveau schéma on retient son nom if (!schemaCollection.Contains("[" + myTable.Schema + "]")) { schemaCollection.Add("[" + myTable.Schema + "]"); } //On ajoute le script de la table à tableCol StringCollection tableScripts = myTable.Script(tablesScriptOptions); // maj de la Dataset DataRow dr = tableDT.NewRow(); foreach (string scriptLine in tableScripts) { dr["sql"] += scriptLine + System.Environment.NewLine; } tableDT.Rows.Add(dr); //On ajoute le script des foreign keys à foreignKeyCol ForeignKeyCollection fk = myTable.ForeignKeys; foreach (ForeignKey myFk in fk) { StringCollection stmp = myFk.Script(fkScriptOptions); // maj de la Dataset DataRow fkDr = fkDT.NewRow(); foreach (string scriptLine in stmp) { fkDr["sql"] += scriptLine + System.Environment.NewLine; } fkDT.Rows.Add(fkDr); } } //Enleve le schéma par défault schemaCollection.Remove("[dbo]"); InfoLogger.Debug("Obtention des Procédures stockées"); ScriptingOptions scrOptProcStoc = new ScriptingOptions() { NoCommandTerminator = false, ScriptBatchTerminator = true, IncludeIfNotExists = true }; foreach (StoredProcedure sp in db.StoredProcedures) { if (!sp.Schema.Equals("sys") && !sp.IsSystemObject) { StringCollection scsp = sp.Script(scrOptProcStoc); // maj de la Dataset DataRow pcDr = procstocDT.NewRow(); foreach (string scriptLine in scsp) { pcDr["sql"] += scriptLine + System.Environment.NewLine; } procstocDT.Rows.Add(pcDr); } } InfoLogger.Debug("Obtention Metadonnees schemas"); SchemaCollection sc = db.Schemas; ScriptingOptions scrOpt_Schema = new ScriptingOptions() { IncludeIfNotExists = true }; foreach (Schema schem in sc) { if (schemaCollection.Contains(schem.ToString())) { StringCollection schemaScripts = schem.Script(scrOpt_Schema); // maj de la Dataset DataRow schemaDr = schemaDT.NewRow(); foreach (string scriptLine in schemaScripts) { schemaDr["sql"] += scriptLine + System.Environment.NewLine; } schemaDT.Rows.Add(schemaDr); } } Connection.Close(); return(ds); }