public static int AddSupplier(Supplier supplier)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string insertStatement = "INSERT Into Suppliers " +
"(SupplierId, SupName) " +
"VALUES (@SupId, @SupName)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@SupName", supplier.SupName);
try
{
// open the connection
connection.Open();
string selectStatement =
"SELECT max(SupplierId) FROM Suppliers"; // this is to select the last id so as to use the next one
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
int supplierId = Convert.ToInt32(selectCommand.ExecuteScalar()) + 1;
insertCommand.Parameters.AddWithValue("@SupId", supplierId);
insertCommand.ExecuteNonQuery();
return supplierId;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
private void btnsave_Click(object sender, EventArgs e)
{
if (!IsValidData()) return;
// ask for confirmation before saving
DialogResult result = MessageBox.Show("Save the entered data?", "Confirm Save", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.No) return;
if (addMode) // add mode
{
// call method to accept the data from the message boxes
supplier = new Supplier();
this.AcceptSupplierData(supplier);
try
{
// call add supplier method to save the entered data and return the id of the new package
supplier.SupplierId = SupplierDB.AddSupplier(supplier);
if (supplier.SupplierId > 0)
{
this.DialogResult = DialogResult.OK;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
else // modify mode
{
Supplier newSupplier = new Supplier();
newSupplier.SupplierId = supplier.SupplierId;
this.AcceptSupplierData(newSupplier);
try
{
// call the edit package methode and check if succeeded
if (SupplierDB.UpdateSupplier(supplier, newSupplier))
{
supplier = newSupplier;
this.DialogResult = DialogResult.OK;
}
else
{
MessageBox.Show("Another user has already updated or deleted that supplier.", "Database Error");
this.DialogResult = DialogResult.Retry;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
}
// make method to get all the suppliers of a specific product
public static List<Supplier> GetSuppliersOfProduct(int productId)
{
List<Supplier> suppliers = new List<Supplier>();
// establish a connection with the database
SqlConnection connection = TravelExpertsDB.GetConnection();
// create select statement to select from the database
string selectStatement =
"SELECT * " +
"FROM Suppliers " +
"WHERE SupplierId in (Select SupplierId from Products_Suppliers where ProductId = @ProductId)";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductId", productId);
// try to catch exceptions
try
{
// open the connection
connection.Open();
SqlDataReader reader =
selectCommand.ExecuteReader(); // retrieve multiple rows
while (reader.Read()) // there is a row
{
// process the row
Supplier supplier = new Supplier();
supplier.SupplierId = Convert.ToInt32(reader["SupplierId"]);
supplier.SupName = reader["SupName"].ToString();
suppliers.Add(supplier);
}
return suppliers;
}
catch (SqlException ex)
{
throw ex; // throw the exception for the form to handle
}
finally
{
connection.Close();
}
}
private void AcceptSupplierData(Supplier supplier)
{
supplier.SupName = txtSupplierName.Text;
}
public static List<string> GetProductsAndSuppliers(int packageId)
{
List<string> listOfProdSup = new List<string>();
// establish a connection with the database
SqlConnection connection = TravelExpertsDB.GetConnection();
// create select statement to select from the database
string selectStatement =
"select packages_products_suppliers.packageId, products.prodname, " +
"suppliers.Supname " +
"from packages_products_suppliers inner join " +
"(products inner join " +
"(products_suppliers inner join suppliers on products_suppliers.supplierid = suppliers.supplierid) " +
"on products.productid = products_suppliers.productid) " +
"on packages_products_suppliers.productsupplierid = products_suppliers.productsupplierid " +
"where PackageId = @PackageId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@PackageId", packageId);
// try to catch exceptions
try
{
// open the connection
connection.Open();
SqlDataReader reader =
selectCommand.ExecuteReader(); // retrieve multiple rows
while (reader.Read()) // there is a row
{
// process the row
Product product = new Product();
product.ProdName = reader["ProdName"].ToString();
Supplier supplier = new Supplier();
supplier.SupName = reader["SupName"].ToString();
listOfProdSup.Add(product.ProdName + " from " + supplier.SupName);
}
return listOfProdSup;
}
catch (SqlException ex)
{
throw ex; // throw the exception for the form to handle
}
finally
{
connection.Close();
}
}
public static bool DeleteSupplier(Supplier supplier)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string deleteStatement = "DELETE FROM SupplierContacts " +
"WHERE SupplierId = @SupplierId ";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection);
deleteCommand.Parameters.AddWithValue("@SupplierId", supplier.SupplierId);
string deleteStatement2 = "DELETE FROM Suppliers " +
"WHERE SupplierId = @SupplierId " +
"AND SupName = @SupName";
SqlCommand deleteCommand2 = new SqlCommand(deleteStatement2, connection);
deleteCommand2.Parameters.AddWithValue("@SupplierId", supplier.SupplierId);
deleteCommand2.Parameters.AddWithValue("@SupName", supplier.SupName);
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
count = deleteCommand2.ExecuteNonQuery();
if (count > 0)
return true;
else
return false;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
// Method to get Supplier by name
public static Supplier GetSupplierByName(string supName)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectStatement = "Select Top 1 SupplierId, SupName From Suppliers Where SupName Like @SupName Order By SupName";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
supName = supName + "%"; // select where the supplier name starts with a string
selectCommand.Parameters.AddWithValue("@SupName", supName);
try
{
// Open Connection
connection.Open();
SqlDataReader supReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (supReader.Read())
{
Supplier supplier = new Supplier();
supplier.SupplierId = (int)supReader["SupplierId"];
supplier.SupName = supReader["SupName"].ToString();
return supplier;
}
else
{
return null;
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool UpdateSupplier(Supplier oldSupplier, Supplier newSupplier)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string updateStatement = "UPDATE Suppliers SET " +
"SupplierId = @NewSupplierId, " +
"SupName = @NewSupName " +
"WHERE SupplierId = @OldSupplierId " +
"AND SupName = @OldSupName";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@NewSupplierId", newSupplier.SupplierId);
updateCommand.Parameters.AddWithValue("@NewSupName", newSupplier.SupName);
updateCommand.Parameters.AddWithValue("@OldSupplierId", oldSupplier.SupplierId);
updateCommand.Parameters.AddWithValue("@OldSupName", oldSupplier.SupName);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
return true;
else
return false;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
// Method to get Supplier
public static Supplier GetSupplier(int supplierId)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectStatement
= "SELECT SupplierId, SupName "
+ "FROM Suppliers "
+ "WHERE SupplierId = @SupplierId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@SupplierId", supplierId);
try
{
// Open Connection
connection.Open();
SqlDataReader supReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (supReader.Read())
{
Supplier supplier = new Supplier();
supplier.SupplierId = (int)supReader["SupplierId"];
supplier.SupName = supReader["SupName"].ToString();
return supplier;
}
else
{
return null;
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
// method to get all suppliers
public static List<Supplier> GetAllSuppliers()
{
List<Supplier> suppliers = new List<Supplier>();
// establish a connection with the database
SqlConnection connection = TravelExpertsDB.GetConnection();
// create select statement to select from the database
string selectStatement =
"SELECT SupplierId, SupName " +
"FROM Suppliers Order By SupName";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
// try to catch exceptions
try
{
// open the connection
connection.Open();
SqlDataReader reader =
selectCommand.ExecuteReader();
while (reader.Read()) // there is row(s)
{
// process the row(s)
Supplier supplier = new Supplier();
supplier.SupplierId = Convert.ToInt32(reader["SupplierId"]);
supplier.SupName = reader["SupName"].ToString();
suppliers.Add(supplier);
}
return suppliers;
}
catch (SqlException ex)
{
throw ex; // throw the exception for the form to handle
}
finally
{
connection.Close();
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
// open the add/modify form in the add mode
frmSupplierDataChange addSupplierForm = new frmSupplierDataChange();
addSupplierForm.addMode = true;
DialogResult result = addSupplierForm.ShowDialog();
if (result == DialogResult.OK)
{
// copy the newly added product from the add/modify form to this form and display the product data
dgvProducts.DataSource = null;
dgvContacts.DataSource = null;
supplier = addSupplierForm.supplier;
this.DisplaySupplier();
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
if (Validator.IsNotEmpty(txtSupplierId))
{
try
{
// call the GetSupplier method which will search for the supplier by its id and retrieve it to supplier variable
supplier = SupplierDB.GetSupplier(Convert.ToInt32(txtSupplierId.Text));
if (supplier == null)
{
MessageBox.Show("No supplier found with this code, please try again.", "Supplier Not Found");
this.ClearControls();
}
else
{
// if a supplier found, displays the data and fill the products and contacts grid with the products of that supplier
this.DisplaySupplier();
FillProductGrid();
FillContactGrid();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
}
private void btnModify_Click(object sender, EventArgs e)
{
// open the add/modify form in the modify mode
frmSupplierDataChange modifySupplierForm = new frmSupplierDataChange();
modifySupplierForm.addMode = false;
modifySupplierForm.supplier = supplier;
DialogResult result = modifySupplierForm.ShowDialog();
if (result == DialogResult.OK)
{
// copy the modified supplier from the add/modify form to this form and display the supplier data after modification
supplier = modifySupplierForm.supplier;
this.DisplaySupplier();
}
else if (result == DialogResult.Retry)
{
btnSearch_Click(sender, e);
if (supplier != null) this.DisplaySupplier();
else this.ClearControls();
}
}