private static async Task DeleteAllData(string connectionString)
{
await using (var connection = new FbConnection(connectionString))
{
await connection.OpenAsync();
var commandText = @"
execute block as
declare name type of column rdb$relations.rdb$relation_name;
begin
for select rdb$relation_name from rdb$relations where coalesce(rdb$system_flag, 0) = 0 into name do
begin
execute statement 'delete from ' || name;
end
end";
await using (var transaction = await connection.BeginTransactionAsync())
{
await using (var command = new FbCommand(commandText, connection, transaction))
{
await command.ExecuteNonQueryAsync();
}
await transaction.CommitAsync();
}
}
}
private static async Task InsertTestData(string connectionString)
{
await using (var connection = new FbConnection(connectionString))
{
await connection.OpenAsync();
var commandText = @"
insert into test (int_field, char_field, varchar_field, bigint_field, smallint_field, float_field, double_field, numeric_field, date_field, time_field, timestamp_field, clob_field, blob_field)
values(@int_field, @char_field, @varchar_field, @bigint_field, @smallint_field, @float_field, @double_field, @numeric_field, @date_field, @time_field, @timestamp_field, @clob_field, @blob_field)";
await using (var transaction = await connection.BeginTransactionAsync())
{
await using (var command = new FbCommand(commandText, connection, transaction))
{
command.Parameters.Add("@int_field", FbDbType.Integer);
command.Parameters.Add("@char_field", FbDbType.Char);
command.Parameters.Add("@varchar_field", FbDbType.VarChar);
command.Parameters.Add("@bigint_field", FbDbType.BigInt);
command.Parameters.Add("@smallint_field", FbDbType.SmallInt);
command.Parameters.Add("@float_field", FbDbType.Double);
command.Parameters.Add("@double_field", FbDbType.Double);
command.Parameters.Add("@numeric_field", FbDbType.Numeric);
command.Parameters.Add("@date_field", FbDbType.Date);
command.Parameters.Add("@time_Field", FbDbType.Time);
command.Parameters.Add("@timestamp_field", FbDbType.TimeStamp);
command.Parameters.Add("@clob_field", FbDbType.Text);
command.Parameters.Add("@blob_field", FbDbType.Binary);
await command.PrepareAsync();
for (var i = 0; i < 100; i++)
{
command.Parameters["@int_field"].Value = i;
command.Parameters["@char_field"].Value = "IRow " + i.ToString();
command.Parameters["@varchar_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@bigint_field"].Value = i;
command.Parameters["@smallint_field"].Value = i;
command.Parameters["@float_field"].Value = (float)(i + 10) / 5;
command.Parameters["@double_field"].Value = (double)(i + 10) / 5;
command.Parameters["@numeric_field"].Value = (decimal)(i + 10) / 5;
command.Parameters["@date_field"].Value = DateTime.Now;
command.Parameters["@time_field"].Value = DateTime.Now;
command.Parameters["@timestamp_field"].Value = DateTime.Now;
command.Parameters["@clob_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@blob_field"].Value = Encoding.UTF8.GetBytes("IRow Number " + i.ToString());
await command.ExecuteNonQueryAsync();
}
await transaction.CommitAsync();
}
}
}
}
public async Task BeginTransactionWithWaitTimeoutInvalidValue1Test()
{
await using (var conn = new FbConnection(BuildConnectionString(ServerType, Compression, WireCrypt)))
{
await conn.OpenAsync();
Assert.ThrowsAsync <ArgumentException>(() => conn.BeginTransactionAsync(new FbTransactionOptions()
{
WaitTimeout = TimeSpan.FromDays(9999)
}));
}
}
private async Task BeginTransactionILTestsHelper(IsolationLevel level)
{
await using (var conn = new FbConnection(BuildConnectionString(ServerType, Compression, WireCrypt)))
{
await conn.OpenAsync();
await using (var tx = await conn.BeginTransactionAsync(level))
{
Assert.NotNull(tx);
await tx.RollbackAsync();
}
}
}
public async Task BeginTransactionWithWaitTimeoutTest()
{
await using (var conn = new FbConnection(BuildConnectionString(ServerType, Compression, WireCrypt)))
{
await conn.OpenAsync();
await using (var tx = await conn.BeginTransactionAsync(new FbTransactionOptions()
{
WaitTimeout = TimeSpan.FromSeconds(10)
}))
{
Assert.NotNull(tx);
await tx.RollbackAsync();
}
}
}
/// <summary>
/// Starts the ordered execution of the SQL statements that are in <see cref="SqlStatements"/> collection.
/// </summary>
/// <param name="autoCommit">Specifies if the transaction should be committed after a DDL command execution</param>
public async Task ExecuteAsync(bool autoCommit = true, CancellationToken cancellationToken = default)
{
if ((_statements?.Count ?? 0) == 0)
{
throw new InvalidOperationException("There are no commands for execution.");
}
_shouldClose = false;
foreach (var statement in Statements)
{
if (!(statement.StatementType == SqlStatementType.Connect ||
statement.StatementType == SqlStatementType.CreateDatabase ||
statement.StatementType == SqlStatementType.Disconnect ||
statement.StatementType == SqlStatementType.DropDatabase ||
statement.StatementType == SqlStatementType.SetAutoDDL ||
statement.StatementType == SqlStatementType.SetDatabase ||
statement.StatementType == SqlStatementType.SetNames ||
statement.StatementType == SqlStatementType.SetSQLDialect))
{
await ProvideCommandAsync(cancellationToken).ConfigureAwait(false);
_sqlCommand.CommandText = statement.Text;
if (_sqlTransaction == null && !(statement.StatementType == SqlStatementType.Commit || statement.StatementType == SqlStatementType.Rollback))
{
_sqlTransaction = await _sqlConnection.BeginTransactionAsync(FbTransaction.DefaultIsolationLevel, null, cancellationToken).ConfigureAwait(false);
}
_sqlCommand.Transaction = _sqlTransaction;
}
try
{
switch (statement.StatementType)
{
case SqlStatementType.AlterCharacterSet:
case SqlStatementType.AlterDatabase:
case SqlStatementType.AlterDomain:
case SqlStatementType.AlterException:
case SqlStatementType.AlterFunction:
case SqlStatementType.AlterIndex:
case SqlStatementType.AlterPackage:
case SqlStatementType.AlterProcedure:
case SqlStatementType.AlterRole:
case SqlStatementType.AlterSequence:
case SqlStatementType.AlterTable:
case SqlStatementType.AlterTrigger:
case SqlStatementType.AlterView:
case SqlStatementType.CommentOn:
case SqlStatementType.CreateCollation:
case SqlStatementType.CreateDomain:
case SqlStatementType.CreateException:
case SqlStatementType.CreateFunction:
case SqlStatementType.CreateGenerator:
case SqlStatementType.CreateIndex:
case SqlStatementType.CreatePackage:
case SqlStatementType.CreatePackageBody:
case SqlStatementType.CreateProcedure:
case SqlStatementType.CreateRole:
case SqlStatementType.CreateSequence:
case SqlStatementType.CreateShadow:
case SqlStatementType.CreateTable:
case SqlStatementType.CreateTrigger:
case SqlStatementType.CreateView:
case SqlStatementType.DeclareCursor:
case SqlStatementType.DeclareExternalFunction:
case SqlStatementType.DeclareFilter:
case SqlStatementType.DeclareStatement:
case SqlStatementType.DeclareTable:
case SqlStatementType.Delete:
case SqlStatementType.DropCollation:
case SqlStatementType.DropDomain:
case SqlStatementType.DropException:
case SqlStatementType.DropExternalFunction:
case SqlStatementType.DropFunction:
case SqlStatementType.DropFilter:
case SqlStatementType.DropGenerator:
case SqlStatementType.DropIndex:
case SqlStatementType.DropPackage:
case SqlStatementType.DropPackageBody:
case SqlStatementType.DropProcedure:
case SqlStatementType.DropSequence:
case SqlStatementType.DropRole:
case SqlStatementType.DropShadow:
case SqlStatementType.DropTable:
case SqlStatementType.DropTrigger:
case SqlStatementType.DropView:
case SqlStatementType.EventInit:
case SqlStatementType.EventWait:
case SqlStatementType.Execute:
case SqlStatementType.ExecuteImmediate:
case SqlStatementType.ExecuteProcedure:
case SqlStatementType.Grant:
case SqlStatementType.Insert:
case SqlStatementType.InsertCursor:
case SqlStatementType.Merge:
case SqlStatementType.Open:
case SqlStatementType.Prepare:
case SqlStatementType.Revoke:
case SqlStatementType.RecreateFunction:
case SqlStatementType.RecreatePackage:
case SqlStatementType.RecreatePackageBody:
case SqlStatementType.RecreateProcedure:
case SqlStatementType.RecreateTable:
case SqlStatementType.RecreateTrigger:
case SqlStatementType.RecreateView:
case SqlStatementType.SetGenerator:
case SqlStatementType.Update:
case SqlStatementType.Whenever:
OnCommandExecuting(_sqlCommand, statement.StatementType);
var rowsAffected = await ExecuteCommandAsync(autoCommit, cancellationToken).ConfigureAwait(false);
_requiresNewConnection = false;
OnCommandExecuted(null, statement.Text, statement.StatementType, rowsAffected);
break;
case SqlStatementType.ExecuteBlock:
case SqlStatementType.Select:
(await ProvideCommandAsync(cancellationToken).ConfigureAwait(false)).CommandText = statement.Text;
OnCommandExecuting(_sqlCommand, statement.StatementType);
var dataReader = await _sqlCommand.ExecuteReaderAsync(CommandBehavior.Default, cancellationToken).ConfigureAwait(false);
try
{
_requiresNewConnection = false;
OnCommandExecuted(dataReader, statement.Text, statement.StatementType, -1);
}
finally
{
#if NET48 || NETSTANDARD2_0
dataReader.Dispose();
#else
await dataReader.DisposeAsync().ConfigureAwait(false);
#endif
}
break;
case SqlStatementType.Commit:
OnCommandExecuting(null, statement.StatementType);
await CommitTransactionAsync(cancellationToken).ConfigureAwait(false);
OnCommandExecuted(null, statement.Text, statement.StatementType, -1);
break;
case SqlStatementType.Rollback:
OnCommandExecuting(null, statement.StatementType);
await RollbackTransactionAsync(cancellationToken).ConfigureAwait(false);
OnCommandExecuted(null, statement.Text, statement.StatementType, -1);
break;
case SqlStatementType.CreateDatabase:
OnCommandExecuting(null, statement.StatementType);
await CreateDatabaseAsync(statement.CleanText, cancellationToken).ConfigureAwait(false);
_requiresNewConnection = false;
OnCommandExecuted(null, statement.Text, statement.StatementType, -1);
break;
case SqlStatementType.DropDatabase:
OnCommandExecuting(null, statement.StatementType);
await FbConnection.DropDatabaseAsync(_connectionString.ToString(), cancellationToken).ConfigureAwait(false);
_requiresNewConnection = true;
OnCommandExecuted(null, statement.Text, statement.StatementType, -1);
break;
case SqlStatementType.Connect:
OnCommandExecuting(null, statement.StatementType);
await ConnectToDatabaseAsync(statement.CleanText, cancellationToken).ConfigureAwait(false);
_requiresNewConnection = false;
OnCommandExecuted(null, statement.Text, statement.StatementType, -1);
break;
case SqlStatementType.Disconnect:
OnCommandExecuting(null, statement.StatementType);
await _sqlConnection.CloseAsync().ConfigureAwait(false);
FbConnection.ClearPool(_sqlConnection);
_requiresNewConnection = false;
OnCommandExecuted(null, statement.Text, statement.StatementType, -1);
break;
case SqlStatementType.SetAutoDDL:
OnCommandExecuting(null, statement.StatementType);
SetAutoDdl(statement.CleanText, ref autoCommit);
_requiresNewConnection = false;
OnCommandExecuted(null, statement.Text, statement.StatementType, -1);
break;
case SqlStatementType.SetNames:
OnCommandExecuting(null, statement.StatementType);
SetNames(statement.CleanText);
_requiresNewConnection = true;
OnCommandExecuted(null, statement.Text, statement.StatementType, -1);
break;
case SqlStatementType.SetSQLDialect:
OnCommandExecuting(null, statement.StatementType);
SetSqlDialect(statement.CleanText);
_requiresNewConnection = true;
OnCommandExecuted(null, statement.Text, statement.StatementType, -1);
break;
case SqlStatementType.Fetch:
case SqlStatementType.Describe:
break;
case SqlStatementType.SetDatabase:
case SqlStatementType.SetStatistics:
case SqlStatementType.SetTransaction:
case SqlStatementType.ShowSQLDialect:
throw new NotImplementedException();
}
}
catch (Exception ex)
{
await DisposeCommandAsync(cancellationToken).ConfigureAwait(false);
await RollbackTransactionAsync(cancellationToken).ConfigureAwait(false);
await CloseConnectionAsync(cancellationToken).ConfigureAwait(false);
var message = string.Format("An exception was thrown when executing command: {1}.{0}Batch execution aborted.{0}The returned message was: {2}.",
Environment.NewLine,
statement.Text,
ex.Message);
throw FbException.Create(message, ex);
}
}
await DisposeCommandAsync(cancellationToken).ConfigureAwait(false);
await CommitTransactionAsync(cancellationToken).ConfigureAwait(false);
await CloseConnectionAsync(cancellationToken).ConfigureAwait(false);
}
