public Product Get(int id)
{
Product pro = new Product();
string sqlCommand = "Select * from Products where ProductID=" + id;
using (IDataReader dr = ConnectionClass.GetInstance().ExecuteReader(sqlCommand))
{
if (dr.Read())
{
pro.ProductID = dr.GetInt32(0);
pro.ProductName = dr.GetString(1);
pro.CategoryID = dr.GetInt32(2);
pro.QuantityPerUnit = dr.GetString(4);
pro.UnitPrice = dr.GetDecimal(5);
pro.UnitsInStock = dr.GetInt16(6);
pro.UnitsOnOrder = dr.GetInt16(7);
pro.ReorderLevel = dr.GetInt16(8);
pro.Discontinued = dr.GetBoolean(9);
}
}
return pro;
}
public IQueryable<Product> GetAll()
{
List<Product> lspro = new List<Product>();
string sqlcm = "Select * from Products";
using (IDataReader dr = ConnectionClass.GetInstance().ExecuteReader(sqlcm))
{
while (dr.Read())
{
Product pro = new Product();
pro.ProductID = dr.GetInt32(0);
pro.ProductName = dr.GetString(1);
pro.CategoryID = dr.GetInt32(3);
pro.QuantityPerUnit = dr.GetString(4);
pro.UnitPrice = dr.GetDecimal(5);
pro.UnitsInStock = dr.GetInt16(6);
pro.UnitsOnOrder = dr.GetInt16(7);
pro.ReorderLevel = dr.GetInt16(8);
pro.Discontinued = dr.GetBoolean(9);
lspro.Add(pro);
}
dr.Close();
}
return lspro.AsQueryable();
}
public int UpdateProduct(Product or)
{
_Proservice = new ProductService();
if (_Proservice.Update(or) == 1)
return 1;
else
return 0;
}
public int AddProduct(Product or)
{
_Proservice = new ProductService();
if (_Proservice.Add(or) == 1)
return 1;
else
return 0;
}
public int Add(Product pro)
{
if(pro.Discontinued==true)
{
string sqlcm = "Insert into Products(ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) " +
"values ('" + pro.ProductName + "',1,'" + pro.CategoryID + "','" + pro.QuantityPerUnit + "'," + pro.UnitPrice +
"," + pro.UnitsInStock + "," + pro.UnitsOnOrder + "," + pro.ReorderLevel + ",1)";
return ConnectionClass.GetInstance().ExecuteNonQuery(sqlcm);
}
else
{
string sqlcm = "Insert into Products(ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) " +
"values ('" + pro.ProductName + "',1,'" + pro.CategoryID + "','" + pro.QuantityPerUnit + "'," + pro.UnitPrice +
"," + pro.UnitsInStock + "," + pro.UnitsOnOrder + "," + pro.ReorderLevel + ",0)";
return ConnectionClass.GetInstance().ExecuteNonQuery(sqlcm);
}
}
public int Update(Product pro)
{
if(pro.Discontinued == true)
{
string sqlcm = "Update Products set" +
"ProductName=" + pro.ProductName + ",SupplierID=1,CategoryID=" + pro.CategoryID +
",QuantityPerUnit=" + pro.QuantityPerUnit + ",UnitPrice= " + pro.UnitPrice +
",UnitsInStock=" + pro.UnitsInStock + ",UnitsOnOrder=" + pro.UnitsOnOrder + ",ReorderLevel=" + pro.ReorderLevel + ",Discontinued=1 where ProductID=" + pro.ProductID;
return ConnectionClass.GetInstance().ExecuteNonQuery(sqlcm);
}
else
{
string sqlcm = "Update Products set" +
" ProductName='" + pro.ProductName + "',SupplierID=1,CategoryID=" + pro.CategoryID +
",QuantityPerUnit='" + pro.QuantityPerUnit + "',UnitPrice= " + pro.UnitPrice +
",UnitsInStock=" + pro.UnitsInStock + ",UnitsOnOrder=" + pro.UnitsOnOrder + ",ReorderLevel=" + pro.ReorderLevel + ",Discontinued=0 where ProductID=" + pro.ProductID;
return ConnectionClass.GetInstance().ExecuteNonQuery(sqlcm);
}
}