private void btnDelete_Click(object sender, EventArgs e)
{
MessageBoxButtons deleteConfirm = MessageBoxButtons.YesNo;
string message = "Are you sure you want to delete this contact?";
string caption = "Delete?";
DialogResult result;
result = MessageBox.Show(message,caption,deleteConfirm);
if (result == System.Windows.Forms.DialogResult.Yes)
{
int index = lstSupplier.SelectedIndex;
contact = contacts[index];
bool work = SupplierContactsDB.DeleteSupplierContact(contact, contact.SupplierContactId);
if (work == true)
{
MessageBox.Show("Supplier contact has been deleted");
lstSupplierContacts.Items.Clear();
setSupplierContacts();
clearSupplierContact();
}
else
{
MessageBox.Show("Delete has failed");
}
}
}
//delete from Supplier Contact for a particular SupplierContactId
public static bool DeleteCustomer(SupplierContacts contact, int supplierContactId)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
//dynamically creating the delete statement
string deleteStatement =
"DELETE FROM SupplierContacts " +
"WHERE SupplierContactId = " + supplierContactId;
//executing the delete statement
SqlCommand deleteCommand =
new SqlCommand(deleteStatement, connection);
try
{
connection.Open(); //open DB connection
int count = deleteCommand.ExecuteNonQuery();
if (count > 0)
return true;
else
return false;
}
catch (SqlException ex) //catch all sql exception
{
throw ex;
}
finally
{
connection.Close();//close DB connection
}
}
private void lstSupplierContacts_SelectedIndexChanged(object sender, EventArgs e)
{
int index = lstSupplierContacts.SelectedIndex;
contact = contacts[index];
this.displaySupplierContact(); //display contact info for supplier contact
}
private void btnSave_Click(object sender, EventArgs e)
{
if (IsValidData())
{
if (isAddSupplierContact)
{ // adding
supplierContact = new SupplierContacts();
this.PutSupplierContactData(supplierContact);
try
{
supplierContact.SupplierContactId = SupplierContactsDB.AddSupplierContact(supplierContact);
this.DialogResult = DialogResult.OK;
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
else
{ // modifying
SupplierContacts newSupplierContact = new SupplierContacts();
this.PutSupplierContactData(newSupplierContact);
try
{
if (!SupplierContactsDB.UpdateSupplierContact(newSupplierContact))
{
MessageBox.Show("Another user has updateed or deleted the supplier contact.","Database Error");
this.DialogResult = DialogResult.Retry;
}
else
{
supplierContact = newSupplierContact;
this.DialogResult = DialogResult.OK;
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
}
}
//delete button event handler
private void btnDelete_Click(object sender, EventArgs e)
{
MessageBoxButtons deleteConfirm = MessageBoxButtons.YesNo; //create confirm dialog
string message = "Are you sure you want to delete this contact?";
string caption = "Delete?";
DialogResult result;
result = MessageBox.Show(message,caption,deleteConfirm); //get result of message dialog
//if yes is clicked, continue with delete
if (result == System.Windows.Forms.DialogResult.Yes)
{
int index = lstSupplierContacts.SelectedIndex;//get contact
contact = contacts[index]; //set contact to contact in List
//call delete method and return a boolean value
bool work = SupplierContactsDB.DeleteSupplierContact(contact, contact.SupplierContactId);
//if delete worked show message affirming delete
if (work == true)
{
MessageBox.Show("Supplier contact has been deleted");
lstSupplierContacts.Items.Clear();
setSupplierContacts();
clearSupplierContact();
}
else //or inform of fail
{
MessageBox.Show("Delete has failed");
}
}
}
//Event handler for supplier contacts list box index changed, displays selected contact
//in display form
private void lstSupplierContacts_SelectedIndexChanged(object sender, EventArgs e)
{
int index = lstSupplierContacts.SelectedIndex; //get selected supplier contact
contact = contacts[index]; //get contact from the List of contacts
this.displaySupplierContact(); //display contact info for supplier contact
btnModify.Enabled = true;
btnDelete.Enabled = true;
}
//event handler for modify button
private void btnModify_Click(object sender, EventArgs e)
{
frmAddModifySupplierContact frm = new frmAddModifySupplierContact(); //create instance of form
frm.isAddSupplierContact = false; //set to false for modify
int index = lstSupplierContacts.SelectedIndex; //get index of supplier
contact = contacts[index];
frm.supplierContact = contact; //pass current contact to form
DialogResult rslt = frm.ShowDialog(); //get result of modify
//if modify ok
if (rslt == DialogResult.OK)
{
setSupplierContacts(); //reset supplier contacts
clearSupplierContact(); //clear display form
}
//if modify failed and retries
else if (rslt == DialogResult.Retry)
{
if (contact != null)
setSupplierContacts();
else
clearSupplierContact();
}
}
//updates supplier contact details in the SupplierContacts table for one SupplierContactId
public static bool UpdateCustomer(SupplierContacts oldContact,
SupplierContacts newContact, int supplierContactId)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
//dynamically creating the update statement
string updateStatement =
"UPDATE SupplierContacts SET " +
"SupConFirstName = @NewFirstName, " +
"SupConLastName = @NewLastName, " +
"SupConCompany = @NewCompany, " +
"SupConAddress = @NewAddress, " +
"SupConCity = @NewCity, " +
"SupConProv = @NewProv, " +
"SupConPostal = @NewPostal, " +
"SupConCountry = @NewCountry, " +
"SupConBusPhone = @NewPhone, " +
"SupConFax = @NewFax, " +
"SupConEmail = @NewEmail, " +
"SupConURL = @NewURL, " +
"AffiliationId = @NewAffilationId, " +
"SupplierId = @NewSupplierId, " +
"WHERE SupplierContactId = " + supplierContactId;
//executing the update statement after adding necessary paramaeters
SqlCommand updateCommand =
new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue(
"@NewFirstName", newContact.SupConFirstName);
updateCommand.Parameters.AddWithValue(
"@NewLastName", newContact.SupConLastName);
updateCommand.Parameters.AddWithValue(
"@NewCompany", newContact.SupConCompany);
updateCommand.Parameters.AddWithValue(
"@NewAddress", newContact.SupConAddress);
updateCommand.Parameters.AddWithValue(
"@NewCity", newContact.SupConCity);
updateCommand.Parameters.AddWithValue(
"@NewProv", newContact.SupConProv);
updateCommand.Parameters.AddWithValue(
"@NewPostal", newContact.SupConPostal);
updateCommand.Parameters.AddWithValue(
"@NewCountry", newContact.SupConCountry);
updateCommand.Parameters.AddWithValue(
"@NewPhone", newContact.SupConBusPhone);
updateCommand.Parameters.AddWithValue(
"@NewFax", newContact.SupConFax);
updateCommand.Parameters.AddWithValue(
"@NewEmail", newContact.SupConEmail);
updateCommand.Parameters.AddWithValue(
"@NewURL", newContact.SupConURL);
updateCommand.Parameters.AddWithValue(
"@NewAffilationId", newContact.AffiliationId);
updateCommand.Parameters.AddWithValue(
"@NewSupplierId", newContact.SupplierId);
try
{
connection.Open(); //open DB connection
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
return true;
else
return false;
}
catch (SqlException ex) //catch all sql exception
{
throw ex;
}
finally
{
connection.Close();//close DB connection
}
}
//returns detail list of supplier contacts for selected SupplierId
public static List<SupplierContacts> GetSupplierContactsList(int supplierId)
{
//declaring a List to hold a set of SupplierContacts details
List<SupplierContacts> contactsList = new List<SupplierContacts>();
//create connection object for connecting to the database
SqlConnection connection = TravelExpertsDB.GetConnection();
//building the select statement dynamically for retriving data
string selectStatement
= "SELECT SupplierContactId, SupConFirstName, SupConLastName, SupConCompany, SupConAddress, "
+ "SupConCity,SupConProv, SupConPostal, SupConCountry, SupConBusPhone, SupConFax, SupConEmail, "
+ "SupConURL, AffiliationId, SupplierId "
+ "FROM SupplierContacts "
+ "WHERE SupplierId = @SupplierId";
//executing the select statement after adding necessary paramaeters
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@SupplierId", supplierId);
try
{
connection.Open(); //open DB connection
SqlDataReader contactReader = selectCommand.ExecuteReader();
while (contactReader.Read())
{
SupplierContacts contact = new SupplierContacts();
//assigning values to different properties of the object for the data fetched
contact.SupplierContactId = (int)contactReader["SupplierContactId"];
contact.SupConFirstName = contactReader["SupConFirstName"].ToString();
contact.SupConLastName = contactReader["SupConLastName"].ToString();
contact.SupConCompany = contactReader["SupConCompany"].ToString();
contact.SupConAddress = contactReader["SupConAddress"].ToString();
contact.SupConCity = contactReader["SupConCity"].ToString();
contact.SupConProv = contactReader["SupConProv"].ToString();
contact.SupConPostal = contactReader["SupConPostal"].ToString();
contact.SupConCountry = contactReader["SupConCountry"].ToString();
contact.SupConBusPhone = contactReader["SupConBusPhone"].ToString();
contact.SupConFax = contactReader["SupConFax"].ToString();
contact.SupConEmail = contactReader["SupConEmail"].ToString();
contact.SupConURL = contactReader["SupConURL"].ToString();
contact.AffiliationId = contactReader["AffiliationId"].ToString();
contact.SupplierId = (int)contactReader["SupplierId"];
//adding object to the list
contactsList.Add(contact);
}
contactReader.Close(); //close data reader
}
catch (SqlException ex) //catch all sql exception
{
throw ex;
}
finally
{
connection.Close(); //close DB connection
}
return contactsList; //return list of objects
}
//add a supplier contact to the SupplierContacts table
public static int AddSupplierContact(SupplierContacts contact)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
//dynamically creating the insert statement
string insertStatement =
"INSERT INTO SupplierContacts " +
"(SupConFirstName, SupConLastName, SupConCompany, SupConAddress, "
+ "SupConCity,SupConProv, SupConPostal, SupConCountry, SupConBusPhone, SupConFax, SupConEmail, "
+ "SupConURL, AffiliationId, SupplierId) "
+ "VALUES (@FisrtName, @LastName, @Company, @Address, @City, @Prov, @Postal, @Country, @Phone, @Fax, @Email, @URL, @AffilationId, @SupplierId)";
//executing the insert statement after adding necessary paramaeters
SqlCommand insertCommand =
new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue(
"@FisrtName", contact.SupConFirstName);
insertCommand.Parameters.AddWithValue(
"@LastName", contact.SupConLastName);
insertCommand.Parameters.AddWithValue(
"@Company", contact.SupConCompany);
insertCommand.Parameters.AddWithValue(
"@Address", contact.SupConAddress);
insertCommand.Parameters.AddWithValue(
"@City", contact.SupConCity);
insertCommand.Parameters.AddWithValue(
"@Prov", contact.SupConProv);
insertCommand.Parameters.AddWithValue(
"@Postal", contact.SupConPostal);
insertCommand.Parameters.AddWithValue(
"@Country", contact.SupConCountry);
insertCommand.Parameters.AddWithValue(
"@Phone", contact.SupConBusPhone);
insertCommand.Parameters.AddWithValue(
"@Fax", contact.SupConFax);
insertCommand.Parameters.AddWithValue(
"@Email", contact.SupConEmail);
insertCommand.Parameters.AddWithValue(
"@URL", contact.SupConURL);
insertCommand.Parameters.AddWithValue(
"@AffilationId", contact.AffiliationId);
insertCommand.Parameters.AddWithValue(
"@SupplierId", contact.SupplierId);
try
{
connection.Open();//open DB connection
insertCommand.ExecuteNonQuery();
string selectStatement =
"SELECT IDENT_CURRENT('SupplierContacts') FROM SupplierContacts";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
int supplierContactId = Convert.ToInt32(selectCommand.ExecuteScalar());
return supplierContactId;
}
catch (SqlException ex) //catch all sql exception
{
throw ex;
}
finally
{
connection.Close(); //close DB connection
}
}
//returns one supplier contacts details for selected SupplierContactId
public static SupplierContacts GetSupplierContacts(int supplierContactId)
{
//create connection object for connecting to the database
SqlConnection connection = TravelExpertsDB.GetConnection();
//building the select statement dynamically for retriving data
string selectStatement
= "SELECT SupplierContactId, SupConFirstName, SupConLastName, SupConCompany, SupConAddress, "
+ "SupConCity,SupConProv, SupConPostal, SupConCountry, SupConBusPhone, SupConFax, SupConEmail, "
+ "SupConURL, AffiliationId, SupplierId "
+ "FROM SupplierContacts "
+ "WHERE SupplierContactId = @SupplierContactId";
//executing the select statement after adding necessary paramaeters
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@SupplierContactId", supplierContactId);
try
{
connection.Open();//open DB connection
//reading the data belonging to a single row
SqlDataReader contactReader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (contactReader.Read())
{
SupplierContacts contact = new SupplierContacts();
//assigning values to different properties of the object for the data fetched
contact.SupplierContactId = (int)contactReader["SupplierContactId"];
contact.SupConFirstName = contactReader["SupConFirstName"].ToString();
contact.SupConLastName = contactReader["SupConLastName"].ToString();
contact.SupConCompany = contactReader["SupConCompany"].ToString();
contact.SupConAddress = contactReader["SupConAddress"].ToString();
contact.SupConCity = contactReader["SupConCity"].ToString();
contact.SupConProv = contactReader["SupConProv"].ToString();
contact.SupConPostal = contactReader["SupConPostal"].ToString();
contact.SupConCountry = contactReader["SupConCountry"].ToString();
contact.SupConBusPhone = contactReader["SupConBusPhone"].ToString();
contact.SupConFax = contactReader["SupConFax"].ToString();
contact.SupConEmail = contactReader["SupConEmail"].ToString();
contact.SupConURL = contactReader["SupConURL"].ToString();
contact.AffiliationId = contactReader["AffiliationId"].ToString();
contact.SupplierId = (int)contactReader["SupplierId"];
//returing the object
return contact;
}
else
{
return null;
}
}
catch (SqlException ex) //catch all sql exception
{
throw ex;
}
finally
{
connection.Close(); //close DB connection
}
}
private void PutSupplierContactData(SupplierContacts supplierContact)
{
if(!isAddSupplierContact)
supplierContact.SupplierContactId = Convert.ToInt32(this.txtSupplierContactId.Text); // for modify only
supplierContact.SupConFirstName = this.txtSupConFirstName.Text.Trim();
supplierContact.SupConLastName = this.txtSupConLastName.Text.Trim();
supplierContact.SupConCompany = this.txtSupConCompany.Text.Trim();
supplierContact.SupConAddress = this.txtSupConAddress.Text.Trim();
supplierContact.SupConCity = this.txtSupConCity.Text.Trim();
supplierContact.SupConProv = this.txtSupConProv.Text.Trim();
supplierContact.SupConPostal = this.txtSupConPostal.Text.Trim();
supplierContact.SupConCountry = this.txtSupConCountry.Text.Trim();
supplierContact.SupConBusPhone = this.txtSupConBusPhone.Text.Trim();
supplierContact.SupConFax = this.txtSupConFax.Text.Trim();
supplierContact.SupConEmail = this.txtSupConEmail.Text.Trim();
supplierContact.SupConURL = this.txtSupConURL.Text.Trim();
supplierContact.AffiliationId = this.cmbAffiliationId.SelectedValue.ToString();
supplierContact.SupplierId = Convert.ToInt32(this.txtSupplierId.Text);
}
