//Uso Particular -> Chamada recursiva
private static void ExecDDL(string resource)
{
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required,
new TransactionOptions()
{
IsolationLevel = IsolationLevel.ReadUncommitted
}))
{
using (var cnn = new AdminContext().ConnectionSql)
{
cnn.OpenConnection();
var connection = cnn.Connection;
try
{
Dictionary <string, bool> statusScripts = new Dictionary <string, bool>();
List <SqlCommand> commands = new List <SqlCommand>();
//cria a transacao
cnn.Transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted, "myTransaction");
//de tudo .sql q esta la dentro
var sqlFiles = new FileManagerIts().ToFilesRecursive(resource);
var sqls = sqlFiles.Where(file => file.ToString().EndsWith(".sql"));
foreach (var sql in sqlFiles)
{
var sqlFileName = Path.GetFileName(sql);
Console.WriteLine("Executando script: " + sqlFileName);
var sqlQuery = FileManagerIts.GetDataStringFile(sql);
// split script on GO command
IEnumerable <string> commandStrings = Regex.Split(sqlQuery, @"^\s*GO\s*$",
RegexOptions.Multiline | RegexOptions.IgnoreCase);
//executando cada script separado por GO
foreach (string commandString in commandStrings)
{
try
{
if (commandString.Trim() != string.Empty)
{
var command = new SqlCommand(commandString, null);
command.Transaction = cnn.Transaction;
var result = cnn.ExecuteNonQuery(command);
commands.Add(command);
if (result.IsCompleted) //se executou o arquivo DDL..
{
//taskLogger("Comando " + commandString + " executado com sucesso.");
Console.WriteLine("Comando " + sqlFileName + " executado com sucesso.");
Console.WriteLine("============================================================================");
statusScripts.Add(commandString, true);
}
}
}
catch (SqlException sqlE)
{
Console.WriteLine("Error: " + sqlE.Number + "Comando: \n" + commandString + " \nLine: " + sqlE.LineNumber + " Message:" + sqlE.Message);
statusScripts.Add(commandString, false);
}
}//fim
}
//se a lista de scripts executados tiver mais de um item falso
if (statusScripts.Where(s => s.Value == false).Count() == 0)
{
cnn.Transaction.Commit();
ts.Complete();
}
else
{
cnn.Transaction.Rollback();
}
}
catch (TransactionAbortedException traCanc)
{
Console.WriteLine("Ocorreram erros no processo. Todas as alterações serao desfeitas.");
Console.WriteLine("Erro: " + traCanc.Message);
}
catch (Exception ex)
{
Console.WriteLine("Ocorreram erros no processo. Todas as alterações serao desfeitas.");
Console.WriteLine("Erro: " + ex.Message);
}
}
}
}
