/// <summary>
///Remplit la dataset donnée en paramètre avec le résultat de la requete
/// sur la Connection Destination
/// </summary>
/// <param name="request"></param>
/// <param name="connection_timeout"></param>
/// <returns></returns>
public override DataSet Execute(String request, int connection_timeout = -1)
{
DataSet DS = new DataSet();
InfoLogger.Debug("La requete utilisée est \n" + request);
try
{
if (Connection != null)
{
SqlDataAdapter DA = new SqlDataAdapter(request, (SqlConnection)Connection);
if (connection_timeout > 0)
{
DA.SelectCommand.CommandTimeout = connection_timeout;
}
int nbLignes = DA.Fill(DS);
InfoLogger.Debug("La requete retourne :" + nbLignes + " Lignes");
}
return(DS);
}
catch (Exception e)
{
ExceptionLogger.Fatal("Impossible d executer la requete: " + request, e);
throw e;
}
}
/// <summary>
/// Execute a prepare statement for performance reason, keep and reuse the SqlCommand object
/// </summary>
/// <param name="command">by reference , an objet SqlCommand</param>
/// <param name="parameters">Request parameters</param>
/// <param name="connection_timeout"></param>
public override void Execute(ref IDbCommand command, IDataParameter[] parameters = null, int connection_timeout = -1)
{
InfoLogger.Debug("La requete utilisée est \n" + command.CommandText);
try
{
if (Connection != null)
{
if (command.Connection == null)
{
command.Connection = (SqlConnection)Connection;
}
else
{
command.Parameters.Clear();
}
if (connection_timeout > 0)
{
command.CommandTimeout = connection_timeout;
}
if (parameters != null)
{
foreach (IDataParameter param in parameters)
{
command.Parameters.Add(param);
}
}
int nbLignes = command.ExecuteNonQuery();
InfoLogger.Debug("La requete affecte :" + nbLignes + " Lignes");
}
}
catch (Exception e)
{
ExceptionLogger.Fatal("Impossible d executer la requete: " + command, e);
throw e;
}
}
/// <summary>
///Copie les données de la bdd Source vers la Destination
///note1: Identity column: copy as it from source to destination, using KeepIdentity option
///note2: Null values: copy as it even if column has default value, using KeepNulls option
/// </summary>
/// <param name="mode">Aucune pour ne pas prendre en compte la liste. Include si la liste est une liste de table à inclure et exclude si la copie est sur toute la base sauf les tables de la liste</param>
/// <param name="ListTableName">La liste des noms de tables</param>
/// <param name="requestSelect">la requete utilisée pour recupérer les données sur chaque table, paramètres {0} pour le schema et {1} pour le nom de table</param>
public void bulkcopyData(ListeMode mode, List <DatabaseTable> ListTableName, string RequestSelect)
{
// Open a sourceConnection to the first database
SourceConnection.Open();
//SMO Server object setup with SQLConnection.
Server server = new Server(new ServerConnection((SqlConnection)SourceConnection.Connection));
string dbName = SourceConnection.Connection.Database.ToString();
//Set Database to the database
Database db = server.Databases[dbName];
//connection en destination
DestinationConnection.Open();
SqlBulkCopyOptions Options = SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.KeepIdentity;
SqlTransaction Transaction = ((SqlConnection)DestinationConnection.Connection).BeginTransaction();
SqlBulkCopy bulkCopy = new SqlBulkCopy((SqlConnection)DestinationConnection.Connection, Options, Transaction);
bulkCopy.BulkCopyTimeout = 120;
bulkCopy.BatchSize = 1500;
string requestSelectToExecute = null;
Table table = null;
SqlDataReader reader = null;
try
{
foreach (Table myTable in db.Tables)
{
// si il y a une liste
if (mode.Equals(ListeMode.Include))// if this table t is in our parameter list => include it
{
if (!ListTableName.Contains(myTable.Name, myTable.Schema))
{
continue;
}
}
else if (mode.Equals(ListeMode.Exclude)) // if this table t is in our parameter list => do not include it
{
if (ListTableName.Contains(myTable.Name, myTable.Schema))
{
continue;
}
}
table = myTable;
//InfoLogger.Debug("Desactivation des cles identity sur la table : " + table.Name);
string requestSet = String.Format(SET_IDENTITY_ONOFF, table.Schema, table.Name, "ON");
SqlCommand SqlCmdSetIdentity = new SqlCommand(requestSet, (SqlConnection)DestinationConnection.Connection, Transaction);
int nb = SqlCmdSetIdentity.ExecuteNonQuery();
InfoLogger.Debug("Desactivation des cles identity sur la table : OK " + nb);
InfoLogger.Debug("Obtention des données table : " + table.Name);
// Get data from the source table as a SqlDataReader.
requestSelectToExecute = String.Format(RequestSelect, table.Schema, table.Name);
SqlCommand commandSourceData = new SqlCommand(
requestSelectToExecute, (SqlConnection)
SourceConnection.Connection);
reader = commandSourceData.ExecuteReader();
InfoLogger.Debug(" -- OK");
InfoLogger.Debug("Ecriture des données nouvelle BDD");
// Write from the source to the destination.
bulkCopy.DestinationTableName = table.Schema + "." + table.Name;
bulkCopy.WriteToServer(reader);
InfoLogger.Debug(" -- OK");
reader.Close();
InfoLogger.Debug("reactivation des cles identity sur la table : " + table.Name);
requestSet = String.Format(SET_IDENTITY_ONOFF, table.Schema, table.Name, "OFF");
SqlCmdSetIdentity = new SqlCommand(requestSet, (SqlConnection)DestinationConnection.Connection, Transaction);
nb = SqlCmdSetIdentity.ExecuteNonQuery();
InfoLogger.Debug("reactivation des cles identity sur la table : OK " + nb);
}//Fin foreach
// Transaction OK : commit all stuff: for all tables
Transaction.Commit();
}
catch (Exception ex)
{
if (table != null)
{
ExceptionLogger.Error("BULK IMPOSSIBLE " + requestSelectToExecute + " DANS " + table.Schema + "." + table.Name);
}
ExceptionLogger.Error(ex);
// cancel all modif
Transaction.Rollback();
throw ex;
}
finally
{
if (reader != null)
{
reader.Close();
}
Transaction.Dispose();
}
DestinationConnection.Close();
SourceConnection.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);
}