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); }