public static long SaleItems(string dateOfPurchase, List <Sale> saleItems) { OleDbConnection cnInsert = new OleDbConnection(DBUtil.GetConnectionString()); long lastInsertId = -1; OleDbTransaction saleTransaction; string query = "INSERT INTO Sales(Sale_Date,Type_ID,Quantity, Sale_Price) VALUES(@saleDate,@typeId, @quantity, @price)"; int iSqlStatus = 0; try { DBUtil.HandleConnection(cnInsert); saleTransaction = cnInsert.BeginTransaction(); foreach (Sale item in saleItems) { OleDbCommand cmdInsert = new OleDbCommand(); cmdInsert.CommandText = query; cmdInsert.CommandType = CommandType.Text; cmdInsert.Connection = cnInsert; cmdInsert.Transaction = saleTransaction; cmdInsert.Parameters.Clear(); cmdInsert.Parameters.AddWithValue("@saleDate", dateOfPurchase); cmdInsert.Parameters.AddWithValue("@typeId", item.ItemTypeId); cmdInsert.Parameters.AddWithValue("@quantity", item.Quantity); cmdInsert.Parameters.AddWithValue("@price", item.SalePrice); float avaliableQuantity = ShopDAL.getStockInHandQuantity(item.ItemTypeId); if (avaliableQuantity < item.Quantity) { saleTransaction.Rollback(); throw new Exception("Error: Low available quantity " + item.ItemName + " has only " + avaliableQuantity); } float newQuantity = avaliableQuantity - item.Quantity; ShopDAL.updateStockQuantityTransaction(cnInsert, saleTransaction, item.ItemTypeId, newQuantity); iSqlStatus = cmdInsert.ExecuteNonQuery(); if (iSqlStatus == 0) { saleTransaction.Rollback(); throw new Exception("Error: Inserting value"); } } saleTransaction.Commit(); lastInsertId = DBUtil.GetMaxID("Sale_ID", "Sales"); return(lastInsertId); } catch (Exception ex) { throw new Exception(ex.Message); } finally { //Now close the connection DBUtil.HandleConnection(cnInsert); } }
public static bool AddPurchasedItem(string dateOfPurchase, long itemTypeId, float quantity, float contractorId) { //Create the objects we need to insert a new record OleDbConnection cnInsert = new OleDbConnection(DBUtil.GetConnectionString()); OleDbCommand cmdInsert = new OleDbCommand(); string query = " INSERT INTO Purchase( Purchase_Date, Type_ID, Quantity, Contractor_ID ) "; query += " VALUES( @purcahseDate, @typeId, @quantity, @contractorId ) "; int iSqlStatus; cmdInsert.Parameters.Clear(); try { cmdInsert.CommandText = query; cmdInsert.CommandType = CommandType.Text; cmdInsert.Parameters.AddWithValue("@purcahseDate", dateOfPurchase); cmdInsert.Parameters.AddWithValue("@typeId", itemTypeId); cmdInsert.Parameters.AddWithValue("@quantity", quantity); cmdInsert.Parameters.AddWithValue("@contractorId", contractorId); cmdInsert.Connection = cnInsert; //Now take care of the connection DBUtil.HandleConnection(cnInsert); //Set the iSqlStatus to the ExecuteNonQuery //status of the insert (0 = failed, 1 = success) iSqlStatus = cmdInsert.ExecuteNonQuery(); //Now check the status if (iSqlStatus == 0) { return(false); } else { return(true); } } catch (Exception ex) { return(false); } finally { //Now close the connection DBUtil.HandleConnection(cnInsert); float currentQuantity = ShopDAL.getStockInHandQuantity(itemTypeId); float newQuantity = currentQuantity + quantity; ShopDAL.updateStockQuantity(cnInsert, itemTypeId, newQuantity); } }