public static bool DeleteProduct(Product product)
{
SqlConnection connection = MMABooksDB.GetConnection();
string deleteStatement =
"DELETE FROM Products " +
"WHERE ProductCode = @ProductCode " +
"AND Description = @Description " +
"AND UnitPrice = @UnitPrice " +
"AND OnHandQuantity = @OnHandQuantity ";
SqlCommand deleteCommand =
new SqlCommand(deleteStatement, connection);
deleteCommand.Parameters.AddWithValue("@ProductCode", product.ProductCode);
deleteCommand.Parameters.AddWithValue("@Description", product.Description);
deleteCommand.Parameters.AddWithValue("@UnitPrice", product.UnitPrice);
deleteCommand.Parameters.AddWithValue("@OnHandQuantity", product.OnHandQuantity);
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery(); //works the same way as modifying
//gives error when there is a concurrency issue
if (count > 0)
return true;
else
return false;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
//method to add a new product to the database
public static void AddProduct(Product product)
{
SqlConnection connection = MMABooksDB.GetConnection();
string insertStatement =
"INSERT INTO Products " +
"(ProductCode, Description, UnitPrice, OnHandQuantity) " +
"VALUES(@ProductCode, @Description, @UnitPrice, @OnHandQuantity)"; // SqL INSERT query saved in a string
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
//create and add values to parameters of the SqlCommand object (insertCommand)
insertCommand.Parameters.AddWithValue("@ProductCode", product.ProductCode);
insertCommand.Parameters.AddWithValue("@Description", product.Description);
insertCommand.Parameters.AddWithValue("@UnitPrice", product.UnitPrice);
insertCommand.Parameters.AddWithValue("@OnHandQuantity", product.OnHandQuantity);
try
{
connection.Open();
insertCommand.ExecuteNonQuery(); //Executes a Transact-SQL statement
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
frmAddModifyProduct addProductForm = new frmAddModifyProduct(); //instantiate new Add form
addProductForm.addProduct = true; //set bool to true that we are adding a product
//this is used to differentiate adding and modifying (form heading)
DialogResult result = addProductForm.ShowDialog();
if (result == DialogResult.OK)
{
product = addProductForm.product; //grab the object from the Add/Modify Product form
// txtProductCode.Text = product.ProductCode.ToString();
this.DisplayProduct(); // populate textboxes with data from the newly created product
}
}
private void btnAccept_Click(object sender, EventArgs e)
{
if (IsDataPresent())
{
if (addProduct) // check Boolean flag for adding a product
{
product = new Product(); //new instance of class Product
this.PutProductData(product); //call PutProductData method to populate product with user's inputs
try
{
ProductDB.AddProduct(product); //returns a product object populated by data from the database
MessageBox.Show("Product '" + product.Description + "' added!");//send user a message that the product has been added
//for peace of mind....
this.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
else
{
Product newProduct = new Product(); //if we are modifying
//create new instance of Product
newProduct.ProductCode = product.ProductCode; //let newProduct get the properties of the product we are modiyfing
this.PutProductData(newProduct); //display properties of the product user is modifying
try
{
if (!ProductDB.ModProduct(product, newProduct))
{
MessageBox.Show("Another user has updated or " +
"deleted that product.", "Database Error");
this.DialogResult = DialogResult.Retry;
}
else
{
product = newProduct;
this.DialogResult = DialogResult.OK;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
}
}
//GetProduct method - accepts product code
private void GetProduct(string productCode)
{
try
{
product = ProductDB.GetProduct(productCode); //returns a product object populated by data from the database
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
private void btnModify_Click(object sender, EventArgs e)
{
frmAddModifyProduct modProductForm = new frmAddModifyProduct(); //instantiate new Add form
modProductForm.addProduct = false; //set bool to false that we are NOT adding a product, but modifying
//this is used to differentiate adding and modifying (form heading)
modProductForm.product = product; // populates modify form with the product info
DialogResult result = modProductForm.ShowDialog();
if (result == DialogResult.OK)
{
product = modProductForm.product;
this.DisplayProduct();
}
else if (result == DialogResult.Retry)
{
this.GetProduct(product.ProductCode);
if (product != null)
this.DisplayProduct();
else
this.ClearControls();
}
}
//basically the opposite of DiplayProduct
//this grabs data from the textboxes and assigns it to variables
private void PutProductData(Product product)
{
product.ProductCode = txtProductCode.Text;
product.Description = txtDesc.Text;
product.UnitPrice = Convert.ToDecimal(txtPrice.Text);
product.OnHandQuantity = Convert.ToInt32(txtQuantity.Text);
}
//get product by product code
public static Product GetProduct(string productCode)
{
SqlConnection connection = MMABooksDB.GetConnection(); //connect to the database
string selectStatement =
"SELECT ProductCode, Description, UnitPrice, OnHandQuantity " +
"FROM Products " +
"WHERE ProductCode = @ProductCode"; // declare string carrying a query
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
//created new SqlCommand object that will execute the select statement we saved in the string
selectCommand.Parameters.AddWithValue("@ProductCode", productCode);
//add value to the parameters
try
{
connection.Open();
SqlDataReader reader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
//CommandBeahviour - Provides a description of the results of the query and its effect on the database
//SingleRow means the query expects just one row
//While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader,
//and no other operations can be performed on the SqlConnection other than closing it.
//"A data-reader is the hose: it provides one-way/once-only access to data as it flies past you"
//good for reading data in the most efficient manner possible
if (reader.Read())
//The default position of the SqlDataReader is before the first record
//Therefore, you must call Read to begin accessing any data
//Read() method ADVANCES the SqlDataReader to the next record
//method returns true as long as there is a row to read
{
//now we process the row
Product product = new Product(); // create a new instance of Product class
product.ProductCode = reader["ProductCode"].ToString();
//read column name "ProductCode" and convert it to string, assign it to ProductCode
product.Description = reader["Description"].ToString();
product.UnitPrice = (decimal)reader["UnitPrice"]; //cast to decimal
product.OnHandQuantity = (int)reader["OnHandQuantity"]; //cast to integer
return product; //return populated object to the calling emthod
}
else // now row = no product
{
return null;
}
}
catch (SqlException ex)//throws exception to the calling method if SqlException occurs
{
throw ex;
}
finally
{
connection.Close();
}
}
//method to modify an existing product in the database
public static bool ModProduct(Product oldProduct,
Product newProduct)
{
SqlConnection connection = MMABooksDB.GetConnection();
string updateStatement =
"UPDATE Products SET " +
"ProductCode = @NewProductCode, " +
"Description = @NewDescription, " +
"UnitPrice = @NewUnitPrice, " +
"OnHandQuantity = @NewOnHandQuantity " +
"WHERE ProductCode = @OldProductCode " +
"AND Description = @OldDescription " +
"AND UnitPrice = @OldUnitPrice " +
"AND OnHandQuantity = @OldOnHandQuantity";
//new update query
//where statemenet will ensure that product will only be modified
//if it has not been changed by another user in the meantine
SqlCommand updateCommand =
new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue(
"@NewProductCode", newProduct.ProductCode);
updateCommand.Parameters.AddWithValue(
"@NewDescription", newProduct.Description);
updateCommand.Parameters.AddWithValue(
"@NewUnitPrice", newProduct.UnitPrice.ToString());
updateCommand.Parameters.AddWithValue(
"@NewOnHandQuantity", newProduct.OnHandQuantity);
updateCommand.Parameters.AddWithValue(
"@OldProductCode", oldProduct.ProductCode);
updateCommand.Parameters.AddWithValue(
"@OldDescription", oldProduct.Description);
updateCommand.Parameters.AddWithValue(
"@OldUnitPrice", oldProduct.UnitPrice.ToString());
updateCommand.Parameters.AddWithValue(
"@OldOnHandQuantity", oldProduct.OnHandQuantity);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();//returns number of rows affected to count
if (count > 0) //if count is more than zero, means query was successful, return true to calling emthod
return true;
else // if now rows were affected, return false to calling method
//this will go back to frmAddModifyProduct and display an error message that
//another user has modified row in the meantime
return false;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}