// 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();
}
}
