public static void DeleteRecord(EmployeeItem item)
{
if (item != null)
{
SqlCommand command = new SqlCommand("Delete from EngineerTable where engineer_id=" + item.ID, con);
ExecuteCommand(command);
}
}
public static void UpdateRecord(EmployeeItem item)
{
if (IntegrityCheck(item))
{
SqlCommand command = new SqlCommand("Update EngineerTable Set " +
"name='" + item.Name + "', surname='" + item.Surname + "', birthdate='" + ToSQLDate(item.Date_of_birth) +
"', wage=" + item.Wage + ", employed_since='" + ToSQLDate(item.Employed_since) + "' where engineer_id=" + item.ID, con);
Console.WriteLine(command.CommandText);
ExecuteCommand(command);
}
}
public static EmployeeItem[] LoadData()
{
List<EmployeeItem> list = new List<EmployeeItem>();
try
{
SqlCommand command3 = new SqlCommand("Select * from EngineerTable", con);
SqlDataReader sdr = command3.ExecuteReader();
while (sdr.Read())
{
EmployeeItem item = new EmployeeItem();
item.ID = (int)sdr["engineer_id"];
item.Name = sdr["name"].ToString();
item.Surname = sdr["surname"].ToString();
item.Date_of_birth = DateTime.Parse(sdr["birthdate"].ToString());
item.Wage = (int)sdr["wage"];
item.Employed_since = DateTime.Parse(sdr["employed_since"].ToString());
list.Add(item);
}
sdr.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return list.ToArray();
}
public static bool IntegrityCheck(EmployeeItem item)
{
if (item == null)
return false;
DateTime birth = item.Date_of_birth;
DateTime employed = item.Employed_since;
TimeSpan ageTS = employed.Subtract(birth);
double age = (ageTS.TotalDays / 365.25);
if (age < 15)
return false;
if (item.Name.Length < 1 | item.Surname.Length < 1 | item.Wage < 0)
return false;
return true;
}
public static void InsertRecord(EmployeeItem item)
{
if (IntegrityCheck(item))
{
SqlCommand command2 = new SqlCommand("Insert into EngineerTable (name, surname, birthdate, wage, employed_since) values (@a, @b, @c, @d, @e);", con);
command2.Parameters.AddWithValue("@a", item.Name);
command2.Parameters.AddWithValue("@b", item.Surname);
command2.Parameters.AddWithValue("@c", ToSQLDate(item.Date_of_birth));
command2.Parameters.AddWithValue("@d", item.Wage);
command2.Parameters.AddWithValue("@e", ToSQLDate(item.Employed_since));
ExecuteCommand(command2);
SqlCommand command3 = new SqlCommand("Select Top 1 * from EngineerTable ORDER BY engineer_id DESC", con);
SqlDataReader sdr = command3.ExecuteReader();
while (sdr.Read())
{
item.ID = (int)sdr["engineer_id"];
}
if (!sdr.IsClosed)
sdr.Close();
}
else
throw new Exception("You can't enter a person employed in age of 14 or less.");
}
private void submitButton_Click(object sender, RoutedEventArgs e)
{
if (nameBox.Text.Length > 0 & surnameBox.Text.Length > 0 & birthdateBox.Text.Length > 0 & employeeBox.Text.Length > 0 & wageBox.Text.Length > 0)
{
EmployeeItem item = new EmployeeItem();
item.Name = nameBox.Text;
item.Surname = surnameBox.Text;
item.Date_of_birth = DateTime.Parse(birthdateBox.Text);
item.Employed_since = DateTime.Parse(employeeBox.Text);
item.Wage = int.Parse(wageBox.Text);
try
{
DB.InsertRecord(item);
clearButton_Click(sender, e);
listView.Items.Add(item);
Storyboard sb = this.FindResource("titleAnimation") as Storyboard;
sb.Begin();
}
catch (Exception ex)
{
errorText.Text = ex.Message;
Storyboard sb = this.FindResource("errorAnimation") as Storyboard;
sb.Begin();
}
}
}
private void listView_KeyUp(object sender, KeyEventArgs e)
{
if (e.Key == Key.Delete)
{
EmployeeItem[] items = new EmployeeItem[listView.SelectedItems.Count];
listView.SelectedItems.CopyTo(items, 0);
if (items.Length > 1)
{
MessageBoxResult result = MessageBox.Show("Do you really want to remove selected records (" + items.Length + ")?", "Delete records", MessageBoxButton.OKCancel, MessageBoxImage.Question, MessageBoxResult.OK);
if (result == MessageBoxResult.OK)
{
foreach (EmployeeItem item in items)
{
DB.DeleteRecord(item);
listView.Items.Remove(item);
}
}
}
else if (items.Length == 1)
{
EmployeeItem selectedItem = (listView.SelectedItem as EmployeeItem);
MessageBoxResult result = MessageBox.Show("Do you really want to remove \"" + selectedItem.Surname + "\"?", "Delete record", MessageBoxButton.OKCancel, MessageBoxImage.Question, MessageBoxResult.OK);
if (result == MessageBoxResult.OK)
{
DB.DeleteRecord(selectedItem);
listView.Items.Remove(selectedItem);
}
}
}
}