public async static Task <bool> SaveSchuelerAsync(string sql, string id, SchuelerDLModel changedschueler)
{
using (NpgsqlConnection connection = new NpgsqlConnection(sql))
{
NpgsqlCommand command;
//Edit Schueler props [person]
connection.Open();
command =
new NpgsqlCommand
($"update person set vname = '{changedschueler.VName}', nname = '{changedschueler.NName}', " +
$"gender = '{changedschueler.Geschlecht.ToUpper()}', email = '{changedschueler.EmailAdresse}' where checkpersonnumber = '{id}'; ", connection);
int i = await command.ExecuteNonQueryAsync();
//Edit Schueler sstufe [schueler]
command =
new NpgsqlCommand
($"update schueler set sstufe = {changedschueler.Schuljahr} where skennzahl = '{id}'; ", connection);
i += await command.ExecuteNonQueryAsync();
connection.Close();
if (i == 2)
{
return(true);
}
return(false);
}
}
public async static Task <SchuelerDLModel> GetByIdSchuelerAsync(string sql, string id)
{
using (NpgsqlConnection connection = new NpgsqlConnection(sql))
{
NpgsqlCommand command;
//Edit Schueler props [person]
connection.Open();
command =
new NpgsqlCommand
($"select skennzahl, checkpersonnumber, nname, vname, gender, email, sstufe from schueler " +
$"join person on skennzahl = checkpersonnumber " +
$"where checkpersonnumber = '{id}' " +
$"order by skennzahl;", connection);
var dr = await command.ExecuteReaderAsync();
SchuelerDLModel editschueler = null;
while (dr.Read())
{
editschueler = new SchuelerDLModel(/*int.Parse(dr[0].ToString()), int.Parse(dr[1].ToString()),*/ dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), int.Parse(dr[6].ToString()));
}
editschueler.Faecher = await LoadSchuelerFaecherAsync(sql, id);
connection.Close();
return(editschueler);
}
}
public async Task <ActionResult <SchuelerModel> > GetSchueler(string id)
{
try
{
await Task.Delay(10);
SchuelerDLModel resultschueler = await SchuelerProcessor.GetByIdSchuelerAsync(ConnectionString, id);
SchuelerModel schueler =
new SchuelerModel
{
Checkpersonnumber = resultschueler.Checkpersonnumber,
NName = resultschueler.NName,
VName = resultschueler.VName,
Geschlecht = resultschueler.Geschlecht,
EmailAdresse = resultschueler.EmailAdresse,
Schuljahr = resultschueler.Schuljahr
};
return(schueler);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public async Task <IActionResult> Details(string id)
{
try
{
SchuelerDLModel resultschueler = await SchuelerProcessor.GetByIdSchuelerAsync(ConnectionString, id);
List <FaecherModel> ViewSchuelerFaecher = new List <FaecherModel>();
foreach (var item in resultschueler.Faecher)
{
ViewSchuelerFaecher.Add(
new FaecherModel()
{
SkennZahl = item.Skennzahl, Fachbezeichnung = item.Fachbezeichnung
});
}
SchuelerModel schueler =
new SchuelerModel
{
Checkpersonnumber = resultschueler.Checkpersonnumber,
NName = resultschueler.NName,
VName = resultschueler.VName,
Geschlecht = resultschueler.Geschlecht,
EmailAdresse = resultschueler.EmailAdresse,
Schuljahr = resultschueler.Schuljahr,
Faecher = ViewSchuelerFaecher
};
return(View(schueler));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public async Task <IActionResult> Edit(string id, SchuelerDLModel changedschueler)
{
try
{
var f = await SchuelerProcessor.SaveSchuelerAsync(ConnectionString, id, changedschueler);
return(RedirectToAction("Index"));
}
catch (Exception ex)
{
return(View(ex.Message));
}
}
public async Task <string> PutSchueler(string id, SchuelerDLModel schueler)
{
try
{
await Task.Delay(10);
await SchuelerProcessor.SaveSchuelerAsync(ConnectionString, id, schueler);
return("schueler_updated");
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public async Task <IActionResult> Edit(string id)
{
try
{
SchuelerDLModel resultschueler = await SchuelerProcessor.GetByIdSchuelerAsync(ConnectionString, id);
SchuelerModel schueler =
new SchuelerModel
{
Checkpersonnumber = resultschueler.Checkpersonnumber,
NName = resultschueler.NName,
VName = resultschueler.VName,
Geschlecht = resultschueler.Geschlecht,
EmailAdresse = resultschueler.EmailAdresse,
Schuljahr = resultschueler.Schuljahr
};
return(View(schueler));
}
catch (Exception ex)
{
return(View(ex.Message));
}
}