public static async Task <bool> Student(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId, int id)
{
var success = 0;
var sql =
"UPDATE \"Schueler\" " +
"SET " +
" \"Anrede\" = @Salutation, \"Nachname\" = @LastName, \"Vorname\" = @FirstName, \"Geschlecht\" = @Gender, " +
" \"Geburtsdatum\" = @Birthdate, \"Strasse\" = @AddressLines, \"PLZ\" = @PostalCode, \"Ort\" = @Locality, " +
" \"Gemeinde\" = @Region, \"EMail\" = @Email, \"Telefon\" = @HomePhoneNumber, \"Mobil\" = @MobileNumber, " +
" \"Krankenkasse\" = @HealthInsuranceProvider, \"ZugangAm\" = @EntryDate, \"Grundschuleintritt\" = @FirstEntryDate " +
"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, "@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.VarChar, 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, "@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"));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[UPDATE ERROR] [Schueler] {e.Message}");
}
return(success > 0);
}
public static async Task <bool> Teacher(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId)
{
var success = 0;
var sql =
"INSERT INTO \"tblLehrer\" " +
"(" +
" \"Mandant\", \"GUIDExtern\", \"Kuerzel\", \"Anrede\", \"Nachname\", \"Vorname\", " +
" \"Geschlecht\", \"Geburtsdatum\", \"Strasse\", \"PLZ\", \"Ort\", \"Email\" " +
") " +
"VALUES ( " +
" @TenantId, @GUIDExtern, @Code, @Salutation, @LastName, @FirstName, @Gender, " +
" @Birthdate, @AddressLines, @PostalCode, @Locality, @Email " +
")";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId);
Helper.SetParamValue(fbCommand, "@GUIDExtern", FbDbType.BigInt, ecfTableReader.GetValue <string>("Id"));
Helper.SetParamValue(fbCommand, "@Code", FbDbType.VarChar, ecfTableReader.GetValue <string>("Code"));
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, "@Email", FbDbType.VarChar, ecfTableReader.GetValue <string>("Email"));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[INSERT ERROR] [Lehrer] {e.Message}");
}
return(success > 0);
}
public static async Task <bool> Teacher(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId, int id)
{
var success = 0;
var sql =
"UPDATE \"tblLehrer\" " +
"SET " +
" \"Anrede\" = @Salutation, \"Nachname\" = @LastName, \"Vorname\" = @FirstName, " +
" \"Geschlecht\" = @Gender, \"Geburtsdatum\" = @Birthdate, \"Strasse\" = @AddressLines, " +
" \"PLZ\" = @PostalCode, \"Ort\" = @Locality, \"Email\" = @Email " +
"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, "@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.VarChar, 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, "@Email", FbDbType.VarChar, ecfTableReader.GetValue <string>("Email"));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[UPDATE ERROR] [tblLehrer] {e.Message}");
}
return(success > 0);
}
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));
}
}
