public static bool UpdateProduct(Product oldProduct,
Product newProduct)
{
SqlConnection connection = MMABooksDB.GetConnection();
string updateStatement =
"UPDATE Products SET " +
"Description = @NewDescription, " +
"UnitPrice = @NewPrice " +
"WHERE ProductCode = @OldCode ";
SqlCommand updateCommand =
new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue(
"@NewDescription", newProduct.Description);
updateCommand.Parameters.AddWithValue(
"@NewPrice", newProduct.Price);
updateCommand.Parameters.AddWithValue(
"@OldCode", oldProduct.Code);
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 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();
}
}
/// <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 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();
}
}
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, UnitPrice, OnHandQuantity " +
"FROM Products " +
"WHERE ProductCode = @ProductCode";
SqlCommand selectCommand = new SqlCommand(selectStatement, 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 ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
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();
}
}
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();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
} catch (SqlException ex)
{
throw ex;
} finally
{
connection.Close();
}
}
