public void AddProduct1(InStock ins)
{
string sql = "INSERT INTO Products (categoryID, productname,UnitPrice,PurchasedPrice, Triggerlevel,UnitInStock, ExpiryDate,Vendor,VendorAddress) values " +
"(@CategoryId, @productName, @unitPrice, @purchasedPrice, @TriggerLevel, @quantity, @expiryDate, @vendor, @vendorAddress)";
SqlCommand cmd = new SqlCommand(sql, conn);
// cmd.Parameters.Add("@productId", SqlDbType.Int).Value = ins.Id;
cmd.Parameters.Add("@CategoryId", SqlDbType.Int).Value = ins.CategoryId;
cmd.Parameters.Add("@productName", SqlDbType.Text).Value = ins.ProductName;
cmd.Parameters.Add("@unitPrice", SqlDbType.Decimal).Value = ins.UnitPrice;
cmd.Parameters.Add("@purchasedPrice", SqlDbType.Decimal).Value = ins.SalePrice;
cmd.Parameters.Add("@TriggerLevel", SqlDbType.Int).Value = ins.TriggerLevel;
cmd.Parameters.Add("@quantity", SqlDbType.Int).Value = ins.Quantity;
cmd.Parameters.Add("@expiryDate", SqlDbType.DateTime).Value = ins.ExpiryDate;
cmd.Parameters.Add("@vendor", SqlDbType.Text).Value = ins.Vendor;
cmd.Parameters.Add("@vendorAddress", SqlDbType.Text).Value = ins.Vendor;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
public void UpdateProduct(InStock ins)
{
string sql =
"update Products set categoryid=@CategoryId, productName=@productName, UnitInStock=@quantity," +
"TriggerLevel=@TriggerLevel,unitPrice=@unitPrice,PurchasedPrice=@purchasedPrice,vendor=@vendor,expiryDate=@expiryDate"
+ " where productId=@productId";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@productId", SqlDbType.Int).Value = ins.Id;
cmd.Parameters.Add("@CategoryId", SqlDbType.Int).Value = ins.CategoryId;
cmd.Parameters.Add("@productName", SqlDbType.Text).Value = ins.ProductName;
cmd.Parameters.Add("@unitPrice", SqlDbType.Decimal).Value = ins.UnitPrice;
cmd.Parameters.Add("@TriggerLevel", SqlDbType.Int).Value = ins.TriggerLevel;
cmd.Parameters.Add("@quantity", SqlDbType.Int).Value = ins.Quantity;
cmd.Parameters.Add("@purchasedPrice", SqlDbType.Decimal).Value = ins.SalePrice;
cmd.Parameters.Add("@vendor", SqlDbType.Text).Value = ins.Vendor;
cmd.Parameters.Add("@expiryDate", SqlDbType.DateTime).Value = ins.ExpiryDate;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
public List <InStock> GetAllProducts()
{
List <InStock> result = new List <InStock>();
try
{
using (SqlCommand command = new SqlCommand("SELECT * FROM Products", conn))
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int Id = (int)reader["productId"];
int categoryId = (int)reader["CategoryId"];
string productName = (string)reader["productName"];
decimal unitPrice = (decimal)reader["unitPrice"];
decimal salePrice = (decimal)reader["PurchasedPrice"];
int unitInStock = (int)reader["unitinstock"];
int trigger = (int)reader["TriggerLevel"];
string vendor = (string)reader["Vendor"];
//string vendorAddress = (string)reader["VendorAddress"];
DateTime expDate = (DateTime)reader["ExpiryDate"];
InStock o = new InStock(Id, categoryId, productName, unitPrice, salePrice, unitInStock, trigger,
vendor, expDate.Date);
result.Add(o);
}
}
}
catch (SqlException e)
{
Console.WriteLine(e.StackTrace);
MessageBox.Show("Error opening database connection: " + e.Message);
Environment.Exit(1);
}
return(result);
}