/// <summary>
/// Retrieve List of products...
/// </summary>
/// <returns></returns>
public List <Product> Retrieve()
{
//code that retrieves selected customer
//create list to store results
List <Product> result = new List <Product>();
//Create SQL query to return all products
string sqlQuery = String.Format("select * from Product");
//Create and open connection to SQL server
SqlConnection connection = new SqlConnection(DatabaseHelper.ConnectionString);
connection.Open();
SqlCommand command = new SqlCommand(sqlQuery, connection);
//Create DataReader to store returned table in memory
SqlDataReader dataReader = command.ExecuteReader();
//clean environment
Product product = null;
//load retrieved rows into result object
if (dataReader.HasRows)
{
while (dataReader.Read())
{
//Create product
product = new Product();
//Populate product
product.ProductName = dataReader["ProductName"].ToString().TrimEnd().MakeAlphaNumericWithHyphenOnly();
product.ProductDescription = dataReader["ProductDescription"].ToString().TrimEnd().MakeAlphaNumericWithHyphenOnly();
//ReturnProductCode temporary placeholder
product.ReturnedProductCode = dataReader["ProductCode"] == DBNull.Value ? "IS NULL VALUE" : dataReader["ProductCode"].ToString().TrimEnd().MakeAlphaNumericWithHyphenOnly();
//Handle enumurated value and null for Entity State
//EntityState SQL value is assigned to string or set to "IS NULL VALUE" if empty.
string entityState = dataReader["EntityState"] == DBNull.Value ? "IS NULL VALUE" : (string)dataReader["EntityState"];
if (entityState != "IS NULL VALUE")
{
//Convert string value of entity state to the EntityState enum.
EntityStateOption productForEntityState = (EntityStateOption)Enum.Parse(typeof(EntityStateOption), entityState);
product.EntityState = productForEntityState;
Console.WriteLine(product.EntityState);
// product.EntityState = dataReader["EntityState"].ToString();
// product.ProductCode = dataReader["ProductCode"].ToString().MakeAlphaNumericWithHyphenOnly();
}
//Add object to result list
result.Add(product);
}
//Tidy up connection TEST
command.Dispose();
connection.Close();
connection.Dispose();
}
return(result);
}
/// <summary>
/// Retrieve product by ID...
/// </summary>
/// <param name="productId"></param>
/// <returns></returns>
public Product Retrieve(int productId)
{
//Test code to create and retrieve product
Product product = new Product(productId);
//Hard code values for test product object
if (productId == 2)
{
product.ProductName = "ultegra di-2 groupset";
product.ProductDescription = "groupset";
product.CurrentPrice = 800.00M;
}
else
{
//SQL Connection code
//Create SQL Query to return an product based on it's primary key
string sqlQuery = String.Format("select * from Product where ProductId={0}", productId);
//Create and open connection to SQL server
SqlConnection connection = new SqlConnection(DatabaseHelper.ConnectionString);
connection.Open();
SqlCommand command = new SqlCommand(sqlQuery, connection);
SqlDataReader dataReader = command.ExecuteReader();
//Load into product object the returned row from database
if (dataReader.HasRows)
{
Console.WriteLine("Retrieving Item");
while (dataReader.Read())
{
// DATAREADER RETURNS ODD FORMATTING IN DEBUG??
product.ProductName = dataReader["ProductName"].ToString().TrimEnd().MakeAlphaNumericWithHyphenOnly();
product.ProductDescription = dataReader["ProductDescription"].ToString().TrimEnd().MakeAlphaNumericWithHyphenOnly();
//ReturnProductCode temporary placeholder
product.ReturnedProductCode = dataReader["ProductCode"] == DBNull.Value ? "IS NULL VALUE" : dataReader["ProductCode"].ToString().TrimEnd().MakeAlphaNumericWithHyphenOnly();
//Entitystate SQL value is assigned to string
string entityState = (string)dataReader["EntityState"];
//Convert string value of entity state to the EntityState enum.
EntityStateOption productForEntityState = (EntityStateOption)Enum.Parse(typeof(EntityStateOption), entityState);
product.EntityState = productForEntityState;
Console.WriteLine(product.EntityState);
}
//Tidy up connection TEST
command.Dispose();
connection.Close();
connection.Dispose();
}
}
//code that retrieves selected product
return(product);
}
