public SqlExecutionResult <object> SetDataBaseVersion(Version version)
{
this.TraceDebug("Mise à jour de la version de la base de données");
var sqlConnection = CreateAdminSqlConnection();
var taskCompletionSource = new TaskCompletionSource <object>();
Task.Factory.StartNew(() =>
{
try
{
sqlConnection.Open();
ExecuteSetDataBaseVersion(sqlConnection, version);
taskCompletionSource.SetResult(null);
}
catch (Exception e)
{
this.TraceError(e, "Une erreur s'est produite lors de la mise à jour de la verion de la base de données");
taskCompletionSource.SetException(e);
}
finally
{
sqlConnection.Close();
}
});
return(SqlExecutionResult.New(sqlConnection, taskCompletionSource.Task));
}
public SqlExecutionResult <Version> GetVersion()
{
this.TraceDebug("Récupération de la version de la base de données...");
var sqlConnection = CreateAdminSqlConnection();
var task = new Task <Version>(() => ExecuteGetVersion(sqlConnection));
task.Start();
return(SqlExecutionResult.New(sqlConnection, task));
}
public void Constructor()
{
//---------------Set up test pack-------------------
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
var executionResult = new SqlExecutionResult(SqlExecutionActionResult.Success);
//---------------Test Result -----------------------
executionResult.Should().NotBeNull();
}
private async Task DoRestore(string source, int version = 3)
{
SqlExecutionResult <string> sqlResult = ServiceBus.Get <IDataBaseService>().Restore(source, version, true);
sqlResult.SqlMessageSent += SqlResultSqlMessageSent;
try
{
await sqlResult.Start();
sqlResult.SqlMessageSent -= SqlResultSqlMessageSent;
this.TraceInfo("Le processus de restauration de la base de données s'est déroulé avec succés");
SqlMessages.Add(new SqlOutput("restore process of the backup file has succeeded.", SqlOutputKind.Success));
try
{
if (File.Exists(source))
{
File.Delete(source);
}
}
catch (Exception ex)
{
SqlMessages.Add(new SqlOutput("An error occured while trying to remove the imported backup file.", SqlOutputKind.Error));
this.TraceError("Suppression du fichier temporaire importé pour le processus de restauration", ex);
}
RestartApplication();
}
catch (Exception e)
{
sqlResult.SqlMessageSent -= SqlResultSqlMessageSent;
this.TraceError(e, "Une erreur s'est produite durant le processus de restauration de base de donnée");
SqlMessages.Add(new SqlOutput(e.InnerException ?? e));
SqlMessages.Add(new SqlOutput("Restoration failed and rolled back.", SqlOutputKind.Error));
try
{
if (File.Exists(source))
{
File.Delete(source);
}
}
catch (Exception ex)
{
SqlMessages.Add(new SqlOutput("An error occured while trying to remove the imported backup file.", SqlOutputKind.Error));
this.TraceError("Suppression du fichier temporaire importé pour le processus de restauration", ex);
}
RestartApplicationWithError();
}
}
private async Task <string> DoBackup()
{
SqlExecutionResult <string> sqlResult = ServiceBus.Get <IDataBaseService>().Backup(true);
sqlResult.SqlMessageSent += SqlResultSqlMessageSent;
try
{
string source = await sqlResult.Start();
sqlResult.SqlMessageSent -= SqlResultSqlMessageSent;
return(source);
}
catch (Exception e)
{
this.TraceError(e, "Une erreur s'est produite durant le processus de backup de base de donnée");
SqlMessages.Add(new SqlOutput(e.InnerException ?? e));
SqlMessages.Add(new SqlOutput("Backup failed and rolled back.", SqlOutputKind.Error));
return(null);
}
}
public SqlExecutionResult <string> Backup(bool preventActionToBegin = false)
{
this.TraceDebug("Création d'un backup de base de données...");
CreateSqlDirForDebugPurpose();
var sqlConnection = CreateAdminSqlConnection();
var destinationPath = Path.Combine(GeBackupDir(), string.Format("DbBackup_{0}.bak3", DateTime.Now.Ticks));
var task = new Task <string>(() =>
{
ExecuteBackup(sqlConnection, destinationPath);
return(destinationPath);
});
if (!preventActionToBegin)
{
task.Start();
}
return(SqlExecutionResult.New(sqlConnection, task));
}
public override void Assert(DbConnection validationConnection, SqlExecutionResult[] results)
{
base.Assert(validationConnection, results);
var result = results[0];
var resultSetList = new List<int>();
try
{
if (ResultSets.ToUpper() == "ALL")
{
resultSetList.AddRange(Enumerable.Range(1, result.DataSet.Tables.Count));
}
else if (ResultSets.Contains(","))
{
resultSetList.AddRange(ResultSets.Split(',').Select(rs => Convert.ToInt16(rs)).Select(dummy => (int)dummy));
}
else if (ResultSets.Contains(":"))
{
var temp = ResultSets.Split(':');
resultSetList.AddRange(Enumerable.Range(Convert.ToInt32(temp[0]), Convert.ToInt32(temp[1])));
}
else
{
throw new ArgumentException("Invalid specification for result sets");
}
}
catch
{
throw new ArgumentException(String.Format("{0} is an invalid result set specification.", ResultSets));
}
//validate ResultSetList does not include result sets that do not exist
foreach (var rs in resultSetList.Where(rs => rs > result.DataSet.Tables.Count))
throw new ArgumentException(String.Format("Result Set {0} does not exist", rs));
var currentResultSetId = resultSetList[0];
var currentColumnSet = result.DataSet.Tables[currentResultSetId - 1].Columns;
resultSetList.RemoveAt(0);
foreach (var rs in resultSetList)
{
var comparisonTable = result.DataSet.Tables[rs - 1];
//verify that the same number of columns is the same for as recorded
if (comparisonTable.Columns.Count != currentColumnSet.Count)
{
throw new Exception
(
String.Format
(
"Result Set {0}: {1} columns in does not match the {2} columns in Result Set {3}",
rs,
comparisonTable.Columns.Count,
currentColumnSet.Count,
currentResultSetId
)
);
}
//loop through columns and verify the base data type of the columns
for (var c = 0; c < currentColumnSet.Count; c++)
{
if (String.Compare(currentColumnSet[c].ColumnName, comparisonTable.Columns[c].ColumnName, !IsCaseSensitive) != 0)
{
throw new Exception
(
String.Format
(
"Result Set {0}: Column {1} ({2}) does not match name for column in Result Set {3} ({4})",
rs,
c + 1,
comparisonTable.Columns[c].ColumnName,
currentResultSetId,
currentColumnSet[c].ColumnName
)
);
}
//compare datatypes
if (IsLooseComparison)
{
var currentType = GetLooseType(currentColumnSet[c].DataType.Name);
var comparisonType = GetLooseType(comparisonTable.Columns[c].DataType.Name);
if (currentType != comparisonType)
{
throw new Exception
(
String.Format
(
"Result Set {0} Column {1} ({2}) does not match type for column in Result Set {3} ({4})",
rs,
c + 1,
comparisonTable.Columns[c].DataType,
currentResultSetId,
currentColumnSet[c].DataType
)
);
}
}
else
{
if (String.Compare(currentColumnSet[c].DataType.ToString(), comparisonTable.Columns[c].DataType.ToString(), !IsCaseSensitive) != 0)
{
throw new Exception
(
String.Format
(
"Result Set {0} Column {1} ({2}) does not match type for column in Result Set {3} ({4})",
rs,
c + 1,
comparisonTable.Columns[c].DataType,
currentResultSetId,
currentColumnSet[c].DataType
)
);
}
}
}
}
}
public static SqlExecutionResult[] ExecuteTest(ConnectionContext ctx, string testSql, params DbParameter[] tsqlParameters)
{
if (ctx == null)
{
throw new AssertFailedException("The ConnectionContext cannot be null");
}
if (ctx.Connection == null)
{
throw new AssertFailedException("The connection cannot be null");
}
if (ctx.Provider == null)
{
throw new AssertFailedException("The provider factory cannot be null");
}
if (testSql == null)
{
throw new AssertFailedException("The T-SQL string cannot be null");
}
AssertConnectionStateValid(ConnectionState.Open, ctx.Connection.State);
DbCommand command = ctx.Connection.CreateCommand();
SqlExecutionResult result = new SqlExecutionResult();
int num = 0;
int num2 = 0;
int rowsAffectedCount = 0;
List <int> rowsAffected = new List <int>();
SqlCommand command2 = command as SqlCommand;
SqlConnection connection = ctx.Connection as SqlConnection;
SqlInfoMessageEventHandler handler = null;
StatementCompletedEventHandler handler2 = null;
if (connection != null)
{
handler = delegate(object sender, SqlInfoMessageEventArgs e) {
ProcessErrors(e.Errors);
};
connection.InfoMessage += handler;
}
if (command2 != null)
{
handler2 = delegate(object sender, StatementCompletedEventArgs e) {
rowsAffectedCount += e.RecordCount;
rowsAffected.Add(e.RecordCount);
};
command2.StatementCompleted += handler2;
}
try
{
DataSet dataSet = new DataSet
{
Locale = CultureInfo.CurrentCulture
};
command.CommandText = testSql;
command.CommandType = CommandType.Text;
command.Transaction = ctx.Transaction;
command.CommandTimeout = ctx.CommandTimeout;
if (tsqlParameters != null)
{
int length = tsqlParameters.Length;
for (int i = 0; i < length; i++)
{
DbParameter parameter = tsqlParameters[i];
command.Parameters.Add(parameter);
}
}
DbDataAdapter adapter1 = ctx.Provider.CreateDataAdapter();
adapter1.SelectCommand = command;
DateTime now = DateTime.Now;
adapter1.Fill(dataSet);
DateTime time2 = DateTime.Now;
result.DataSet = dataSet;
result.ExecutionTime = time2.Subtract(now);
result.RowsAffected = rowsAffected.ToArray();
num++;
num2 += dataSet.Tables.Count;
}
catch (SqlException exception1)
{
ProcessErrors(exception1.Errors);
throw;
}
finally
{
if (connection != null)
{
connection.InfoMessage -= handler;
}
if (command2 != null)
{
command2.StatementCompleted -= handler2;
}
}
PrintMessage("{0} batches, {1} ResultSets, {2} rows affected", num, num2, rowsAffectedCount);
return(new[] { result });
}
public override void Assert(DbConnection validationConnection, SqlExecutionResult[] results)
{
base.Assert(validationConnection, results);
var result = results[0];
var resultSetList = new List<int>();
try
{
if (ResultSets.ToUpper() == "ALL")
{
resultSetList.AddRange(Enumerable.Range(1, result.DataSet.Tables.Count));
}
else if (ResultSets.Contains(","))
{
resultSetList.AddRange(ResultSets.Split(',').Select(rs => Convert.ToInt16(rs)).Select(dummy => (int) dummy));
}
else if (ResultSets.Contains(":"))
{
var temp = ResultSets.Split(':');
resultSetList.AddRange(Enumerable.Range(Convert.ToInt32(temp[0]), Convert.ToInt32(temp[1])));
}
else
{
throw new ArgumentException("Invalid specification for result sets");
}
}
catch
{
throw new ArgumentException(String.Format("{0} is an invalid result set specification.", ResultSets));
}
foreach (var rs in resultSetList.Where(rs => rs > result.DataSet.Tables.Count))
throw new ArgumentException(String.Format("Result Set {0} does not exist", rs));
var currentResultSetId = resultSetList[0];
var table = result.DataSet.Tables[currentResultSetId - 1];
var numberOfRows = table.Rows.Count;
resultSetList.RemoveAt(0);
//loop through batches verifying that the column count for each batch is the same
foreach (var rs in resultSetList)
{
//verify resultset exists
if (result.DataSet.Tables.Count < rs)
{
throw new Exception(String.Format("Result Set {0} does not exist", rs));
}
//actual condition verification
//verify resultset row count matches expected
table = result.DataSet.Tables[rs - 1];
if (table.Rows.Count != numberOfRows)
throw new Exception
(
String.Format
(
"Result Set {0}: {1} rows does not match the {2} rows in Result Set {3}",
rs,
table.Rows.Count,
numberOfRows,
currentResultSetId
)
);
}
}
public SqlExecutionResult <string> Restore(string sourcePath, int version = 3, bool preventActionToBegin = false)
{
this.TraceDebug("Restauration d'un backup de base de données...");
CreateSqlDirForDebugPurpose();
SqlConnection sqlConn = CreateAdminSqlConnection(true);
SqlCommand sqlCmd;
var task = new Task <string>(() =>
{
try
{
// On retrouve l'emplacement de la nouvelle base
SqlDataReader reader;
sqlCmd = new SqlCommand($@"SELECT physical_name FROM sys.master_files WHERE physical_name LIKE '%\{Const.DataBaseName_v3}.mdf'", sqlConn);
sqlConn.Open();
reader = sqlCmd.ExecuteReader();
if (!reader.Read())
{
throw new Exception("Impossible de retrouver l'emplacement de la nouvelle base de donnée.");
}
string newDbPath = Directory.GetParent((string)reader[0]).FullName;
sqlConn.Close();
// On récupère la version de la nouvelle base
sqlCmd = new SqlCommand($"USE [{Const.DataBaseName_v3}]", sqlConn);
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
sqlCmd = new SqlCommand("GetDatabaseVersion", sqlConn)
{
CommandType = System.Data.CommandType.StoredProcedure
};
reader = sqlCmd.ExecuteReader();
if (!reader.Read())
{
throw new Exception("Impossible de retrouver la version de la nouvelle base de donnée.");
}
Version newDbVersion = Version.Parse((string)reader[0]);
sqlConn.Close();
//On coupe toutes les connexions à la base en la passant en single user
sqlCmd = new SqlCommand($"ALTER DATABASE [{Const.DataBaseName_v3}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE", sqlConn);
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
sqlConn.Close();
// On supprime la nouvelle base
var deleteText = $@"IF EXISTS(SELECT * FROM sysdatabases WHERE name='{Const.DataBaseName_v3}') DROP DATABASE [{Const.DataBaseName_v3}]";
sqlCmd = new SqlCommand(deleteText, sqlConn);
this.TraceDebug("Suppression de la base");
this.TraceDebug(deleteText);
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
sqlConn.Close();
// On restaure le backup dans la nouvelle instance
string restoreCmdText;
// On récupère les noms logiques du backup
restoreCmdText = $@"RESTORE FILELISTONLY FROM DISK=N'{sourcePath}'";
sqlCmd = new SqlCommand(restoreCmdText, sqlConn);
this.TraceDebug("Restauration de la base (récupération des noms logiques)");
this.TraceDebug(restoreCmdText);
sqlConn.Open();
var dataReader = sqlCmd.ExecuteReader();
string LogicalNameDb = "";
string LogicalNameLog = "";
while (dataReader.Read())
{
string logicalName = (string)dataReader["LogicalName"];
string type = (string)dataReader["Type"];
if (type == "D")
{
LogicalNameDb = logicalName;
}
else if (type == "L")
{
LogicalNameLog = logicalName;
}
}
sqlConn.Close();
this.TraceDebug($"Db : {LogicalNameDb}, Log : {LogicalNameLog}");
restoreCmdText = $@"RESTORE DATABASE [{Const.DataBaseName_v3}] FROM DISK=N'{sourcePath}' WITH MOVE '{LogicalNameDb}' TO '{newDbPath}\{Const.DataBaseName_v3}.mdf', MOVE '{LogicalNameLog}' TO '{newDbPath}\{Const.DataBaseName_v3}.LDF'";
sqlCmd = new SqlCommand(restoreCmdText, sqlConn);
this.TraceDebug("Restauration de la base");
this.TraceDebug(restoreCmdText);
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
sqlConn.Close();
// On effectue l'upgrade jusqu'à la version de l'installeur
this.TraceDebug($"Update de la base vers {newDbVersion}");
Scripts.GoTo(sqlConn, newDbVersion);
// On restore les droits utilisateurs sur la base
this.TraceDebug($"Restauration des droits utilisateurs");
Scripts.Execute(sqlConn, "RestoreUserRights");
}
catch (Exception e)
{
this.TraceError("Erreur pendant l'execution du script sql de restauration", e);
throw e;
}
return(sourcePath);
});
if (!preventActionToBegin)
{
task.Start();
}
return(SqlExecutionResult.New(sqlConn, task));
}
public override void Assert(DbConnection validationConnection, SqlExecutionResult[] results)
{
base.Assert(validationConnection, results);
var result = results[0];
var resultSetList = new List<int>();
try
{
if (ResultSets.ToUpper() == "ALL")
{
resultSetList.AddRange(Enumerable.Range(1, result.DataSet.Tables.Count));
}
else if (ResultSets.Contains(","))
{
resultSetList.AddRange(ResultSets.Split(',').Select(rs => Convert.ToInt16(rs)).Select(dummy => (int)dummy));
}
else if (ResultSets.Contains(":"))
{
var temp = ResultSets.Split(':');
resultSetList.AddRange(Enumerable.Range(Convert.ToInt32(temp[0]), Convert.ToInt32(temp[1])));
}
else
{
throw new ArgumentException("Invalid specification for result sets");
}
}
catch
{
throw new ArgumentException(String.Format("{0} is an invalid result set specification.", ResultSets));
}
foreach (var rs in resultSetList.Where(rs => rs > result.DataSet.Tables.Count))
throw new ArgumentException(String.Format("ResultSet {0} does not exist", rs));
var currentResultSetId = resultSetList[0];
var firstTable = result.DataSet.Tables[currentResultSetId - 1];
resultSetList.RemoveAt(0);
//loop through batches verifying that the column count for each batch is the same
foreach (var rs in resultSetList)
{
//verify resultset exists
if (result.DataSet.Tables.Count < rs)
{
throw new Exception(String.Format("ResultSet {0} does not exist", rs));
}
var secondTable = result.DataSet.Tables[rs - 1];
//verify that the same number of columns is the same for as recorded
if (firstTable.Columns.Count != secondTable.Columns.Count)
{
throw new Exception
(
String.Format
(
"Result Set {0}: {1} columns in does not match the {2} columns in Result Set {3}",
currentResultSetId,
firstTable.Columns.Count,
secondTable.Columns.Count,
rs
)
);
}
//verify that the number of rows is the same for as recorded
if (firstTable.Rows.Count != secondTable.Rows.Count)
{
throw new Exception
(
String.Format
(
"Result Set {0}: {1} rows does not match the {2} rows in Result Set {3}",
currentResultSetId,
firstTable.Rows.Count,
secondTable.Rows.Count,
rs
)
);
}
var differenceTable = DataSetComparison.CompareDataTables(firstTable, secondTable, IsCaseSensitive, EnforceOrder);
if (differenceTable.Rows.Count != 0)
{
throw new Exception
(
String.Format
(
"Result Set {0} and Result Set {2} had {1} mismatched records total",
currentResultSetId,
differenceTable.Rows.Count,
rs
)
);
}
}
}
