public static async Task <bool> Custodian(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId) { Guid guidExtern = GuidFactory.Create(GuidFactory.DnsNamespace, ecfTableReader.GetValue <string>("Id")); var success = 0; var sql = "INSERT INTO \"Sorgeberechtigte\" " + "(" + " \"Mandant\", \"GUIDExtern\", \"Nachname\", \"Vorname\", \"Anrede\", " + " \"Strasse\", \"Land\", \"PLZ\", \"Ort\", \"Email\", \"TelefonPrivat\", \"TelefonBeruf\", " + " \"Status2\" " + ") " + "VALUES ( " + " @TenantId, @GUIDExtern, @LastName, @FirstName, @Salutation, " + " @AddressLines, @Country, @PostalCode, @Locality, @Email, @HomePhoneNumber, @OfficePhoneNumber, " + " @Status2 " + ")"; 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, "@LastName", FbDbType.VarChar, ecfTableReader.GetValue <string>("LastName")); Helper.SetParamValue(fbCommand, "@FirstName", FbDbType.VarChar, ecfTableReader.GetValue <string>("FirstName")); Helper.SetParamValue(fbCommand, "@Salutation", FbDbType.VarChar, ValueConvert.Salutation(ecfTableReader.GetValue <string>("Salutation"))); Helper.SetParamValue(fbCommand, "@AddressLines", FbDbType.VarChar, ecfTableReader.GetValue <string>("AddressLines")); Helper.SetParamValue(fbCommand, "@Country", FbDbType.VarChar, ecfTableReader.GetValue <string>("CountryId")); 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")); Helper.SetParamValue(fbCommand, "@HomePhoneNumber", FbDbType.VarChar, ecfTableReader.GetValue <string>("HomePhoneNumber")); Helper.SetParamValue(fbCommand, "@OfficePhoneNumber", FbDbType.VarChar, ecfTableReader.GetValue <string>("OfficePhoneNumber")); Helper.SetParamValue(fbCommand, "@Status2", FbDbType.SmallInt, StatusType.Active); // Status success = await fbCommand.ExecuteNonQueryAsync(); await fbTransaction.CommitAsync(); } catch (Exception e) { fbTransaction.Rollback(); Console.WriteLine($"[INSERT ERROR] [Sorgeberechtigte] {e.Message}"); } return(success > 0); }
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)); } }