public static void DeleteCommand(string[] arguments)
{
int id;
EngineerItem item = new EngineerItem();
if (IntArgument(arguments, "ID", 1, out id))
{
if (allowPrompt)
{
string s = DB.LoadRecord(id, out item);
if (item == null)
{
WriteDB(s);
return;
}
ConsoleTable.WriteReadTable(item);
Console.ForegroundColor = textCol;
Console.WriteLine("Are you sure? (y/n)", item.Surname, item.Name);
char ch = GetInputChar(new[] { 'y', 'n', 'Y', 'N' });
if (ch == 'y' | ch == 'Y')
WriteDB(DB.DeleteRecord(id));
}
else
{
WriteDB(DB.DeleteRecord(id));
}
}
}
public static string DeleteRecord(int id)
{
EngineerItem item = new EngineerItem();
string str = LoadRecord(id, out item);
if (item == null)
return str;
SqlCommand command = new SqlCommand("Delete from EngineerTable where engineer_id = '" + id + "';", con);
return ExecuteCommand(command, "Record " + id + " deleted.");
}
public static EngineerItem WriteEditTable()
{
EngineerItem item = new EngineerItem();
int wageWidth = 7;
int dateWidth1 = "Birth (yyyy-mm-dd)".Length;
int dateWidth2 = "Employed (yyyy-mm-dd)".Length;
int nameWidth = (Console.BufferWidth - dateWidth1 - dateWidth2 - wageWidth - 6) / 2;
int[] columnWidths = new[] { nameWidth, nameWidth, dateWidth1, dateWidth2, wageWidth };
WriteSplitLine('╔', '╦', '╗', columnWidths);
WriteColumn("Name", nameWidth);
WriteColumn("Surname", nameWidth);
WriteColumn("Birth (yyyy-mm-dd)", dateWidth1);
WriteColumn("Employed (yyyy-mm-dd)", dateWidth2);
WriteColumn("Wage", wageWidth);
WriteSplitLine('╠', '╬', '╣', columnWidths);
Console.CursorTop += 1;
WriteSplitLine('╚', '╩', '╝', columnWidths);
Console.CursorTop -= 2;
WriteSplitLine('║', '║', '║', ' ', columnWidths);
Console.CursorTop -= 1;
Console.ForegroundColor = textColor;
Console.CursorLeft = 1;
item.Name = EditValue(item.Name);
Console.CursorTop -= 1;
Console.CursorLeft = 2 + nameWidth;
item.Surname = EditValue(item.Surname);
Console.CursorTop -= 1;
Console.CursorLeft = 3 + (nameWidth * 2);
item.Date_of_birth = EditValue(item.Date_of_birth);
Console.CursorTop -= 1;
Console.CursorLeft = 4 + (nameWidth * 2) + dateWidth1;
item.Employed_since = EditValue(item.Employed_since);
Console.CursorTop -= 1;
Console.CursorLeft = 5 + (nameWidth * 2) + dateWidth1 + dateWidth2;
item.Wage = EditValue(item.Wage);
Console.CursorTop += 1;
return item;
}
static int IDComparer(EngineerItem a, EngineerItem b)
{
return (a.ID > b.ID) ? 1 : ((a.ID < b.ID) ? -1 : 0);
}
public static void UpdateCommand(string[] arguments)
{
int id;
EngineerItem item = new EngineerItem();
if (IntArgument(arguments, "ID", 1, out id))
{
WriteDB(DB.LoadRecord(id, out item));
if (item == null)
return;
}
else
return;
ConsoleTable.WriteEditTable(ref item);
WriteDB(DB.UpdateRecord(item));
}
static int MaxID(EngineerItem[] items)
{
int max = int.MinValue;
for (int i = 0; i < items.Length; i++)
{
if (items[i].ID > max)
max = items[i].ID;
}
return max;
}
static int LargestWage(EngineerItem[] items)
{
int max = int.MinValue;
for (int i = 0; i < items.Length; i++)
{
if (items[i].Wage > max)
max = items[i].Wage;
}
return max;
}
public static string DeleteRecord(EngineerItem item)
{
return DeleteRecord(item.ID);
}
public static string UpdateRecord(EngineerItem 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);
return ExecuteCommand(command);
}
public static string LoadRecords(out EngineerItem[] items, int count)
{
List<EngineerItem> itemsList = new List<EngineerItem>();
items = null;
if (con == null)
return "Connection is not initialized";
try
{
SqlCommand command2 = null;
if (count < 1)
command2 = new SqlCommand("Select * from EngineerTable", con);
else
command2 = new SqlCommand("Select TOP " + count + " * from EngineerTable", con);
SqlDataReader sdr1 = command2.ExecuteReader();
while (sdr1.Read())
{
EngineerItem item = new EngineerItem();
item.ID = (int)sdr1["engineer_id"];
item.Name = sdr1["name"].ToString();
item.Surname = sdr1["surname"].ToString();
item.Date_of_birth = ToReaderDate(sdr1["birthdate"].ToString());
item.Wage = (int)sdr1["wage"];
item.Employed_since = ToReaderDate(sdr1["Employed_since"].ToString());
itemsList.Add(item);
}
sdr1.Close();
}
catch (Exception e)
{
return e.ToString();
}
items = itemsList.ToArray();
if (items.Length != 1)
return items.Length + " records loaded";
else
return items.Length + " record loaded";
}
public static string LoadRecords(out EngineerItem[] items)
{
return LoadRecords(out items, 0);
}
public static string LoadRecord(int id, out EngineerItem item)
{
List<EngineerItem> itemsList = new List<EngineerItem>();
item = null;
if (con == null)
return "Connection is not initialized";
try
{
SqlCommand command2 = new SqlCommand("Select * from EngineerTable where engineer_id='" + id + "';", con);
SqlDataReader sdr1 = command2.ExecuteReader();
while (sdr1.Read())
{
item = new EngineerItem();
item.ID = (int)sdr1["engineer_id"];
item.Name = sdr1["name"].ToString();
item.Surname = sdr1["surname"].ToString();
item.Date_of_birth = ToReaderDate(sdr1["birthdate"].ToString());
item.Wage = (int)sdr1["wage"];
item.Employed_since = ToReaderDate(sdr1["Employed_since"].ToString());
}
sdr1.Close();
}
catch (Exception e)
{
return e.ToString();
}
if (item != null)
return "Record loaded";
else
return "Record " + id + " not found";
}
public static string InsertRecord(EngineerItem 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));
return ExecuteCommand(command2, "Record saved");
}
