/// <summary>
/// Inicia a execução assíncrona da instrução Transact-SQL ou procedimento armazenado.
/// </summary>
/// <param name="querySql"></param>
/// <returns>true se executado com sucesso caso contrário false</returns>
public virtual void AddTransaction(string querySql)
{
// split script on GO command
var commands = SplitCommandFromGo(querySql);
//executando cada script quebrado no comando GO
foreach (SqlCommand command in commands)
{
try
{
//inicia a transação
var result = command.BeginExecuteNonQuery();
//executa do comando
command.EndExecuteNonQuery(result);
//query executed sucessful
}
catch (SqlException sqlException)
{
//message
string errorMessage = LoggerUtilIts.GetInnerException(sqlException);
//exceção interna
Exception innerException = new Exception(command.CommandText);
//cria a exceção a ser lançada
throw new TransactionAbortedException(errorMessage, innerException);
}
}
}
private void executeDDL()
{
try
{
//eventos a serem disparados
TaskLogStringDelegate taskLogDelegate = new TaskLogStringDelegate(taskLog);
TaskLogExceptionDelegate taskLogExceptionDelegate = new TaskLogExceptionDelegate(addPkgError);
TaskLogUpdateDelegate taskLogUpdateDelegate = new TaskLogUpdateDelegate(addPkgInfo);
using (var connection = createConnection())
{
var sqls = getQueriesSql();
connection.BeginTransaction();
foreach (var sql in sqls)
{
taskLog("Script file: " + sql.FileName);
taskLog("'============================================================================'");
string querySql = StringUtilIts.GetStringFromBytes(sql.DataFile);
try
{
StringBuilder queryLog = new StringBuilder();
queryLog.Append("Preparing Command: ...\n");
queryLog.Append("'============================================================================'\n");
queryLog.Append(querySql);
queryLog.Append("\n");
queryLog.Append("'============================================================================'");
//passa o parametro e dispara o delegate
taskLogDelegate.DynamicInvoke(queryLog.ToString());
//add comando para execução
connection.AddTransaction(querySql);
//delay para garantir a geração do log na tela
Thread.Sleep(100);
}
catch (Exception ex)
{
string scriptError = "Error in file: " + sql.FileName;
//passa o parametro e dispara o delegate
taskLogDelegate.DynamicInvoke(scriptError);
//dispara o delegate para add update info do erro
taskLogExceptionDelegate.DynamicInvoke(ex);
//delay para garantir a geração do log na tela
Thread.Sleep(100);
//para a porra toda
cancelation();
}
}
try
{
StringBuilder queryLog = new StringBuilder();
queryLog.Append("/****************************************************************************/\n");
queryLog.Append("Executing Query ...\n");
queryLog.Append("'****************************************************************************'\n");
//limpa o log builder
queryLog.Clear();
queryLog.Append("'============================================================================'\n");
queryLog.Append("Query executed sucessfully !\n");
queryLog.Append("'============================================================================'\n");
//delay para garantir a geração do log na tela
Thread.Sleep(100);
//passa o parametro e dispara o delegate
taskLogDelegate.DynamicInvoke(queryLog.ToString());
//efetiva transação
connection.CommitTransaction();
//se consegui chegar aqui ta tudo ok
taskLogUpdateDelegate.DynamicInvoke();
}
catch (Exception ex)
{
StringBuilder logScreen = new StringBuilder("Falha ao efetivar a transação.");
logScreen.Append("Mensagem: " + LoggerUtilIts.GetInnerException(ex));
//defaz todas as alterações
connection.RollbackTransaction();
}
}
}
catch (OperationCanceledException oc)
{
StringBuilder queryLog = new StringBuilder();
queryLog.Append("'****************************************************************************'\n");
queryLog.Append("Failed sql statement.\n");
queryLog.Append("'****************************************************************************'\n");
queryLog.Append(oc.Message);
taskLog(queryLog.ToString());
}
}
/// <summary>
/// Execute um script e executa o commit somnete de todas as instruções forem executadas com sucesso.
/// </summary>
/// <param name="querySql"></param>
/// <param name="taskLogDelegate">Metódo anônimo</param>
/// <returns>true se executado com sucesso caso contrário false</returns>
public bool ExecuteNonQueryTransaction(string querySql, Delegate taskLogDelegate)
{
Dictionary <string, bool> statusScripts = new Dictionary <string, bool>();
var tso = new TransactionOptions()
{
IsolationLevel = System.Transactions.IsolationLevel.Serializable
};
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, tso))
{
try
{
//open connection
OpenConnection();
// split script on GO command
List <SqlCommand> commands = SplitCommandFromGo(querySql);
//cria a transacao
this.Transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted, "myTransaction");
SqlException sqlException = null;
//executando cada script quebrado no comando GO
foreach (SqlCommand command in commands)
{
string commandString = command.CommandText;
try
{
command.Connection = this.connection;
command.Transaction = this.Transaction;
StringBuilder queryLog = new StringBuilder();
queryLog.Append("Preparing Command: ...\n");
queryLog.Append("--============================================================================\n");
queryLog.Append(commandString);
queryLog.Append("\n");
queryLog.Append("--============================================================================");
//delay for log screen
Thread.Sleep(10);
//passa o parametro e dispara o delegate
taskLogDelegate.DynamicInvoke(queryLog.ToString());
//inicia a transação
var result = command.BeginExecuteNonQuery();
//executa do comando
command.EndExecuteNonQuery(result);
//query executed sucessful
if (result.IsCompleted)
{
//salva o comando executado
statusScripts.Add(commandString, result.IsCompleted);
}
}
catch (SqlException sqlex)
{
//registra o log
sqlException = sqlex;
//guarda a ref pro erro
statusScripts.Add(commandString, false);
}
}
//se tudo deu ok
if (statusScripts.Count(s => s.Value == false) == 0)
{
//avisa o banco da transação
this.Transaction.Commit();
//transaction complete
ts.Complete();
//encerra a conexão
CloseConnection();
//log
var queryLog = new StringBuilder();
queryLog.Append("Executing Query ...\n");
//limpa o log builder
queryLog.Clear();
queryLog.Append("============================================================================\n");
queryLog.Append("Query executed sucessfully !\n");
queryLog.Append("============================================================================\n");
//passa o parametro e dispara o delegate
taskLogDelegate.DynamicInvoke(queryLog.ToString());
return(true);
}
else
{
//desafaz as alterações
this.Transaction.Rollback();
//recupera o comando que falhou
string command = statusScripts.FirstOrDefault(s => s.Value == false)
.ToString();
//message
string errorMessage = LoggerUtilIts.GetInnerException(sqlException);
//exceção interna
Exception innerException = new Exception(command);
//cria a exceção a ser lançada
TransactionAbortedException transactionAborted =
new TransactionAbortedException(errorMessage, innerException);
var log = new StringBuilder();
log.Append("****************************************************************************\n");
log.Append("Erro: ");
log.Append(errorMessage);
log.Append("\n****************************************************************************\n");
//passa o parametro e dispara o delegate
taskLogDelegate.DynamicInvoke(log.ToString());
//transaction aborted
throw transactionAborted;
}
}
catch (TransactionAbortedException tae)
{
//transaction aborted
throw tae;
}
catch (Exception ex)
{
StringBuilder logScreen = new StringBuilder("Falha durante a transação.");
logScreen.Append("Mensagem: " + LoggerUtilIts.GetInnerException(ex));
//others errors
throw new Exception(logScreen.ToString(), ex);
}
}
}