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);
}
}
static async Task GetSomethingHelper(string connectionString)
{
await using (var conn = new FbConnection(connectionString))
{
await conn.OpenAsync();
await using (var command = new FbCommand("select current_timestamp from mon$database", conn))
{
await command.ExecuteScalarAsync();
}
}
}
public static async Task <DbResult> StudentSchoolClassAttendance(FbConnection fbConnection, int tenantId, int studentId, Career career)
{
var id = -1;
var sql =
"INSERT INTO \"SchuelerZeitraeume\" " +
"(" +
" \"Mandant\", \"KlassenZeitraumID\", \"Schueler\", " +
" \"Klasse\", \"Zeitraum\", \"Gewechselt\", " +
" \"TeilnahmeZusatzangebot\", \"SportBefreit\" " +
") " +
"VALUES ( " +
" @TenantId, @ClassTermId, @StudentId, " +
" @SchoolClassId, @SchoolTermId, @Gewechselt, " +
" @TeilnahmeZusatzangebot, @SportBefreit " +
") RETURNING 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, "@ClassTermId", FbDbType.BigInt, career.MagellanValues.ClassTermId);
Helper.SetParamValue(fbCommand, "@StudentId", FbDbType.BigInt, studentId);
Helper.SetParamValue(fbCommand, "@SchoolClassId", FbDbType.BigInt, career.MagellanValues.SchoolClassId);
Helper.SetParamValue(fbCommand, "@SchoolTermId", FbDbType.BigInt, career.MagellanValues.SchoolTermId);
Helper.SetParamValue(fbCommand, "@Gewechselt", FbDbType.VarChar, career.MagellanValues.Gewechselt);
Helper.SetParamValue(fbCommand, "@TeilnahmeZusatzangebot", FbDbType.VarChar, "N");
Helper.SetParamValue(fbCommand, "@SportBefreit", FbDbType.VarChar, "N");
FbParameter IdParam = fbCommand.Parameters.Add("@Id", FbDbType.Integer, Int32.MaxValue, "ID");
IdParam.Direction = ParameterDirection.Output;
id = (int)await fbCommand.ExecuteScalarAsync();
await fbTransaction.CommitAsync();
return(new DbResult(true, id));
}
catch (Exception e)
{
await fbTransaction.RollbackAsync();
Console.WriteLine($"[INSERT ERROR] [SchuelerZeitraeume] {e.Message}");
return(new DbResult(false, id));
}
}
public static async Task <DbResult> SchoolTerm(FbConnection fbConnection, EcfTableReader ecfTableReader)
{
var section = ecfTableReader.GetValue <string>("Section") switch
{
"1" => 0,
"2" => 1,
"3" => 2,
_ => - 1,
};
var id = -1;
var sql =
"INSERT INTO \"Zeitraeume\" " +
"(" +
" \"Von\", \"Bis\", \"Art\", " +
" \"Bezeichnung\", \"Ausdruck1\", \"Ausdruck2\" " +
" )" +
"VALUES ( " +
" @ValidFrom, @ValidTo, @Section, " +
" @Code, @Name, @Name" +
") RETURNING ID";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@ValidFrom", FbDbType.Date, ecfTableReader.GetValue <string>("ValidFrom"));
Helper.SetParamValue(fbCommand, "@ValidTo", FbDbType.Date, ecfTableReader.GetValue <string>("ValidTo"));
Helper.SetParamValue(fbCommand, "@Section", FbDbType.SmallInt, section);
Helper.SetParamValue(fbCommand, "@Code", FbDbType.VarChar, ecfTableReader.GetValue <string>("Code"));
Helper.SetParamValue(fbCommand, "@Name", FbDbType.VarChar, ecfTableReader.GetValue <string>("Name"));
FbParameter IdParam = fbCommand.Parameters.Add("@Id", FbDbType.Integer, Int32.MaxValue, "ID");
IdParam.Direction = ParameterDirection.Output;
id = (int)await fbCommand.ExecuteScalarAsync();
await fbTransaction.CommitAsync();
return(new DbResult(true, id));
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[INSERT ERROR] [Zeitraeume] {e.Message}");
return(new DbResult(false, id));
}
}
public async Task UpdateTimeStampTest()
{
var dtValue = DateTime.Now;
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var command = new FbCommand("select * from TEST where int_field = @int_field", Connection, transaction))
{
using (var adapter = new FbDataAdapter(command))
{
using (var builder = new FbCommandBuilder(adapter))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
using (var ds = new DataSet())
{
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["TIMESTAMP_FIELD"] = dtValue;
adapter.Update(ds, "TEST");
}
}
}
}
await transaction.CommitAsync();
}
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var command = new FbCommand("SELECT timestamp_field FROM TEST WHERE int_field = @int_field", Connection, transaction))
{
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var val = (DateTime)await command.ExecuteScalarAsync();
Assert.AreEqual(dtValue.Day, val.Day, "timestamp_field has not correct day");
Assert.AreEqual(dtValue.Month, val.Month, "timestamp_field has not correct month");
Assert.AreEqual(dtValue.Year, val.Year, "timestamp_field has not correct year");
Assert.AreEqual(dtValue.Hour, val.Hour, "timestamp_field has not correct hour");
Assert.AreEqual(dtValue.Minute, val.Minute, "timestamp_field has not correct minute");
Assert.AreEqual(dtValue.Second, val.Second, "timestamp_field has not correct second");
}
await transaction.CommitAsync();
}
}
public static async Task <DbResult> SchoolClass(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId)
{
var id = -1;
Guid guidExtern = GuidFactory.Create(GuidFactory.DnsNamespace, ecfTableReader.GetValue <string>("FederationId"));
var sql =
"INSERT INTO \"Klassen\" " +
"(" +
" \"Mandant\", \"Kuerzel\", \"KuerzelStatistik\", \"Langname1\", \"Langname2\", " +
" \"GUIDExtern\", \"Schulform\", \"Klassenart\", \"Notenart\" " +
") " +
"VALUES ( " +
" @TenantId, @Code, @StatisticalCode, @Name1, @Name2, " +
" @GUIDExtern, @SchoolCategoryId, @SchoolClassTypeId, @GradeSystemId" +
") RETURNING 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, "@Code", FbDbType.VarChar, ecfTableReader.GetValue <string>("Code"));
Helper.SetParamValue(fbCommand, "@StatisticalCode", FbDbType.VarChar, ecfTableReader.GetValue <string>("StatisticalCode"));
Helper.SetParamValue(fbCommand, "@Name1", FbDbType.VarChar, ecfTableReader.GetValue <string>("Name1"));
Helper.SetParamValue(fbCommand, "@Name2", FbDbType.VarChar, ecfTableReader.GetValue <string>("Name2"));
Helper.SetParamValue(fbCommand, "@GUIDExtern", FbDbType.Guid, guidExtern);
Helper.SetParamValue(fbCommand, "@SchoolCategoryId", FbDbType.VarChar, ecfTableReader.GetValue <string>("SchoolCategoryId"));
Helper.SetParamValue(fbCommand, "@SchoolClassTypeId", FbDbType.SmallInt, ecfTableReader.GetValue <string>("SchoolClassTypeId"));
Helper.SetParamValue(fbCommand, "@GradeSystemId", FbDbType.SmallInt, ValueConvert.GradeSystem(ecfTableReader.GetValue <string>("GradeSystemId")));
FbParameter IdParam = fbCommand.Parameters.Add("@ClassId", FbDbType.Integer, Int32.MaxValue, "ID");
IdParam.Direction = ParameterDirection.Output;
id = (int)await fbCommand.ExecuteScalarAsync();
await fbTransaction.CommitAsync();
return(new DbResult(true, id));
}
catch (Exception e)
{
await fbTransaction.RollbackAsync();
Console.WriteLine($"[INSERT ERROR] [Klassen] {e.Message}");
return(new DbResult(false, id));
}
}
public async Task UpdateDecimalTest()
{
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var command = new FbCommand("select * from TEST where int_field = @int_field", Connection, transaction))
{
using (var adapter = new FbDataAdapter(command))
{
using (var builder = new FbCommandBuilder(adapter))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
using (var ds = new DataSet())
{
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["DECIMAL_FIELD"] = int.MaxValue;
adapter.Update(ds, "TEST");
}
}
}
}
await transaction.CommitAsync();
}
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var command = new FbCommand("SELECT decimal_field FROM TEST WHERE int_field = @int_field", Connection, transaction))
{
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var val = (decimal)await command.ExecuteScalarAsync();
Assert.AreEqual(int.MaxValue, val, "decimal_field has not correct value");
}
await transaction.CommitAsync();
}
}
public async Task UpdateVarCharTest()
{
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var command = new FbCommand("select * from TEST where int_field = @int_field", Connection, transaction))
{
using (var adapter = new FbDataAdapter(command))
{
using (var builder = new FbCommandBuilder(adapter))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
using (var ds = new DataSet())
{
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["VARCHAR_FIELD"] = "ONE VAR THOUSAND";
adapter.Update(ds, "TEST");
}
}
}
}
await transaction.CommitAsync();
}
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var command = new FbCommand("SELECT varchar_field FROM TEST WHERE int_field = @int_field", Connection, transaction))
{
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var val = (string)await command.ExecuteScalarAsync();
Assert.AreEqual("ONE VAR THOUSAND", val.Trim(), "varchar_field has not correct value");
}
await transaction.CommitAsync();
}
}
public async Task BinaryBlobTest()
{
var id_value = RandomNumberGenerator.GetInt32(int.MinValue, int.MaxValue);
var insert_values = RandomNumberGenerator.GetBytes(100000 * 4);
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 static async Task <DbResult> Student(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId)
{
Guid guidExtern = GuidFactory.Create(GuidFactory.DnsNamespace, ecfTableReader.GetValue <string>("Id"));
var id = -1;
var sql =
"INSERT INTO \"Schueler\" " +
"(" +
" \"Mandant\", \"GUIDExtern\", \"Status\", \"Anrede\", \"Nachname\", \"Vorname\", \"Geschlecht\", " +
" \"Geburtsdatum\", \"Strasse\", \"PLZ\", \"Ort\", \"Gemeinde\", \"EMail\", \"Telefon\", \"Mobil\", " +
" \"Staatsangeh1\", \"Staatsangeh2\", \"Verkehrssprache\", \"Konfession\", \"Krankenkasse\", \"ZugangAm\", " +
" \"Grundschuleintritt\" " +
") " +
"VALUES ( " +
" @TenantId, @GUIDExtern, @Status, @Salutation, @LastName, @FirstName, @Gender, " +
" @Birthdate, @AddressLines, @PostalCode, @Locality, @Region, @Email, @HomePhoneNumber, @MobileNumber, " +
" @Nationality1, @Nationality2, @NativeLanguage, @Religion, @HealthInsuranceProvider, @EntryDate, " +
" @FirstEntryDate " +
") RETURNING ID";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.Integer, tenantId);
Helper.SetParamValue(fbCommand, "@GUIDExtern", FbDbType.VarChar, guidExtern);
Helper.SetParamValue(fbCommand, "@Status", FbDbType.SmallInt, 2);
Helper.SetParamValue(fbCommand, "@Salutation", FbDbType.VarChar, ValueConvert.Salutation(ecfTableReader.GetValue <string>("Salutation")));
Helper.SetParamValue(fbCommand, "@LastName", FbDbType.VarChar, ecfTableReader.GetValue <string>("LastName"));
Helper.SetParamValue(fbCommand, "@FirstName", FbDbType.VarChar, ecfTableReader.GetValue <string>("FirstName"));
Helper.SetParamValue(fbCommand, "@Gender", FbDbType.VarChar, ValueConvert.Gender(ecfTableReader.GetValue <string>("Gender")));
Helper.SetParamValue(fbCommand, "@Birthdate", FbDbType.Date, ecfTableReader.GetValue <string>("Birthdate"));
Helper.SetParamValue(fbCommand, "@AddressLines", FbDbType.VarChar, ecfTableReader.GetValue <string>("AddressLines"));
Helper.SetParamValue(fbCommand, "@PostalCode", FbDbType.VarChar, ecfTableReader.GetValue <string>("PostalCode"));
Helper.SetParamValue(fbCommand, "@Locality", FbDbType.VarChar, ecfTableReader.GetValue <string>("Locality"));
Helper.SetParamValue(fbCommand, "@Region", FbDbType.VarChar, ecfTableReader.GetValue <string>("RegionId"));
Helper.SetParamValue(fbCommand, "@Email", FbDbType.VarChar, ecfTableReader.GetValue <string>("Email"));
Helper.SetParamValue(fbCommand, "@HomePhoneNumber", FbDbType.VarChar, ecfTableReader.GetValue <string>("HomePhoneNumber"));
Helper.SetParamValue(fbCommand, "@MobileNumber", FbDbType.VarChar, ecfTableReader.GetValue <string>("MobileNumber"));
Helper.SetParamValue(fbCommand, "@Nationality1", FbDbType.VarChar, ecfTableReader.GetValue <string>("Nationality1Id"));
Helper.SetParamValue(fbCommand, "@Nationality2", FbDbType.VarChar, ecfTableReader.GetValue <string>("Nationality2Id"));
Helper.SetParamValue(fbCommand, "@NativeLanguage", FbDbType.VarChar, ecfTableReader.GetValue <string>("NativeLanguage"));
Helper.SetParamValue(fbCommand, "@Religion", FbDbType.VarChar, ecfTableReader.GetValue <string>("ReligionId"));
Helper.SetParamValue(fbCommand, "@HealthInsuranceProvider", FbDbType.VarChar, ecfTableReader.GetValue <string>("HealthInsuranceProvider"));
Helper.SetParamValue(fbCommand, "@EntryDate", FbDbType.Date, ecfTableReader.GetValue <string>("EntryDate"));
Helper.SetParamValue(fbCommand, "@FirstEntryDate", FbDbType.Date, ecfTableReader.GetValue <string>("FirstEntryDate"));
FbParameter IdParam = fbCommand.Parameters.Add("@Id", FbDbType.Integer, Int32.MaxValue, "ID");
IdParam.Direction = ParameterDirection.Output;
id = (int)await fbCommand.ExecuteScalarAsync();
await fbTransaction.CommitAsync();
return(new DbResult(true, id));
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[INSERT ERROR] [Schueler] {e.Message}");
return(new DbResult(false, id));
}
}