/// <summary>
/// get all product
/// </summary>
/// <returns></returns>
public List <ProductDE> selectAll()
{
List <ProductDE> productList = new List <ProductDE>();
ProductDE productResult = new ProductDE();
SqlConnection connection = new SqlConnection(connectstring);
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = "select * from Product";
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable table = new DataTable();
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
ProductDE product = new ProductDE()
{
ProductId = Convert.ToInt32(row["ProductId"]),
ProductName = Convert.ToString(row["ProductName"]),
ProductPrice = Convert.ToDecimal(row["ProductPrice"]),
CategoryId = Convert.ToInt32(row["CategoryId"])
};
CategoryDA categoryDA = new CategoryDA();
product.Category = categoryDA.GetById(Convert.ToInt32(row["CategoryId"]));
productList.Add(product);
}
connection.Close();
return(productList);
}
/// <summary>
/// update a product
/// </summary>
/// <param name="product"></param>
/// <returns></returns>
public bool Update(ProductDE product)
{
int result = -1;
bool success = false;
if (product != null)
{
SqlConnection connection = new SqlConnection(connectstring);
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "update Product set ProductName=@ProductName,ProductPrice=@ProductPrice,CategoryId=@CategoryId where ProductId=@ProductId";
command.Parameters.AddWithValue("ProductId", product.ProductId);
command.Parameters.AddWithValue("ProductName", product.ProductName);
command.Parameters.AddWithValue("ProductPrice", product.ProductPrice);
command.Parameters.AddWithValue("CategoryId", product.CategoryId);
result = Convert.ToInt32(command.ExecuteScalar());
if (result != -1)
{
success = true;
}
}
}
return(success);
}
public ProductDE GetById(int id)
{
ProductDE Product = new ProductDE();
dataAccess.Open();
try
{
string sql = "select ProductName from Product where ProductId=" + id;
SqlCommand cmd = new SqlCommand(sql);
Product.ProductName = cmd.ExecuteScalar().ToString();
//string sql = "select ProductName from Product where ProductId=@ProductId";
//QueryParameter p = new QueryParameter("ProductId", id, DbType.Int32);
//Product.ProductName = dataAccess.ExecuteScalar(sql, p).ToString();
//IDataReader reader = dataAccess.GetReader(sql, p);
//while (reader.Read())
//{
// Product.ProductId = id;
// Product.ProductName = reader.GetString(1);
//}
}
catch (Exception e)
{
throw e;
}
finally
{
dataAccess.Close();
}
return(Product);
}
public void GetByIdTest()
{
OrderDetailsDA target = new OrderDetailsDA(); // TODO: Initialize to an appropriate value
int id = 0; // TODO: Initialize to an appropriate value
ProductDE expected = null; // TODO: Initialize to an appropriate value
ProductDE actual;
actual = target.GetById(id);
Assert.AreEqual(expected, actual);
Assert.Inconclusive("Verify the correctness of this test method.");
}
/// <summary>
/// insert a new product
/// </summary>
/// <param name="product"></param>
/// <returns></returns>
public ProductDE Insert(ProductDE product)
{
if (product != null)
{
SqlConnection connection = new SqlConnection(connectstring);
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "insert into Product(ProductName,ProductPrice,CategoryId) values (@ProductName,@ProductPrice,@CategoryId) select SCOPE_IDENTITY()";
command.Parameters.AddWithValue("ProductName", product.ProductName);
command.Parameters.AddWithValue("ProductPrice", product.ProductPrice);
command.Parameters.AddWithValue("CategoryId", product.Category.CategoryId);
product.ProductId = Convert.ToInt32(command.ExecuteScalar());
}
}
return(product);
}
public List <OrderDetailsDE> GetAllOrderDetails()
{
List <OrderDetailsDE> list = new List <OrderDetailsDE>();
dataAccess.Open();
try
{
string sql = "select * from OrderDetails";
IDataReader reader = dataAccess.GetReader(sql);
while (reader.Read())
{
OrderDetailsDE OrderDetails = new OrderDetailsDE();
OrderDetails.OrderDetailsId = reader.GetInt32(0);
OrderDetails.OrderId = reader.GetInt32(1);
//Product.ProductId = OrderDetails.ProductDE.ProductId;
//OrderDetails.ProductDE.ProductName = reader.GetString(2);
OrderDetails.Quantity = reader.GetInt32(3);
OrderDetails.TotalPrice = reader.GetDecimal(4);
int productId = reader.GetInt32(2);
//OrderDetails.ProductDE.ProductId = reader.GetInt32(2);
//string sql1 = "select ProductName from Product where ProductId=" + productId;
//dataAccess.Open();
//SqlCommand cmd = new SqlCommand(sql1);
ProductDE product = GetById(productId);
//product.ProductName = cmd.ExecuteScalar().ToString();
//ProductDE Product = GetById(productId);
OrderDetails.ProductDE = product;
list.Add(OrderDetails);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dataAccess.Close();
}
return(list);
}
/// <summary>
/// get product by id
/// </summary>
/// <param name="productid"></param>
/// <returns></returns>
public ProductDE GetById(int productid)
{
List <ProductDE> productList = new List <ProductDE>();
ProductDE productResult = new ProductDE();
if (productid >= 0)
{
SqlConnection connection = new SqlConnection(connectstring);
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "select * from Product where ProductId = @ProductId";
command.Parameters.AddWithValue("ProductId", productid);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable table = new DataTable();
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
ProductDE product = new ProductDE()
{
ProductId = Convert.ToInt32(row["ProductId"]),
ProductName = Convert.ToString(row["ProductName"]),
ProductPrice = Convert.ToDecimal(row["ProductPrice"]),
CategoryId = Convert.ToInt32(row["CategoryId"])
};
CategoryDA categoryDA = new CategoryDA();
product.Category = categoryDA.GetById(Convert.ToInt32(row["CategoryId"]));
productList.Add(product);
productResult = productList.First();
}
}
}
return(productResult);
}
