//GET PRODUCT FROM DB BY PRODUCT CODE
private void GetProduct(string productID)
{
try
{
product = ProductDB.GetProduct(productID);
if (product == null)
MessageBox.Show("No product found with this product code.", "Product Not Found");
else
this.ShowProduct();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
//RETURN ONE PRODUCT BASED ON PRODUCT CODE
public static Product GetProduct(string productCode)
{
Product p = new Product();
SqlConnection connection = AppRepairsDB.GetConnection();
string selectStatement =
"SELECT ProductCode, Name, YearsWarranty, ReleaseDate " +
"FROM Products " +
"WHERE ProductCode = @ProductCode " +
"ORDER BY ProductCode";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductCode", productCode); //assign value to @ProductCode
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read()) //if there was a matching product code
{
p.ProductCode = reader["ProductCode"].ToString();
p.ProductName = reader["Name"].ToString();
p.YearsWarranty = Convert.ToDecimal(reader["YearsWarranty"]);
p.ReleaseDate = (DateTime)reader["ReleaseDate"];
}
else
p = null; //product does not exist
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return p;
}
//SAVE CHANGES
private void btnAccept_Click(object sender, EventArgs e)
{
if (IsValidData())
{
if (addProduct)
{
product = new Product(); //product being added
this.SaveToProduct(product);
try
{
ProductDB.AddProduct(product); //save product to DB
this.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
else //edit product
{
Product newProduct = new Product(); //updated product
this.SaveToProduct(newProduct);
try
{
if (!ProductDB.UpdateProduct(product, newProduct)) //if bool is false (concurrency error)
{
MessageBox.Show("Another user has updated or deleted that product.", "Database Error");
this.DialogResult = DialogResult.Retry;
}
else //bool is true, no concurrency issue
{
product = newProduct;
this.DialogResult = DialogResult.OK;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
}
}
//UPDATE PRODUCT DATA
public static bool UpdateProduct(Product oldProduct, Product newProduct)
{
SqlConnection connection = AppRepairsDB.GetConnection();
string updateStatement =
"UPDATE Products SET " +
"ProductCode = @NewProductCode, " +
"Name = @NewName, " +
"YearsWarranty = @NewYearsWarranty, " +
"ReleaseDate = @NewReleaseDate " +
"WHERE ProductCode = @OldProductCode " +
"AND Name = @OldName " +
"AND YearsWarranty = @OldYearsWarranty " +
"AND ReleaseDate = @OldReleaseDate";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@NewProductCode", newProduct.ProductCode);
updateCommand.Parameters.AddWithValue("@NewName", newProduct.ProductName);
updateCommand.Parameters.AddWithValue("@NewYearsWarranty", newProduct.YearsWarranty);
updateCommand.Parameters.AddWithValue("@NewReleaseDate", newProduct.ReleaseDate);
updateCommand.Parameters.AddWithValue("@OldProductCode", oldProduct.ProductCode);
updateCommand.Parameters.AddWithValue("@OldName", oldProduct.ProductName);
updateCommand.Parameters.AddWithValue("@OldYearsWarranty", oldProduct.YearsWarranty);
updateCommand.Parameters.AddWithValue("@OldReleaseDate", oldProduct.ReleaseDate);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0) //a row was affected
return true;
else
return false;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
//DELETE PRODUCT
public static int DeleteProduct(Product product)
{
int deleteCount = 0;
SqlConnection connection = AppRepairsDB.GetConnection();
string deleteStatement =
"DELETE FROM Products " +
"WHERE ProductCode = @ProductCode " +
"AND Name = @Name " +
"AND YearsWarranty = @YearsWarranty " +
"AND ReleaseDate = @ReleaseDate";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection);
deleteCommand.Parameters.AddWithValue("@ProductCode", product.ProductCode);
deleteCommand.Parameters.AddWithValue("@Name", product.ProductName);
deleteCommand.Parameters.AddWithValue("@YearsWarranty", product.YearsWarranty);
deleteCommand.Parameters.AddWithValue("@ReleaseDate", product.ReleaseDate);
try
{
connection.Open();
deleteCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return deleteCount;
}
//ADD NEW PRODUCT
public static void AddProduct(Product product)
{
SqlConnection connection = AppRepairsDB.GetConnection();
string insertStatement =
"INSERT Products (ProductCode, Name, YearsWarranty, ReleaseDate) " +
"VALUES (@ProductCode, @Name, @YearsWarranty, @ReleaseDate)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@ProductCode", product.ProductCode);
insertCommand.Parameters.AddWithValue("@Name", product.ProductName);
insertCommand.Parameters.AddWithValue("@YearsWarranty", product.YearsWarranty);
insertCommand.Parameters.AddWithValue("@ReleaseDate", product.ReleaseDate);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
//SAVE TEXTBOX TEXT TO A PRODUCT
private void SaveToProduct(Product product)
{
product.ProductCode = txtProductCode.Text.ToUpper();
product.ProductName = txtName.Text;
product.YearsWarranty = Convert.ToDecimal(txtYearsWarranty.Text);
product.ReleaseDate = Convert.ToDateTime(txtReleaseDate.Text);
}
//ADD NEW PRODUCT
private void btnAdd_Click(object sender, EventArgs e)
{
frmAddEditProduct addForm = new frmAddEditProduct();
addForm.addProduct = true;
DialogResult result = addForm.ShowDialog();
if (result == DialogResult.OK)
{
product = addForm.product;
this.FillComboBox();
cboProductCodes.Text = product.ProductCode;
this.ShowProduct();
}
}
//EDIT CURRENT PRODUCT
private void btnEdit_Click(object sender, EventArgs e)
{
frmAddEditProduct editForm = new frmAddEditProduct();
editForm.addProduct = false; //editing
editForm.product = product; //use current product to pass data to product in editing form
DialogResult result = editForm.ShowDialog();
if (result == DialogResult.OK)
{
product = editForm.product; //new current product equal to edits made in editForm
this.FillComboBox();
cboProductCodes.Text = product.ProductCode;
this.ShowProduct();
}
else if (result == DialogResult.Retry) //retry due to concurrency error
{
this.ClearControls();
this.GetProduct(product.ProductCode); //re-get product based on product code
}
}
