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