public ProductEntity GetProduct(int id)
{
ProductEntity p = null;
using (var conn = new SqlConnection(_connectionString))
{
var comm = new SqlCommand
{CommandText = "select * from Products where ProductID=" + id, Connection = conn};
conn.Open();
SqlDataReader reader = comm.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
p = new ProductEntity
{
ProductID = id,
ProductName = (string) reader["ProductName"],
QuantityPerUnit = (string) reader["QuantityPerUnit"],
UnitPrice = (decimal) reader["UnitPrice"],
UnitsInStock = (short) reader["UnitsInStock"],
UnitsOnOrder = (short) reader["UnitsOnOrder"],
ReorderLevel = (short) reader["ReorderLevel"],
Discontinued = (bool) reader["Discontinued"]
};
}
}
return p;
}
private Product TranslateProductEntityToProductContractData(
ProductEntity productEntity)
{
return new Product
{
ProductID = productEntity.ProductID,
ProductName = productEntity.ProductName,
QuantityPerUnit = productEntity.QuantityPerUnit,
UnitPrice = productEntity.UnitPrice,
Discontinued = productEntity.Discontinued
};
}
public bool UpdateProduct(ProductEntity product)
{
using (var conn = new SqlConnection(_connectionString))
{
var cmd =
new SqlCommand(
"UPDATE products SET ProductName=@name,QuantityPerUnit=@unit,UnitPrice=@price,Discontinued=@discontinued WHERE ProductID=@id",
conn);
cmd.Parameters.AddWithValue("@name", product.ProductName);
cmd.Parameters.AddWithValue("@unit", product.QuantityPerUnit);
cmd.Parameters.AddWithValue("@price", product.UnitPrice);
cmd.Parameters.AddWithValue("@discontinued", product.Discontinued);
cmd.Parameters.AddWithValue("@id", product.ProductID);
conn.Open();
int numRows = cmd.ExecuteNonQuery();
if (numRows != 1)
{
return false;
}
}
return true;
}
public bool UpdateProduct(ProductEntity product)
{
// TODO: call data access layer to update product
// first check to see if it is a valid price
if (product.UnitPrice <= 0)
{
return false;
}
// ProductName can't be empty
if (string.IsNullOrEmpty(product.ProductName))
{
return false;
}
// QuantityPerUnit can't be empty
if (string.IsNullOrEmpty(product.QuantityPerUnit))
{
return false;
}
// then validate other properties
var productInDB = GetProduct(product.ProductID);
// invalid product to update
if (productInDB == null)
{
return false;
}
// a product can't be discontinued if there are non-fulfilled orders
if (product.Discontinued == true && productInDB.UnitsOnOrder > 0)
{
return false;
}
return _productDAO.UpdateProduct(product);
}
