public void DeleteKundeKontaktTest()
{
KundeKontaktTable k = new KundeKontaktTable();
k.ID = -1;
k.Type = false;
this.mdb.DeleteKundeKontakt(k);
}
/// <summary>
/// Gets requested Kontakte out of the database.
/// </summary>
/// <param name="k">The Kunde/Kontakt object that shall be searched for</param>
/// <param name="errorlabel">A label in the homeform in which errormessages can be displayed</param>
/// <returns>List of matching Kontakt</returns>
public List<KundeKontaktTable> LoadKundenKontakte(KundeKontaktTable k, Label errorlabel)
{
IRule doubv = new PositiveDoubleValidator();
IRule intv = new PositiveIntValidator();
IRule datev = new DateValidator();
IRule lengthv = new StringLength150Validator();
IRule percv = new PercentValidator();
IRule lnhsv = new LettersNumbersHyphenSpaceValidator();
IRule lhv = new LettersHyphenValidator();
DataBindingFramework.BindFromString(k.Vorname, "Vorname", errorlabel, true, lhv, lengthv);
DataBindingFramework.BindFromString(k.NachnameFirmenname, "Nachname", errorlabel, true, lnhsv, lengthv);
k.ID = -1; // indicates that we don't want to search for an ID
// if validation failed
if (doubv.HasErrors || intv.HasErrors || datev.HasErrors || lengthv.HasErrors || percv.HasErrors || lnhsv.HasErrors || lhv.HasErrors)
{
throw new InvalidInputException();
}
// load elements
try
{
return DALFactory.GetDAL().GetKundenKontakte(k);
}
catch (SQLiteException)
{
throw;
}
}
/// <summary>
/// Changes data of an existing Kunde/Kontakt
/// </summary>
/// <param name="k">The to-be-changed Kunde/Kontakt</param>
/// <param name="errorlabel">The label in which errormessages may be written</param>
public void Change(KundeKontaktTable k, Label errorlabel)
{
errorlabel.Hide();
IRule lnhsv = new LettersNumbersHyphenSpaceValidator();
IRule lhv = new LettersHyphenValidator();
IRule lengthv = new StringLength150Validator();
DataBindingFramework.BindFromString(k.Vorname, "Vorname", errorlabel, true, lhv, lengthv);
DataBindingFramework.BindFromString(k.NachnameFirmenname, "Nachname", errorlabel, false, lnhsv, lengthv);
if (errorlabel.Visible)
{
throw new InvalidInputException();
}
// else save new Kunde or Kontakt in database
if (k.Vorname.Length == 0)
{
this.logger.Log(Logger.Level.Info, "No first name will be stored.");
}
this.logger.Log(Logger.Level.Info, "User requested to change " + (k.Type == false ? "Kunde" : "Kontakt") + " with ID " + k.ID);
// Update data
try
{
DALFactory.GetDAL().UpdateKundeKontakte(k);
}
catch (SQLiteException)
{
throw;
}
}
public void SaveNewKundeTest()
{
KundenKontakteSaver target = new KundenKontakteSaver(); // TODO: Initialize to an appropriate value
KundeKontaktTable k = new KundeKontaktTable();
k.Vorname = "Hans4*";
k.NachnameFirmenname = "Mayer";
k.Type = false;
target.SaveNewKundeKontakt(k, new System.Windows.Forms.Label());
}
public void GetKundenKontakteSQLTest1()
{
DataBaseManager target = new DataBaseManager();
KundeKontaktTable k = new KundeKontaktTable();
k.ID = -1; // indicates that it shall not be searched for ID
k.Type = false;
k.Vorname = "Franz";
k.NachnameFirmenname = string.Empty;
string expected = "SELECT * FROM Kunde WHERE Vorname = ?";
string actual;
actual = target.GetKundenKontakteSQL(k);
Assert.AreEqual(expected, actual);
}
/// <summary>
/// Gets an Kunden/Kontakt ID and type and loads requested data
/// </summary>
/// <param name="id">The requested ID</param>
/// <param name="type">Kunde (false) or Kontakt (true)?</param>
/// <returns></returns>
public List<KundeKontaktTable> LoadKundenKontakte(int id, bool type)
{
KundeKontaktTable k = new KundeKontaktTable();
k.ID = id;
k.Vorname = string.Empty;
k.NachnameFirmenname = string.Empty;
k.Type = type;
try
{
return DALFactory.GetDAL().GetKundenKontakte(k);
}
catch (SQLiteException)
{
throw;
}
}
/// <summary>
/// Deletes an existing Kunde/Kontakt
/// </summary>
/// <param name="k">The Kunde/Kontakt object that shall be deleted</param>
/// <param name="label">The label in which errormessages can be shown</param>
public void Delete(KundeKontaktTable k, Label label)
{
PositiveIntValidator checkkundenid = new PositiveIntValidator();
checkkundenid.Eval(k.ID);
if (checkkundenid.HasErrors)
{
this.logger.Log(Logger.Level.Error, "No valid ID provided from GUI layer!");
throw new InvalidInputException("Es wurde keine gültige ID übergeben!");
}
this.logger.Log(Logger.Level.Info, "User requested to delete " + (k.Type == false ? "Kunde" : "Kontakt") + " with ID " + k.ID);
try
{
DALFactory.GetDAL().DeleteKundeKontakt(k);
}
catch (SQLiteException)
{
throw;
}
}
/// <summary>
/// Gets all existing Kunden from the Database and adds them to the existingKundenComboBox
/// </summary>
/// <param name="sender">The sender</param>
/// <param name="e">The event args</param>
/// <param name="type">Is false in case of Kunde, is true in case of Kontakt</param>
public static void BindFromExistingKundenToComboBox(object sender, EventArgs e, bool type = false)
{
// check sender for null
if (sender == null)
{
return;
}
List<KundeKontaktTable> results;
// add empty element to make empty choices possible
List<string> listItems = new List<string>();
listItems.Add("");
KundenKontakteLoader loader = new KundenKontakteLoader();
// Create empty Kunden object with type "false"
KundeKontaktTable k = new KundeKontaktTable();
k.Vorname = string.Empty;
k.NachnameFirmenname = string.Empty;
k.Type = type; // only get Kunden or Kontakte
// Load all existing Kunden to object result list
results = loader.LoadKundenKontakte(k, null);
// if there are results, add them to string result list
if (results.Count != 0)
{
foreach (KundeKontaktTable kunde in results)
{
string entry = kunde.ID + ": " + kunde.Vorname + " - " + kunde.NachnameFirmenname;
listItems.Add(entry);
}
}
// set data source
(sender as ComboBox).DataSource = listItems;
}
/// <summary>
/// Reads values of concerning fields and asks business layer to create a new Angebot with the provided parameters
/// </summary>
/// <param name="sender">The sender</param>
/// <param name="e">The event params</param>
private void CreateNewAngebot(object sender, EventArgs e)
{
// reset error/success labels
this.createAngebotMsgLabel.Hide();
this.createAngebotMsgLabel.Text = string.Empty;
// existing or newly to-be-created
bool createKunde = false;
string kundenID = null;
// Table for the Kontakt
KundeKontaktTable k = new KundeKontaktTable();
// new Kunde
if (this.angebotErstellenSubTab.SelectedTab == this.angebotErstellenSubTab.TabPages[0])
{
createKunde = true;
}
// existing Kunde
else
{
// no Kunde chosen or first element chosen (which is empty) -> show error label
if (this.createAngebotExistingKundeComboBox.SelectedIndex <= 0)
{
this.createAngebotMsgLabel.Text = "Error: kein Kunde ausgewählt";
this.createAngebotMsgLabel.ForeColor = Color.Red;
this.createAngebotMsgLabel.Show();
return; // skip rest of function
}
// Kunde chosen - get ID out of ComboBox
else
{
kundenID = this.createAngebotExistingKundeComboBox.SelectedItem.ToString();
kundenID = kundenID.Substring(0, kundenID.IndexOf(':'));
IRule posint = new PositiveIntValidator();
k.ID = DataBindingFramework.BindFromInt(kundenID, "KundenID", this.createAngebotMsgLabel, false, posint);
// Check for errors while databinding
if (posint.HasErrors)
{
this.logger.Log(Logger.Level.Error, k.ID + " is an invalid Kunden ID");
return;
}
// get Kunde table out of Database
KundenKontakteLoader loader = new KundenKontakteLoader();
List<KundeKontaktTable> results = loader.LoadKundenKontakte(k.ID, false);
// there must be exactly one result, for ID is unique!
if (results.Count != 1)
{
this.logger.Log(Logger.Level.Error, "More than one Kunde returned - impossible, because ID is unique!");
this.createAngebotMsgLabel.Text = "Error: Datenbank inkonsistent!";
this.createAngebotMsgLabel.Visible = true;
return; // skip rest of function
}
// everything went fine
k = results[0];
}
}
// Create new Kunde, if requested
if (createKunde)
{
// define Rules
IRule lhv = new LettersHyphenValidator();
IRule lnhsv = new LettersNumbersHyphenSpaceValidator();
IRule slv = new StringLength150Validator();
// validate data
k.Vorname = DataBindingFramework.BindFromString(this.createAngebotNewKundeVnTextBox.Text, "Vorname", this.createAngebotMsgLabel, true, lhv, slv);
k.NachnameFirmenname = DataBindingFramework.BindFromString(this.createAngebotNewKundeNnTextBox.Text, "Nachname", this.createAngebotMsgLabel, false, lnhsv, slv);
k.Type = false; // Kunde
if (lhv.HasErrors || lnhsv.HasErrors || slv.HasErrors)
{
this.logger.Log(Logger.Level.Error, "Invalid signs within create Angebot / create Kunde");
return;
}
KundenKontakteSaver saver = new KundenKontakteSaver();
try
{
k.ID = saver.SaveNewKundeKontakt(k, this.createAngebotMsgLabel);
}
catch (InvalidInputException ex)
{
this.logger.Log(Logger.Level.Error, ex.Message + ex.StackTrace);
this.createAngebotMsgLabel.Text = "Error: Kundenfelder ungültig!";
this.createAngebotMsgLabel.Visible = true;
return;
}
catch (SQLiteException ex)
{
this.logger.Log(Logger.Level.Error, ex.Message + ex.StackTrace);
this.createAngebotMsgLabel.Text = "Error: Datenbankproblem!";
this.createAngebotMsgLabel.Visible = true;
return;
}
this.createAngebotMsgLabel.Text = "Kunde wurde gespeichert.";
}
// Create Angebot business object
this.logger.Log(Logger.Level.Info, "Start creating new Angebot...");
// define Rules
IRule pdv2 = new PositiveDoubleValidator();
IRule pcv2 = new PercentValidator();
IRule dv2 = new DateValidator();
IRule lnhsv2 = new LettersNumbersHyphenSpaceValidator();
IRule slv2 = new StringLength150Validator();
IRule piv2 = new PositiveIntValidator();
AngebotTable angebot = new AngebotTable();
angebot.Angebotssumme = DataBindingFramework.BindFromDouble(this.createAngebotAngebotssummeTextBox.Text, "Angebotssumme", this.createAngebotMsgLabel, false, pdv2);
angebot.Umsetzungschance = DataBindingFramework.BindFromInt(this.createAngebotUmsetzungswahrscheinlichkeitTextBox.Text, "Umsetzungschance", this.createAngebotMsgLabel, false, pcv2);
angebot.Angebotsdauer = DataBindingFramework.BindFromString(this.angebotValidUntilDateTimePicker.Value.ToShortDateString(), "GültigBis", this.createAngebotMsgLabel, false, dv2);
angebot.Beschreibung = DataBindingFramework.BindFromString(this.createAngebotDescriptionTextBox.Text, "Beschreibung", this.createAngebotMsgLabel, false, lnhsv2, slv2);
angebot.KundenID = DataBindingFramework.BindFromInt(k.ID.ToString(), "kundenID", this.createAngebotMsgLabel, false, piv2);
angebot.Erstellungsdatum = DateTime.Now.ToShortDateString();
// in case of errors in Databinding
if (createAngebotMsgLabel.Visible)
{
this.logger.Log(Logger.Level.Error, "No angebot has been saved because of invalid inputs.");
//createAngebotMsgLabel.ForeColor = Color.Red;
//createAngebotMsgLabel.Show();
return;
}
// send Angebot object to database
try
{
AngebotManager manager = new AngebotManager();
manager.Create(angebot, createAngebotMsgLabel);
}
catch (InvalidInputException ex)
{
this.logger.Log(Logger.Level.Error, ex.Message + ex.StackTrace);
this.createAngebotMsgLabel.Text += "\nError: " + ex.Message;
this.createAngebotMsgLabel.Show();
}
catch (SQLiteException ex)
{
this.logger.Log(Logger.Level.Error, ex.Message + ex.StackTrace);
this.createAngebotMsgLabel.Text += "\nError: " + ex.Message;
this.createAngebotMsgLabel.Show();
}
// show success message, if no error has been thrown
if (!createAngebotMsgLabel.Visible)
{
this.createAngebotMsgLabel.Text += "\nAngebot wurde gespeichert.";
this.createAngebotMsgLabel.ForeColor = Color.Green;
this.createAngebotMsgLabel.Show();
}
}
/// <summary>
/// Saves a new Kunde or Kontakt to the mock database (IList)
/// </summary>
/// <param name="k">The to-be-inserted Kunde (DAL table)</param>
/// <returns>The ID of the newly inserted Kunde/Kontakt</returns>
public int SaveNewKundeKontakt(KundeKontaktTable k)
{
if(k.Type == false)
{
k.ID = MockDataBaseManager.KundenID;
// save to list
MockDataBaseManager.SavedKunden.Add(k);
this.logger.Log(Logger.Level.Info, "A new Kunde has been saved in the mockDB.");
return k.ID;
}
else
{
k.ID = MockDataBaseManager.KontaktID;
// save to list
MockDataBaseManager.SavedKontakte.Add(k);
this.logger.Log(Logger.Level.Info, "A new Kontakt has been saved in the mockDB.");
return k.ID;
}
}
/// <summary>
/// This function gets (a) certain Kontakt(e) from the mock database.
/// If firstname and lastname should be empty, display all
/// </summary>
/// <param name="k">The Kunde or Kontakt table object that shall be searched for</param>
/// <returns>A list of the requested Kontakte</returns>
public List<KundeKontaktTable> GetKundenKontakte(KundeKontaktTable k)
{
List<KundeKontaktTable> resultlist = new List<KundeKontaktTable>();
// get Kunden
if (k.Type == false)
{
this.logger.Log(Logger.Level.Info, "Starts getting Kunden out of database...");
foreach (KundeKontaktTable kunde in MockDataBaseManager.SavedKunden)
{
// if it shall be searched for ID, really only search for ID and ignore other field values
if (k.ID == kunde.ID)
{
resultlist.Add(kunde);
break; // Kunde found, there is no other for ID is unique
}
else if (k.Vorname.Length == 0 && k.NachnameFirmenname.Length == 0)
{ resultlist.Add(kunde); }
else if (k.Vorname.Length != 0 && k.NachnameFirmenname.Length == 0)
{
if(kunde.Vorname == k.Vorname)
resultlist.Add(kunde);
}
else if (k.NachnameFirmenname.Length != 0 && k.NachnameFirmenname.Length == 0)
{
if (kunde.NachnameFirmenname == k.NachnameFirmenname)
{ resultlist.Add(kunde); }
}
else
{
if (kunde.NachnameFirmenname == k.NachnameFirmenname && kunde.Vorname == k.Vorname)
{ resultlist.Add(kunde); }
}
}
}
// get Kontakt
else if (k.Type == true)
{
this.logger.Log(Logger.Level.Info, "Starts getting Kontakte out of database...");
foreach (KundeKontaktTable kontakt in MockDataBaseManager.SavedKontakte)
{
// if it shall be searched for ID, really only search for ID and ignore other field values
if (k.ID == kontakt.ID)
{
resultlist.Add(kontakt);
break; // Kontakt found, there is no other for ID is unique
}
else if (k.Vorname.Length == 0 && k.NachnameFirmenname.Length == 0)
{ resultlist.Add(kontakt); }
else if (k.Vorname.Length != 0 && k.NachnameFirmenname.Length == 0)
{
if (kontakt.Vorname == k.Vorname)
resultlist.Add(kontakt);
}
else if (k.NachnameFirmenname.Length != 0 && k.Vorname.Length == 0)
{
if (kontakt.NachnameFirmenname == k.NachnameFirmenname)
{ resultlist.Add(kontakt); }
}
else
{
if (kontakt.NachnameFirmenname == k.NachnameFirmenname && kontakt.Vorname == k.Vorname)
{ resultlist.Add(kontakt); }
}
}
}
return resultlist;
}
public void SaveNewKundeTest2()
{
int count_before = MockDataBaseManager.SavedKontakte.Count;
KundeKontaktTable k = new KundeKontaktTable();
k.Vorname = "Hans";
k.NachnameFirmenname = "Huber";
k.Type = true; // Kontakt
this.mdb.SaveNewKundeKontakt(k);
int count_after = MockDataBaseManager.SavedKontakte.Count;
Assert.AreEqual(count_before + 1, count_after);
}
/// <summary>
/// Updates information of an existing Kunde or Kontakt
/// </summary>
/// <param name="k">The to-be-changed Kunde or Kontakt</param>
public void UpdateKundeKontakte(KundeKontaktTable k)
{
// Kunde
if(k.Type == false)
{
int index = MockDataBaseManager.SavedKunden.IndexOf(k);
if (index < 0)
{
this.logger.Log(Logger.Level.Error, "Kunde which shall be updated has not been found!");
}
MockDataBaseManager.SavedKunden[index].Vorname = k.Vorname;
MockDataBaseManager.SavedKunden[index].NachnameFirmenname = k.NachnameFirmenname;
this.logger.Log(Logger.Level.Info, "Kunde has been updated within mockDB.");
}
// Kontakt
else
{
int index = MockDataBaseManager.SavedKontakte.IndexOf(k);
if (index < 0)
{
this.logger.Log(Logger.Level.Error, "Kontakt which shall be updated has not been found!");
}
MockDataBaseManager.SavedKontakte[index].Vorname = k.Vorname;
MockDataBaseManager.SavedKontakte[index].NachnameFirmenname = k.NachnameFirmenname;
this.logger.Log(Logger.Level.Info, "Kontakt has been updated within mockDB.");
}
}
/// <summary>
/// Gets Kunden or Kontakte out of the database (over the business layer, which checks for valid input)
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void SearchKundenOrKontakte(object sender, EventArgs e)
{
// hide error message
this.HideMsgLabels();
// define rules
IRule lhv = new LettersHyphenValidator();
IRule lnhsv = new LettersNumbersHyphenSpaceValidator();
IRule slv = new StringLength150Validator();
KundeKontaktTable k = new KundeKontaktTable();
k.Vorname = DataBindingFramework.BindFromString(this.searchKundeVornameTextBlock.Text, "Vorname", this.searchKundeMsgLabel, true, lhv, slv);
k.NachnameFirmenname = DataBindingFramework.BindFromString(this.searchKundeNachnameTextBlock.Text, "Nachname", this.searchKundeMsgLabel, true, lnhsv, slv);
k.Type = this.searchKontaktRadioButton.Checked; // False...Kunde, true...Kontakt
KundenKontakteLoader loader = new KundenKontakteLoader();
// only if binding had no errors
if (!this.searchKundeMsgLabel.Visible)
{
List<KundeKontaktTable> results;
results = loader.LoadKundenKontakte(k, this.searchKundeMsgLabel);
this.kundenSuchenBindingSource.DataSource = results;
}
this.BindToKundenSearchLabels(this.kundenSearchDataGridView, null);
}
/// <summary>
/// Deletes an existing Kunde or Kontakt out of the SQLite database
/// </summary>
/// <param name="k">The Kunde/Kontakt object that shall be deleted from the SQLite database</param>
public void DeleteKundeKontakt(KundeKontaktTable k)
{
string s_type = k.Type == false ? "Kunde" : "Kontakt";
string sql = "DELETE FROM " + s_type + " WHERE ID = ?";
try
{
this.SendStatementToDatabase(sql, k.ID);
}
catch (SQLiteException)
{
throw;
}
// success logging
string successmessage = s_type + " has been dropped from the SQLite database. ID: " + k.ID;
this.logger.Log(Logger.Level.Info, successmessage);
}
public void SaveNewKundeTest1()
{
KundenKontakteSaver target = new KundenKontakteSaver();
KundeKontaktTable k = new KundeKontaktTable();
k.Vorname = string.Empty;
k.NachnameFirmenname = "#";
k.Type = false;
target.SaveNewKundeKontakt(k, new System.Windows.Forms.Label());
}
/// <summary>
/// Get values of GUI elements and send them to the business layer, they shall be stored in the database.
/// </summary>
/// <param name="sender">object sender</param>
/// <param name="e">EventArgs</param>
private void CreateKundeOrKontakt(object sender, EventArgs e)
{
// hide error label
this.HideMsgLabels();
KundeKontaktTable k = new KundeKontaktTable();
// define rules
IRule lhv = new LettersHyphenValidator();
IRule lnhsv = new LettersNumbersHyphenSpaceValidator();
IRule slv = new StringLength150Validator();
// Bind data
k.Vorname = DataBindingFramework.BindFromString(this.createKundeVornameTextBlock.Text, "Vorname", this.kundeNeuMsgLabel, true, lhv, slv);
k.NachnameFirmenname = DataBindingFramework.BindFromString(this.createKundeNachnameTextBlock.Text, "Nachname/Firmenname", this.kundeNeuMsgLabel, false, lnhsv, slv);
k.Type = this.createKontaktRadioButton.Checked; // false - Kunde, true - Kontakt
// if no errors, send to business layer and show success message
if (!this.kundeNeuMsgLabel.Visible)
{
KundenKontakteSaver saver = new KundenKontakteSaver();
saver.SaveNewKundeKontakt(k, this.kundeNeuMsgLabel);
GlobalActions.ShowSuccessLabel(this.kundeNeuMsgLabel);
ResetFields();
}
}
/// <summary>
/// Saves a new Kunde or Kontakt to the database
/// </summary>
/// <param name="k">The Kunde or Kontakt object that shall be saved</param>
public int SaveNewKundeKontakt(KundeKontaktTable k)
{
int insertedID;
string s_type = k.Type == false ? "Kunde" : "Kontakt";
string sql = "INSERT INTO " + s_type + " (Vorname, Nachname_Firmenname) VALUES (?, ?)";
// open connection and save new Kunde/Kontakt in database
SQLiteConnection con = null;
SQLiteTransaction tra = null;
SQLiteCommand cmd = null;
try
{
// initialise connection
con = new SQLiteConnection(ConfigFileManager.ConnectionString);
con.Open();
// initialise transaction
tra = con.BeginTransaction();
cmd = new SQLiteCommand(sql, con);
// initialise parameter
SQLiteParameter p_firstname = new SQLiteParameter();
SQLiteParameter p_lastname = new SQLiteParameter();
// bind first name
p_firstname.Value = k.Vorname;
cmd.Parameters.Add(p_firstname);
// bind last name
p_lastname.Value = k.NachnameFirmenname;
cmd.Parameters.Add(p_lastname);
// execute and commit
cmd.ExecuteNonQuery();
tra.Commit();
// get rowID
cmd.Parameters.Clear();
cmd.CommandText = "SELECT last_insert_rowid() AS id FROM " + s_type;
cmd.ExecuteNonQuery();
System.Object temp = cmd.ExecuteScalar();
insertedID = int.Parse(temp.ToString());
}
catch(SQLiteException)
{
throw;
}
finally
{
if (tra != null) { tra.Dispose(); }
if (cmd != null) { cmd.Dispose(); }
if (con != null) { con.Dispose(); }
}
// success logging
string successmessage = "A new " + s_type + " has been saved to the database: " + insertedID + " " + k.Vorname + " " + k.NachnameFirmenname;
this.logger.Log(Logger.Level.Info, successmessage);
// return ID of inserted item
return insertedID;
}
/// <summary>
/// Create SQL-wrapper string for Kunden/Kontakte
/// </summary>
/// <param name="k">The to-be-searched Kunde/Kontakt table object</param>
/// <returns>SQLite prepared statement string</returns>
public string GetKundenKontakteSQL(KundeKontaktTable k)
{
string type = k.Type == false ? "Kunde" : "Kontakt";
// if it shall be searched for ID, really only search for ID and ignore other fields
// ID will be -1 if it shall not be searched for ID (set in Business Layer)
if (k.ID >= 0)
{
return "SELECT * FROM " + type + " WHERE ID = ?";
}
else if (k.Vorname.Length == 0 && k.NachnameFirmenname.Length == 0)
{
return "SELECT * FROM " + type;
}
else if (k.Vorname.Length != 0 && k.NachnameFirmenname.Length == 0)
{
return "SELECT * FROM " + type + " WHERE Vorname = ?";
}
else if (k.Vorname.Length == 0 && k.NachnameFirmenname.Length != 0)
{
return "SELECT * FROM " + type + " WHERE Nachname_Firmenname = ?";
}
else
{
return "SELECT * FROM " + type + " WHERE Vorname = ? AND Nachname_Firmenname = ?";
}
}
/// <summary>
/// This function gets (a) certain Kontakt(e) from the saved objects in the database.
/// If firstname and lastname should be empty, display all
/// </summary>
/// <param name="k">The Kunden/Kontakt table object</param>
/// <returns>A list of all found Kunden or Kontakte</returns>
public List<KundeKontaktTable> GetKundenKontakte(KundeKontaktTable k)
{
string s_type = k.Type == false ? "Kunde" : "Kontakt";
string sql = GetKundenKontakteSQL(k);
List<KundeKontaktTable> resultlist = new List<KundeKontaktTable>();
// open connection and get requested Kontakt(e) out of database
SQLiteConnection con = null;
SQLiteTransaction tra = null;
SQLiteCommand cmd = null;
SQLiteDataReader reader = null;
try
{
// initialise connection
con = new SQLiteConnection(ConfigFileManager.ConnectionString);
con.Open();
// initialise transaction
tra = con.BeginTransaction();
cmd = new SQLiteCommand(sql, con);
// if it shall be searched for ID, really only search for ID and ignore other fields
if (k.ID != -1)
{
SQLiteParameter p_ID = new SQLiteParameter();
p_ID.Value = k.ID;
cmd.Parameters.Add(p_ID);
}
// bind first name
if (k.ID < 0 && k.Vorname.Length != 0)
{
SQLiteParameter p_firstname = new SQLiteParameter();
p_firstname.Value = k.Vorname;
cmd.Parameters.Add(p_firstname);
}
// bind last name
if (k.ID < 0 && k.NachnameFirmenname.Length != 0)
{
SQLiteParameter p_lastname = new SQLiteParameter();
p_lastname.Value = k.NachnameFirmenname;
cmd.Parameters.Add(p_lastname);
}
// execute and get results
reader = cmd.ExecuteReader();
while (reader.Read())
{
KundeKontaktTable result = new KundeKontaktTable();
result.ID = reader.GetInt32(0);
result.Vorname = reader.GetString(1);
result.NachnameFirmenname = reader.GetString(2);
result.Type = k.Type;
// if nothing is stored within field "Vorname", just return empty string
if (result.Vorname == "<null>")
{ result.Vorname = string.Empty; }
resultlist.Add(result);
}
return resultlist;
}
catch (SQLiteException)
{
throw;
}
finally
{
if (reader != null) { reader.Dispose(); }
if (tra != null) { tra.Dispose(); }
if (cmd != null) { cmd.Dispose(); }
if (con != null) { con.Dispose(); }
}
}
/// <summary>
/// Deletes an existing Kunde or Kontakt out of the mock database
/// </summary>
/// <param name="k">The Kunde/Kontakt object that shall be deleted</param>
public void DeleteKundeKontakt(KundeKontaktTable k)
{
int removed = 0;
// delete Kunde
if (k.Type == false)
{
foreach (KundeKontaktTable kunde in MockDataBaseManager.SavedKunden)
{
if(kunde.ID == k.ID)
{
MockDataBaseManager.savedKunden.Remove(kunde);
removed++;
logger.Log(Logger.Level.Info, "Kunde has been removed out of mockDB: " + kunde.ID + " " + kunde.Vorname + " " + kunde.NachnameFirmenname);
break;
}
}
}
// delete Kontakt
else if (k.Type == true)
{
foreach (KundeKontaktTable kontakt in MockDataBaseManager.SavedKontakte)
{
if (kontakt.ID == k.ID)
{
MockDataBaseManager.savedKontakte.Remove(kontakt);
removed++;
logger.Log(Logger.Level.Info, "Kontakt has been removed out of mockDB: " + kontakt.ID + " " + kontakt.Vorname + " " + kontakt.NachnameFirmenname);
break;
}
}
}
// no entry found
if (removed != 1)
{
this.logger.Log(Logger.Level.Error, "There is no entry in the mockDB with the ID " + k.ID);
throw new EntryNotFoundException("There is no entry in the mockDB with the ID " + k.ID);
}
}
public void SaveNewKundeTest()
{
int count_before = MockDataBaseManager.SavedKunden.Count;
KundeKontaktTable k = new KundeKontaktTable();
k.Vorname = "Karl";
k.NachnameFirmenname = "Grausgruber";
k.Type = false; // Kunde
this.mdb.SaveNewKundeKontakt(k);
int count_after = MockDataBaseManager.SavedKunden.Count;
Assert.AreEqual(count_before + 1, count_after);
}
/// <summary>
/// Updates an existing Kunde or Kontakt in the SQLite database
/// </summary>
/// <param name="k">The to be updated Kunde/Kontakt object</param>
public void UpdateKundeKontakte(KundeKontaktTable k)
{
string s_type = k.Type == false ? "Kunde" : "Kontakt";
string sql = "UPDATE " + s_type + " SET Vorname = ?, Nachname_Firmenname = ? WHERE ID = ?";
try
{
this.SendStatementToDatabase(sql, k.ID, k.Vorname, k.NachnameFirmenname);
}
catch (SQLiteException)
{
throw;
}
// success logging
string successmessage = s_type + " has been updated in the SQLite database: ID: " + k.ID + " " + k.Vorname + " " + k.NachnameFirmenname;
this.logger.Log(Logger.Level.Info, successmessage);
}
public void GetKundenKontakteSQLTest3()
{
DataBaseManager target = new DataBaseManager();
KundeKontaktTable k = new KundeKontaktTable();
k.ID = -1; // don't search for ID
k.Type = true;
k.Vorname = "Hans";
k.NachnameFirmenname = "Huber";
string expected = "SELECT * FROM Kontakt WHERE Vorname = ? AND Nachname_Firmenname = ?";
string actual;
actual = target.GetKundenKontakteSQL(k);
Assert.AreEqual(expected, actual);
}
