Skip to content

Instantly share code, notes, and snippets.

@d630

d630/DB.cs

Last active July 6, 2018 01:33
Show Gist options
  • Save d630/33a31ef671970a21a921e1cf6e3df829 to your computer and use it in GitHub Desktop.
Save d630/33a31ef671970a21a921e1cf6e3df829 to your computer and use it in GitHub Desktop.
Download ZIP
Übung 2: C# + Windows Forms + MySQL + XML (without DataGridView and MySqlDataAdapter)
Raw
DB.cs
using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
// TODO: avoid injections
// TODO: Better exception handling
namespace PersonalDB
{
class DB
{
private MySqlConnection dbConnection = null;
private MySqlCommand comm = null;
private MySqlDataReader reader = null;
private string[] connArgs = {
"persist security info = false",
"server = 127.0.0.1",
"database = ihk1",
"uid = root",
"password = root",
"encrypt = false"
};
private void dbOpen()
{
this.dbConnection = new MySqlConnection(string.Join(";", this.connArgs));
this.dbConnection.Open();
}
private void dbClose()
{
this.dbConnection.Close();
}
private void dbComm()
{
comm = this.dbConnection.CreateCommand();
}
private void dbCommExecute()
{
comm.ExecuteNonQuery();
}
private void dbReaderExecute()
{
reader = comm.ExecuteReader();
}
public List<Mitarbeiter> selectMitarbeiter(int ma_id = -2)
{
List<Mitarbeiter> li = new List<Mitarbeiter>();
try
{
dbOpen();
dbComm();
if (ma_id == -1)
{
comm.CommandText = "SELECT * FROM mitarbeiter WHERE ma_id = (SELECT MAX(ma_id) FROM mitarbeiter);";
}
else if (ma_id < -1)
{
comm.CommandText = "SELECT * FROM mitarbeiter;";
}
else
{
comm.CommandText = $"SELECT * FROM mitarbeiter WHERE ma_id = {ma_id};";
}
dbReaderExecute();
while (reader.Read())
{
li.Add(new Mitarbeiter(
reader.IsDBNull(0) ? -1 : reader.GetInt32(0),
reader.IsDBNull(1) ? "NULL" : reader.GetString(1),
reader.IsDBNull(2) ? "NULL" : reader.GetString(2),
reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3),
reader.IsDBNull(4) ? -1 : reader.GetInt32(4),
reader.IsDBNull(5) ? -1 : reader.GetInt32(5)));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (reader != null)
reader.Close();
if (dbConnection != null)
dbClose();
}
return li;
}
public bool insertMitarbeiter(string ma_id, string nachname, string vorname,
string geb_datum, string tagesarbeitszeit,
string urlaubsanspruchjahr)
{
bool ret = true;
if (ma_id == "")
ma_id = "NULL";
if (nachname == "")
nachname = "NULL";
if (vorname == "")
vorname = "NULL";
if (geb_datum == "")
geb_datum = "NULL";
if (tagesarbeitszeit == "")
tagesarbeitszeit = "NULL";
if (urlaubsanspruchjahr == "")
urlaubsanspruchjahr = "NULL";
try
{
dbOpen();
dbComm();
comm.CommandText = $"INSERT INTO mitarbeiter VALUES({ma_id}, '{nachname}', '{vorname}', '{geb_datum}', {tagesarbeitszeit}, {urlaubsanspruchjahr});";
dbCommExecute();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
ret = false;
}
finally
{
if (dbConnection != null)
dbClose();
}
return ret;
}
public bool updateMitarbeiter(string ma_id, string nachname, string vorname,
string geb_datum, string tagesarbeitszeit,
string urlaubsanspruchjahr)
{
bool ret = true;
if (ma_id == "" || ma_id.ToLower() == "null")
return false;
if (nachname == "")
nachname = "NULL";
if (vorname == "")
vorname = "NULL";
if (geb_datum == "")
geb_datum = "NULL";
if (tagesarbeitszeit == "")
tagesarbeitszeit = "NULL";
if (urlaubsanspruchjahr == "")
urlaubsanspruchjahr = "NULL";
try
{
dbOpen();
dbComm();
comm.CommandText = $"UPDATE mitarbeiter SET nachname = '{nachname}', vorname = '{vorname}', geb_datum = '{geb_datum}', tagesarbeitszeit = {tagesarbeitszeit}, urlaubsanspruchjahr = {urlaubsanspruchjahr} WHERE ma_id = {ma_id};";
dbCommExecute();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
ret = false;
}
finally
{
if (dbConnection != null)
dbClose();
}
return ret;
}
public bool deleteMitarbeiter(string ma_id)
{
bool ret = true;
if (ma_id == "" || ma_id.ToLower() == "null")
return false;
try
{
dbOpen();
dbComm();
comm.CommandText = $"DELETE FROM mitarbeiter WHERE ma_id = {ma_id};";
dbCommExecute();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
ret = false;
}
finally
{
if (dbConnection != null)
dbClose();
}
return ret;
}
public List<Einsatz> selectEinsatz(int e_id = -2)
{
List<Einsatz> li = new List<Einsatz>();
try
{
dbOpen();
dbComm();
if (e_id == -1)
{
comm.CommandText = "SELECT * FROM einsatz WHERE e_id = (SELECT MAX(e_id) FROM einsatz);";
}
else if (e_id < -1)
{
comm.CommandText = "SELECT * FROM einsatz;";
}
else
{
comm.CommandText = $"SELECT * FROM einsatz WHERE e_id = {e_id};";
}
dbReaderExecute();
while (reader.Read())
{
li.Add(new Einsatz(
reader.IsDBNull(0) ? -1 : reader.GetInt32(0),
reader.IsDBNull(1) ? -1 : reader.GetInt32(1),
reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2),
reader.IsDBNull(3) ? TimeSpan.MinValue : reader.GetTimeSpan(3),
reader.IsDBNull(4) ? TimeSpan.MinValue : reader.GetTimeSpan(4)));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (reader != null)
reader.Close();
if (dbConnection != null)
dbClose();
}
return li;
}
public bool insertEinsatz(string e_id, string ma_id, string datum, string einsatzvon_zeit,
string einsatzbis_zeit)
{
bool ret = true;
if (e_id == "")
e_id = "NULL";
if (ma_id == "" || ma_id.ToLower() == "null")
return false;
if (datum == "")
datum = "NULL";
if (einsatzvon_zeit == "")
einsatzvon_zeit = "NULL";
if (einsatzbis_zeit == "")
einsatzbis_zeit = "NULL";
try
{
dbOpen();
dbComm();
comm.CommandText = $"INSERT INTO einsatz VALUES({e_id}, {ma_id}, '{datum}', '{einsatzvon_zeit}', '{einsatzbis_zeit}');";
dbCommExecute();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
ret = false;
}
finally
{
if (dbConnection != null)
dbClose();
}
return ret;
}
public bool updateEinsatz(string e_id, string ma_id, string datum, string einsatzvon_zeit,
string einsatzbis_zeit)
{
bool ret = true;
if (e_id == "" || e_id.ToLower() == "null")
return false;
if (ma_id == "")
ma_id = "NULL";
if (datum == "")
datum = "NULL";
if (einsatzvon_zeit == "")
einsatzvon_zeit = "NULL";
if (einsatzbis_zeit == "")
einsatzbis_zeit = "NULL";
try
{
dbOpen();
dbComm();
comm.CommandText = $"UPDATE einsatz SET ma_id = {ma_id}, datum = '{datum}', einsatzvon_zeit = '{einsatzvon_zeit}', einsatzbis_zeit = '{einsatzbis_zeit}' WHERE e_id = {e_id};";
dbCommExecute();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
ret = false;
}
finally
{
if (dbConnection != null)
dbClose();
}
return ret;
}
public bool deleteEinsatz(string e_id)
{
bool ret = true;
if (e_id == "" || e_id.ToLower() == "null")
return false;
try
{
dbOpen();
dbComm();
comm.CommandText = $"DELETE FROM einsatz WHERE e_id = {e_id};";
dbCommExecute();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
ret = false;
}
finally
{
if (dbConnection != null)
dbClose();
}
return ret;
}
public List<Fehlgrund> selectFehlgrund(int fg_id = -2)
{
List<Fehlgrund> li = new List<Fehlgrund>();
try
{
dbOpen();
dbComm();
if (fg_id == -1)
{
comm.CommandText = "SELECT * FROM fehlgrund WHERE fg_id = (SELECT MAX(fg_id) FROM fehlgrund);";
}
else if (fg_id < -1)
{
comm.CommandText = "SELECT * FROM fehlgrund;";
}
else
{
comm.CommandText = $"SELECT * FROM fehlgrund WHERE fg_id = {fg_id};";
}
dbReaderExecute();
while (reader.Read())
{
li.Add(new Fehlgrund(
reader.IsDBNull(0) ? -1 : reader.GetInt32(0),
reader.IsDBNull(1) ? "NULL" : reader.GetString(1)));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (reader != null)
reader.Close();
if (dbConnection != null)
dbClose();
}
return li;
}
public bool insertFehlgrund(string fg_id, string name)
{
bool ret = true;
if (fg_id == "")
fg_id = "NULL";
if (name == "")
name = "NULL";
try
{
dbOpen();
dbComm();
comm.CommandText = $"INSERT INTO fehlgrund VALUES({fg_id}, '{name}');";
dbCommExecute();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
ret = false;
}
finally
{
if (dbConnection != null)
dbClose();
}
return ret;
}
public bool updateFehlgrund(string fg_id, string name)
{
bool ret = true;
if (fg_id == "" || fg_id.ToLower() == "null")
return false;
if (name == "")
name = "NULL";
try
{
dbOpen();
dbComm();
comm.CommandText = $"UPDATE fehlgrund SET name = '{name}' WHERE fg_id = {fg_id};";
dbCommExecute();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
ret = false;
}
finally
{
if (dbConnection != null)
dbClose();
}
return ret;
}
public bool deleteFehlgrund(string fg_id)
{
bool ret = true;
if (fg_id == "" || fg_id.ToLower() == "null")
return false;
try
{
dbOpen();
dbComm();
comm.CommandText = $"DELETE FROM fehlgrund WHERE fg_id = {fg_id};";
dbCommExecute();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
ret = false;
}
finally
{
if (dbConnection != null)
dbClose();
}
return ret;
}
public List<Fehlzeit> selectFehlzeit(int fz_id = -2)
{
List<Fehlzeit> li = new List<Fehlzeit>();
try
{
dbOpen();
dbComm();
if (fz_id == -1)
{
comm.CommandText = "SELECT * FROM fehlzeit WHERE fz_id = (SELECT MAX(fz_id) FROM fehlzeit);";
}
else if (fz_id < -1)
{
comm.CommandText = "SELECT * FROM fehlzeit;";
}
else
{
comm.CommandText = $"SELECT * FROM fehlzeit WHERE fz_id = {fz_id};";
}
dbReaderExecute();
while (reader.Read())
{
li.Add(new Fehlzeit(
reader.IsDBNull(0) ? -1 : reader.GetInt32(0),
reader.IsDBNull(1) ? -1 : reader.GetInt32(1),
reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2),
reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3),
reader.IsDBNull(4) ? -1 : reader.GetInt32(4),
reader.IsDBNull(5) ? -1 : reader.GetInt32(5)));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (reader != null)
reader.Close();
if (dbConnection != null)
dbClose();
}
return li;
}
public bool insertFehlzeit(string fz_id, string ma_id, string von_datum,
string bis_datum, string fg_id, string fehltage)
{
bool ret = true;
if (fz_id == "")
fz_id = "NULL";
if (ma_id == "" || ma_id.ToLower() == "null")
return false;
if (von_datum == "")
von_datum = "NULL";
if (bis_datum == "")
bis_datum = "NULL";
if (fg_id == "" || fg_id.ToLower() == "null")
return false;
if (fehltage == "")
fehltage = "NULL";
try
{
dbOpen();
dbComm();
comm.CommandText = $"INSERT INTO fehlzeit VALUES({fz_id}, {ma_id}, '{von_datum}', '{bis_datum}', {fg_id}, {fehltage});";
dbCommExecute();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
ret = false;
}
finally
{
if (dbConnection != null)
dbClose();
}
return ret;
}
public bool updateFehlzeit(string fz_id, string ma_id, string von_datum, string bis_datum,
string fg_id, string fehltage)
{
bool ret = true;
if (fz_id == "" || fz_id.ToLower() == "null")
return false;
if (ma_id == "")
ma_id = "NULL";
if (von_datum == "")
von_datum = "NULL";
if (bis_datum == "")
bis_datum = "NULL";
if (fg_id == "")
fg_id = "NULL";
if (fehltage == "")
fehltage = "NULL";
try
{
dbOpen();
dbComm();
comm.CommandText = $"UPDATE fehlzeit SET ma_id = {ma_id}, von_datum = '{von_datum}', bis_datum = '{bis_datum}', fg_id = {fg_id}, fehltage = {fehltage} WHERE fz_id = {fz_id};";
dbCommExecute();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
ret = false;
}
finally
{
if (dbConnection != null)
dbClose();
}
return ret;
}
public bool deleteFehlzeit(string fz_id)
{
bool ret = true;
if (fz_id == "" || fz_id.ToLower() == "null")
return false;
try
{
dbOpen();
dbComm();
comm.CommandText = $"DELETE FROM fehlzeit WHERE fz_id = {fz_id};";
dbCommExecute();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
ret = false;
}
finally
{
if (dbConnection != null)
dbClose();
}
return ret;
}
}
}
Raw
Einsatz.cs
using System;
namespace PersonalDB
{
public class Einsatz
{
public Einsatz(int e_id, int ma_id, DateTime datum, TimeSpan einsatzvon_zeit,
TimeSpan einsatzbis_zeit)
{
this.E_id = e_id;
this.Ma_id = ma_id;
this.Datum = datum;
this.Einsatzvon_zeit = einsatzvon_zeit;
this.Einsatzbis_zeit = einsatzbis_zeit;
}
public int E_id { get; set; }
public int Ma_id { get; set; }
public DateTime Datum { get; set; }
public TimeSpan Einsatzvon_zeit { get; set; }
public TimeSpan Einsatzbis_zeit { get; set; }
}
}
Raw
Fehlgrund.cs
namespace PersonalDB
{
public class Fehlgrund
{
public Fehlgrund(int fg_id, string name)
{
this.Fg_id = fg_id;
this.Name = name;
}
public int Fg_id { get; set; }
public string Name { get; set; }
}
}
Raw
Fehlzeit.cs
using System;
namespace PersonalDB
{
class Fehlzeit
{
public Fehlzeit(int fz_id, int ma_id, DateTime von_datum, DateTime bis_datum,
int fg_id, int fehltage)
{
this.Fz_id = fz_id;
this.Ma_id = ma_id;
this.Von_datum = von_datum;
this.Bis_datum = bis_datum;
this.Fg_id = fg_id;
this.Fehltage = fehltage;
}
public int Fz_id { get; set; }
public int Ma_id { get; set; }
public DateTime Von_datum { get; set; }
public DateTime Bis_datum { get; set; }
public int Fg_id { get; set; }
public int Fehltage { get; set; }
}
}
Raw
Form0.cs
using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.Windows.Forms;
namespace PersonalDB
{
// abstract
public partial class Form0 : Form
{
protected List<Form0> forms;
protected bool[] inputValidated;
public Form0()
{
//InitializeComponent();
}
public virtual string OperationDesc { get; set; }
//public virtual bool InputValidated { get; set; }
public virtual void setEmpty()
{
}
public virtual bool validateTextBox(TextBox tb, string type)
{
string pat;
switch (type)
{
case "date":
pat = @"^\d{4}-\d{2}-\d{2}$|^$|null";
break;
case "digit":
pat = @"^\d+$|^$|null";
break;
case "double":
pat = @"^\d+\.\d+|^$|null$";
break;
case "time":
pat = @"^\d{2}:\d{2}:\d{2}$|^$|null";
break;
default:
return false;
}
Regex rgx = new Regex(pat, RegexOptions.IgnoreCase);
return rgx.IsMatch(tb.Text);
}
}
}
Raw
Form1.cs
using System;
using System.Collections.Generic;
using System.Windows.Forms;
namespace PersonalDB
{
public partial class Form1 : Form0
{
public Form1()
{
InitializeComponent();
forms = new List<Form0>();
fillForms();
}
private void Form1_Load(object sender, EventArgs e)
{
fillListBox1();
}
private void fillListBox1()
{
listBox1.Items.Clear();
foreach (Form0 f in forms)
{
if (f.OperationDesc != null)
this.listBox1.Items.Add(f.OperationDesc);
}
}
private void fillForms()
{
forms.Clear();
forms.Add(new Form2());
forms.Add(new Form3());
forms.Add(new Form4());
forms.Add(new Form5());
forms.Add(new Form6());
}
private void addNewForm(Form0 f, int index)
{
forms.RemoveAt(index);
forms.Insert(index, f);
}
private void button1_Click(object sender, EventArgs e)
{
if (listBox1.Items.Count == 0 || listBox1.SelectedIndex == -1)
{
MessageBox.Show("No operation selected");
}
else
{
Form0 copy = forms[listBox1.SelectedIndex];
try
{
forms[listBox1.SelectedIndex].ShowDialog();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
copy.setEmpty();
addNewForm(copy, listBox1.SelectedIndex);
}
}
}
private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
{
}
}
}
Raw
Form2.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
namespace PersonalDB
{
public partial class Form2 : Form0
{
DB db = new DB();
XmlData xml = new XmlData();
List<Fehlgrund> liFehlgrund = new List<Fehlgrund>();
public Form2()
{
InitializeComponent();
this.OperationDesc = "Fehlgruende bearbeiten";
this.inputValidated = Enumerable.Repeat(true, 2).ToArray();
liFehlgrund = db.selectFehlgrund();
}
//public override string OperationDesc { get; set; }
private void Form2_Load(object sender, EventArgs e)
{
fillListBox1();
}
private void fillListBox1()
{
listBox1.Items.Clear();
foreach (Fehlgrund f in liFehlgrund)
{
listBox1.Items.Add($"{f.Fg_id}, {f.Name}");
}
}
private void addLast()
{
List<Fehlgrund> liLast = db.selectFehlgrund(-1);
if (liLast.Count == 0)
{
MessageBox.Show("Error: Could not adapt changes.");
return;
}
liFehlgrund.Add(liLast[0]);
listBox1.Items.Add($"{liLast[0].Fg_id}, {liLast[0].Name}");
}
public override void setEmpty()
{
textBox1.Text = string.Empty;
textBox2.Text = string.Empty;
listBox1.SelectedIndex = -1;
}
private void button1_Click(object sender, EventArgs e)
{
setEmpty();
}
private void button2_Click(object sender, EventArgs e)
{
if (listBox1.SelectedIndex != -1)
{
MessageBox.Show("Error: Do not select an entry in the List box.");
return;
}
if (!this.inputValidated.All(x => x))
{
MessageBox.Show("Error: Input is invalid.");
return;
}
if (!db.insertFehlgrund(textBox1.Text, textBox2.Text))
return;
addLast();
listBox1.SelectedIndex = listBox1.Items.Count - 1;
}
private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
{
if (listBox1.SelectedIndex == -1)
{
setEmpty();
return;
}
int index = listBox1.SelectedIndex;
textBox1.Text = liFehlgrund[index].Fg_id.ToString();
textBox2.Text = liFehlgrund[index].Name;
}
private void button3_Click(object sender, EventArgs e)
{
if (listBox1.SelectedIndex == -1)
return;
if (!this.inputValidated.All(x => x))
{
MessageBox.Show("Error: Input is invalid.");
return;
}
if (!db.updateFehlgrund(textBox1.Text, textBox2.Text))
return;
int index_old = listBox1.SelectedIndex;
updateAt(listBox1.SelectedIndex, int.Parse(textBox1.Text));
listBox1.SelectedIndex = index_old;
}
private void updateAt(int index, int fg_id)
{
List<Fehlgrund> liUpdated = db.selectFehlgrund(fg_id);
if (liUpdated.Count == 0)
{
MessageBox.Show("Error: Could not adapt changes.");
return;
}
liFehlgrund.RemoveAt(index);
liFehlgrund.Insert(index, liUpdated[0]);
listBox1.Items.RemoveAt(index);
listBox1.Items.Insert(index, $"{liUpdated[0].Fg_id}, {liUpdated[0].Name}");
}
private void button4_Click(object sender, EventArgs e)
{
if (listBox1.SelectedIndex == -1)
return;
if (!this.inputValidated.All(x => x))
{
MessageBox.Show("Error: Input is invalid.");
return;
}
if (!db.deleteFehlgrund(textBox1.Text))
return;
int index_old = listBox1.SelectedIndex;
deleteAt(listBox1.SelectedIndex);
listBox1.SelectedIndex = index_old - 1 == -1 ? listBox1.Items.Count - 1 : index_old - 1;
}
private void deleteAt(int index)
{
liFehlgrund.RemoveAt(index);
listBox1.Items.RemoveAt(index);
}
private void textBox2_MouseMove(object sender, MouseEventArgs e)
{
toolTip1.SetToolTip(textBox2, "string");
}
private void button5_Click(object sender, EventArgs e)
{
List<Fehlgrund> liImported = this.xml.parseFehlgrund();
if (liImported.Count == 0)
return;
foreach (Fehlgrund f in liImported)
{
liFehlgrund.Add(f);
this.db.insertFehlgrund(f.Fg_id.ToString(), f.Name);
}
fillListBox1();
listBox1.SelectedIndex = listBox1.Items.Count - 1;
}
private void button6_Click(object sender, EventArgs e)
{
if (listBox1.Items.Count == 0)
return;
this.xml.writeFehlgrund(liFehlgrund);
}
}
}
Raw
Form3.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
namespace PersonalDB
{
public partial class Form3 : Form0
{
DB db = new DB();
XmlData xml = new XmlData();
List<Mitarbeiter> liMitarbeiter = new List<Mitarbeiter>();
public Form3()
{
InitializeComponent();
this.OperationDesc = "Mitarbeiter bearbeiten";
this.inputValidated = Enumerable.Repeat(true, 6).ToArray();
liMitarbeiter = db.selectMitarbeiter();
}
//public override string OperationDesc { get; set; }
private void Form3_Load(object sender, EventArgs e)
{
fillListBox1();
}
private void fillListBox1()
{
listBox1.Items.Clear();
foreach (Mitarbeiter m in liMitarbeiter)
{
listBox1.Items.Add($"{m.Nachname}, {m.Vorname}");
}
}
private void addLast()
{
List<Mitarbeiter> liLast = db.selectMitarbeiter(-1);
if (liLast.Count == 0)
{
MessageBox.Show("Error: Could not adapt changes.");
return;
}
liMitarbeiter.Add(liLast[0]);
listBox1.Items.Add($"{liLast[0].Nachname}, {liLast[0].Vorname}");
}
private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
{
if (listBox1.SelectedIndex == -1)
{
setEmpty();
return;
}
int index = listBox1.SelectedIndex;
textBox1.Text = liMitarbeiter[index].Ma_id.ToString();
textBox2.Text = liMitarbeiter[index].Nachname;
textBox3.Text = liMitarbeiter[index].Vorname;
textBox4.Text = liMitarbeiter[index].Geb_datum.ToString("yyyy-MM-dd");
textBox5.Text = liMitarbeiter[index].Tagesarbeitszeit.ToString();
textBox6.Text = liMitarbeiter[index].Urlaubsanspruchjahr.ToString();
}
public override void setEmpty()
{
textBox1.Text = string.Empty;
textBox2.Text = string.Empty;
textBox3.Text = string.Empty;
textBox4.Text = string.Empty;
textBox5.Text = string.Empty;
textBox6.Text = string.Empty;
listBox1.SelectedIndex = -1;
}
private void button1_Click(object sender, EventArgs e)
{
setEmpty();
}
private void button2_Click(object sender, EventArgs e)
{
if (listBox1.SelectedIndex != -1)
{
MessageBox.Show("Error: Do not select an entry in the List box.");
return;
}
if (!this.inputValidated.All(x => x))
{
MessageBox.Show("Error: Input is invalid.");
return;
}
if (!db.insertMitarbeiter(textBox1.Text, textBox2.Text, textBox3.Text,
textBox4.Text, textBox5.Text, textBox6.Text))
return;
addLast();
listBox1.SelectedIndex = listBox1.Items.Count - 1;
}
private void button3_Click(object sender, EventArgs e)
{
if (listBox1.SelectedIndex == -1)
return;
if (!this.inputValidated.All(x => x))
{
MessageBox.Show("Error: Input is invalid.");
return;
}
if (!db.updateMitarbeiter(textBox1.Text, textBox2.Text, textBox3.Text,
textBox4.Text, textBox5.Text, textBox6.Text))
return;
int index_old = listBox1.SelectedIndex;
updateAt(listBox1.SelectedIndex, int.Parse(textBox1.Text));
listBox1.SelectedIndex = index_old;
}
private void updateAt(int index, int fg_id)
{
List<Mitarbeiter> liUpdated = db.selectMitarbeiter(fg_id);
if (liUpdated.Count == 0)
{
MessageBox.Show("Error: Could not adapt changes.");
return;
}
liMitarbeiter.RemoveAt(index);
liMitarbeiter.Insert(index, liUpdated[0]);
listBox1.Items.RemoveAt(index);
listBox1.Items.Insert(index, $"{liUpdated[0].Nachname}, {liUpdated[0].Vorname}");
}
private void button4_Click(object sender, EventArgs e)
{
if (listBox1.SelectedIndex == -1)
return;
if (!this.inputValidated.All(x => x))
{
MessageBox.Show("Error: Input is invalid.");
return;
}
if (!db.deleteMitarbeiter(textBox1.Text))
return;
int index_old = listBox1.SelectedIndex;
deleteAt(listBox1.SelectedIndex);
listBox1.SelectedIndex = index_old - 1 == -1 ? listBox1.Items.Count - 1 : index_old - 1;
}
private void deleteAt(int index)
{
liMitarbeiter.RemoveAt(index);
listBox1.Items.RemoveAt(index);
}
private void textBox4_Leave(object sender, EventArgs e)
{
this.inputValidated[3] = validateTextBox(textBox4, "date");
if (!this.inputValidated[3])
MessageBox.Show("Error: Invalid format");
}
private void textBox5_Leave(object sender, EventArgs e)
{
this.inputValidated[4] = validateTextBox(textBox5, "digit");
if (!this.inputValidated[4])
MessageBox.Show("Error: Invalid format");
}
private void textBox6_Leave(object sender, EventArgs e)
{
this.inputValidated[5] = validateTextBox(textBox6, "digit");
if (!this.inputValidated[5])
MessageBox.Show("Error: Invalid format");
}
private void button5_Click(object sender, EventArgs e)
{
List<Mitarbeiter> liImported = this.xml.parseMitarbeiter();
if (liImported.Count == 0)
return;
foreach (Mitarbeiter m in liImported)
{
liMitarbeiter.Add(m);
this.db.insertMitarbeiter(m.Ma_id.ToString(), m.Nachname, m.Vorname,
m.Geb_datum.ToString("yyyy-MM-dd"), m.Tagesarbeitszeit.ToString(), m.Urlaubsanspruchjahr.ToString());
}
fillListBox1();
listBox1.SelectedIndex = listBox1.Items.Count - 1;
}
private void button6_Click(object sender, EventArgs e)
{
if (listBox1.Items.Count == 0)
return;
this.xml.writeMitarbeiter(liMitarbeiter);
}
}
}
Raw
Form4.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
namespace PersonalDB
{
public partial class Form4 : Form0
{
DB db = new DB();
XmlData xml = new XmlData();
List<Einsatz> liEinsatz = new List<Einsatz>();
List<Mitarbeiter> liMitarbeiter = new List<Mitarbeiter>();
public Form4()
{
InitializeComponent();
this.OperationDesc = "Einsatzzeiten bearbeiten";
this.inputValidated = Enumerable.Repeat(true, 5).ToArray();
liEinsatz = db.selectEinsatz();
liMitarbeiter = db.selectMitarbeiter();
}
//public override string OperationDesc { get; set; }
private void Form4_Load(object sender, EventArgs e)
{
fillListBox1();
fillComboBox1();
}
private void fillListBox1()
{
listBox1.Items.Clear();
foreach (Einsatz e in liEinsatz)
{
listBox1.Items.Add($"{e.E_id}, {e.Ma_id}, {e.Datum.ToString("yyyy-MM-dd")}");
}
}
private void fillComboBox1()
{
comboBox1.Items.Clear();
foreach (Mitarbeiter m in liMitarbeiter)
{
comboBox1.Items.Add($"{m.Ma_id}|{m.Nachname}|{m.Vorname}");
}
}
private void addLast()
{
List<Einsatz> liLast = db.selectEinsatz(-1);
if (liLast.Count == 0)
{
MessageBox.Show("Error: Could not adapt changes.");
return;
}
liEinsatz.Add(liLast[0]);
listBox1.Items.Add($"{liLast[0].E_id}, {liLast[0].Ma_id}, {liLast[0].Datum.ToString("yyyy-MM-dd")}");
}
public override void setEmpty()
{
textBox1.Text = string.Empty;
//textBox2.Text = string.Empty;
textBox3.Text = string.Empty;
textBox4.Text = string.Empty;
textBox5.Text = string.Empty;
listBox1.SelectedIndex = -1;
comboBox1.SelectedIndex = -1;
}
private void button1_Click(object sender, EventArgs e)
{
setEmpty();
}
private void button2_Click(object sender, EventArgs e)
{
if (listBox1.SelectedIndex != -1)
{
MessageBox.Show("Error: Do not select an entry in the List box.");
return;
}
if (!this.inputValidated.All(x => x))
{
MessageBox.Show("Error: Input is invalid.");
}
if (!db.insertEinsatz(textBox1.Text, comboBox1.SelectedIndex == -1 ? "" : liMitarbeiter[comboBox1.SelectedIndex].Ma_id.ToString(), textBox3.Text, textBox4.Text, textBox5.Text))
return;
addLast();
listBox1.SelectedIndex = listBox1.Items.Count - 1;
}
private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
{
if (listBox1.SelectedIndex == -1)
{
setEmpty();
return;
}
int index = listBox1.SelectedIndex;
textBox1.Text = liEinsatz[index].E_id.ToString();
//textBox2.Text = liEinsatz[index].Ma_id.ToString();
textBox3.Text = liEinsatz[index].Datum.ToString("yyyy-MM-dd");
textBox4.Text = liEinsatz[index].Einsatzvon_zeit.ToString();
textBox5.Text = liEinsatz[index].Einsatzbis_zeit.ToString();
comboBox1.SelectedIndex = liMitarbeiter.FindIndex(
x => x.Ma_id == liEinsatz[index].Ma_id);
}
private void button3_Click(object sender, EventArgs e)
{
if (listBox1.SelectedIndex == -1)
return;
if (!this.inputValidated.All(x => x))
{
MessageBox.Show("Error: Input is invalid.");
return;
}
if (!db.updateEinsatz(textBox1.Text, comboBox1.SelectedIndex == -1 ? "" : liMitarbeiter[comboBox1.SelectedIndex].Ma_id.ToString(), textBox3.Text, textBox4.Text, textBox5.Text))
return;
int index_old = listBox1.SelectedIndex;
updateAt(listBox1.SelectedIndex, int.Parse(textBox1.Text));
listBox1.SelectedIndex = index_old;
}
private void updateAt(int index, int fg_id)
{
List<Einsatz> liUpdated = db.selectEinsatz(fg_id);
if (liUpdated.Count == 0)
{
MessageBox.Show("Error: Could not adapt changes.");
return;
}
liEinsatz.RemoveAt(index);
liEinsatz.Insert(index, liUpdated[0]);
listBox1.Items.RemoveAt(index);
listBox1.Items.Insert(index, $"{liUpdated[0].E_id}, {liUpdated[0].Ma_id}, {liUpdated[0].Datum.ToString("yyyy-MM-dd")}");
}
private void button4_Click(object sender, EventArgs e)
{
if (listBox1.SelectedIndex == -1)
return;
if (!this.inputValidated.All(x => x))
{
MessageBox.Show("Error: Input is invalid.");
return;
}
if (!db.deleteEinsatz(textBox1.Text))
return;
int index_old = listBox1.SelectedIndex;
deleteAt(listBox1.SelectedIndex);
listBox1.SelectedIndex = index_old - 1 == -1 ? listBox1.Items.Count - 1 : index_old - 1;
}
private void deleteAt(int index)
{
liEinsatz.RemoveAt(index);
listBox1.Items.RemoveAt(index);
}
private void textBox3_Leave(object sender, EventArgs e)
{
this.inputValidated[2] = validateTextBox(textBox3, "date");
if (!this.inputValidated[2])
MessageBox.Show("Error: Invalid format");
}
private void textBox4_Leave(object sender, EventArgs e)
{
this.inputValidated[3] = validateTextBox(textBox4, "time");
if (!this.inputValidated[3])
MessageBox.Show("Error: Invalid format");
}
private void textBox5_Leave(object sender, EventArgs e)
{
this.inputValidated[4] = validateTextBox(textBox5, "time");
if (!this.inputValidated[4])
MessageBox.Show("Error: Invalid format");
}
private void button5_Click(object sender, EventArgs e)
{
List<Einsatz> liImported = this.xml.parseEinsatz();
if (liImported.Count == 0)
return;
foreach (Einsatz ei in liImported)
{
liEinsatz.Add(ei);
this.db.insertEinsatz(ei.E_id.ToString(), ei.Ma_id.ToString(), ei.Datum.ToString("yyyy-MM-dd"), ei.Einsatzvon_zeit.ToString(), ei.Einsatzbis_zeit.ToString());
}
fillListBox1();
listBox1.SelectedIndex = listBox1.Items.Count - 1;
}
private void button6_Click(object sender, EventArgs e)
{
if (listBox1.Items.Count == 0)
return;
this.xml.writeEinsatz(liEinsatz);
}
}
}
Raw
Form5.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
namespace PersonalDB
{
public partial class Form5 : Form0
{
DB db = new DB();
XmlData xml = new XmlData();
List<Fehlzeit> liFehlzeit = new List<Fehlzeit>();
List<Mitarbeiter> liMitarbeiter = new List<Mitarbeiter>();
List<Fehlgrund> liFehlgrund = new List<Fehlgrund>();
public Form5()
{
InitializeComponent();
this.OperationDesc = "Fehlzeiten bearbeiten";
this.inputValidated = Enumerable.Repeat(true, 6).ToArray();
liFehlzeit = db.selectFehlzeit();
liMitarbeiter = db.selectMitarbeiter();
liFehlgrund = db.selectFehlgrund();
}
//public override string OperationDesc { get; set; }
private void Form5_Load(object sender, EventArgs e)
{
fillListBox1();
fillComboBox1();
fillComboBox2();
}
private void fillListBox1()
{
listBox1.Items.Clear();
foreach (Fehlzeit f in liFehlzeit)
{
listBox1.Items.Add($"{f.Fz_id}, {f.Ma_id}, {f.Fg_id}");
}
}
private void fillComboBox1()
{
comboBox1.Items.Clear();
foreach (Mitarbeiter m in liMitarbeiter)
{
comboBox1.Items.Add($"{m.Ma_id}|{m.Nachname}|{m.Vorname}");
}
}
private void fillComboBox2()
{
comboBox2.Items.Clear();
foreach (Fehlgrund f in liFehlgrund)
{
comboBox2.Items.Add(f.Name);
}
}
private void addLast()
{
List<Fehlzeit> liLast = db.selectFehlzeit(-1);
if (liLast.Count == 0)
{
MessageBox.Show("Error: Could not adapt changes.");
return;
}
liFehlzeit.Add(liLast[0]);
listBox1.Items.Add($"{liLast[0].Fz_id}, {liLast[0].Ma_id}, {liLast[0].Fg_id}");
}
public override void setEmpty()
{
textBox1.Text = string.Empty;
//textBox2.Text = string.Empty;
textBox3.Text = string.Empty;
textBox4.Text = string.Empty;
//textBox5.Text = string.Empty;
textBox6.Text = string.Empty;
listBox1.SelectedIndex = -1;
comboBox1.SelectedIndex = -1;
comboBox2.SelectedIndex = -1;
}
private void button1_Click(object sender, EventArgs e)
{
setEmpty();
}
private void button2_Click(object sender, EventArgs e)
{
if (listBox1.SelectedIndex != -1)
{
MessageBox.Show("Error: Do not select an entry in the List box.");
return;
}
if (!this.inputValidated.All(x => x))
{
MessageBox.Show("Error: Input is invalid.");
return;
}
if (!db.insertFehlzeit(textBox1.Text, comboBox1.SelectedIndex == -1 ? "" : liMitarbeiter[comboBox1.SelectedIndex].Ma_id.ToString(), textBox3.Text, textBox4.Text, comboBox2.SelectedIndex == -1 ? "" : liFehlgrund[comboBox2.SelectedIndex].Fg_id.ToString(), textBox6.Text))
return;
addLast();
listBox1.SelectedIndex = listBox1.Items.Count - 1;
}
private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
{
if (listBox1.SelectedIndex == -1)
{
setEmpty();
return;
}
int index = listBox1.SelectedIndex;
textBox1.Text = liFehlzeit[index].Fz_id.ToString();
//textBox2.Text = liFehlzeit[index].Ma_id.ToString();
textBox3.Text = liFehlzeit[index].Von_datum.ToString("yyyy-MM-dd");
textBox4.Text = liFehlzeit[index].Bis_datum.ToString("yyyy-MM-dd");
//textBox5.Text = liFehlzeit[index].Fg_id.ToString();
textBox6.Text = liFehlzeit[index].Fehltage.ToString();
comboBox1.SelectedIndex = liMitarbeiter.FindIndex(
x => x.Ma_id == liFehlzeit[index].Ma_id);
comboBox2.SelectedIndex = liFehlgrund.FindIndex(
x => x.Fg_id == liFehlzeit[index].Fg_id);
}
private void button3_Click(object sender, EventArgs e)
{
if (listBox1.SelectedIndex == -1)
return;
if (!this.inputValidated.All(x => x))
{
MessageBox.Show("Error: Input is invalid.");
return;
}
if (!db.updateFehlzeit(textBox1.Text, comboBox1.SelectedIndex == -1 ? "" : liMitarbeiter[comboBox1.SelectedIndex].Ma_id.ToString(), textBox3.Text, textBox4.Text, comboBox2.SelectedIndex == -1 ? "" : liFehlgrund[comboBox2.SelectedIndex].Fg_id.ToString(), textBox6.Text))
return;
int index_old = listBox1.SelectedIndex;
updateAt(listBox1.SelectedIndex, int.Parse(textBox1.Text));
listBox1.SelectedIndex = index_old;
}
private void updateAt(int index, int fg_id)
{
List<Fehlzeit> liUpdated = db.selectFehlzeit(fg_id);
if (liUpdated.Count == 0)
{
MessageBox.Show("Error: Could not adapt changes.");
return;
}
liFehlzeit.RemoveAt(index);
liFehlzeit.Insert(index, liUpdated[0]);
listBox1.Items.RemoveAt(index);
listBox1.Items.Insert(index, $"{liUpdated[0].Fz_id}, {liUpdated[0].Ma_id}, {liUpdated[0].Fg_id}");
}
private void button4_Click(object sender, EventArgs e)
{
if (listBox1.SelectedIndex == -1)
return;
if (!this.inputValidated.All(x => x))
{
MessageBox.Show("Error: Input is invalid.");
return;
}
if (!db.deleteFehlzeit(textBox1.Text))
return;
int index_old = listBox1.SelectedIndex;
deleteAt(listBox1.SelectedIndex);
listBox1.SelectedIndex = index_old - 1 == -1 ? listBox1.Items.Count - 1 : index_old - 1;
}
private void deleteAt(int index)
{
liFehlzeit.RemoveAt(index);
listBox1.Items.RemoveAt(index);
}
private void textBox3_Leave(object sender, EventArgs e)
{
this.inputValidated[2] = validateTextBox(textBox3, "date");
if (!this.inputValidated[2])
MessageBox.Show("Error: Invalid format");
}
private void textBox4_Leave(object sender, EventArgs e)
{
this.inputValidated[3] = validateTextBox(textBox4, "date");
if (!this.inputValidated[3])
MessageBox.Show("Error: Invalid format");
}
private void textBox6_Leave(object sender, EventArgs e)
{
this.inputValidated[5] = validateTextBox(textBox6, "digit");
if (!this.inputValidated[5])
MessageBox.Show("Error: Invalid format");
}
private void button5_Click(object sender, EventArgs e)
{
List<Fehlzeit> liImported = this.xml.parseFehlzeit();
if (liImported.Count == 0)
return;
foreach (Fehlzeit f in liImported)
{
liFehlzeit.Add(f);
this.db.insertFehlzeit(f.Fz_id.ToString(), f.Ma_id.ToString(), f.Von_datum.ToString("yyyy-MM-dd"), f.Bis_datum.ToString("yyyy-MM-dd"), f.Fg_id.ToString(), f.Fehltage.ToString());
}
fillListBox1();
listBox1.SelectedIndex = listBox1.Items.Count - 1;
}
private void button6_Click(object sender, EventArgs e)
{
if (listBox1.Items.Count == 0)
return;
this.xml.writeFehlzeit(liFehlzeit);
}
}
}
Raw
ini.sql
start transaction;
set foreign_key_checks=0;
drop database if exists ihk1;
create database ihk1;
use ihk1;
create table mitarbeiter (
ma_id integer primary key auto_increment,
nachname varchar(100),
vorname varchar(100),
geb_datum date,
tagesarbeitszeit integer,
urlaubsanspruchjahr integer
);
create table einsatz (
e_id integer primary key auto_increment,
ma_id integer,
datum date,
einsatzvon_zeit time,
einsatzbis_zeit time,
foreign key(ma_id) references mitarbeiter(ma_id) on delete cascade
);
create table fehlgrund (
fg_id integer primary key auto_increment,
name varchar(100)
);
create table fehlzeit (
fz_id integer primary key auto_increment,
ma_id integer,
von_datum date,
bis_datum date,
fg_id integer,
fehltage integer,
foreign key(ma_id) references mitarbeiter(ma_id) on delete cascade,
foreign key(fg_id) references fehlgrund(fg_id) on delete cascade
);
insert into mitarbeiter values
(811, 'mueller', 'jens', '1982-04-14', 8, 26),
(812, 'scholz', 'birgit', '1964-08-23', 4, 27),
(815, 'schmidt', 'ulrich', '1957-11-02', 8, 28),
(817, 'storck', 'hans', '1990-11-14', 6, 24),
(841, 'ullmann', 'franz', '1959-12-21', 8, 28),
(902, 'sorge', 'susanne', '1952-03-02', 8, 30),
(999, 'mustermann', 'max', '1901-01-01', 0, 0);
insert into einsatz values
(1, 811, '2009-04-17', '07:00:00', '11:45:00'),
(2, 811, '2009-04-17', '12:15:00', '16:00:00'),
(3, 811, '2009-04-18', '07:32:00', '08:10:00'),
(4, 902, '2009-04-17', '07:21:00', '12:06:00');
insert into fehlgrund values
(1, 'urlaub'),
(2, 'krank');
insert into fehlzeit values
(1, 811, '2009-04-18', '2009-04-23', 1, 4),
(2, 902, '2009-04-18', '2009-05-08', 2, 14),
(3, 811, '2009-06-19', '2009-06-20', 2, 2),
(4, 811, '2009-11-17', '2009-11-17', 1, 1),
(5, 904, '2009-12-31', '2009-12-31', 1, 1),
(6, 904, '2010-01-01', '2010-01-09', 1, 6),
(7, 999, '2009-04-18', '2009-04-23', 1, 4),
(8, 999, '2009-11-17', '2009-11-17', 1, 1);
commit;
-- vim: set ft=sql :
Raw
Mitarbeiter.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace PersonalDB
{
public class Mitarbeiter
{
public Mitarbeiter(int ma_id, string nachname, string vorname, DateTime geb_datum,
int tagesarbeitszeit, int urlaubsanspruchjahr)
{
this.Ma_id = ma_id;
this.Nachname = nachname;
this.Vorname = vorname;
this.Geb_datum = geb_datum;
this.Tagesarbeitszeit = tagesarbeitszeit;
this.Urlaubsanspruchjahr = urlaubsanspruchjahr;
}
public int Ma_id { get; set; }
public string Nachname { get; set; }
public string Vorname { get; set; }
public DateTime Geb_datum { get; set; }
public int Tagesarbeitszeit { get; set; }
public int Urlaubsanspruchjahr { get; set; }
}
}
Raw
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace PersonalDB
{
static class Program
{
/// <summary>
/// Der Haupteinstiegspunkt für die Anwendung.
/// </summary>
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
}
Raw
XmlData.cs
using System;
using System.Collections.Generic;
using System.Windows.Forms;
using System.Xml;
namespace PersonalDB
{
public class XmlData
{
private XmlReader xmlReader = null;
private XmlTextWriter xmlTextWriter = null;
public void writeMitarbeiter(List<Mitarbeiter> liMi)
{
try
{
this.xmlTextWriter = new XmlTextWriter("mitarbeiter_exported.xml", System.Text.Encoding.UTF8);
this.xmlTextWriter.Formatting = Formatting.Indented;
this.xmlTextWriter.WriteStartDocument(false);
this.xmlTextWriter.WriteStartElement("personal");
foreach (var m in liMi)
{
this.xmlTextWriter.WriteStartElement("mitarbeiter", null);
this.xmlTextWriter.WriteAttributeString("ma_id", m.Ma_id.ToString());
this.xmlTextWriter.WriteAttributeString("nachname", m.Nachname);
this.xmlTextWriter.WriteAttributeString("vorname", m.Vorname);
this.xmlTextWriter.WriteAttributeString("geb_datum", m.Geb_datum.ToString("yyyy-MM-dd"));
this.xmlTextWriter.WriteAttributeString("tagesarbeitszeit", m.Tagesarbeitszeit.ToString());
this.xmlTextWriter.WriteAttributeString("urlaubsanspruchjahr", m.Urlaubsanspruchjahr.ToString());
this.xmlTextWriter.WriteEndElement();
}
this.xmlTextWriter.WriteEndElement();
this.xmlTextWriter.Flush();
this.xmlTextWriter.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public List<Mitarbeiter> parseMitarbeiter()
{
List<Mitarbeiter> li = new List<Mitarbeiter>();
try
{
this.xmlReader = XmlReader.Create("mitarbeiter.xml");
while (this.xmlReader.Read())
{
if (this.xmlReader.NodeType == XmlNodeType.Element && this.xmlReader.Name == "mitarbeiter")
{
if (this.xmlReader.HasAttributes)
{
if (this.xmlReader.AttributeCount == 6)
{
DateTime dateOut;
DateTime.TryParseExact(
this.xmlReader.GetAttribute("geb_datum"),
"yyyy-MM-dd",
null,
0,
out dateOut);
li.Add(new Mitarbeiter(
int.Parse(this.xmlReader.GetAttribute("ma_id")),
this.xmlReader.GetAttribute("nachname"),
this.xmlReader.GetAttribute("vorname"),
dateOut,
int.Parse(this.xmlReader.GetAttribute("tagesarbeitszeit")),
int.Parse(this.xmlReader.GetAttribute("urlaubsanspruchjahr"))));
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return li;
}
public void writeEinsatz(List<Einsatz> liEin)
{
try
{
this.xmlTextWriter = new XmlTextWriter("einsatz_exported.xml", System.Text.Encoding.UTF8);
this.xmlTextWriter.Formatting = Formatting.Indented;
this.xmlTextWriter.WriteStartDocument(false);
this.xmlTextWriter.WriteStartElement("einsatzzeiten");
foreach (var e in liEin)
{
this.xmlTextWriter.WriteStartElement("einsatz", null);
this.xmlTextWriter.WriteAttributeString("e_id", e.Ma_id.ToString());
this.xmlTextWriter.WriteAttributeString("ma_id", e.Ma_id.ToString());
this.xmlTextWriter.WriteAttributeString("datum", e.Datum.ToString("yyyy-MM-dd"));
this.xmlTextWriter.WriteAttributeString("einsatzvon_zeit", e.Einsatzvon_zeit.ToString());
this.xmlTextWriter.WriteAttributeString("einsatzbis_zeit", e.Einsatzbis_zeit.ToString());
this.xmlTextWriter.WriteEndElement();
}
this.xmlTextWriter.WriteEndElement();
this.xmlTextWriter.Flush();
this.xmlTextWriter.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public List<Einsatz> parseEinsatz()
{
List<Einsatz> li = new List<Einsatz>();
try
{
this.xmlReader = XmlReader.Create("einsatz.xml");
while (this.xmlReader.Read())
{
if (this.xmlReader.NodeType == XmlNodeType.Element && this.xmlReader.Name == "einsatz")
{
if (this.xmlReader.HasAttributes)
{
if (this.xmlReader.AttributeCount == 5)
{
DateTime dateOut;
TimeSpan einsatzbis_zeit;
TimeSpan einsatzvon_zeit;
DateTime.TryParseExact(
this.xmlReader.GetAttribute("datum"),
"yyyy-MM-dd",
null,
0,
out dateOut);
TimeSpan.TryParseExact(
this.xmlReader.GetAttribute("einsatzvon_zeit"),
"hh\\:mm\\:ss",
null,
out einsatzvon_zeit);
TimeSpan.TryParseExact(
this.xmlReader.GetAttribute("einsatzbis_zeit"),
"hh\\:mm\\:ss",
null,
out einsatzbis_zeit);
li.Add(new Einsatz(
int.Parse(this.xmlReader.GetAttribute("e_id")),
int.Parse(this.xmlReader.GetAttribute("ma_id")),
dateOut,
einsatzvon_zeit,
einsatzbis_zeit));
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return li;
}
public void writeFehlgrund(List<Fehlgrund> liFehlg)
{
try
{
this.xmlTextWriter = new XmlTextWriter("fehlgrund_exported.xml", System.Text.Encoding.UTF8);
this.xmlTextWriter.Formatting = Formatting.Indented;
this.xmlTextWriter.WriteStartDocument(false);
this.xmlTextWriter.WriteStartElement("fehlgruende");
foreach (var f in liFehlg)
{
this.xmlTextWriter.WriteStartElement("fehlgrund", null);
this.xmlTextWriter.WriteAttributeString("fg_id", f.Fg_id.ToString());
this.xmlTextWriter.WriteAttributeString("name", f.Name);
this.xmlTextWriter.WriteEndElement();
}
this.xmlTextWriter.WriteEndElement();
this.xmlTextWriter.Flush();
this.xmlTextWriter.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public List<Fehlgrund> parseFehlgrund()
{
List<Fehlgrund> li = new List<Fehlgrund>();
try
{
this.xmlReader = XmlReader.Create("fehlgrund.xml");
while (this.xmlReader.Read())
{
if (this.xmlReader.NodeType == XmlNodeType.Element && this.xmlReader.Name == "fehlgrund")
{
if (this.xmlReader.HasAttributes)
{
if (this.xmlReader.AttributeCount == 2)
{
li.Add(new Fehlgrund(
int.Parse(this.xmlReader.GetAttribute("fg_id")),
this.xmlReader.GetAttribute("name")));
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return li;
}
public void writeFehlzeit(List<Fehlzeit> liFehlz)
{
try
{
this.xmlTextWriter = new XmlTextWriter("fehlzeit_exported.xml", System.Text.Encoding.UTF8);
this.xmlTextWriter.Formatting = Formatting.Indented;
this.xmlTextWriter.WriteStartDocument(false);
this.xmlTextWriter.WriteStartElement("fehlzeiten");
foreach (var f in liFehlz)
{
this.xmlTextWriter.WriteStartElement("fehlzeit", null);
this.xmlTextWriter.WriteAttributeString("fz_id", f.Fz_id.ToString());
this.xmlTextWriter.WriteAttributeString("ma_id", f.Ma_id.ToString());
this.xmlTextWriter.WriteAttributeString("von_datum", f.Von_datum.ToString("yyyy-MM-dd"));
this.xmlTextWriter.WriteAttributeString("bis_datum", f.Bis_datum.ToString("yyyy-MM-dd"));
this.xmlTextWriter.WriteAttributeString("fg_id", f.Fg_id.ToString());
this.xmlTextWriter.WriteAttributeString("fehltage", f.Fehltage.ToString());
this.xmlTextWriter.WriteEndElement();
}
this.xmlTextWriter.WriteEndElement();
this.xmlTextWriter.Flush();
this.xmlTextWriter.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public List<Fehlzeit> parseFehlzeit()
{
List<Fehlzeit> li = new List<Fehlzeit>();
try
{
this.xmlReader = XmlReader.Create("fehlzeit.xml");
while (this.xmlReader.Read())
{
if (this.xmlReader.NodeType == XmlNodeType.Element && this.xmlReader.Name == "fehlzeit")
{
if (this.xmlReader.HasAttributes)
{
if (this.xmlReader.AttributeCount == 6)
{
DateTime von_datum;
DateTime bis_datum;
DateTime.TryParseExact(
this.xmlReader.GetAttribute("von_datum"),
"yyyy-MM-dd",
null,
0,
out von_datum);
DateTime.TryParseExact(
this.xmlReader.GetAttribute("bis_datum"),
"yyyy-MM-dd",
null,
0,
out bis_datum);
li.Add(new Fehlzeit(
int.Parse(this.xmlReader.GetAttribute("fz_id")),
int.Parse(this.xmlReader.GetAttribute("ma_id")),
von_datum,
bis_datum,
int.Parse(this.xmlReader.GetAttribute("fg_id")),
int.Parse(this.xmlReader.GetAttribute("fehltage"))));
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return li;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment