private void itemRowUpdated(object sender, OleDbRowUpdatedEventArgs e)
{
if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert)
{
long maxId = DBUtil.GetMaxIDWithConnection("Type_ID", "ItemType", e.Command.Connection);
ShopDAL.insertValueOfStockInHand(e.Command.Connection, maxId);
}
}
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);
}
}
public static bool InsertNewItemType(ItemType item)
{
//Create the objects we need to insert a new record
OleDbConnection cnInsert = new OleDbConnection(DBUtil.GetConnectionString());
OleDbCommand cmdInsert = new OleDbCommand();
string query = " INSERT INTO ItemType(Name,Price, Sale_Price, UoM_ID, Vendor_ID) ";
query += " VALUES(@name, @price, @salePrice, @uomId, @vendorId)";
int iSqlStatus;
//Clear any parameters
cmdInsert.Parameters.Clear();
try
{
//Set the OleDbCommand Object Properties
//Tell it what to execute
cmdInsert.CommandText = query;
//Tell it its a text query
cmdInsert.CommandType = CommandType.Text;
//Now add the parameters to our query
//NOTE: Replace @value1.... with your parameter names in your query
//and add all your parameters in this fashion
cmdInsert.Parameters.AddWithValue("@name", item.ItemName);
cmdInsert.Parameters.AddWithValue("@price", item.Price);
cmdInsert.Parameters.AddWithValue("@salePrice", item.SalePrice);
cmdInsert.Parameters.AddWithValue("@uomId", item.Uom.Id);
cmdInsert.Parameters.AddWithValue("@vendorId", item.Vendor.Id);
//Set the connection of the object
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);
long maxId = DBUtil.GetMaxID("Type_ID", "ItemType");
ShopDAL.insertValueOfStockInHand(cnInsert, maxId);
}
}
