public void SplitSqlStatements_BlockComment_MultiLine_Test()
{
// Arrange
var target = new SqlTextExtractor(new[] { ";" }, new Db2DatabaseServer());
const string text = "/*SELECT * FROM TABLE3;\r\nSELECT * FROM TEST;\r\n */SELECT ';',* FROM TABLE1;";
// Act
var actual = target.SplitSqlStatements(text);
// Assert
Assert.AreEqual(1, actual.Count);
Assert.AreEqual("SELECT ';',* FROM TABLE1", actual[0]);
}
public void SplitSqlStatements_MultiTerminator_Test()
{
// Arrange
var target = new SqlTextExtractor(new[] { ";" }, new Db2DatabaseServer());
const string text = " SELECT * FROM TABLE1;; \r\n SELECT * FROM TABLE2";
// Act
var actual = target.SplitSqlStatements(text);
// Assert
Assert.AreEqual(2, actual.Count);
Assert.AreEqual("SELECT * FROM TABLE1", actual[0]);
Assert.AreEqual("SELECT * FROM TABLE2", actual[1]);
}
private async Task RunSqlFilesAsync()
{
// Get files to run
var sqlFiles = _ugFiles.Rows.Select(x => (SqlFileDetails)x.ListObject).ToList();
if (sqlFiles.Count == 0)
{
throw new Exception("No files found to run");
}
// Get options
var useTransaction = ((StateButtonTool)_utm.Tools["Use Database Transaction"]).Checked;
var continueOnError = ((StateButtonTool)_utm.Tools["Continue on Error"]).Checked;
var runEntireFile = ((StateButtonTool)_utm.Tools["Run Each File as Single Statement"]).Checked;
// Disable buttons
foreach (var tool in _utm.Tools.Cast <ToolBase>().Where(x => x.Key != "Stop"))
{
tool.SharedProps.Enabled = false;
}
_utm.Tools["Stop"].SharedProps.Enabled = true;
// Clear output
_teResults.DoEditAction(new SelectWholeDocument());
_teResults.DoEditAction(new Delete());
// Remove highlight ranges
_errorRanges.Clear();
_queryRanges.Clear();
// Reset all files
foreach (var sqlFile in sqlFiles)
{
sqlFile.Status = "Pending";
sqlFile.ElapsedTime = TimeSpan.Zero;
}
// Setup cancellation
_cancellationTokenSource = new CancellationTokenSource();
try
{
using (var connection =
await
_databaseConnection.CreateNewConnectionAsync().WithCancellation(_cancellationTokenSource.Token))
{
connection.OpenIfRequired();
IDbTransaction transaction = null;
if (useTransaction)
{
transaction = connection.BeginTransaction();
}
var stopwatch = new Stopwatch();
foreach (var sqlFile in sqlFiles)
{
// Start the timer
stopwatch.Restart();
// Read all text for file
var sqlFileText = File.ReadAllText(sqlFile.FileName);
// Split file text into individual SQL statements
List <string> sqlStatements = new List <string>();
if (runEntireFile)
{
sqlStatements.Add(sqlFileText);
}
else
{
var sqlSplitter = new SqlTextExtractor(_databaseConnection.DatabaseServer.SqlTerminators, _databaseConnection.DatabaseServer);
sqlStatements.AddRange(sqlSplitter.SplitSqlStatements(sqlFileText));
}
// Run each statement
sqlFile.Status = "Running";
foreach (var sqlStatement in sqlStatements)
{
await Task.Run(() => Thread.Sleep(3000));
try
{
_cancellationTokenSource.Token.ThrowIfCancellationRequested();
var sqlFirstKeyword = SqlHelper.GetFirstKeyword(sqlStatement,
_databaseConnection.DatabaseServer
.BlockCommentRegex,
_databaseConnection.DatabaseServer
.LineCommentRegex);
var sqlType = SqlHelper.GetSqlType(sqlStatement, sqlFirstKeyword);
Task <SqlQueryResult> queryTask;
if (sqlType == SqlType.Query)
{
// Execute SELECT query
_log.Debug("Running query ...");
queryTask = connection.ExecuteQueryKeepAliveAsync(transaction, sqlStatement, 100,
_cancellationTokenSource.Token);
var results = await queryTask.WithCancellation(_cancellationTokenSource.Token);
_log.Debug("Query complete.");
SetResults(results.Result, sqlStatement);
_log.Debug("Results bound.");
}
else
{
// Execute DML or DDL query
_log.Debug("Running non-query - it will use a transaction ...");
queryTask = connection.ExecuteNonQueryTransactionAsync(transaction, sqlStatement,
_cancellationTokenSource
.Token);
var results = await queryTask.WithCancellation(_cancellationTokenSource.Token);
_log.Debug("Non-query complete.");
var resultsTable = new DataTable();
resultsTable.Columns.Add("Results", typeof(string));
if (sqlType == SqlType.Dml && results.RowsAffected >= 0)
{
// Ex: Inserted x rows
resultsTable.Rows.Add(string.Format("{0}{1}D {2} row{3}",
sqlFirstKeyword.Trim().ToUpper(),
sqlFirstKeyword.ToUpper()
.Trim()
.EndsWith("E")
? string.Empty
: "E",
results.RowsAffected.ToString("#,0"),
results.RowsAffected != 1
? "s"
: string.Empty));
}
else if (sqlType == SqlType.Dml)
{
resultsTable.Columns.Add("Results", typeof(int));
resultsTable.Rows.Add(string.Format("{0} affected {1} row{2}",
sqlFirstKeyword.Trim().ToUpper(),
results.RowsAffected.ToString("#,0"),
results.RowsAffected > 1
? "s"
: string.Empty));
}
else
{
resultsTable.Rows.Add(string.Format("{0} successful",
sqlFirstKeyword.Trim().ToUpper()));
}
SetResults(resultsTable, sqlStatement);
_log.Debug("Results bound.");
}
// Set status
sqlFile.Status = "Success";
}
catch (OperationCanceledException)
{
sqlFile.Status = "Cancelled";
throw;
}
catch (Exception ex)
{
_log.Error("Error running query:");
_log.Error(sqlStatement);
_log.Error(ex.Message, ex);
SetResults(GetErrorMessageTable(ex), sqlStatement, true);
sqlFile.Status = "Failed";
// If we're not goingto continue, throw OperationCanceledException s it gets caught in the outside scope and doesn't report errors
if (!continueOnError)
{
throw new OperationCanceledException();
}
}
} // end foreach sql stamenet
// Stop the timer
stopwatch.Stop();
// Set elapsed time
sqlFile.ElapsedTime = stopwatch.Elapsed;
} // end foreach sql file
if (transaction != null)
{
transaction.Commit();
}
}
}
catch (OperationCanceledException)
{
_log.Debug("Operation cancelled.");
}
finally
{
MarkScriptMessages(_queryHighlightGroups, _queryRanges, Color.LightSkyBlue);
MarkScriptMessages(_errorHighlightGroups, _errorRanges, Color.LightCoral);
// Enable buttons
foreach (var tool in _utm.Tools.Cast <ToolBase>().Where(x => x.Key != "Stop"))
{
tool.SharedProps.Enabled = true;
}
_utm.Tools["Stop"].SharedProps.Enabled = false;
_utm.Tools["Run"].SharedProps.Enabled = _databaseConnection.IsConnected;
}
}
