/* public List<Address> getAllAddresses() { try { MySqlCommand cmd = new MySqlCommand(); cmd.CommandText = "getaddresses"; cmd.CommandType = CommandType.StoredProcedure; //cmd.Connection = connection; MySqlDataReader data; List<Address> listOfAddresses = new List<Address>(); connection.Open(); data = cmd.ExecuteReader(); while (data.Read()) { int addressID = data.GetInt32("idAddress"); string addressln1 = data.GetString("AddressLine1"); string addressln2 = data.GetString("AddressLine2"); string postCode = data.GetString("Postcode"); string region = data.GetString("Region"); string country = data.GetString("Country"); Address address = new Address(addressID, addressln1, addressln2, postCode, region, country); listOfAddresses.Add(address); } connection.Close(); return listOfAddresses; } catch (Exception e) { MessageBox.Show(e.Message); return null; } } */ public List<Worker> getAllWorkers() { try { connection.Open(); MySqlCommand command; MySqlDataReader data; string query = "BEGIN; CREATE OR REPLACE VIEW t AS SELECT * FROM worker; SELECT * FROM t;"; List<Worker> listOfWorkers = new List<Worker>(); command = new MySqlCommand(query, connection); data = command.ExecuteReader(); //THIS NEEDS EXPANDING FOR CONTACT DETAILS while (data.Read()) { int workerID = data.GetInt32("WorkerId"); int storeID = data.GetInt32("Store_idStore"); int jobID = data.GetInt32("Job_idJob"); string forename = data.GetString("FirstName"); string surname = data.GetString("LastName"); Worker worker = new Worker(workerID, storeID, jobID, forename, surname); listOfWorkers.Add(worker); } connection.Close(); for (int i = 0; i < listOfWorkers.Count; i++) { Salary addToWorker = getJobDetails(listOfWorkers[i].getJobID()); String storeName = getStoreName(listOfWorkers[i].getStoreID()); listOfWorkers[i].setJobName(addToWorker.getJobName()); listOfWorkers[i].setSalary(addToWorker.getSingleSalary()); listOfWorkers[i].setStoreName(storeName); } return listOfWorkers; } catch (Exception e) { MessageBox.Show(e.Message); return null; } }
public List<Worker> chartWorkersSalaries() { try { connection.Open(); MySqlCommand command; MySqlDataReader data; string query = "SELECT WorkerId,Store_idStore,Job_idJob FROM worker;"; List<Worker> listOfWorkers = new List<Worker>(); command = new MySqlCommand(query, connection); data = command.ExecuteReader(); while (data.Read()) { int workerID = data.GetInt32("WorkerId"); int storeID = data.GetInt32("Store_idStore"); int jobID = data.GetInt32("Job_idJob"); Worker worker = new Worker(); worker.setWorkerID(workerID); worker.setStoreID(storeID); worker.setJobID(jobID); listOfWorkers.Add(worker); } connection.Close(); foreach (Worker wrkr in listOfWorkers) { decimal salary = getWorkerSalary(wrkr.getJobID()); wrkr.setSalary(salary); } return listOfWorkers; } catch (Exception e) { MessageBox.Show(e.Message); return null; } }