public static async Task <bool> GradeValue(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId, int id) { var success = 0; var sql = "UPDATE \"Noten\" " + "SET " + " \"Bezeichnung\" = @Name, \"Notenwert\" = @Value, \"Notenart\" = @GradeSystemId " + "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")); Helper.SetParamValue(fbCommand, "@Value", FbDbType.Integer, ecfTableReader.GetValue <string>("Value")); Helper.SetParamValue(fbCommand, "@GradeSystemId", FbDbType.SmallInt, ValueConvert.GradeSystem(ecfTableReader.GetValue <string>("GradeSystemId"))); success = await fbCommand.ExecuteNonQueryAsync(); await fbTransaction.CommitAsync(); } catch (Exception e) { fbTransaction.Rollback(); Console.WriteLine($"[UPDATE ERROR] [Noten] {e.Message}"); } return(success > 0); }
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 static async Task <bool> GradeValue(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId) { var success = 0; var sql = $"INSERT INTO \"Noten\" " + "( \"Mandant\", \"Notenkuerzel\", \"Bezeichnung\", \"Notenart\", \"Notenwert\" )" + "VALUES ( @TenantId, @Code, @Name, @GradeSystemId, @Value )"; using var fbTransaction = fbConnection.BeginTransaction(); try { using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction); // Token based table with max. length of 20, code will be cut, if longer string code = ecfTableReader.GetValue <string>("Code"); if (code.Length > 20) { code = code.Substring(0, 20); } Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId); Helper.SetParamValue(fbCommand, "@Code", FbDbType.VarChar, code); Helper.SetParamValue(fbCommand, "@Name", FbDbType.VarChar, ecfTableReader.GetValue <string>("Name")); Helper.SetParamValue(fbCommand, "@GradeSystemId", FbDbType.SmallInt, ValueConvert.GradeSystem(ecfTableReader.GetValue <string>("GradeSystemId"))); Helper.SetParamValue(fbCommand, "@Value", FbDbType.Integer, ecfTableReader.GetValue <string>("Value")); success = await fbCommand.ExecuteNonQueryAsync(); await fbTransaction.CommitAsync(); } catch (Exception e) { fbTransaction.Rollback(); Console.WriteLine($"[INSERT ERROR] [Noten] {e.Message}"); } return(success > 0); }
public static async Task <bool> SchoolClass(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId, int schoolClassesId) { var success = 0; var sql = "UPDATE \"Klassen\" " + "SET " + " \"Langname1\" = @Name1, \"Langname2\" = @Name2, \"KuerzelStatistik\" = @StatisticalCode, " + " \"Schulform\" = @SchoolCategoryId, \"Klassenart\" = @SchoolClassTypeId, \"Notenart\" = @GradeSystemId " + "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, schoolClassesId); Helper.SetParamValue(fbCommand, "@Name1", FbDbType.VarChar, ecfTableReader.GetValue <string>("Name1")); Helper.SetParamValue(fbCommand, "@Name2", FbDbType.VarChar, ecfTableReader.GetValue <string>("Name2")); Helper.SetParamValue(fbCommand, "@StatisticalCode", FbDbType.VarChar, ecfTableReader.GetValue <string>("StatisticalCode")); 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"))); success = await fbCommand.ExecuteNonQueryAsync(); await fbTransaction.CommitAsync(); } catch (Exception e) { fbTransaction.Rollback(); Console.WriteLine($"[UPDATE ERROR] [Klassen] {e.Message}"); } return(success > 0); }