// method to return a list based on a filter from the db public static List <SupplierContacts> listSuppliersByFilter(string field, string filter) { List <SupplierContacts> supplier = new List <SupplierContacts>(); // set up the connection SqlConnection connection = TravelExpertsDB.GetConnection(); // sql statement string selectStatement = "SELECT * FROM SupplierContacts WHERE " + field + " LIKE @filter ORDER BY SupConCompany"; // creates the sql command and parameters SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@filter", "%" + filter + "%"); // use a try catch to attampt to get the data try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); // reads the data into the object while (reader.Read()) { SupplierContacts s = new SupplierContacts(); s.SupplierContactId = Convert.ToInt32(reader["SupplierContactId"]); s.SupConFirstName = reader["SupConFirstName"].ToString(); s.SupConLastName = reader["SupConLastName"].ToString(); s.SupConCompany = reader["SupConCompany"].ToString(); s.SupConAddress = reader["SupConAddress"].ToString(); s.SupConCity = reader["SupConCity"].ToString(); s.SupConProv = reader["SupConProv"].ToString(); s.SupConPostal = reader["SupConPostal"].ToString(); s.SupConCountry = reader["SupConCountry"].ToString(); s.SupConBusPhone = reader["SupConBusPhone"].ToString(); s.SupConFax = reader["SupConFax"].ToString(); s.SupConEmail = reader["SupConEmail"].ToString(); s.SupConURL = reader["SupConURL"].ToString(); s.AffiliationId = reader["AffiliationId"].ToString(); s.SupplierId = Convert.ToInt32(reader["SupplierID"]); supplier.Add(s); } reader.Close(); return(supplier); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static SupplierContacts GetSupplierbySupID(int Supplierid) { // set up the connection SqlConnection connection = TravelExpertsDB.GetConnection(); // sql statement string selectStatement = "SELECT * FROM SupplierContacts WHERE SupplierId = @SupplierId"; // creates the sql command and parameters SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@SupplierId", Supplierid); // use a try catch to attampt to get the data try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); // reads the data into the object if (reader.Read()) { SupplierContacts s = new SupplierContacts(); s.SupplierContactId = Convert.ToInt32(reader["SupplierContactId"]); s.SupConFirstName = reader["SupConFirstName"].ToString(); s.SupConLastName = reader["SupConLastName"].ToString(); s.SupConCompany = reader["SupConCompany"].ToString(); s.SupConAddress = reader["SupConAddress"].ToString(); s.SupConCity = reader["SupConCity"].ToString(); s.SupConProv = reader["SupConProv"].ToString(); s.SupConPostal = reader["SupConPostal"].ToString(); s.SupConCountry = reader["SupConCountry"].ToString(); s.SupConBusPhone = reader["SupConBusPhone"].ToString(); s.SupConFax = reader["SupConFax"].ToString(); s.SupConEmail = reader["SupConEmail"].ToString(); s.SupConURL = reader["SupConURL"].ToString(); s.AffiliationId = reader["AffiliationId"].ToString(); s.SupplierId = Convert.ToInt32(reader["SupplierID"]); return(s); } else { return(null); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
//method to update the supplier data to the db public static bool UpdateSupplier(SupplierContacts oldSup, SupplierContacts newSup) { // set up connection SqlConnection connection = TravelExpertsDB.GetConnection(); // sql statement string updateStatement = "UPDATE SupplierContacts SET " + "SupplierContactId = @newSupplierContactId, SupConFirstName = @newSupConFirstName, SupConLastName = @newSupConLastName, " + "SupConCompany = @newSupConCompany, SupConAddress = @newSupConAddress, SupConCity = @newSupConCity, SupConProv = @newSupConProv, " + "SupConPostal = @newSupConPostal, SupConCountry = @newSupConCountry, SupConBusPhone = @newSupConBusPhone, SupConFax = @newSupConFax, " + "SupConEmail = @newSupConEmail, SupConURL = @newSupConURL" + " WHERE SupplierContactId = @oldSupplierContactId"; // creates the sql command and parameters SqlCommand command = new SqlCommand(updateStatement, connection); command.Parameters.AddWithValue("@newSupplierContactId", newSup.SupplierContactId); command.Parameters.AddWithValue("@newSupConFirstName", newSup.SupConFirstName); command.Parameters.AddWithValue("@newSupConLastName", newSup.SupConLastName); command.Parameters.AddWithValue("@newSupConCompany", newSup.SupConCompany); command.Parameters.AddWithValue("@newSupConAddress", newSup.SupConAddress); command.Parameters.AddWithValue("@newSupConCity", newSup.SupConCity); command.Parameters.AddWithValue("@newSupConProv", newSup.SupConProv); command.Parameters.AddWithValue("@newSupConPostal", newSup.SupConPostal); command.Parameters.AddWithValue("@newSupConCountry", newSup.SupConCountry); command.Parameters.AddWithValue("@newSupConBusPhone", newSup.SupConBusPhone); command.Parameters.AddWithValue("@newSupConFax", newSup.SupConFax); command.Parameters.AddWithValue("@newSupConEmail", newSup.SupConEmail); command.Parameters.AddWithValue("@newSupConURL", newSup.SupConURL); command.Parameters.AddWithValue("@newAffiliationId", newSup.AffiliationId); command.Parameters.AddWithValue("@newSupplierId", newSup.SupplierId); command.Parameters.AddWithValue("@oldSupplierContactId", oldSup.SupplierContactId); // use a try catch to attampt to update the data try { connection.Open(); int count = command.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
// btn to delete a record private void btnDelete_Click(object sender, EventArgs e) { if (dgvSuppliers.SelectedRows.Count > 0) { // sets the object to delete int index = dgvSuppliers.SelectedRows[0].Index; suplierContact = suppliersContacts[index]; // asks user if they are sure DialogResult result = MessageBox.Show("Delete the supplier " + suplierContact.SupConCompany + "?", "Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (result == DialogResult.Yes) { // creates the other table objects Supplier supplier = new Supplier(); List <Products_Suppliers> products_suppliers = new List <Products_Suppliers>(); try // tries to delete the table records { // gets the data for the tasble records that need to be deleted supplier = SupplierDB.GetSupplier(suplierContact.SupplierId); products_suppliers = Products_SuppliersDB.GetAllProdSupOnID(suplierContact.SupplierId); // deletes each productsuppliers table record foreach (Products_Suppliers ps in products_suppliers) { Products_SuppliersDB.DeleteProdSup(ps); } // deltes the suppliercontacts record SupplierContactsDB.DeleteSup(suplierContact); // deltes the supplier record SupplierDB.DeleteSup(supplier); // redisplays the dgv suppliersContacts = SupplierContactsDB.listSuppliers(); refreshDGV(); } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } } } else { MessageBox.Show("Please select a Product"); } }
// method to add data to the db public static void AddSupp(SupplierContacts sup) { // set up the connection SqlConnection connection = TravelExpertsDB.GetConnection(); // sql statement string insertStatement = "INSERT INTO SupplierContacts VALUES " + "(@SupplierContactId, @SupConFirstName, @SupConLastName, @SupConCompany, @SupConAddress, @SupConCity, @SupConProv, @SupConPostal, " + "@SupConCountry, @SupConBusPhone, @SupConFax, @SupConEmail, @SupConURL, NULL, @SupplierId)"; // creates the sql command and parameters SqlCommand command = new SqlCommand(insertStatement, connection); command.Parameters.AddWithValue("@SupplierContactId", sup.SupplierContactId); command.Parameters.AddWithValue("@SupConFirstName", sup.SupConFirstName); command.Parameters.AddWithValue("@SupConLastName", sup.SupConLastName); command.Parameters.AddWithValue("@SupConCompany", sup.SupConCompany); command.Parameters.AddWithValue("@SupConAddress", sup.SupConAddress); command.Parameters.AddWithValue("@SupConCity", sup.SupConCity); command.Parameters.AddWithValue("@SupConProv", sup.SupConProv); command.Parameters.AddWithValue("@SupConPostal", sup.SupConPostal); command.Parameters.AddWithValue("@SupConCountry", sup.SupConCountry); command.Parameters.AddWithValue("@SupConBusPhone", sup.SupConBusPhone); command.Parameters.AddWithValue("@SupConFax", sup.SupConFax); command.Parameters.AddWithValue("@SupConEmail", sup.SupConEmail); command.Parameters.AddWithValue("@SupConURL", sup.SupConURL); //command.Parameters.AddWithValue("@AffiliationId", sup.AffiliationId); command.Parameters.AddWithValue("@SupplierId", sup.SupplierId); // use a try catch to attampt to add the data try { connection.Open(); command.ExecuteNonQuery(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
// btn to bring up the addmodify form to edit a record private void btnEdit_Click(object sender, EventArgs e) { // checks if there is a record selected if (dgvSuppliers.SelectedRows.Count > 0) { //creates the form and sets the options int index = dgvSuppliers.SelectedRows[0].Index; frmAddModifySupplier editsupplier = new frmAddModifySupplier(); editsupplier.add = false; editsupplier.supplierContact = suppliersContacts[index]; DialogResult result = editsupplier.ShowDialog(); if (result == DialogResult.OK) { try // if the edit for good redisplay the dgv { suplierContact = editsupplier.supplierContact; suppliersContacts = SupplierContactsDB.listSuppliers(); refreshDGV(); // selects the edited record int n = -1; foreach (SupplierContacts sc in suppliersContacts) { if (sc.SupplierContactId == suplierContact.SupplierContactId) { n = suppliersContacts.IndexOf(sc); break; } } dgvSuppliers.Rows[n].Selected = true; //dgvSuppliers.FirstDisplayedScrollingRowIndex = n; } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } } } }
private void DeleteSupplier(Supplier sp) { SupplierContacts suplierContact = SupplierContactsDB.GetSupplierbySupID(sp.SupplierId); // asks user if they are sure DialogResult result = MessageBox.Show("Delete the supplier " + suplierContact.SupConCompany + "?", "Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (result == DialogResult.Yes) { // creates the other table objects Supplier supplier = new Supplier(); List <Products_Suppliers> products_suppliers = new List <Products_Suppliers>(); try // tries to delete the table records { // gets the data for the tasble records that need to be deleted supplier = SupplierDB.GetSupplier(suplierContact.SupplierId); products_suppliers = Products_SuppliersDB.GetAllProdSupOnID(suplierContact.SupplierId); // deletes each productsuppliers table record foreach (Products_Suppliers ps in products_suppliers) { Products_SuppliersDB.DeleteProdSup(ps); } // deletes the suppliercontacts record SupplierContactsDB.DeleteSup(suplierContact); // deletes the supplier record SupplierDB.DeleteSup(supplier); } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } RefreshSuppliers(); } }
// button to bring up the addmodify form to add data to the DB private void btnAdd_Click(object sender, EventArgs e) { // creates the new form and sets the options frmAddModifySupplier newaddfrm = new frmAddModifySupplier(); newaddfrm.add = true; DialogResult result = newaddfrm.ShowDialog(); // if the results are good redisplay the dgv if (result == DialogResult.OK) { try // tries to get the data from the db and refresh the dgv { suplierContact = newaddfrm.supplierContact; suppliersContacts = SupplierContactsDB.listSuppliers(); refreshDGV(); // select the newly made record int n = -1; foreach (SupplierContacts sc in suppliersContacts) { if (sc.SupplierContactId == suplierContact.SupplierContactId) { n = suppliersContacts.IndexOf(sc); break; } } dgvSuppliers.Rows[n].Selected = true; dgvSuppliers.FirstDisplayedScrollingRowIndex = n; } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } } }
// class to delete a prodoctsupplier from the database public static bool DeleteSup(SupplierContacts sup) { // set up the connection SqlConnection connection = TravelExpertsDB.GetConnection(); // sql statement string deleteStatement = "DELETE FROM SupplierContacts WHERE SupplierContactId = @SupplierContactId"; // creates the sql command and parameters SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.Parameters.AddWithValue("@SupplierContactId", sup.SupplierContactId); // use a try catch to attampt to delete the data try { connection.Open(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
// button for accepting the form private void btnAccept_Click(object sender, EventArgs e) { if (txtCName.IsPresent()) { //for add if (add) { this.createSupplierObjects(); try { SupplierDB.AddSupp(supplier); SupplierContactsDB.AddSupp(supplierContact); foreach (Products_Suppliers ps in productSuppliers) { ps.supplierid = supplier.SupplierId; Products_SuppliersDB.AddProdSupp(ps); } this.DialogResult = DialogResult.OK; } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } } // for modify else { SupplierContacts newSupplierinfo = new SupplierContacts(); newSupplierinfo.SupplierContactId = Convert.ToInt32(txtSCID.Text); newSupplierinfo.SupConFirstName = txtFirst.Text.ToString(); newSupplierinfo.SupConLastName = txtLast.Text.ToString(); newSupplierinfo.SupConCompany = txtCName.Text.ToString(); newSupplierinfo.SupConAddress = txtAddress.Text.ToString(); newSupplierinfo.SupConCity = txtCity.Text.ToString(); newSupplierinfo.SupConProv = txtProv.Text.ToString(); newSupplierinfo.SupConPostal = txtPO.Text.ToString(); newSupplierinfo.SupConCountry = txtCountry.Text.ToString(); newSupplierinfo.SupConBusPhone = txtPhone.Text.ToString(); newSupplierinfo.SupConFax = txtFax.Text.ToString(); newSupplierinfo.SupConEmail = txtEmail.Text.ToString(); newSupplierinfo.SupConURL = txtURL.Text.ToString(); newSupplierinfo.AffiliationId = ""; newSupplierinfo.SupplierId = Convert.ToInt32(txtSCID.Text); try { if (!SupplierContactsDB.UpdateSupplier(supplierContact, newSupplierinfo)) { MessageBox.Show("That Supplier has been updated or deleted already.", "Database Error"); this.DialogResult = DialogResult.Retry; } else { supplierContact = newSupplierinfo; this.DialogResult = DialogResult.OK; } } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } } } }