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"); } }