public static Product GetProduct(string productCode)
{
SqlConnection connection = MMABooksDB.GetConnection();
SqlCommand selectCommand = new SqlCommand(
"SELECT * FROM Products WHERE ProductCode = @ProductCode", connection);
selectCommand.Parameters.AddWithValue("@ProductCode", productCode);
try
{
connection.Open();
SqlDataReader productReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (productReader.Read())
{
Product product = new Product();
product.Code = productReader["ProductCode"].ToString();
product.Description = productReader["Description"].ToString();
product.Price = (decimal)productReader["UnitPrice"];
return(product);
}
else
{
return(null);
}
}
catch (SqlException exception)
{
throw exception;
}
finally
{
connection.Close();
}
}
public static bool AddProduct(Product product)
{
SqlConnection connection = MMABooksDB.GetConnection();
string insertStatement = "INSERT Products" +
"(ProductCode, Description, UnitPrice)" +
"VALUES (@ProductCode, @Description, @UnitPrice)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@ProductCode", product.Code);
insertCommand.Parameters.AddWithValue("@Description", product.Description);
insertCommand.Parameters.AddWithValue("@UnitPrice", product.Price);
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery();
return(count > 0);
}
catch (SqlException s)
{
throw s;
}
finally
{
connection.Close();
}
}
public static bool DeleteProduct(Product product)
{
SqlConnection connection = MMABooksDB.GetConnection();
string deleteStatement = "DELETE FROM Products" +
"WHERE ProductCode = @ProductCode" +
"AND Description = @Description" +
"AND UnitPrice = @UnitPrice";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection);
deleteCommand.Parameters.AddWithValue("@ProductCode", product.Code);
deleteCommand.Parameters.AddWithValue("@Description", product.Description);
deleteCommand.Parameters.AddWithValue("@UnitPrice", product.Price);
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
return(count > 0);
}
catch (SqlException s)
{
throw s;
}
finally
{
connection.Close();
}
}
public static bool DeleteProduct(Product product)
{
SqlConnection connection = MMABooksDB.GetConnection();
SqlCommand deleteCommand = new SqlCommand(
"DELETE FROM Products " +
"WHERE ProductCode = @ProductCode ", connection);
deleteCommand.Parameters.AddWithValue(
"@ProductCode", product.Code);
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException exception)
{
throw exception;
}
finally
{
connection.Close();
}
}
public static bool UpdateProduct(Product oldProduct, Product newProduct)
{
SqlConnection connection = MMABooksDB.GetConnection();
string query = @"
UPDATE Products SET
Description = @newDescription,
UnitPrice = @newUnitPrice,
OnHandQuantity = @newQuantity
WHERE ProductCode = @productCode
AND Description = @oldDescription
AND UnitPrice = @oldUnitPrice
AND OnHandQuantity = @oldQuantity;
";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@newDescription", newProduct.Description);
command.Parameters.AddWithValue("@newUnitPrice", newProduct.UnitPrice);
command.Parameters.AddWithValue("@newQuantity", newProduct.OnHandQuantity);
command.Parameters.AddWithValue("@productCode", oldProduct.ProductCode);
command.Parameters.AddWithValue("@oldDescription", oldProduct.Description);
command.Parameters.AddWithValue("@oldUnitPrice", oldProduct.UnitPrice);
command.Parameters.AddWithValue("@oldQuantity", oldProduct.OnHandQuantity);
try
{
connection.Open();
int rowCount = command.ExecuteNonQuery();
if (rowCount > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool AddProduct(Product newProduct)
{
SqlConnection connection = MMABooksDB.GetConnection();
string actionQuery = @"
INSERT INTO Products (ProductCode, Description, UnitPrice, OnHandQuantity)
VALUES (@newProductCode, @newDescription, @newUnitPrice, @newQuantity);
";
SqlCommand command = new SqlCommand(actionQuery, connection);
command.Parameters.AddWithValue("@newProductCode", newProduct.ProductCode);
command.Parameters.AddWithValue("@newDescription", newProduct.Description);
command.Parameters.AddWithValue("@newUnitPrice", newProduct.UnitPrice);
command.Parameters.AddWithValue("@newQuantity", newProduct.OnHandQuantity);
try
{
connection.Open();
command.ExecuteNonQuery();
// Returns whether or not the product was actually added.
string getProduct = "SELECT * FROM Products WHERE ProductCode = @newProductCode";
SqlCommand getProductRows = new SqlCommand(getProduct, connection);
getProductRows.Parameters.AddWithValue("@newProductCode", newProduct.ProductCode);
int rows = getProductRows.ExecuteNonQuery();
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static Product GetProduct(string productCode)
{
// I made the MMABooksDB class public for this. I'm
// not sure if that was a good idea or not.
SqlConnection connection = MMABooksDB.GetConnection();
string query = @"
SELECT *
FROM Products
WHERE ProductCode = @productCode;
";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@productCode", productCode);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
Product product = new Product();
product.ProductCode = (string)reader["ProductCode"];
product.Description = (string)reader["Description"];
product.UnitPrice = (decimal)reader["UnitPrice"];
product.OnHandQuantity = (int)reader["OnHandQuantity"];
return(product);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// This method takes in a code, description and price from the user,
/// checks if there is a duplicate code, and if there isnt, adds
/// a new code with its description and price to the database.
/// </summary>
/// <param name="product"> the object of Product </param>
/// <returns> true if it adds, false if it doesnt </returns>
public static bool AddProduct(Product product)
{
SqlConnection connection = MMABooksDB.GetConnection();
string insertStatement =
"INSERT Products " +
"(ProductCode, Description, UnitPrice) " +
"VALUES (@Code, @Description, @Price)";
SqlCommand check = new SqlCommand("SELECT ProductCode FROM Products WHERE ProductCode = @ProductCode", connection);
check.Parameters.AddWithValue("@ProductCode", product.Code);
SqlCommand insertCommand =
new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@Code", product.Code);
insertCommand.Parameters.AddWithValue("@Description", product.Description);
insertCommand.Parameters.AddWithValue("@Price", product.Price);
// Opens a connection for check.executeScalar(this will return a
// ProductCode from the database if it exist, null if it doesnt).
connection.Open();
object temp = check.ExecuteScalar();
if (temp == null)
{
// close out the connection used only for check.executeScalar
// because it was only used to check for a duplicate, not for
// any changes.
connection.Close();
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
return(true);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
else
{
return(false);
}
}
public static bool UpdateProduct(Product oldProduct,
Product newProduct)
{
SqlConnection connection = MMABooksDB.GetConnection();
using (connection)
{
var updateString = "UPDATE Products" +
" SET ProductCode = '@newCode'" +
" SET Description = '@description'" +
" SET UnitPrice = @unitPrice" +
" WHERE ProductCode = '@oldCode'";
var command = new SqlCommand(updateString, connection);
command.Parameters.AddWithValue("@oldCode", oldProduct.Code);
command.Parameters.AddWithValue("@newCode", newProduct.Code);
command.Parameters.AddWithValue("@description", newProduct.Description);
command.Parameters.AddWithValue("@unitPrice", newProduct.Price);
// execute query
try
{
connection.Open();
var rows = command.ExecuteNonQuery();
if (rows < 1)
{
return(false);
}
else
{
return(true);
}
}
catch (SqlException e)
{
Console.WriteLine("SQL Error" + e.ToString());
Console.ReadKey();
}
return(false);
}
}
public static bool DeleteProduct(Product toDelete)
{
SqlConnection connection = MMABooksDB.GetConnection();
string query = @"
DELETE FROM Products
WHERE ProductCode = @productCode
AND Description = @description
AND UnitPrice = @unitPrice
AND OnHandQuantity = @quantity;
";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@productCode", toDelete.ProductCode);
command.Parameters.AddWithValue("@description", toDelete.Description);
command.Parameters.AddWithValue("@unitPrice", toDelete.UnitPrice);
command.Parameters.AddWithValue("@quantity", toDelete.OnHandQuantity);
try
{
connection.Open();
int rows = command.ExecuteNonQuery();
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// This class takes the old product info from the columns and replaces
/// it with new product info, except for the actual product code.
/// </summary>
/// <param name="oldProduct"> Object of product </param>
/// <param name="newProduct"> Another object for product </param>
/// <returns> true if it worked, false if it didnt </returns>
public static bool UpdateProduct(Product oldProduct, Product newProduct)
{
SqlConnection connection = MMABooksDB.GetConnection();
string updateStatement =
"UPDATE Products SET " +
"Description = @NewDescription, " +
"UnitPrice = @NewPrice " +
"WHERE ProductCode = @OldCode " +
"AND Description = @OldDescription " +
"AND UnitPrice = @OldPrice";
SqlCommand updateCommand =
new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@NewDescription", newProduct.Description);
updateCommand.Parameters.AddWithValue("@NewPrice", newProduct.Price);
updateCommand.Parameters.AddWithValue("@OldCode", oldProduct.Code);
updateCommand.Parameters.AddWithValue("@OldDescription", oldProduct.Description);
updateCommand.Parameters.AddWithValue("@OldPrice", oldProduct.Price);
// try to open a connection and sets count equal to
// updatecommand.executenonquery. If executenonquery returns 1 or
// more (number represents rows affect by a change) return true
// else return false(no rows were changed).
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 UpdateProduct(Product oldProduct, Product newProduct)
{
SqlConnection connection = MMABooksDB.GetConnection();
string updateStatement =
"UPDATE Products SET " +
"ProductCode = @NewProductCode, " +
"Description = @NewDescription, " +
"UnitPrice = @NewPrice " +
"WHERE ProductCode = @oldProductCode " +
"AND Description = @oldDescription " +
"AND UnitPrice = @oldUnitPrice";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@NewProductCode", newProduct.Code);
updateCommand.Parameters.AddWithValue("@NewDescription", newProduct.Description);
updateCommand.Parameters.AddWithValue("@NewPrice", newProduct.Price);
updateCommand.Parameters.AddWithValue("@OldProductCode", oldProduct.Code);
updateCommand.Parameters.AddWithValue("@oldDescription", oldProduct.Description);
updateCommand.Parameters.AddWithValue("@oldUnitPrice", oldProduct.Price);
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 Product GetProduct(string code)
{
SqlConnection connection = MMABooksDB.GetConnection();
using (connection)
{
var queryString = "SELECT * FROM Products WHERE ProductCode = '@code'";
var command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@code", code);
// execute query
try
{
connection.Open();
var reader = command.ExecuteReader();
if (!reader.HasRows)
{
throw new InvalidOperationException();
}
var returnProduct = new Product()
{
Code = $"{ reader["ProductCode"] }",
Description = $"{ reader["Description"]}",
Price = decimal.Parse($"{reader["Price"]}")
};
return(returnProduct);
}
catch (SqlException e)
{
Console.WriteLine("SQL Error" + e.ToString());
Console.ReadKey();
}
return(null);
}
}
public static bool AddProduct(Product product)
{
SqlConnection connection = MMABooksDB.GetConnection();
using (connection)
{
var insertString = "INSERT INTO Products (ProductCode, Description, UnitPrice) " +
"VALUES ('@code', '@description', @unitPrice)";
var command = new SqlCommand(insertString, connection);
command.Parameters.AddWithValue("@code", product.Code);
command.Parameters.AddWithValue("@description", product.Description);
command.Parameters.AddWithValue("@unitPrice", product.Price);
// execute query
try
{
connection.Open();
var rows = command.ExecuteNonQuery();
if (rows < 1)
{
return(false);
}
else
{
return(true);
}
}
catch (SqlException e)
{
Console.WriteLine("SQL Error" + e.ToString());
Console.ReadKey();
}
return(false);
}
}
public static bool AddProduct(Product product)
{
SqlConnection connection = MMABooksDB.GetConnection();
SqlCommand insertCommand =
new SqlCommand(
"INSERT Products " +
"(ProductCode, Description, UnitPrice) " +
"VALUES (@ProductCode, @Description, @UnitPrice)", connection);
insertCommand.Parameters.AddWithValue(
"@ProductCode", product.Code);
insertCommand.Parameters.AddWithValue(
"@Description", product.Description);
insertCommand.Parameters.AddWithValue(
"@UnitPrice", product.Price);
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException exception)
{
throw exception;
}
finally
{
connection.Close();
}
}
public static Product GetProduct(string code) // code here references PRODUCT CODE
{
SqlConnection connection = MMABooksDB.GetConnection(); // call without instance because static
string selectStatement
= "SELECT ProductCode, Description, UnitPrice "
+ "FROM Products "
+ "WHERE ProductCode = @Code";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@Code", code);
try
{
connection.Open();
SqlDataReader productReader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow); // only produce a single row of information
if (productReader.Read())
{
Product product = new Product();
product.Code = productReader["ProductCode"].ToString();
product.Description = productReader["Description"].ToString();
product.Price = (decimal)productReader["UnitPrice"];
return(product);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Connects to the database and gets the the information from each
/// column for a single row of the products table.
/// </summary>
/// <param name="code"> the ProductCode</param>
/// <returns> returns the product or null </returns>
public static Product GetProduct(string code)
{
SqlConnection connection = MMABooksDB.GetConnection();
string selectStatement = "SELECT ProductCode, Description, UnitPrice "
+ "FROM Products "
+ "WHERE ProductCode = @ProductCode";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductCode", code);
// Trys to open a connection to the database and reads the data.
// if its able to read set code, description, and price equal to
// what is read in, else return null.
try
{
connection.Open();
SqlDataReader productReader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (productReader.Read())
{
Product product = new Product();
product.Code = productReader["ProductCode"].ToString();
product.Description = productReader["Description"].ToString();
product.Price = (decimal)productReader["UnitPrice"];
return(product);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// This method deletes a product code and its description and price.
/// </summary>
/// <param name="product"> and object of Product </param>
/// <returns> true if was successful, false if not </returns>
public static bool DeleteProduct(Product product)
{
SqlConnection connection = MMABooksDB.GetConnection();
string deleteStatement =
"DELETE FROM Products " +
"WHERE ProductCode = @Code " +
"AND Description = @Description " +
"AND UnitPrice = @UnitPrice";
SqlCommand deleteCommand =
new SqlCommand(deleteStatement, connection);
deleteCommand.Parameters.AddWithValue("@Code", product.Code);
deleteCommand.Parameters.AddWithValue("@Description",
product.Description);
deleteCommand.Parameters.AddWithValue("@UnitPrice", product.Price);
// executenonquery returns number of rows affected. If more than 0,
// this means rows were successfully deleted, 0 mean nothing was.
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool DeleteProduct(Product product)
{
SqlConnection connection = MMABooksDB.GetConnection();
using (connection)
{
var deleteString = "DELETE FROM Products WHERE ProductCode = '@productCode'";
var command = new SqlCommand(deleteString, connection);
command.Parameters.AddWithValue("@code", product.Code);
// execute query
try
{
connection.Open();
var rows = command.ExecuteNonQuery();
if (rows < 1)
{
return(false);
}
else
{
return(true);
}
}
catch (SqlException e)
{
Console.WriteLine("SQL Error" + e.ToString());
Console.ReadKey();
}
return(false);
}
}
public static Product GetProduct(string productCode)
{
SqlConnection connection = MMABooksDB.GetConnection();
string selectStatement = "SELECT ProductCode, Description, Price " +
"FROM Products " +
"WHERE ProductCode = @ProductCode";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductCode", productCode);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
if (reader.Read())
{
Product p = new Product();
p.Code = reader["ProductCode"].ToString();
p.Description = reader["Description"].ToString();
p.Price = Convert.ToDecimal(reader["UnitPrice"]);
return(p);
}
else
{
return(null);
}
}
catch (SqlException s)
{
throw s;
}
finally
{
connection.Close();
}
}
