// When the form first loads private void SuppliersForm_Load(object sender, EventArgs e) { // get the info from the DB and display it in the visible fields in the form DisplaySuppliers(); // set the current supplier (first in the list on load) currentSupplier = suppliers.First<Supplier>(); // the current selection stores the List index value of whatever Supplier Object is currently selected // on load it will be 0, the index of the first supplier currentSelection = suppliers.IndexOf(currentSupplier); // hide the modify and delete buttons until there is something selected to modify or delete modifyBtn.Enabled = false; deleteBtn.Enabled = false; }
// updates one supplier by supplierId public static bool UpdateSupplier(Supplier supplier) { SqlConnection connection = TravelExpertsDB.GetConnection(); string updateSuppliers = "UPDATE Suppliers SET SupName=@SupName WHERE SupplierId=@SupplierId"; SqlCommand updateCommand = new SqlCommand(updateSuppliers, connection); updateCommand.Parameters.AddWithValue("@SupplierId", supplier.SupplierId); updateCommand.Parameters.AddWithValue("@SupName", supplier.SupName); try { connection.Open(); // since no data is acutally being requested from the DB, only given, we use a non query SQL command updateCommand.ExecuteNonQuery(); } // if the update failed, catch the exception catch (Exception ex) { //send it to the GUI to be processed and displayed to the user throw ex; } finally { // close the connection connection.Close(); } return true; }
// Inserts one supplier by supplierId public static Supplier AddSupplier(string supName) { int maxId = -1; Supplier supplier = new Supplier(maxId, supName); SqlConnection connection = TravelExpertsDB.GetConnection(); // select SQL string which finds the last row in the table and stores its ID string findMaxId = "SELECT * " + "FROM Suppliers " + "WHERE SupplierId = (" + "SELECT MAX(SupplierId) " + "FROM Suppliers)"; // finding and storing the last row in the table is necessary because: // 1. the supplier IDs in the table are non-sequential and // 2. the primary keys (in this case the suplier IDs) are not set to // auto increment which presents a problem when adding new rows to the table SqlCommand findMaxIdCommand = new SqlCommand(findMaxId, connection); // insert SQL string for adding a new supplier string addString = "INSERT " + "INTO Suppliers (SupplierId, SupName) " + "VALUES (@SupplierId, @SupName)"; try { connection.Open(); SqlDataReader supplierReader = findMaxIdCommand.ExecuteReader(CommandBehavior.SingleRow); // if you get a row back it will be the last row in the table if (supplierReader.HasRows) { // read that row supplierReader.Read(); // and set the maxId variable to that row's supplier ID // + 1 so we can add the next record after that last one maxId = ((int)supplierReader[0]) + 1; // close the connection connection.Close(); // then actually start to add the new row SqlCommand addCommand = new SqlCommand(addString, connection); // unless you finish one SQL command before you start another you get errors // so it's important to close and reopen the connection for multiple SQL commands // this also took me over an hour to figure out... sigh... :) connection.Open(); // add the ID addCommand.Parameters.AddWithValue("@SupplierId", maxId); // add the name addCommand.Parameters.AddWithValue("@SupName", supName); addCommand.ExecuteNonQuery(); // Set the supplier object with the new values supplier.SupplierId = maxId; supplier.SupName = supName; } } catch (Exception ex) { throw ex; } finally { // close the second connection connection.Close(); } // return the supplier object return supplier; }
// Gets a List of all Suppliers public static List<Supplier> GetSuppliers() { // Get the connection string stored centrally in the TravelExpertsDB class SqlConnection connection = TravelExpertsDB.GetConnection(); // set the select statement, the * means all suppliers string selectStatement = "SELECT * FROM Suppliers"; // build the actually SQL command using the connection string and select statement SqlCommand selectCommand = new SqlCommand(selectStatement, connection); // instantiate a new List of Suppliers to put all that DB info into List<Supplier> suppliers = new List<Supplier>(); // try to access the database.. an exception will be caught if any failure occurrs try { connection.Open(); // um.. open the database.. // prepare to execute the SQL command, this two step execution took me a while to understand // This actually reads the data from the database and stores it. But a separate command // actually retrieves the data from the SqlDataReader. I wasn't putting in the // supplierReader.Read() code which actually moves through the rows of data stored when // accessed from the DB. This took me about an hour to figure out... sigh SqlDataReader supplierReader = selectCommand.ExecuteReader(); // if there were actually any rows retrieved from the DB if (supplierReader.HasRows) { // loop through those rows in the SqlDataReader and store them in the List while (supplierReader.Read()) { // instantiate a new object of type Supplier and use the default constructor with parameters from the DB Supplier supplier = new Supplier((int)supplierReader["SupplierId"], (string)supplierReader["SupName"]); // add that object to the List created earlier suppliers.Add(supplier); } return suppliers; } // if there were no rows in the SqlDataReader else { return null; // as far as I know this is different from a database exception so it isn't redundant } } // catch any exceptions that may have occured when the DB was accessed catch (SqlException ex) { throw ex; } finally { // after everything has been attempted, close the connection connection.Close(); } }
// Gets one supplier by supplierId public static Supplier GetSupplier(int supplierId) { // a supplier object to store the returning row of data Supplier supplier; // the connection string from TravelExpertsDB SqlConnection connection = TravelExpertsDB.GetConnection(); // SELECT everything from the Supliers Table by a unique supplierId string selectStatement = "SELECT * " + "FROM Suppliers " + "WHERE SupplierID = @supplierId"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); // when the select statement is built using the selectCommand, substitute the // @supplierId placeholder with the value stored in the supplierId variable // passed into this method when it was called from the GUI using the Supplier // entity class selectCommand.Parameters.AddWithValue("@SupplierId", supplierId); try { connection.Open(); SqlDataReader supplierReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); if (supplierReader.HasRows) { // since we are only trying to retrieve one row from the Suppliers table in the DB there is no // need to loop through the supplierReader when we execute the supplierReader.Read() method supplierReader.Read(); // instantiate a new object of type Supplier and use the default constructor with parameters from the DB supplier = new Supplier((int)supplierReader["SupplierId"], (string)supplierReader["SupName"]); // send the resulting info, stored in this new object, back to the GUI which requested it return supplier; } else { // if there were no rows read from the DB return null return null; } } // catch any DB exceptions that may have occured catch (SqlException ex) { throw ex; } finally { // close the connection connection.Close(); } }
// when the delete button is clicked private void deleteBtn_Click(object sender, EventArgs e) { // confirm witht the user that they really want to delete the selected supplier DialogResult result = MessageBox.Show("Delete " + currentSupplier.SupName.ToString() + "?", "Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question); // if the user's answer is yes if (result == DialogResult.Yes) { try { // call SuppliersDB and use the DeleteSupplier() Method // if the DeleteSupplier() Method succeeds if (SuppliersDB.DeleteSupplier(currentSupplier.SupplierId)) { // tell the user the Supplier was deleted successfully MessageBox.Show("Supplier deleted."); // now that the last item in the list has been deleted, reset the current supplier to the first in the list currentSupplier = suppliers.First<Supplier>(); // update the Suppliers list box and clear all other fields DisplaySuppliers(); } // if the DeleteSupplier() Method did not succeed else { // read the current Supplier from the DB currentSupplier = SuppliersDB.GetSupplier(currentSupplier.SupplierId); // if it is not there if (currentSupplier == null) { // it has already been deleted MessageBox.Show("Another user has already deleted that Supplier.", "DB Error"); } else { // it has been modified concurrently MessageBox.Show("Another user has has changed that Supplier and it could not be deleted.", "DB Error"); } } } // catch any other problems not foreseen catch (Exception) { MessageBox.Show("Couldn't delete this supplier. It still has products associated with it."); } } }
// when the list box selected item is changed private void suppliersLbx_SelectedIndexChanged(object sender, EventArgs e) { // reset the currentSelection currentSelection = suppliersLbx.SelectedIndex; // reset the current supplier based on the currentSelection currentSupplier = suppliers[currentSelection]; supplierIdTxt.Text = currentSupplier.SupplierId.ToString(); nameTxt.Text = currentSupplier.SupName; // enable the modify and delete buttons modifyBtn.Enabled = true; deleteBtn.Enabled = true; //paul ProductsLst.Items.Clear(); foreach (Product p in SuppliersDB.GetSuppliersProducts(Convert.ToInt32(supplierIdTxt.Text))) { ProductsLst.Items.Add(p); } // end Paul }
private void btnSearch_Click(object sender, EventArgs e) { int searchId = -1; // check to see if the user input is actually a valid integer bool result = Int32.TryParse(supplierIdTxt.Text, out searchId); // if it is a valid and positive integer if (result == true && searchId >= 0) { currentSupplier = SuppliersDB.GetSupplier(searchId); if (currentSupplier == null) { // Supplier ID is not in the database MessageBox.Show("The Supplier ID you entered is not in the database.", "DB Error"); suppliersLbx.SetSelected((currentSelection), true); } else { // if a valid Supplier was returned from the database display it to the user currentSelection = suppliers.FindIndex(supplier => supplier.SupplierId.Equals(searchId)); supplierIdTxt.Text = currentSupplier.SupplierId.ToString(); nameTxt.Text = currentSupplier.SupName; // change the currently selected package in the combo box to the one returned by from the database suppliersLbx.SetSelected(currentSelection, true); } } else { // Package ID is not a positive integer MessageBox.Show("The Supplier ID must be a positive integer"); currentSelection = CurrentSupplier.SupplierId; supplierIdTxt.Text = currentSelection.ToString(); } }