public void GetData()
{
string ShipmentQuery = "select A.ItemId, (select Name from Item where rowid=A.ItemId) AS Name, A.Quantity, A.DateTime from Production A";
DBDataObject DataObject = Utility.ExecuteSelect(ShipmentQuery);
productionView.PopulateView(DataObject);
}
//Standard menthod for update query with parameters
public static DBDataObject ExecuteUpdate(String query, byte[] input, String parameter)
{
SQLiteConnection sqlConnection = new SQLiteConnection(DBConnectionString());
DBDataObject dbDataObject = new DBDataObject();
try
{
SQLiteCommand sqlCommand = new SQLiteCommand(query, sqlConnection);
SQLiteParameter sqlParameter = sqlCommand.Parameters.Add("@" + parameter, DbType.Binary);
sqlParameter.Value = input;
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
dbDataObject.code = "Success";
}
catch (Exception ex)
{
dbDataObject.message = ex.Message;
dbDataObject.code = "Error";
Log(ex.Message);
}
finally
{
sqlConnection.Close();
}
return(dbDataObject);
}
public void PopulateView(DBDataObject dataObject)
{
dataGridView1.Rows.Clear();
foreach (DataRow r in dataObject.data.Rows)
{
dataGridView1.Rows.Add(r["ItemId"].ToString(), r["Name"].ToString(), r["Quantity"].ToString(), r["DateTime"].ToString());
}
}
public void getStockData()
{
string query = "select * from [Item]";
DBDataObject ItemdataObject = Utility.ExecuteSelect(query);
string ProductionQuery = "select A.ItemId, (select SUM(Quantity) from Shipping where ItemId=A.ItemId) As SQuantity, (select Name from Item where rowid=A.ItemId) AS Name, SUM(A.Quantity) AS PQuantity from Production A GROUP BY A.ItemId";
DBDataObject MainDataObject = Utility.ExecuteSelect(ProductionQuery);
stockView.PopulateGridView(MainDataObject);
}
public void GetReport()
{
string ProductionQuery = "select SUM(Quantity) AS Quantity from Production";
DBDataObject MainDataObject = Utility.ExecuteSelect(ProductionQuery);
string ShipmentQuery = "select SUM(Quantity) AS Quantity from Shipping";
DBDataObject ShipDataObject = Utility.ExecuteSelect(ShipmentQuery);
reportView.PopulateData(ShipDataObject, MainDataObject);
}
public static DBDataObject ExecuteBulkInsert(List <string> queryList)
{
SQLiteConnection sqlConnection = new SQLiteConnection(DBConnectionString());
DBDataObject dbDataObject = new DBDataObject();
bool isbreaked = false;
try
{
_readerWriterLock.EnterWriteLock();
if (_readerWriterLock.WaitingReadCount > 0)
{
isbreaked = true;
}
else
{
sqlConnection.Open();
using (var cmd = new SQLiteCommand(sqlConnection))
{
using (var transaction = sqlConnection.BeginTransaction())
{
//inserts
foreach (var item in queryList)
{
cmd.CommandText = item;
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
}
}
}
catch (Exception ex)
{
dbDataObject.message = ex.Message;
dbDataObject.code = "Error";
Log(ex.Message);
}
finally
{
sqlConnection.Close();
_readerWriterLock.ExitWriteLock();
}
if (isbreaked)
{
Thread.Sleep(3);
return(ExecuteBulkInsert(queryList));
}
return(dbDataObject);
}
//Getting the configuration parameter
public static DBDataObject GetSettings()
{
string[] fieldstoselect = { "ReceiptPrinter", "BarcodeReceiptPrinter", "StoreName", "StoreAddress", "StorePhone" };
NameValueCollection whereValues = new NameValueCollection()
{
{ "rowid", "1" }
};
string[] relator = { " = " };
string query = Utility.SelectQuery(fieldstoselect, "[Settings]", whereValues, relator);
DBDataObject dbDataObject = Utility.ExecuteSelect(query);
return(dbDataObject);
}
//Standard menthod for insert query
public static DBDataObject ExecuteInsert(String query)
{
SQLiteConnection sqlConnection = new SQLiteConnection(DBConnectionString());
DBDataObject dbDataObject = new DBDataObject();
bool isbreaked = false;
try
{
_readerWriterLock.EnterWriteLock();
if (_readerWriterLock.WaitingReadCount > 0)
{
isbreaked = true;
}
else
{
SQLiteCommand sqlCommand = new SQLiteCommand(query, sqlConnection);
sqlConnection.Open();
Int32 count = (Int32)sqlCommand.ExecuteNonQuery();
query = "SELECT last_insert_rowid()";
sqlCommand = new SQLiteCommand(query, sqlConnection);
var lastID = sqlCommand.ExecuteScalar();
dbDataObject.message = lastID.ToString();
dbDataObject.code = "Success";
}
}
catch (Exception ex)
{
dbDataObject.message = ex.Message;
dbDataObject.code = "Error";
Log(ex.Message);
}
finally
{
sqlConnection.Close();
_readerWriterLock.ExitWriteLock();
}
if (isbreaked)
{
Thread.Sleep(3);
return(ExecuteInsert(query));
}
return(dbDataObject);
}
public void PopulateGridView(DBDataObject dataObject)
{
foreach (DataRow r in dataObject.data.Rows)
{
string SQuantity = r["PQuantity"].ToString();
string PQuantity = r["SQuantity"].ToString();;
if (r["PQuantity"] == null || r["PQuantity"].ToString() == "")
{
SQuantity = "0";
}
if (r["SQuantity"] == null || r["SQuantity"].ToString() == "")
{
PQuantity = "0";
}
Decimal Instock = Decimal.Parse(SQuantity) - Decimal.Parse(PQuantity);
MainStockdataGridView.Rows.Add(r["ItemId"].ToString(), r["Name"].ToString(), Instock);
}
}
//Standard menthod for select query
public static DBDataObject ExecuteSelect(String query)
{
SQLiteConnection sqlConnection = new SQLiteConnection(DBConnectionString());
DBDataObject dbDataObject = new DBDataObject();
try
{
_readerWriterLock.EnterReadLock();
SQLiteCommand sqlCommand = new SQLiteCommand(sqlConnection);
sqlConnection.Open();
sqlCommand.CommandText = query;
SQLiteDataReader reader = sqlCommand.ExecuteReader();
dbDataObject.data = new DataTable();
//for (int i = 0; i < reader.FieldCount; i++)
//{
// dbDataObject.data.Columns.Add(reader.GetName(i));
//}
//var j = reader.FieldCount;
//while (reader.Read())
//{
// DataRow row = dbDataObject.data.NewRow();
// row["Name"] = reader[reader];
// row["Marks"] = "500";
//}
dbDataObject.data.Load(reader);
}
catch (Exception ex)
{
dbDataObject.message = ex.Message;
Log(ex.Message);
}
finally
{
sqlConnection.Close();
_readerWriterLock.ExitReadLock();
}
return(dbDataObject);
}
public void PopulateData(DBDataObject Shipping, DBDataObject Production)
{
decimal ShippingTotal = 0;
decimal ProductionTotal = 0;
foreach (DataRow r in Shipping.data.Rows)
{
ShippingTotal += Decimal.Parse(r["Quantity"].ToString());
}
foreach (DataRow r in Production.data.Rows)
{
ProductionTotal += Decimal.Parse(r["Quantity"].ToString());
}
label4.Text = ProductionTotal.ToString();
label5.Text = ShippingTotal.ToString();
label6.Text = (ProductionTotal - ShippingTotal).ToString();
}
//Standard menthod for update query
public static DBDataObject ExecuteUpdate(String query)
{
SQLiteConnection sqlConnection = new SQLiteConnection(DBConnectionString());
DBDataObject dbDataObject = new DBDataObject();
bool isbreaked = false;
try
{
_readerWriterLock.EnterWriteLock();
if (_readerWriterLock.WaitingReadCount > 0)
{
isbreaked = true;
}
else
{
SQLiteCommand sqlCommand = new SQLiteCommand(query, sqlConnection);
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
dbDataObject.code = "Success";
}
}
catch (Exception ex)
{
dbDataObject.message = ex.Message;
Log(ex.Message);
}
finally
{
sqlConnection.Close();
_readerWriterLock.ExitWriteLock();
}
if (isbreaked)
{
Thread.Sleep(3);
return(ExecuteUpdate(query));
}
return(dbDataObject);
}
public void AddProduction(string SKU, string Quantity)
{
try
{
string ItemQuery = "select rowid from Item where SKU='" + SKU + "'";
DBDataObject DataObject = Utility.ExecuteSelect(ItemQuery);
string rowid = "";
foreach (DataRow r in DataObject.data.Rows)
{
rowid = r["rowid"].ToString();
}
//INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
string query = "INSERT INTO Production (ItemId, Quantity, DateTime) VALUES ('" + rowid + "', '" + Quantity + "', '" + Utility.GetDateTimeNow() + "')";
DBDataObject Dataobject = Utility.ExecuteInsert(query);
GetData();
}
catch
{
MessageBox.Show("Cant Find Fields");
}
}
