public List<materials> GetMaterials(string CmdText)
{
try
{
Connect();
Debug.WriteLine(CmdText);
if (CmdText == "")
{
CmdText = "SELECT * FROM materials";
}
datareader = GetReader(CmdText);
List<materials> materialslist = new List<materials>();
while (datareader.Read())
{
materials mat = new materials();
mat.material_id = Convert.ToInt16(datareader["material_id"]);
mat.material_name = datareader["material_name"].ToString();
mat.measuring_unit = datareader["measuring_unit"].ToString();
materialslist.Add(mat);
}
datareader.Close();
datareader = null;
return materialslist;
}
catch { return null; }
}
// function to get a whole table from the database
public Container GetWholeEntity()
{
try
{
// switch based on the option
string CmdText = "";
Container c = new Container();
if (Connect())
{
// get all clients
{
CmdText = "SELECT * FROM clients ";
datareader = GetReader(CmdText);
c._clientlist = new List<clients>();
while (datareader.Read())
{
clients Client = new clients();
Client.client_id = Convert.ToInt32(datareader["client_id"]);
Client.client_name = datareader["client_name"].ToString();
Client.email = datareader["email"].ToString();
Client.contact = datareader["contact"].ToString();
c._clientlist.Add(Client);
}
datareader.Close();
datareader = null;
}
// get all employees
{
CmdText = "SELECT * FROM employees ";
datareader = GetReader(CmdText);
c._employeeslist = new List<employees>();
while (datareader.Read())
{
employees emp = new employees();
emp.employee_name = datareader["employee_name"].ToString();
emp.employee_contact_number = datareader["employee_contact_number"].ToString();
emp.employee_id = Convert.ToInt32(datareader["employee_id"].ToString());
c._employeeslist.Add(emp);
}
datareader.Close();
datareader = null;
}
//get all job types
{
CmdText = "SELECT * FROM job_type ";
datareader = GetReader(CmdText);
c._job_typelist = new List<job_type>();
while (datareader.Read())
{
job_type job = new job_type();
job.job_name = datareader["job_name"].ToString();
job.job_type_id = Convert.ToInt32(datareader["job_type_id"].ToString());
job.unit_pay = Convert.ToInt32(datareader["unit_pay"].ToString());
c._job_typelist.Add(job);
}
datareader.Close();
datareader = null;
}
// get all projects
{
CmdText = "SELECT * FROM Projects ";
datareader = GetReader(CmdText);
c._projectlist = new List<projects>();
while (datareader.Read())
{
projects proj = new projects();
proj.project_id = Convert.ToInt32(datareader["project_id"].ToString());
proj.project_name = datareader["project_name"].ToString();
proj.location = datareader["location"].ToString();
proj.description = datareader["description"].ToString();
proj.start_date = Convert.ToDateTime(datareader["start_date"].ToString());
proj.end_date = Convert.ToDateTime(datareader["end_Date"].ToString());
proj.status = datareader["status"].ToString();
proj.client_id = Convert.ToInt32(datareader["client_id"].ToString());
c._projectlist.Add(proj);
}
datareader.Close();
datareader = null;
}
// get all stock material names
{
CmdText = "SELECT * FROM stock_materials";
datareader = GetReader(CmdText);
c._stock_materialslist = new List<stock_materials>();
while (datareader.Read())
{
stock_materials stock = new stock_materials();
stock.stock_id = Convert.ToInt32(datareader["stock_id"]);
stock.material_id = Convert.ToInt32(datareader["material_id"]);
stock.quanitity = Convert.ToInt32(datareader["quantity"]);
c._stock_materialslist.Add(stock);
}
datareader.Close();
datareader = null;
}
// get all material names
{
CmdText = "SELECT * FROM materials";
datareader = GetReader(CmdText);
c._materialslist = new List<materials>();
while (datareader.Read())
{
materials mat = new materials();
mat.material_id = Convert.ToInt16(datareader["material_id"]);
mat.material_name = datareader["material_name"].ToString();
mat.measuring_unit = datareader["measuring_unit"].ToString();
c._materialslist.Add(mat);
}
datareader.Close();
datareader = null;
}
//get all tasks
{
CmdText = "SELECT * FROM tasks";
datareader = GetReader(CmdText);
c._tasklist = new List<task>();
while (datareader.Read())
{
task t = new task();
t.task_id = Convert.ToInt32(datareader["task_id"].ToString());
t.task_name = datareader["task_name"].ToString();
t.start_date = Convert.ToDateTime(datareader["start_date"].ToString());
t.end_date = Convert.ToDateTime(datareader["end_date"].ToString());
t.budget = Convert.ToInt32(datareader["budget"].ToString());
t.status = datareader["status"].ToString();
t.employee_id = Convert.ToInt32(datareader["employee_id"]);
t.project_id = Convert.ToInt32(datareader["project_id"].ToString());
c._tasklist.Add(t);
}
datareader.Close();
datareader = null;
}
return c;
}
else { return null; }
}
catch
{
return null;
}
}
public bool UpdateMaterial(materials m)
{
try
{
string CmdText = "UPDATE materials set material_name = @material_name , measuring_unit = @measuring_unit where material_id = " + m.material_id;
cmd = new MySqlCommand(CmdText, this.conn);
//add node values
cmd.Parameters.AddWithValue("@material_name", m.material_name);
cmd.Parameters.AddWithValue("@measuring_unit", m.measuring_unit);
cmd.ExecuteNonQuery();
return true;
}
catch
{
return false;
}
}