public async Task SavePointTest()
{
await using (var command = new FbCommand())
{
await using (var transaction = await Connection.BeginTransactionAsync("InitialSavePoint"))
{
command.Connection = Connection;
command.Transaction = transaction;
command.CommandText = "insert into TEST (INT_FIELD) values (200)";
await command.ExecuteNonQueryAsync();
await transaction.SaveAsync("FirstSavePoint");
command.CommandText = "insert into TEST (INT_FIELD) values (201)";
await command.ExecuteNonQueryAsync();
await transaction.SaveAsync("SecondSavePoint");
command.CommandText = "insert into TEST (INT_FIELD) values (202)";
await command.ExecuteNonQueryAsync();
await transaction.RollbackAsync("InitialSavePoint");
await transaction.CommitAsync();
}
}
}
public async Task TimeStampArrayTest()
{
var id_value = RandomNumberGenerator.GetInt32(int.MinValue, int.MaxValue);
var insert_values = new DateTime[] { DateTime.Now.AddSeconds(10), DateTime.Now.AddSeconds(20), DateTime.Now.AddSeconds(30), DateTime.Now.AddSeconds(40) };
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var insert = new FbCommand("INSERT INTO TEST (int_field, tsarray_field) values(@int_field, @array_field)", Connection, transaction))
{
insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values;
await insert.ExecuteNonQueryAsync();
}
await transaction.CommitAsync();
}
await using (var select = new FbCommand($"SELECT tsarray_field FROM TEST WHERE int_field = {id_value}", Connection))
{
await using (var reader = await select.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
if (!await reader.IsDBNullAsync(0))
{
var select_values = new DateTime[insert_values.Length];
Array.Copy((Array)reader.GetValue(0), select_values, select_values.Length);
insert_values = insert_values.Select(x => new DateTime(x.Ticks / 1000 * 1000)).ToArray();
CollectionAssert.AreEqual(insert_values, select_values);
}
}
}
}
}
public static async Task <bool> Subject(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId, object category = null)
{
var success = 0;
var sql =
"INSERT INTO \"Faecher\" " +
"(" +
" \"Mandant\", \"Kuerzel\", \"Bezeichnung\", \"Kategorie\" " +
") " +
"VALUES ( " +
" @TenantId, @Code, @Name, @Category " +
")";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.Integer, tenantId);
Helper.SetParamValue(fbCommand, "@Code", FbDbType.VarChar, ecfTableReader.GetValue <string>("Code"));
Helper.SetParamValue(fbCommand, "@Name", FbDbType.VarChar, ecfTableReader.GetValue <string>("Name"));
Helper.SetParamValue(fbCommand, "@Category", FbDbType.SmallInt, category);
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[INSERT ERROR] [Faecher] {e.Message}");
}
return(success > 0);
}
public static async Task <bool> Subject(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId, int id)
{
var success = 0;
var sql =
"UPDATE \"Faecher\" " +
"SET " +
" \"Bezeichnung\" = @Name " +
"WHERE" +
" \"Mandant\" = @TenantId AND " +
" \"ID\" = @Id";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.VarChar, tenantId);
Helper.SetParamValue(fbCommand, "@Id", FbDbType.VarChar, id);
Helper.SetParamValue(fbCommand, "@Name", FbDbType.VarChar, ecfTableReader.GetValue <string>("Name"));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[UPDATE ERROR] [Faecher] {e.Message}");
}
return(success > 0);
}
public static async Task <bool> StudentStatus(FbConnection fbConnection, int tenantId, int id, int status)
{
var success = 0;
var sql =
"UPDATE \"Schueler\" " +
"SET " +
" \"Status\" = @Status " +
"WHERE" +
" \"Mandant\" = @TenantId AND " +
" \"ID\" = @Id";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId);
Helper.SetParamValue(fbCommand, "@Id", FbDbType.BigInt, id);
Helper.SetParamValue(fbCommand, "@Status", FbDbType.SmallInt, status);
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[UPDATE ERROR] [Schueler] [Status] {e.Message}");
}
return(success > 0);
}
public static async Task <bool> StudentCustodian(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId, int id)
{
var success = 0;
var sql =
"UPDATE \"SchuelerSorgebe\" " +
"SET \"Verhaeltnis\" = @RelationshipType " +
"WHERE" +
" \"Mandant\" = @TenantId AND " +
" \"ID\" = @Id";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId);
Helper.SetParamValue(fbCommand, "@Id", FbDbType.BigInt, id);
Helper.SetParamValue(fbCommand, "@RelationshipType", FbDbType.SmallInt, ValueConvert.RelationShip(ecfTableReader.GetValue <string>("RelationshipType")));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[UPDATE ERROR] [StudentCustodian] {e.Message}");
}
return(success > 0);
}
public static async Task <bool> SchoolTerm(FbConnection fbConnection, EcfTableReader ecfTableReader, int id)
{
var success = 0;
var sql =
"UPDATE \"Zeitraeume\" " +
"SET" +
" \"Art\" = @Section, \"Bezeichnung\" = @Code, " +
" \"Ausdruck1\" = @Name, \"Ausdruck2\" = @Name " +
"WHERE" +
" \"ID\" = @Id";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@Id", FbDbType.VarChar, id);
Helper.SetParamValue(fbCommand, "@Section", FbDbType.VarChar, ecfTableReader.GetValue <string>("Section"));
Helper.SetParamValue(fbCommand, "@Code", FbDbType.VarChar, ecfTableReader.GetValue <string>("Code"));
Helper.SetParamValue(fbCommand, "@Name", FbDbType.VarChar, ecfTableReader.GetValue <string>("Name"));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[UPDATE ERROR] [SchoolTerms] {e.Message}");
}
return(success > 0);
}
public async Task VarCharArrayTest()
{
var id_value = GetId();
var insert_values = new string[] { "abc", "abcdef", "abcdefghi", "abcdefghijkl" };
await using (Transaction = await Connection.BeginTransactionAsync())
{
await using (var insert = new FbCommand("INSERT INTO TEST (int_field, varray_field) values(@int_field, @array_field)", Connection, Transaction))
{
insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values;
await insert.ExecuteNonQueryAsync();
}
await Transaction.CommitAsync();
}
await using (var select = new FbCommand($"SELECT varray_field FROM TEST WHERE int_field = {id_value}", Connection))
{
await using (var reader = await select.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
if (!await reader.IsDBNullAsync(0))
{
var select_values = new string[insert_values.Length];
Array.Copy((Array)reader.GetValue(0), select_values, select_values.Length);
CollectionAssert.AreEqual(insert_values, select_values);
}
}
}
}
}
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();
}
}
}
public async Task NumericArrayTest()
{
var id_value = GetId();
var insert_values = new decimal[] { 210.10M, 220.20M, 230.30M, 240.40M };
await using (Transaction = await Connection.BeginTransactionAsync())
{
await using (var insert = new FbCommand("INSERT INTO TEST (int_field, narray_field) values(@int_field, @array_field)", Connection, Transaction))
{
insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values;
await insert.ExecuteNonQueryAsync();
}
await Transaction.CommitAsync();
}
await using (var select = new FbCommand($"SELECT narray_field FROM TEST WHERE int_field = {id_value}", Connection))
{
await using (var reader = await select.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
if (!await reader.IsDBNullAsync(0))
{
var select_values = new decimal[insert_values.Length];
Array.Copy((Array)reader.GetValue(0), select_values, select_values.Length);
CollectionAssert.AreEqual(insert_values, select_values);
}
}
}
}
}
public async Task InsertTest()
{
var csb = BuildConnectionStringBuilder(ServerType, Compression, WireCrypt);
csb.Enlist = true;
using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
await using (var c = new FbConnection(csb.ToString()))
{
await c.OpenAsync();
var sql = "insert into TEST (int_field, date_field) values (1002, @date)";
await using (var command = new FbCommand(sql, c))
{
command.Parameters.Add("@date", FbDbType.Date).Value = DateTime.Now.ToString();
var ra = await command.ExecuteNonQueryAsync();
Assert.AreEqual(ra, 1);
}
}
scope.Complete();
}
}
public async Task AbortTransaction()
{
FbTransaction transaction = null;
FbCommand command = null;
try
{
transaction = await Connection.BeginTransactionAsync();
command = new FbCommand("ALTER TABLE \"TEST\" drop \"INT_FIELD\"", Connection, transaction);
await command.ExecuteNonQueryAsync();
await transaction.CommitAsync();
}
catch (Exception)
{
await transaction.RollbackAsync();
}
finally
{
if (transaction != null)
{
await command.DisposeAsync();
}
if (transaction != null)
{
await command.DisposeAsync();
}
}
}
public async Task TimeArrayTest()
{
var id_value = GetId();
var insert_values = new TimeSpan[] { new TimeSpan(3, 9, 10), new TimeSpan(4, 11, 12), new TimeSpan(6, 13, 14), new TimeSpan(8, 15, 16) };
await using (Transaction = await Connection.BeginTransactionAsync())
{
await using (var insert = new FbCommand("INSERT INTO TEST (int_field, tarray_field) values(@int_field, @array_field)", Connection, Transaction))
{
insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values;
await insert.ExecuteNonQueryAsync();
}
await Transaction.CommitAsync();
}
await using (var select = new FbCommand($"SELECT tarray_field FROM TEST WHERE int_field = {id_value}", Connection))
{
await using (var reader = await select.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
if (!await reader.IsDBNullAsync(0))
{
var select_values = new TimeSpan[insert_values.Length];
Array.Copy((Array)reader.GetValue(0), select_values, select_values.Length);
CollectionAssert.AreEqual(insert_values, select_values);
}
}
}
}
}
public async Task BinaryBlobTest()
{
var id_value = GetId();
var insert_values = new byte[100000 * 4];
using (var rng = new RNGCryptoServiceProvider())
{
rng.GetBytes(insert_values);
}
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var insert = new FbCommand("INSERT INTO TEST (int_field, blob_field) values(@int_field, @blob_field)", Connection, transaction))
{
insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
insert.Parameters.Add("@blob_field", FbDbType.Binary).Value = insert_values;
await insert.ExecuteNonQueryAsync();
}
await transaction.CommitAsync();
}
await using (var select = new FbCommand($"SELECT blob_field FROM TEST WHERE int_field = {id_value}", Connection))
{
var select_values = (byte[])await select.ExecuteScalarAsync();
CollectionAssert.AreEqual(insert_values, select_values);
}
}
public async Task ShortArrayTest()
{
var id_value = RandomNumberGenerator.GetInt32(int.MinValue, int.MaxValue);
var insert_values = new short[] { 50, 60, 70, 80 };
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var insert = new FbCommand("INSERT INTO TEST (int_field, sarray_field) values(@int_field, @array_field)", Connection, transaction))
{
insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values;
await insert.ExecuteNonQueryAsync();
}
await transaction.CommitAsync();
}
await using (var select = new FbCommand($"SELECT sarray_field FROM TEST WHERE int_field = {id_value}", Connection))
{
await using (var reader = await select.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
if (!await reader.IsDBNullAsync(0))
{
var select_values = new short[insert_values.Length];
Array.Copy((Array)reader.GetValue(0), select_values, select_values.Length);
CollectionAssert.AreEqual(insert_values, select_values);
}
}
}
}
}
public async Task InsertNullGuidTest()
{
var id = RandomNumberGenerator.GetInt32(int.MinValue, int.MaxValue);
await using (var insert = new FbCommand("INSERT INTO GUID_TEST (INT_FIELD, GUID_FIELD) VALUES (@IntField, @GuidValue)", Connection))
{
insert.Parameters.Add("@IntField", FbDbType.Integer).Value = id;
insert.Parameters.Add("@GuidValue", FbDbType.Guid).Value = DBNull.Value;
await insert.ExecuteNonQueryAsync();
}
await using (var select = new FbCommand("SELECT * FROM GUID_TEST WHERE INT_FIELD = @IntField", Connection))
{
select.Parameters.Add("@IntField", FbDbType.Integer).Value = id;
await using (var r = await select.ExecuteReaderAsync())
{
if (await r.ReadAsync())
{
if (!await r.IsDBNullAsync(1))
{
Assert.Fail();
}
}
}
}
}
public async Task BigIntArrayPartialUpdateTest()
{
var new_values = new long[] { 900, 1000, 1100, 1200 };
await using (var update = new FbCommand("update TEST set larray_field = @array_field WHERE int_field = 1", Connection))
{
update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values;
await update.ExecuteNonQueryAsync();
}
}
public async Task ParameterDescribeTest()
{
await using (var command = new FbCommand("insert into TEST (int_field) values (@value)", Connection))
{
await command.PrepareAsync();
command.Parameters.Add("@value", FbDbType.Integer).Value = 100000;
await command.ExecuteNonQueryAsync();
}
}
public async Task InsertDateTest()
{
await using (var command = new FbCommand("insert into TEST (int_field, date_field) values (1002, @date)", Connection))
{
command.Parameters.Add("@date", FbDbType.Date).Value = DateTime.Now.ToString();
var ra = await command.ExecuteNonQueryAsync();
Assert.AreEqual(ra, 1);
}
}
public async Task DoubleArrayPartialUpdateTest()
{
var new_values = new double[] { 1700.10, 1800.20 };
await using (var update = new FbCommand("update TEST set barray_field = @array_field WHERE int_field = 1", Connection))
{
update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values;
await update.ExecuteNonQueryAsync();
}
}
private async Task <int> ExecuteCommandAsync(bool autoCommit, CancellationToken cancellationToken = default)
{
var rowsAffected = await _sqlCommand.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
if (autoCommit && _sqlCommand.IsDDLCommand)
{
await CommitTransactionAsync(cancellationToken).ConfigureAwait(false);
}
return(rowsAffected);
}
public async Task TimeStampArrayPartialUpdateTest()
{
var new_values = new DateTime[] { DateTime.Now.AddSeconds(100), DateTime.Now.AddSeconds(200) };
await using (var update = new FbCommand("update TEST set tsarray_field = @array_field WHERE int_field = 1", Connection))
{
update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values;
await update.ExecuteNonQueryAsync();
}
}
public async Task TimeArrayPartialUpdateTest()
{
var new_values = new TimeSpan[] { new TimeSpan(11, 13, 14), new TimeSpan(12, 15, 16) };
await using (var update = new FbCommand("update TEST set tarray_field = @array_field WHERE int_field = 1", Connection))
{
update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values;
await update.ExecuteNonQueryAsync();
}
}
public async Task NumericArrayPartialUpdateTest()
{
var new_values = new decimal[] { 2100.10M, 2200.20M };
await using (var update = new FbCommand("update TEST set narray_field = @array_field WHERE int_field = 1", Connection))
{
update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values;
await update.ExecuteNonQueryAsync();
}
}
public async Task VarCharArrayPartialUpdateTest()
{
var new_values = new string[] { "abc", "abcdef" };
await using (var update = new FbCommand("update TEST set varray_field = @array_field WHERE int_field = 1", Connection))
{
update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values;
await update.ExecuteNonQueryAsync();
}
}
public async Task FloatArrayPartialUpdateTest()
{
var new_values = new float[] { 1300.10F, 1400.20F };
await using (var update = new FbCommand("update TEST set farray_field = @array_field WHERE int_field = 1", Connection))
{
update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values;
await update.ExecuteNonQueryAsync();
}
}
public async Task NamedParametersAndLiterals()
{
await using (var command = new FbCommand("update test set char_field = '*****@*****.**', bigint_field = @bigint, varchar_field = '*****@*****.**' where int_field = @integer", Connection))
{
command.Parameters.Add("@bigint", FbDbType.BigInt).Value = 200;
command.Parameters.Add("@integer", FbDbType.Integer).Value = 1;
var recordsAffected = await command.ExecuteNonQueryAsync();
Assert.AreEqual(recordsAffected, 1, "Invalid number of records affected.");
}
}
public async Task UpdatedBlobFieldTest()
{
await using (var command = new FbCommand("update TEST set blob_field = @blob_field where int_field = @int_field", Connection))
{
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
command.Parameters.Add("@blob_field", FbDbType.Binary).Value = Encoding.UTF8.GetBytes("Blob field update with implicit transaction");
var i = await command.ExecuteNonQueryAsync();
Assert.AreEqual(i, 1, "Blob field update with implicit transaction failed");
}
}
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 UpdatedClobFieldTest()
{
await using (var command = new FbCommand("update TEST set clob_field = @clob_field where int_field = @int_field", Connection))
{
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
command.Parameters.Add("@clob_field", FbDbType.Text).Value = "Clob field update with implicit transaction";
var i = await command.ExecuteNonQueryAsync();
Assert.AreEqual(i, 1, "Clob field update with implicit transaction failed");
}
}