private async Task <int> ImportCustodians(FbConnection fbConnection, EcfTableReader ecfTableReader)
{
var recordCounter = 0;
while (ecfTableReader.ReadAsync().Result > 0)
{
// read needed field-values
var ecfId = ecfTableReader.GetValue <string>("Id");
DbResult dbResult = await RecordExists.ByGuidExtern(fbConnection, MappingTables.Map(_currentCsv), _tenantId, ecfId);
if (dbResult.Success)
{
// UPDATE
await RecordUpdate.Custodian(fbConnection, ecfTableReader, _tenantId, (int)dbResult.Value);
}
else
{
// INSERT
await RecordInsert.Custodian(fbConnection, ecfTableReader, _tenantId);
}
recordCounter += 1;
}
return(await Task.FromResult(recordCounter));
}
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);
}
private async Task Execute(string csvTableName, FbConnection fbConnection, Func <FbConnection, EcfTableReader, Task <int> > action)
{
var sourceFile = Path.ChangeExtension(Path.Combine(_sourceFolder.FullName, csvTableName), ".csv");
if (!File.Exists(sourceFile))
{
return;
}
// Report status
Console.WriteLine($"[Importing] [{csvTableName}] Start...");
// Init CSV file stream
using var csvfileStream = new FileStream(sourceFile, FileMode.Open, FileAccess.Read, FileShare.None);
// Init CSV Reader
using var csvReader = new CsvReader(csvfileStream, Encoding.UTF8);
// Init ECF Reader
var ecfTableReader = new EcfTableReader(csvReader);
await ecfTableReader.ReadHeadersAsync();
_currentCsv = csvTableName;
// Call table specific action
var ecfRecordCounter = await action(fbConnection, ecfTableReader);
// Inc counters
_recordCounter += ecfRecordCounter;
_tableCounter++;
// Report status
Console.WriteLine($"[Importing] [{csvTableName}] {ecfRecordCounter} record(s) imported");
}
public static void ProcessStudents(EcfTableReader ecfTableReader, List <Students> students, List <SimpleCache> schoolTerms)
{
while (ecfTableReader.ReadAsync().Result > 0)
{
var studentId = ecfTableReader.GetValue <string>("StudentId");
var student = students.Find(s => s.EcfId.Equals(studentId));
var schoolTermId = ecfTableReader.GetValue <string>("SchoolTermId");
var schoolTerm = schoolTerms.Find(t => t.EcfId.Equals(schoolTermId));
var classTermId = ecfTableReader.GetValue <string>("SchoolClassId");
if (!String.IsNullOrEmpty(studentId) && !String.IsNullOrEmpty(schoolTermId) && !String.IsNullOrEmpty(classTermId) && (student != null) && (schoolTerm != null))
{
student.Career.Add(new Career(
ecfTableReader.GetValue <string>("Id"),
ecfTableReader.GetValue <string>("SchoolTermId"),
ecfTableReader.GetValue <string>("SchoolClassId"),
ecfTableReader.GetValue <string>("Status")
)
);
}
}
// Sort student careers
students.ForEach(s => s.Career.Sort((x, y) => x.EcfValues.SchoolTermId.CompareTo(y.EcfValues.SchoolTermId)));
students.ForEach(s => s.SetLastGewechseltToN());
//students.ForEach(s => s.Career.ForEach(c => Console.WriteLine($"Student: {c.EcfIds.StudentId} - {c.EcfIds.SchoolTermId} - {c.Gewechselt}")));
}
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> 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 static void AddStudentsSubjects(EcfTableReader ecfTableReader, List <Students> students, List <SimpleCache> schoolTerms)
{
while (ecfTableReader.ReadAsync().Result > 0)
{
var studentId = ecfTableReader.GetValue <string>("StudentId");
var student = students.Find(s => s.EcfId.Equals(studentId));
var schoolTermId = ecfTableReader.GetValue <string>("SchoolTermId");
var schoolTerm = schoolTerms.Find(t => t.EcfId.Equals(schoolTermId));
var classTermId = ecfTableReader.GetValue <string>("SchoolClassId");
if (!String.IsNullOrEmpty(studentId) && !String.IsNullOrEmpty(schoolTermId) && !String.IsNullOrEmpty(classTermId) && (student != null) && (schoolTerm != null))
{
var career = student.Career.Find(c => c.EcfValues.SchoolTermId.Equals(schoolTermId) && c.EcfValues.ClassTermId.Equals(classTermId));
if (career != null)
{
career.StudentSubjects.Add(new StudentSubjects(
ecfTableReader.GetValue <string>("SubjectId"),
ecfTableReader.GetValue <string>("TeacherId"),
ecfTableReader.GetValue <string>("Grade1ValueId"),
ecfTableReader.GetValue <string>("CourseNo"),
ecfTableReader.GetValue <string>("CourseTypeId"),
ecfTableReader.GetValue <string>("Grade1AchievementTypeId"),
ecfTableReader.GetValue <string>("Passfail")
));
}
}
}
}
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);
}
private async Task ValidateSchooClassesFile(DirectoryInfo ecfFolder)
{
using var csvReader = new CsvReader(Path.Combine(ecfFolder.FullName, EcfTables.SchoolClasses + ".csv"), true);
var ecfTableReader = new EcfTableReader(csvReader);
await ecfTableReader.ReadHeadersAsync();
Assert.Equal(2, ecfTableReader.Headers.Count);
Assert.Equal(EcfHeaders.Id, ecfTableReader.Headers[0]);
Assert.Equal(EcfHeaders.Code, ecfTableReader.Headers[1]);
}
static async void InsertSchoolClassAndTerm(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId, int schoolTermId, string classTermId)
{
DbResult SchoolClassResult = await RecordInsert.SchoolClass(fbConnection, ecfTableReader, tenantId);
if (SchoolClassResult.Success)
{
await RecordInsert.SchoolClassTerm(fbConnection, tenantId, (int)SchoolClassResult.Value, schoolTermId, classTermId, ecfTableReader);
}
else
{
Console.WriteLine("[ERROR] [SchoolClass] could not be inserted");
}
}
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 <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 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> AchievementType(FbConnection fbConnection, EcfTableReader ecfTableReader)
{
var success = 0;
var sql =
"INSERT INTO \"Leistungsarten\" " +
"( \"Kuerzel\", \"Bezeichnung\", \"Art\" )" +
"VALUES ( @Code, @Name, @Art )";
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);
}
string category = String.Empty;
if (ecfTableReader.Headers.Contains("Category"))
{
category = ecfTableReader.GetValue <string>("Category");
}
Helper.SetParamValue(fbCommand, "@Code", FbDbType.VarChar, code);
Helper.SetParamValue(fbCommand, "@Name", FbDbType.VarChar, ecfTableReader.GetValue <string>("Name"));
Helper.SetParamValue(fbCommand, "@Art", FbDbType.SmallInt, category, 0);
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[INSERT ERROR] [Leistungsarten] {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> TokenCatalog(FbConnection fbConnection, EcfTableReader ecfTableReader, string tableName)
{
var success = 0;
var sql =
$"UPDATE \"{tableName}\" " +
"SET \"Schluessel\" = @InternalCode, \"Bezeichnung\" = @Name " +
"WHERE " +
" \"Kuerzel\" = @Code";
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);
}
string InternalCode = String.Empty;
if (ecfTableReader.Headers.Contains("InternalCode"))
{
InternalCode = ecfTableReader.GetValue <string>("InternalCode");
}
Helper.SetParamValue(fbCommand, "@Code", FbDbType.VarChar, code);
Helper.SetParamValue(fbCommand, "@InternalCode", FbDbType.VarChar, InternalCode);
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] [{tableName}] {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 void ProcessStudentsSubjects(EcfTableReader ecfTableReader, List <Students> students)
{
while (ecfTableReader.ReadAsync().Result > 0)
{
var studentId = ecfTableReader.GetValue <string>("StudentId");
var student = students.Find(s => s.EcfId.Equals(studentId));
var schoolTermId = ecfTableReader.GetValue <string>("SchoolTermId");
var classTermId = ecfTableReader.GetValue <string>("SchoolClassId");
var subjectId = ecfTableReader.GetValue <string>("SubjectId");
if (!String.IsNullOrEmpty(studentId) && !String.IsNullOrEmpty(schoolTermId) && !String.IsNullOrEmpty(classTermId) && !String.IsNullOrEmpty(subjectId) && (student != null))
{
var careerId = ecfTableReader.GetValue <string>("Id");
var career = student.Career.Find(c => c.EcfValues.Id.Equals(careerId));
if (career == null)
{
career = new Career(
ecfTableReader.GetValue <string>("Id"),
ecfTableReader.GetValue <string>("SchoolTermId"),
ecfTableReader.GetValue <string>("SchoolClassId"),
ecfTableReader.GetValue <string>("StudentId")
);
student.Career.Add(career);
}
StudentSubjects studentSubjects = new StudentSubjects(
ecfTableReader.GetValue <string>("SubjectId"),
ecfTableReader.GetValue <string>("TeacherId"),
ecfTableReader.GetValue <string>("Grade1ValueId"),
ecfTableReader.GetValue <string>("CourseNo"),
ecfTableReader.GetValue <string>("CourseTypeId"),
ecfTableReader.GetValue <string>("Grade1AchievementTypeId"),
ecfTableReader.GetValue <string>("Passfail")
);
career.StudentSubjects.Add(studentSubjects);
}
}
}
public static async Task <bool> Custodian(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId, int id)
{
var success = 0;
var sql =
"UPDATE \"Sorgeberechtigte\" " +
"SET " +
" \"Nachname\" = @LastName, \"Vorname\" = @FirstName, " +
" \"Strasse\" = @AddressLines, \"PLZ\" = @PostalCode, " +
" \"Ort\" = @Locality, \"Email\" = @Email, \"TelefonPrivat\" = @HomePhoneNumber " +
"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, "@LastName", FbDbType.VarChar, ecfTableReader.GetValue <string>("LastName"));
Helper.SetParamValue(fbCommand, "@FirstName", FbDbType.VarChar, ecfTableReader.GetValue <string>("FirstName"));
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"));
Helper.SetParamValue(fbCommand, "@HomePhoneNumber", FbDbType.VarChar, ecfTableReader.GetValue <string>("HomePhoneNumber"));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[UPDATE ERROR] [Sorgeberechtigte] {e.Message}");
}
return(success > 0);
}
public static async Task <bool> SchoolClassTerm(FbConnection fbConnection, int tenantId, int classId, int termId, string classTermId,
EcfTableReader ecfTableReader)
{
Guid guidExtern = GuidFactory.Create(GuidFactory.DnsNamespace, classTermId);
var success = 0;
var sql =
"INSERT INTO \"KlassenZeitraeume\" " +
"(" +
" \"Mandant\", \"Klasse\", \"Zeitraum\", \"GUIDExtern\", " +
" \"Jahrgang\" " +
") " +
"VALUES ( " +
" @TenantId, @ClassId, @TermId, @GUIDExtern, @SchoolClassYear " +
")";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId);
Helper.SetParamValue(fbCommand, "@ClassId", FbDbType.BigInt, classId);
Helper.SetParamValue(fbCommand, "@TermId", FbDbType.BigInt, termId);
Helper.SetParamValue(fbCommand, "@GUIDExtern", FbDbType.Guid, guidExtern);
Helper.SetParamValue(fbCommand, "@SchoolClassYear", FbDbType.SmallInt, ecfTableReader.GetValue <string>("SchoolClassYear"));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[INSERT ERROR] [KlassenZeitraeume] {e.Message}");
}
return(success > 0);
}
private async Task ValidateStudentsFile(DirectoryInfo ecfFolder)
{
using var csvReader = new CsvReader(Path.Combine(ecfFolder.FullName, EcfTables.Students + ".csv"), true);
var ecfTableReader = new EcfTableReader(csvReader);
await ecfTableReader.ReadHeadersAsync();
Assert.Equal(5, ecfTableReader.Headers.Count);
Assert.Equal(EcfHeaders.Id, ecfTableReader.Headers[0]);
Assert.Equal(EcfHeaders.LastName, ecfTableReader.Headers[1]);
Assert.Equal(EcfHeaders.FirstName, ecfTableReader.Headers[2]);
Assert.Equal(EcfHeaders.Gender, ecfTableReader.Headers[3]);
Assert.Equal(EcfHeaders.Birthdate, ecfTableReader.Headers[4]);
await ecfTableReader.ReadAsync();
Assert.Equal("Duck", ecfTableReader.GetValue <string>(EcfHeaders.LastName));
Assert.Equal("Tick", ecfTableReader.GetValue <string>(EcfHeaders.FirstName));
Assert.Equal(EcfGender.Female, ecfTableReader.GetValue <EcfGender>(EcfHeaders.Gender));
Assert.Equal(new Date(2001, 1, 1), ecfTableReader.GetValue <Date>(EcfHeaders.Birthdate));
}
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);
}
public static async Task <bool> StudentCustodian(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId, int studentId, int custodianId)
{
var success = 0;
var sql =
"INSERT INTO \"SchuelerSorgebe\" " +
"(" +
" \"Mandant\", \"Schueler\", \"Sorgebe\", \"Verhaeltnis\", " +
" \"Benachrichtigung\", \"TelefonPrioritaet\", \"Position\" " +
" )" +
"VALUES ( " +
" @TenantId, @StudentId, @CustodianId, @RelationshipType, @CustodianNotification, @ContactPriority, @Order " +
")";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId);
Helper.SetParamValue(fbCommand, "@StudentId", FbDbType.BigInt, studentId);
Helper.SetParamValue(fbCommand, "@CustodianId", FbDbType.BigInt, custodianId);
Helper.SetParamValue(fbCommand, "@RelationshipType", FbDbType.SmallInt, ValueConvert.RelationShip(ecfTableReader.GetValue <string>("RelationshipType")));
Helper.SetParamValue(fbCommand, "@CustodianNotification", FbDbType.SmallInt, ValueConvert.Notification(ecfTableReader.GetValue <string>("CustodianNotification")));
Helper.SetParamValue(fbCommand, "@ContactPriority", FbDbType.SmallInt, ValueConvert.Priority(ecfTableReader.GetValue <string>("ContactPriority")));
Helper.SetParamValue(fbCommand, "@Order", FbDbType.SmallInt, ecfTableReader.GetValue <string>("Order"));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[INSERT ERROR] [SchuelerSorgebe] {e.Message}");
}
return(success > 0);
}
public static async Task <bool> AchievementType(FbConnection fbConnection, EcfTableReader ecfTableReader)
{
var success = 0;
var sql =
"UPDATE \"Leistungsarten\" " +
"SET \"Bezeichnung\" = @Name " +
"WHERE " +
" \"Kuerzel\" = @Code";
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, "@Code", FbDbType.VarChar, 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] [Leistungsarten] {e.Message}");
}
return(success > 0);
}
public static async Task <bool> StudentForeignLanguage(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId, int studentId, int languageId)
{
var success = 0;
int sqNo = int.TryParse(ecfTableReader.GetValue <string>("SequenceNo"), out sqNo) ? sqNo : -1;
if ((sqNo >= 1) && (sqNo <= 4))
{
var sqlPart =
$" \"Fremdsprache{sqNo}\" = @LanguageId, \"Fremdsprache{sqNo}Von\" = @FromLevel, " +
$" \"Fremdsprache{sqNo}Bis\" = @ToLevel ";
var sql =
"UPDATE \"Schueler\" " +
$"SET {sqlPart} " +
"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, studentId);
Helper.SetParamValue(fbCommand, "@LanguageId", FbDbType.VarChar, languageId);
Helper.SetParamValue(fbCommand, "@FromLevel", FbDbType.VarChar, ecfTableReader.GetValue <string>("FromLevel"));
Helper.SetParamValue(fbCommand, "@ToLevel", FbDbType.VarChar, ecfTableReader.GetValue <string>("ToLevel"));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[UPDATE ERROR] [Schueler] {e.Message}");
}
return(success > 0);
}
else
{
Console.WriteLine($"[UPDATE ERROR] [Schueler] SequenceNo out of bounds");
return(false);
}
}
private async Task <int> ImportSchoolClasses(FbConnection fbConnection, EcfTableReader ecfTableReader)
{
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));
}
}