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