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