public static Vendor GetVendor(int vendorID)
{
SqlDataReader reader = null;
Vendor vendor = new Vendor();
SqlConnection connection = PayablesDB.GetConnection();
// ADD here your SELECT statement to retrieve the Vendor with
// the VendorID entered in the VendorIdTextBox
string selectStatement = "SELECT VendorId, Name, Address1, Address2, City, State, ZipCode, Phone, ContactFName, ContactLName, DefaultAccountNo, DefaultTermsID "
+ "FROM Vendors "
+ "WHERE VendorID= @VendorID";
// CREATE your SqlCommand object here
SqlCommand selectCommand = new SqlCommand(selectStatement,connection);
// ADD the parameter to the command object here
selectCommand.Parameters.AddWithValue("@VendorID", vendorID);
try
{
connection.Open();
reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
vendor.VendorID = (int)reader["VendorID"];
vendor.Name = reader["Name"].ToString();
vendor.Address1 = reader["Address1"].ToString();
vendor.Address2 = reader["Address2"].ToString();
vendor.City = reader["City"].ToString();
vendor.State = reader["State"].ToString();
vendor.ZipCode = reader["ZipCode"].ToString();
vendor.Phone = reader["Phone"].ToString();
vendor.ContactLName = reader["ContactLName"].ToString();
vendor.ContactFName = reader["ContactFName"].ToString();
vendor.DefaultAccountNo = (int)reader["DefaultAccountNo"];
vendor.DefaultTermsID = (int)reader["DefaultTermsID"];
}
else
{
vendor = null;
}
}
catch (SqlException ex)
{
throw;
}
finally
{
if ((reader != null)&& ! reader.IsClosed)
{
reader.Close();
}
if (connection != null)
{
connection.Close();
}
}
return vendor;
}
public static int AddVendor(Vendor vendor)
{
SqlConnection connection = PayablesDB.GetConnection();
string insertStatement =
"INSERT Vendors " +
"(Name, Address1, Address2, City, State, ZipCode, Phone, " +
"ContactFName, ContactLName, DefaultTermsID, DefaultAccountNo) " +
"VALUES (@Name, @Address1, @Address2, @City, @State, @ZipCode, " +
"@Phone, @ContactFName, @ContactLName, @DefaultTermsID, " +
"@DefaultAccountNo)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@Name", vendor.Name);
insertCommand.Parameters.AddWithValue("@Address1", vendor.Address1);
if (vendor.Address2 == "")
insertCommand.Parameters.AddWithValue("@Address2", DBNull.Value);
else
insertCommand.Parameters.AddWithValue("@Address2",
vendor.Address2);
insertCommand.Parameters.AddWithValue("@City", vendor.City);
insertCommand.Parameters.AddWithValue("@State", vendor.State);
insertCommand.Parameters.AddWithValue("@ZipCode", vendor.ZipCode);
if (vendor.Phone == "")
insertCommand.Parameters.AddWithValue("@Phone", DBNull.Value);
else
insertCommand.Parameters.AddWithValue("@Phone", vendor.Phone);
if (vendor.ContactFName == "")
insertCommand.Parameters.AddWithValue("@ContactFName",
DBNull.Value);
else
insertCommand.Parameters.AddWithValue("@ContactFName",
vendor.ContactFName);
if (vendor.ContactLName == "")
insertCommand.Parameters.AddWithValue("@ContactLName",
DBNull.Value);
else
insertCommand.Parameters.AddWithValue("@ContactLName",
vendor.ContactLName);
insertCommand.Parameters.AddWithValue("@DefaultTermsID",
vendor.DefaultTermsID);
insertCommand.Parameters.AddWithValue("@DefaultAccountNo",
vendor.DefaultAccountNo);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
string selectStatement =
"SELECT IDENT_CURRENT('Vendors') FROM Vendors";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
int vendorID = Convert.ToInt32(selectCommand.ExecuteScalar());
return vendorID;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static int AddVendor(Vendor vendor)
{
SqlConnection connection = PayablesDB.GetConnection();
// ADD here your INSERT statement to add a Vendor to the Vendors table
// (values entered in the textboxex)
string insertStatement = "INSERT Vendros "+
"(Name, Adress1, Adress2, City, State, ZipCode, Phone, "+
"ContactFName, ContactLName, DefaultTermsID, DefaultAccountNo) "+
"Values (@Name, @Adress1, @Adress2, @City, @State, @ZipCode, @Phone, "+
"@ContactFName, @ContactLName, @DefaultTermsID, @DefaultAccountNo)" ;
// CREATE here your insert Command
SqlCommand insertCommand = new SqlCommand(insertStatement,connection);
// Add here the parameters to your insertCommand
insertCommand.Parameters.AddWithValue("@Name", vendor.Name);
insertCommand.Parameters.AddWithValue("@Adress1", vendor.Address1);
if (vendor.Address2 == "")
{
insertCommand.Parameters.AddWithValue("@Adress2", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@adress2",vendor.Address2);
}
insertCommand.Parameters.AddWithValue("@City", vendor.City);
insertCommand.Parameters.AddWithValue("@State", vendor.State);
insertCommand.Parameters.AddWithValue("@ZipCode", vendor.ZipCode);
if (vendor.Phone == "") insertCommand.Parameters.AddWithValue("@Phone", DBNull.Value);
else insertCommand.Parameters.AddWithValue("@Phone", vendor.Phone);
if (vendor.ContactFName == "") insertCommand.Parameters.AddWithValue("@ContactFName", DBNull.Value);
else insertCommand.Parameters.AddWithValue("@ContactFName", vendor.ContactFName);
if (vendor.ContactLName == "") insertCommand.Parameters.AddWithValue("@ContactLName", DBNull.Value);
else insertCommand.Parameters.AddWithValue("@ContactLName", vendor.ContactLName);
insertCommand.Parameters.AddWithValue("@DefaultTermsID", vendor.DefaultTermsID);
insertCommand.Parameters.AddWithValue("@DefaultAccountNo", vendor.DefaultAccountNo);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
string selectStatement =
"SELECT IDENT_CURRENT('Vendors') FROM Vendors";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
int vendorID = Convert.ToInt32(selectCommand.ExecuteScalar());
return vendorID;
}
catch (SqlException ex)
{
throw;
}
finally
{
if(connection!=null)
connection.Close();
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
frmAddModifyVendor addModifyVendorForm = new frmAddModifyVendor();
addModifyVendorForm.addVendor = true;
DialogResult result = addModifyVendorForm.ShowDialog();
if (result == DialogResult.OK)
{
vendor = addModifyVendorForm.vendor;
txtVendorID.Text = vendor.VendorID.ToString();
this.DisplayVendor();
}
}
public static Vendor GetVendor(int vendorID)
{
Vendor vendor = new Vendor();
SqlConnection connection = PayablesDB.GetConnection();
string selectStatement =
"SELECT VendorID, Name, Address1, Address2, City, State, " +
"ZipCode, Phone, ContactFName, ContactLName, " +
"DefaultAccountNo, DefaultTermsID " +
"FROM Vendors " +
"WHERE VendorID = @VendorID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@VendorID", vendorID);
try
{
connection.Open();
SqlDataReader reader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
vendor.VendorID = (int)reader["VendorID"];
vendor.Name = reader["Name"].ToString();
vendor.Address1 = reader["Address1"].ToString();
vendor.Address2 = reader["Address2"].ToString();
vendor.City = reader["City"].ToString();
vendor.State = reader["State"].ToString();
vendor.ZipCode = reader["ZipCode"].ToString();
vendor.Phone = reader["Phone"].ToString();
vendor.ContactLName = reader["ContactLName"].ToString();
vendor.ContactFName = reader["ContactFName"].ToString();
vendor.DefaultAccountNo = (int)reader["DefaultAccountNo"];
vendor.DefaultTermsID = (int)reader["DefaultTermsID"];
}
else
{
vendor = null;
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return vendor;
}
private void GetVendor(int vendorID)
{
try
{
vendor = VendorDB.GetVendor(vendorID);
if (vendor == null)
MessageBox.Show("No vendor found with this ID. " +
"Please try again.", "Vendor Not Found");
else
this.DisplayVendor();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
public static Vendor GetVendorNameAndAddress(int vendorID)
{
SqlDataReader reader = null;
Vendor vendor = new Vendor();
SqlConnection connection = PayablesDB.GetConnection();
string selectStatement =
"SELECT VendorID, Name, Address1, Address2, " +
" City, State, ZipCode " +
"FROM Vendors " +
"WHERE VendorID = @VendorID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@VendorID", vendorID);
try
{
connection.Open();
reader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
vendor.VendorID = (int)reader["VendorID"];
vendor.Name = reader["Name"].ToString();
vendor.Address1 = reader["Address1"].ToString();
vendor.Address2 = reader["Address2"].ToString();
vendor.City = reader["City"].ToString();
vendor.State = reader["State"].ToString();
vendor.ZipCode = reader["ZipCode"].ToString();
}
else
{
vendor = null;
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if(reader!=null)
reader.Close();
if(connection!=null)
connection.Close();
}
return vendor;
}
private void GetVendorData()
{
int vendorID = (int)nameComboBox.SelectedValue;
try
{
vendor = VendorDB.GetVendorNameAndAddress(vendorID);
System.Windows.Data.CollectionViewSource vendorViewSource = ((System.Windows.Data.CollectionViewSource)(this.FindResource("vendorViewSource")));
List<Vendor> vendorList = new List<Vendor>();
vendorList.Add(vendor);
vendorViewSource.Source = vendorList;
invoiceList = InvoiceDB.GetUnpaidVendorInvoices(vendorID);
System.Windows.Data.CollectionViewSource invoiceViewSource = ((System.Windows.Data.CollectionViewSource)(this.FindResource("invoiceViewSource")));
invoiceViewSource.Source = invoiceList;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
private void btnAdd_Click(object sender, RoutedEventArgs e)
{
AddModifyVendorWindow wpfAddModify = new AddModifyVendorWindow();
wpfAddModify.addVendor = true;
vendor = new Vendor();
DataContext = vendor;
wpfAddModify.vendor = vendor;
wpfAddModify.ShowDialog();
if (wpfAddModify.DialogResult.HasValue && wpfAddModify.DialogResult.Value)
{
vendor = wpfAddModify.vendor;
this.DisplayVendor();
MessageBox.Show("Vendor succesfully inserted ...");
}
else
{
MessageBox.Show("User clicked Cancel");
}
}
private void GetVendor(int vendorID)
{
try
{
//Vendor ophalen via de business laag
vendor = VendorDB.GetVendor(vendorID);
if (vendor == null)
MessageBox.Show("No vendor found with this ID. " +
"Please try again.", "Vendor Not Found");
else
{
this.DisplayVendor();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
MessageBox.Show(ex.StackTrace.ToString());
}
}
public static List<Vendor> GetVendorsWithBalanceDue()
{
SqlDataReader reader = null;
List<Vendor> vendorList = new List<Vendor>();
SqlConnection connection = PayablesDB.GetConnection();
string selectStatement =
"SELECT VendorID, Name " +
"FROM Vendors " +
"WHERE (SELECT SUM (InvoiceTotal - PaymentTotal " +
" - CreditTotal) " +
" FROM Invoices " +
" WHERE Invoices.VendorID = Vendors.VendorID) " +
" > 0 " +
"ORDER BY Name";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Vendor vendor = new Vendor();
vendor.VendorID = (int)reader["VendorID"];
vendor.Name = reader["Name"].ToString();
vendorList.Add(vendor);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if(reader!=null)
reader.Close();
if(connection!=null)
connection.Close();
}
return vendorList;
}
private void btnAccept_Click(object sender, RoutedEventArgs e)
{
if (IsValidData())
{
if (addVendor)
{
vendor.Phone = VendorPhoneTextBox.Text.Replace(".", "");
try
{
vendor.VendorID = VendorDB.AddVendor(vendor);
this.DialogResult = true;
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
else
{
vendor.Phone = VendorPhoneTextBox.Text.Replace(".", "");
Vendor newVendor = new Vendor();
newVendor = vendor;
newVendor.VendorID = vendor.VendorID;
try
{
if (!VendorDB.UpdateVendor(vendor, newVendor))
{
MessageBox.Show("Another user has updated or deleted that vendor.", "DataBase Error");
this.DialogResult = false;
}
else
{
vendor = newVendor;
this.DialogResult = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
}
else
{
MessageBox.Show("You have entered invalid data ...");
}
}
private void btnAccept_Click(object sender, EventArgs e)
{
if (IsValidData())
{
if (addVendor)
{
vendor = new Vendor();
this.PutVendorData(vendor);
try
{
vendor.VendorID = VendorDB.AddVendor(vendor);
this.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
else
{
Vendor newVendor = new Vendor();
newVendor.VendorID = vendor.VendorID;
this.PutVendorData(newVendor);
try
{
if (!VendorDB.UpdateVendor(vendor, newVendor))
{
MessageBox.Show("Another user has updated or " +
"deleted that vendor.", "Database Error");
this.DialogResult = DialogResult.Retry;
}
else
{
vendor = newVendor;
this.DialogResult = DialogResult.OK;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
}
}
private void PutVendorData(Vendor vendor)
{
vendor.Name = txtName.Text;
vendor.Address1 = txtAddress1.Text;
vendor.Address2 = txtAddress2.Text;
vendor.City = txtCity.Text;
vendor.State = cboStates.SelectedValue.ToString();
vendor.ZipCode = txtZipCode.Text;
vendor.DefaultTermsID = (int)cboTerms.SelectedValue;
vendor.DefaultAccountNo = (int)cboAccounts.SelectedValue;
vendor.Phone = txtPhone.Text.Replace(".", "");
vendor.ContactFName = txtFirstName.Text;
vendor.ContactLName = txtLastName.Text;
}
public static bool UpdateVendor(Vendor oldVendor, Vendor newVendor)
{
SqlConnection connection = PayablesDB.GetConnection();
string updateStatement =
"UPDATE Vendors SET " +
"Name = @NewName, " +
"Address1 = @NewAddress1, " +
"Address2 = @NewAddress2, " +
"City = @NewCity, " +
"State = @NewState, " +
"ZipCode = @NewZipCode, " +
"Phone = @NewPhone, " +
"ContactFName = @NewContactFName, " +
"ContactLName = @NewContactLName, " +
"DefaultTermsID = @NewDefaultTermsID, " +
"DefaultAccountNo = @NewDefaultAccountNo " +
"WHERE VendorID = @OldVendorID " ;
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@NewName", newVendor.Name);
updateCommand.Parameters.AddWithValue("@NewAddress1", newVendor.Address1);
if (newVendor.Address2 == "")
updateCommand.Parameters.AddWithValue("@NewAddress2", DBNull.Value);
else
updateCommand.Parameters.AddWithValue("@NewAddress2",
newVendor.Address2);
updateCommand.Parameters.AddWithValue("@NewCity", newVendor.City);
updateCommand.Parameters.AddWithValue("@NewState", newVendor.State);
updateCommand.Parameters.AddWithValue("@NewZipCode", newVendor.ZipCode);
if (newVendor.Phone == "")
updateCommand.Parameters.AddWithValue("@NewPhone", DBNull.Value);
else
updateCommand.Parameters.AddWithValue("@NewPhone", newVendor.Phone);
if (newVendor.ContactFName == "")
updateCommand.Parameters.AddWithValue("@NewContactFName",
DBNull.Value);
else
updateCommand.Parameters.AddWithValue("@NewContactFName",
newVendor.ContactFName);
if (newVendor.ContactLName == "")
updateCommand.Parameters.AddWithValue("@NewContactLName",
DBNull.Value);
else
updateCommand.Parameters.AddWithValue("@NewContactLName",
newVendor.ContactLName);
updateCommand.Parameters.AddWithValue("@NewDefaultTermsID",
newVendor.DefaultTermsID);
updateCommand.Parameters.AddWithValue("@NewDefaultAccountNo",
newVendor.DefaultAccountNo);
updateCommand.Parameters.AddWithValue("@OldVendorID", oldVendor.VendorID);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
return true;
else
return false;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if(connection!=null)
{
connection.Close();
}
}
}
private void btnModify_Click(object sender, EventArgs e)
{
frmAddModifyVendor addModifyVendorForm = new frmAddModifyVendor();
addModifyVendorForm.addVendor = false;
addModifyVendorForm.vendor = vendor;
DialogResult result = addModifyVendorForm.ShowDialog();
if (result == DialogResult.OK)
{
vendor = addModifyVendorForm.vendor;
this.DisplayVendor();
}
else if (result == DialogResult.Retry)
{
this.ClearControls();
this.GetVendor(vendor.VendorID);
}
}
public static bool UpdateVendor(Vendor oldVendor, Vendor newVendor)
{
// The columns that can have null values are null in the
// oldVendor and newVendor objects if the vendor was just added
// but are an empty string for an existing vendor. Because
// of that, the properties are checked for both nulls and
// empty strings. This wasn't necessary in the VB application
// because these properties were always empty strings. Since a
// null value can't be displayed in a text box, they should be
// converted to strings but apparently aren't.
SqlConnection connection = PayablesDB.GetConnection();
string updateStatement =
"UPDATE Vendors SET " +
"Name = @NewName, " +
"Address1 = @NewAddress1, " +
"Address2 = @NewAddress2, " +
"City = @NewCity, " +
"State = @NewState, " +
"ZipCode = @NewZipCode, " +
"Phone = @NewPhone, " +
"ContactFName = @NewContactFName, " +
"ContactLName = @NewContactLName, " +
"DefaultTermsID = @NewDefaultTermsID, " +
"DefaultAccountNo = @NewDefaultAccountNo " +
"WHERE VendorID = @OldVendorID " +
"AND Name = @OldName " +
"AND Address1 = @OldAddress1 " +
"AND (Address2 = @OldAddress2 " +
"OR Address2 IS NULL AND @OldAddress2 IS NULL) " +
"AND City = @OldCity " +
"AND State = @OldState " +
"AND ZipCode = @OldZipCode " +
"AND (Phone = @OldPhone " +
"OR Phone IS NULL AND @OldPhone IS NULL) " +
"AND (ContactFName = @OldContactFName " +
"OR ContactFName IS NULL AND @OldContactFName IS NULL) " +
"AND (ContactLName = @OldContactLName " +
"OR ContactLName IS NULL AND @OldContactLName IS NULL) " +
"AND DefaultTermsID = @OldDefaultTermsID " +
"AND DefaultAccountNo = @OldDefaultAccountNo";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@NewName", newVendor.Name);
updateCommand.Parameters.AddWithValue("@NewAddress1", newVendor.Address1);
if (newVendor.Address2 == "" || newVendor.Address2 == null)
{
updateCommand.Parameters.AddWithValue("@NewAddress2", DBNull.Value);
}
else
{
updateCommand.Parameters.AddWithValue("@NewAddress2",
newVendor.Address2);
}
updateCommand.Parameters.AddWithValue("@NewCity", newVendor.City);
updateCommand.Parameters.AddWithValue("@NewState", newVendor.State);
updateCommand.Parameters.AddWithValue("@NewZipCode", newVendor.ZipCode);
if (newVendor.Phone == "" || newVendor.Phone == null)
{
updateCommand.Parameters.AddWithValue("@NewPhone", DBNull.Value);
}
else
{
updateCommand.Parameters.AddWithValue("@NewPhone", newVendor.Phone);
}
if (newVendor.ContactFName == "" || newVendor.ContactFName == null)
{
updateCommand.Parameters.AddWithValue("@NewContactFName",
DBNull.Value);
}
else
{
updateCommand.Parameters.AddWithValue("@NewContactFName",
newVendor.ContactFName);
}
if (newVendor.ContactLName == "" || newVendor.ContactLName == null)
{
updateCommand.Parameters.AddWithValue("@NewContactLName",
DBNull.Value);
}
else
{
updateCommand.Parameters.AddWithValue("@NewContactLName",
newVendor.ContactLName);
}
updateCommand.Parameters.AddWithValue("@NewDefaultTermsID",
newVendor.DefaultTermsID);
updateCommand.Parameters.AddWithValue("@NewDefaultAccountNo",
newVendor.DefaultAccountNo);
updateCommand.Parameters.AddWithValue("@OldVendorID", oldVendor.VendorID);
updateCommand.Parameters.AddWithValue("@OldName", oldVendor.Name);
updateCommand.Parameters.AddWithValue("@OldAddress1", oldVendor.Address1);
if (oldVendor.Address2 == "" || oldVendor.Address2 == null)
{
updateCommand.Parameters.AddWithValue("@OldAddress2", DBNull.Value);
}
else
{
updateCommand.Parameters.AddWithValue("@OldAddress2",
oldVendor.Address2);
}
updateCommand.Parameters.AddWithValue("@OldCity", oldVendor.City);
updateCommand.Parameters.AddWithValue("@OldState", oldVendor.State);
updateCommand.Parameters.AddWithValue("@OldZipCode", oldVendor.ZipCode);
if (oldVendor.Phone == "" || oldVendor.Phone == null)
{
updateCommand.Parameters.AddWithValue("@OldPhone", DBNull.Value);
}
else
{
updateCommand.Parameters.AddWithValue("@OldPhone", oldVendor.Phone);
}
if (oldVendor.ContactFName == "" || oldVendor.ContactFName == null)
{
updateCommand.Parameters.AddWithValue("@OldContactFName",
DBNull.Value);
}
else
{
updateCommand.Parameters.AddWithValue("@OldContactFName",
oldVendor.ContactFName);
}
if (oldVendor.ContactLName == "" || oldVendor.ContactLName == null)
{
updateCommand.Parameters.AddWithValue("@OldContactLName",
DBNull.Value);
}
else
{
updateCommand.Parameters.AddWithValue("@OldContactLName",
oldVendor.ContactLName);
}
updateCommand.Parameters.AddWithValue("@OldDefaultTermsID",
oldVendor.DefaultTermsID);
updateCommand.Parameters.AddWithValue("@OldDefaultAccountNo",
oldVendor.DefaultAccountNo);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool DeleteVendor(Vendor vendor)
{
if (vendor == null) { return false; }
SqlConnection connection = PayablesDB.GetConnection();
string deleteStatement =
"DELETE from Vendors " +
"WHERE VendorID = @VendorID";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection);
deleteCommand.Parameters.AddWithValue("@VendorID", vendor.VendorID);
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count > 0)
return true;
else
return false;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
private void btnModify_Click(object sender, RoutedEventArgs e)
{
AddModifyVendorWindow wpfAddModify;
wpfAddModify = new AddModifyVendorWindow();
wpfAddModify.addVendor = false;
wpfAddModify.vendor = vendor;
wpfAddModify.ShowDialog();
if (wpfAddModify.DialogResult.HasValue && wpfAddModify.DialogResult.Value == true)
{
vendor = wpfAddModify.vendor;
this.DisplayVendor();
MessageBox.Show("Vendor succesfully updated ...");
}
else
{
this.ClearControls();
this.GetVendor(vendor.VendorID);
}
}
public static int AddVendor(Vendor vendor)
{
SqlConnection connection = PayablesDB.GetConnection();
string insertStatement =
"INSERT Vendors " +
"(Name, Address1, Address2, City, State, ZipCode, Phone, " +
"ContactFName, ContactLName, DefaultTermsID, DefaultAccountNo) " +
"VALUES (@Name, @Address1, @Address2, @City, @State, @ZipCode, " +
"@Phone, @ContactFName, @ContactLName, @DefaultTermsID, " +
"@DefaultAccountNo)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@Name", vendor.Name);
insertCommand.Parameters.AddWithValue("@Address1", vendor.Address1);
if (vendor.Address2 == null)
{
insertCommand.Parameters.AddWithValue("@Address2", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@Address2",
vendor.Address2);
}
insertCommand.Parameters.AddWithValue("@City", vendor.City);
insertCommand.Parameters.AddWithValue("@State", vendor.State);
insertCommand.Parameters.AddWithValue("@ZipCode", vendor.ZipCode);
if (vendor.Phone == null)
{
insertCommand.Parameters.AddWithValue("@Phone", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@Phone", vendor.Phone);
}
if (vendor.ContactFName == null)
{
insertCommand.Parameters.AddWithValue("@ContactFName",
DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@ContactFName",
vendor.ContactFName);
}
if (vendor.ContactLName == null)
{
insertCommand.Parameters.AddWithValue("@ContactLName",
DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@ContactLName",
vendor.ContactLName);
}
insertCommand.Parameters.AddWithValue("@DefaultTermsID",
vendor.DefaultTermsID);
insertCommand.Parameters.AddWithValue("@DefaultAccountNo",
vendor.DefaultAccountNo);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
string selectStatement =
"SELECT IDENT_CURRENT('Vendors') FROM Vendors";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
int vendorID = Convert.ToInt32(selectCommand.ExecuteScalar());
return(vendorID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}