/// <summary>
/// Execute one or more SQL statements as a transaction. Roll back the transaction if any of them fail.
/// </summary>
public async Task Execute(
IUi ui,
int rowLimit,
string connectionString)
{
List <string> statements = SqlQuery.SplitIntoStatements(Query);
if (statements.Count == 0)
{
return;
}
var sw = new System.Diagnostics.Stopwatch();
var currentStatement = "";
try
{
using (var cn = new OdbcConnection(connectionString))
{
// START CONNECT
ui.SetStatusLabel(ExecuteStatus.Connecting.Description());
sw.Restart();
cn.Open();
Duration_Connect = sw.Elapsed;
// END CONNECT
using (var cmd = new OdbcCommand())
{
cmd.Connection = cn;
// START EXECUTE
ui.SetStatusLabel(ExecuteStatus.Executing.Description());
sw.Restart();
using (var t = cn.BeginTransaction())
{
cmd.Transaction = t; // Assign transaction object for a pending local transaction.
foreach (var statement in statements)
{
if (string.IsNullOrEmpty(statement))
{
continue;
}
currentStatement = statement.Trim();
cmd.CommandText = currentStatement;
var statementResult = new StatementResult();
if (!currentStatement.StartsWith("select", StringComparison.OrdinalIgnoreCase))
{
statementResult.RowsAffected = await cmd.ExecuteNonQueryAsync();
statementResult.Duration_Execute = sw.Elapsed;
// END EXECUTE
}
else
{
using (var reader = await cmd.ExecuteReaderAsync())
{
statementResult.Duration_Execute = sw.Elapsed;
// END EXECUTE
// BEGIN STREAM
ui.SetStatusLabel(ExecuteStatus.Streaming.Description());
sw.Restart();
statementResult.Data.Add(GetColumns(reader));
int readCount = 1;
while (readCount <= rowLimit && reader.Read())
{
statementResult.Data.Add(GetData(reader));
readCount += 1;
}
statementResult.Duration_Stream = sw.Elapsed;
// END STREAM
ui.SetStatusLabel("");
statementResult.RowsAffected = reader.RecordsAffected;
}
}
Results.Add(statementResult);
}
try
{
t.Commit();
}
catch
{
t.Rollback();
throw;
}
}
}
}
}
catch (ArgumentOutOfRangeException ex)
{
if (ex.Message == "Year, Month, and Day parameters describe an un-representable DateTime.")
{
// filemaker allows importing incorrect data into fields, so we need to catch these errors!
Error = ExecuteError.UnrepresentableDateTimeValue.Description();
}
else
{
Error = ex.Message;
}
Error += Environment.NewLine + Environment.NewLine + currentStatement;
}
catch (Exception ex)
{
Error = ex.Message + Environment.NewLine + Environment.NewLine + currentStatement;
}
}