public bool Update(productsTable p)
{
//create a boolean variable and set its initial value to false
bool isSuccess = false;
//Create SQL Connection for DAtabase
SQLiteConnection conn = new SQLiteConnection(gParams.myConnection);
try
{
//SQL Query to Update Data in dAtabase
String sql = "UPDATE tbl_products SET name=@name, description=@description, datePurchased=@datepurchased, cost=@cost, invoice_number=@invoice_number WHERE id=@id";
//Create SQL Cmmand to pass the value to query
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//Passing the values using parameters and cmd
cmd.Parameters.AddWithValue("@name", p.name);
cmd.Parameters.AddWithValue("@description", p.description);
cmd.Parameters.AddWithValue("@datepurchased", p.datePurchased);
cmd.Parameters.AddWithValue("@cost", p.cost);
cmd.Parameters.AddWithValue("@invoice_number", p.invoice_number);
cmd.Parameters.AddWithValue("@id", p.id);
//Open the Database connection
conn.Open();
//Create Int Variable to check if the query is executed successfully or not
int rows = cmd.ExecuteNonQuery();
//if the query is executed successfully then the value of rows will be greater than 0 else it will be less than zero
if (rows > 0)
{
//Query ExecutedSuccessfully
isSuccess = true;
}
else
{
//Failed to Execute Query
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Insert(productsTable p)
{
//Creating Boolean Variable and set its default value to false
bool isSuccess = false;
//Sql Connection for DAtabase
SQLiteConnection conn = new SQLiteConnection(gParams.myConnection);
try
{
//SQL Query to insert product into database
String sql = "INSERT INTO tbl_products (name, description, datePurchased, cost, invoice_number) VALUES (@name, @description, @datePurchased, @cost, @invoice_number)";
//Creating SQL Command to pass the values
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//Passign the values through parameters
cmd.Parameters.AddWithValue("@name", p.name);
cmd.Parameters.AddWithValue("@description", p.description);
cmd.Parameters.AddWithValue("@datepurchased", p.datePurchased);
cmd.Parameters.AddWithValue("@cost", p.cost);
cmd.Parameters.AddWithValue("@invoice_number", p.invoice_number);
//Opening the Database connection
conn.Open();
int rows = cmd.ExecuteNonQuery();
//If the query is executed successfully then the value of rows will be greater than 0 else it will be less than 0
if (rows > 0)
{
//Query Executed Successfully
isSuccess = true;
}
else
{
//FAiled to Execute Query
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Delete(productsTable p)
{
//Create Boolean Variable and Set its default value to false
bool isSuccess = false;
//SQL Connection for DB connection
SQLiteConnection conn = new SQLiteConnection(gParams.myConnection);
try
{
//Write Query Product from DAtabase
String sql = "DELETE FROM tbl_products WHERE id=@id";
//Sql Command to Pass the Value
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//Passing the values using cmd
cmd.Parameters.AddWithValue("@id", p.id);
//Open Database Connection
conn.Open();
int rows = cmd.ExecuteNonQuery();
//If the query is executed successfullly then the value of rows will be greated than 0 else it will be less than 0
if (rows > 0)
{
//Query Executed Successfully
isSuccess = true;
}
else
{
//Failed to Execute Query
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public productsTable GetProductsForTransaction(string keyword)
{
//Create an object of productsBLL and return it
productsTable p = new productsTable();
//SQLiteConnection
SQLiteConnection conn = new SQLiteConnection(gParams.myConnection);
//Datatable to store data temporarily
DataTable dt = new DataTable();
try
{
//Write the Query to Get the detaisl
string sql = "SELECT name, rate, qty FROM tbl_products WHERE id LIKE @keyword OR name LIKE @keyword";
//Create Sql Data Adapter to Execute the query
SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, conn);
adapter.SelectCommand.Parameters.AddWithValue("@keyword", keyword);
adapter.SelectCommand.Parameters.AddWithValue("@keyword", keyword);
//Open DAtabase Connection
conn.Open();
//Pass the value from adapter to dt
adapter.Fill(dt);
//If we have any values on dt then set the values to productsBLL
if (dt.Rows.Count > 0)
{
p.name = dt.Rows[0]["name"].ToString();
p.cost = decimal.Parse(dt.Rows[0]["rate"].ToString());
p.invoice_number = int.Parse(dt.Rows[0]["invoice_number"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//Close Database Connection
conn.Close();
}
return(p);
}
public DataTable GetProductsByDateRange(long start, long end)
{
//First Create an Object of DeaCust BLL and REturn it
productsTable p = new productsTable();
//SQL Conection here
SQLiteConnection conn = new SQLiteConnection(gParams.myConnection);
//Data TAble to Holdthe data temporarily
DataTable dt = new DataTable();
try
{
//SQL Query to Get id based on Name
string sql = "SELECT * FROM tbl_products WHERE datePurchased BETWEEN @startdate AND @enddate";
//Create the SQL Data Adapter to Execute the Query
SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, conn);
adapter.SelectCommand.Parameters.AddWithValue("@startdate", start);
adapter.SelectCommand.Parameters.AddWithValue("@enddate", end);
conn.Open();
//Passing the CAlue from Adapter to DAtatable
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//Pass the value from dt to DeaCustBLL dc
p.id = int.Parse(dt.Rows[0]["id"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(dt);
}
public DataTable GetProductsFromInvoice(int invoiceNumber)
{
//First Create an Object of DeaCust BLL and REturn it
productsTable p = new productsTable();
//SQL Conection here
SQLiteConnection conn = new SQLiteConnection(gParams.myConnection);
//Data TAble to Holdthe data temporarily
DataTable dt = new DataTable();
try
{
//SQL Query to Get id based on Invoice Number
string sql = "SELECT * FROM tbl_products WHERE invoice_number=@productname";
//Create the SQL Data Adapter to Execute the Query
SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, conn);
adapter.SelectCommand.Parameters.AddWithValue("@productname", invoiceNumber);
conn.Open();
//Passing the CAlue from Adapter to DAtatable
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
return(dt);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conn.Close();
}
return(dt);
}
