public bool Delete(productsBLL p)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
String sql = "DELETE from tbl_products where id=@id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@id", p.id);
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
//Delete Success
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public productsBLL GetProductIDFromName(string ProductName)
{
//First create an object of Deacust BLL and retrurn it
productsBLL p = new productsBLL();
//SQL
SqlConnection conn = new SqlConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
string sql = "select id from tbl_products where name='" + ProductName + "' ";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
conn.Open();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
p.id = int.Parse(dt.Rows[0]["id"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(p);
}
public productsBLL GetProductsForTransaction(string keyword)
{
//create an object of productBLL and return it
productsBLL p = new productsBLL();
SqlConnection conn = new SqlConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
//write the query to get the details
string sql = "SELECT id,name, rate, qty FROM tbl_products WHERE id LIKE '%" + keyword + "%' OR name LIKE '%" + keyword + "%'";
//create sql data adapter to execute the query
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
conn.Open();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
p.id = int.Parse(dt.Rows[0]["id"].ToString());
p.name = dt.Rows[0]["name"].ToString();
p.rate = decimal.Parse(dt.Rows[0]["rate"].ToString());
p.qty = decimal.Parse(dt.Rows[0]["qty"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(p);
}
public bool Delete(productsBLL c)
{
bool isSucces = false;
SqlConnection conn = new SqlConnection(myconnstring);
try
{
string sql = "DELETE FROM tbl_products WHERE Id=@Id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@Id", c.Id);
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSucces = true;
}
else
{
isSucces = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSucces);
}
public productsBLL GetProductsIDFromName(string ProductName)
{
//First create an object of deacustBLL and return it
productsBLL p = new productsBLL();
//sql conn
SqlConnection conn = new SqlConnection(myconnstring);
//data table to hold data temporariliy
DataTable dt = new DataTable();
try
{
// sql query to get id based on name
string sql = "SELECT id FROM tbl_products WHERE name='" + ProductName + "'";
//create the sql data adapter
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
conn.Open();
//passing the value from adapter to data table
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//pass the value from dt to deacustBLLdc
p.id = int.Parse(dt.Rows[0]["id"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(p);
}
public productsBLL GetProductsFromTransaction(string keyword)
{
productsBLL p = new productsBLL();
SqlConnection conn = new SqlConnection(myconnstring);
DataTable dt = new DataTable();
try
{
string sql = "SELECT name,rate,qty FROM tbl_products WHERE Id LIKE '%" + keyword + "%' OR name LIKE '%" + keyword + "%'";
SqlDataAdapter adaptor = new SqlDataAdapter(sql, conn);
conn.Open();
adaptor.Fill(dt);
if (dt.Rows.Count > 0)
{
p.name = dt.Rows[0]["name"].ToString();
p.rate = decimal.Parse(dt.Rows[0]["rate"].ToString());
p.qty = decimal.Parse(dt.Rows[0]["qty"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(p);
}
public productsBLL GetIDFromUsername(string username)
{
productsBLL p = new productsBLL();
SqlConnection conn = new SqlConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
string sql = "select id from tbl_users where username='******'";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
conn.Open();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
p.id = int.Parse(dt.Rows[0]["id"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(p);
}
public productsBLL GetProductIdFromName(string ProductName)
{
productsBLL p = new productsBLL();
SqlConnection conn = new SqlConnection(myconnstring);
DataTable dt = new DataTable();
try
{
string sql = "SELECT Id FROM tbl_products WHERE name ='" + ProductName + "'";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
conn.Open();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
p.Id = int.Parse(dt.Rows[0]["Id"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(p);
}
public bool Update(productsBLL p)
{
//create a boolean variable and set its initial value to false
bool isSuccess = false;
//Create SQL Connection for DAtabase
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
//SQL Query to Update Data in dAtabase
String sql = "UPDATE tbl_products SET name=@name, category=@category, description=@description, rate=@rate, added_date=@added_date, added_by=@added_by WHERE id=@id";
//Create SQL Cmmand to pass the value to query
SqlCommand cmd = new SqlCommand(sql, conn);
//Passing the values using parameters and cmd
cmd.Parameters.AddWithValue("@name", p.name);
cmd.Parameters.AddWithValue("@category", p.category);
cmd.Parameters.AddWithValue("@description", p.description);
cmd.Parameters.AddWithValue("@rate", p.rate);
cmd.Parameters.AddWithValue("@qty", p.qty);
cmd.Parameters.AddWithValue("@added_date", p.added_date);
cmd.Parameters.AddWithValue("@added_by", p.added_by);
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 update(productsBLL p)
{
//Creating A Boollean Variable and set it default value to false;
bool isSuccess = false;
//Create Sql connection for database
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
//Create Sql Quary to update in database
string sql = "UPDATE tbl_products SET name=@name, category=@category, description=@description, rate=@rate, added_date=@added_date, added_by=@added_by WHERE id=@id";
//creating Sql command to pass the Quary
SqlCommand cmd = new SqlCommand(sql, conn);
//passing the value through parameter and cmd;
cmd.Parameters.AddWithValue("name", p.name);
cmd.Parameters.AddWithValue("category", p.category);
cmd.Parameters.AddWithValue("description", p.description);
cmd.Parameters.AddWithValue("rate", p.rate);
cmd.Parameters.AddWithValue("qty", p.qty);
cmd.Parameters.AddWithValue("added_date", p.added_date);
cmd.Parameters.AddWithValue("added_by", p.added_by);
cmd.Parameters.AddWithValue("id", p.id);
//open the database connection
conn.Open();
//create Int variable to check if the quary is executed Successfully
int rows = cmd.ExecuteNonQuery();
//if the quary is executed successfully then the value of rows is greater than 0 else it will be less than 0.
if (rows > 0)
{
//Quary executed Successfully
isSuccess = true;
}
else
{
//Failed to execute Quary
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
private void cmbPro_SelectedIndexChanged(object sender, EventArgs e)
{
string keyword = cmbPro.Text;
productsBLL p = pdal.GetProductsForPurchaseSales(keyword);
txtNamePro.Text = p.name;
txtInventory.Text = p.qty.ToString();
txtRate.Text = p.rate.ToString();
txtmin.Text = p.min.ToString();
}
public bool Insert(productsBLL c)
{
//Connecting A Boolean Variabled and set its default value to false
bool isSucess = false;
//Connecting to Database
SqlConnection conn = new SqlConnection(myconnstring);
try
{
//Writing Query to Add New Category
string sql = "INSERT INTO tbl_products(name,category,description,rate,qty,added_date,added_by) VALUES(@name,@category,@description,@rate,@qty,@added_date,@added_by)";
//Creating SQL Command to pass values in our query
SqlCommand cmd = new SqlCommand(sql, conn);
//Passing Values through parameterd
cmd.Parameters.AddWithValue("@name", c.name);
cmd.Parameters.AddWithValue("@category", c.category);
cmd.Parameters.AddWithValue("@description", c.description);
cmd.Parameters.AddWithValue("@rate", c.rate);
cmd.Parameters.AddWithValue("@qty", c.qty);
cmd.Parameters.AddWithValue("@added_date", c.added_date);
cmd.Parameters.AddWithValue("@added_by", c.added_by);
//Open Database Connection
conn.Open();
//Creating the int Variable to execute query
int rows = cmd.ExecuteNonQuery();
//If the query is executed successfully then its value will be greater than 0 else it will be less than 0
if (rows > 0)
{
//Query Executed Succesfully
isSucess = true;
}
else
{
//Failed to execute
isSucess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//Closing Database Connection
conn.Close();
}
return(isSucess);
}
public bool Update(productsBLL c)
{
//Creating Boolean variable and set its default value to false
bool isSuccess = false;
//Creating SQL Connection
SqlConnection conn = new SqlConnection(myconnstring);
try
{
//Query to update category
string sql = "UPDATE tbl_products SET name=@name, category=@category, description=@description, rate=@rate,added_date=@added_date, added_by=@added_by WHERE id =@id";
//SQL command to pass the value on sql query
SqlCommand cmd = new SqlCommand(sql, conn);
//Passing Value using cmd
cmd.Parameters.AddWithValue("name", c.name);
cmd.Parameters.AddWithValue("category", c.category);
cmd.Parameters.AddWithValue("description", c.description);
cmd.Parameters.AddWithValue("rate", c.rate);
cmd.Parameters.AddWithValue("qty", c.qty);
cmd.Parameters.AddWithValue("added_date", c.added_date);
cmd.Parameters.AddWithValue("added_by", c.added_by);
cmd.Parameters.AddWithValue("id", c.id);
//Open Database Connection
conn.Open();
//Create Int Variable to execute query
int rows = cmd.ExecuteNonQuery();
//if the query is successfully executed than the value will be greater than 0
if (rows > 0)
{
//Query Executed succesfull
isSuccess = true;
}
else
{
//failed executed
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Update(productsBLL p)
{
//create a boolean variable and set the inisiatial value to false
bool isSuccess = false;
//create sql connection for database
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
//sql query to update databse
string sql = "UPDATE tbl_products SET name=@name, category=@category, description=@description, rate=@rate, added_date=@added_date, added_by=@added_by WHERE id=@id";
//create sql command to pass the value to query
SqlCommand cmd = new SqlCommand(sql, conn);
//passing the value using parameters and cmd
cmd.Parameters.AddWithValue("@name", p.name);
cmd.Parameters.AddWithValue("@category", p.category);
cmd.Parameters.AddWithValue("@description", p.description);
cmd.Parameters.AddWithValue("@rate", p.rate);
cmd.Parameters.AddWithValue("@qty", p.qty);
cmd.Parameters.AddWithValue("@added_date", p.added_date);
cmd.Parameters.AddWithValue("@added_by", p.added_by);
cmd.Parameters.AddWithValue("@id", p.id);
//open the database connection
conn.Open();
//create int variable to check is query is executed successfully or not
int rows = cmd.ExecuteNonQuery();
//if query executed successfully then rows are greater than 0 else less than 0
if (rows > 0)
{
//query executed successfully
isSuccess = true;
}
else
{
//query execution fails
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Insert(productsBLL p)
{
//creating boolean variable and set its default value to false
bool isSuccess = false;
//sql connection to database
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
//sql query to insert products in to database
String sql = "INSERT INTO tbl_products (name, category, description, rate, qty, added_date, added_by) VALUES (@name, @category, @description, @rate, @qty, @added_date, @added_by)";
//create sql command to pass the value
SqlCommand cmd = new SqlCommand(sql, conn);
//passing the value through parameters
cmd.Parameters.AddWithValue("@name", p.name);
cmd.Parameters.AddWithValue("@category", p.category);
cmd.Parameters.AddWithValue("@description", p.description);
cmd.Parameters.AddWithValue("@rate", p.rate);
cmd.Parameters.AddWithValue("@qty", p.qty);
cmd.Parameters.AddWithValue("@added_date", p.added_date);
cmd.Parameters.AddWithValue("@added_by", p.added_by);
//open the databse connection
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query is executed success then the value of rows is greater than 0 else less tham 0
if (rows > 0)
{
//query executed successfully
isSuccess = true;
}
else
{
//query executing failed
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Update(productsBLL p)
{
//create a bool variable and set its default value to false
bool isSuccess = false;
//Create sql connection for database
SqlConnection conn = new SqlConnection(myconnstring);
try
{
//sql query to update and database
string sql = "UPDATE tbl_products SET name=@name, category=@category, description=@description, rate=@rate, added_date=@added_date, added_by=@added_by WHERE id=@id";
//Create sql command to pass the value to query
SqlCommand cmd = new SqlCommand(sql, conn);
//Passing the values using parameters and cmd
cmd.Parameters.AddWithValue("@name", p.name);
cmd.Parameters.AddWithValue("@category", p.category);
cmd.Parameters.AddWithValue("@description", p.description);
cmd.Parameters.AddWithValue("@rate", p.rate);
cmd.Parameters.AddWithValue("@qty", p.qty);
cmd.Parameters.AddWithValue("@added_date", p.added_date);
cmd.Parameters.AddWithValue("@added_by", p.added_by);
cmd.Parameters.AddWithValue("@id", p.id);
//Open the database connection
conn.Open();
//create int variable to check the query is executed or not
int rows = cmd.ExecuteNonQuery();
//If the query is executed then the rows will be >0 else <0
if (rows > 0)
{
//Query successfully Execuuted
isSuccess = true;
}
else
{
//Failed to Execute the query
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Insert(productsBLL p)
{
//Creating Boolean Variable and set its default value to false
bool isSuccess = false;
//Sql Connection for DAtabase
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
//SQL Query to insert product into database
String sql = "INSERT INTO tbl_products (name, category, description, rate, qty, added_date, added_by) VALUES (@name, @category, @description, @rate, @qty, @added_date, @added_by)";
//Creating SQL Command to pass the values
SqlCommand cmd = new SqlCommand(sql, conn);
//Passign the values through parameters
cmd.Parameters.AddWithValue("@name", p.name);
cmd.Parameters.AddWithValue("@category", p.category);
cmd.Parameters.AddWithValue("@description", p.description);
cmd.Parameters.AddWithValue("@rate", p.rate);
cmd.Parameters.AddWithValue("@qty", p.qty);
cmd.Parameters.AddWithValue("@added_date", p.added_date);
cmd.Parameters.AddWithValue("@added_by", p.added_by);
//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 productsBLL GetProductsForTransaction(string keyword)
{
// Create an abject of productBLL and return it
productsBLL p = new productsBLL();
// SQL Connection first
SqlConnection conn = new SqlConnection(myconnstring);
// create data table to store data temporarily
DataTable dt = new DataTable();
try
{
// here we will write Query to get the details
string sql = "SELECT name,qty,rate FROM tbl_products WHERE id LIKE '%" + keyword + "%' OR name LIKE '%" + keyword + "%'";
// Execute Query
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
// Open Database Connection
conn.Open();
// Pass values from adapter to dt
adapter.Fill(dt);
// If we have any values on dt then set the values to products
if (dt.Rows.Count > 0)
{
p.name = dt.Rows[0]["name"].ToString();
p.qty = decimal.Parse(dt.Rows[0]["qty"].ToString());
p.rate = decimal.Parse(dt.Rows[0]["rate"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(p);
}
public bool Delete(productsBLL p)
{
// create a boolean variable and set its value to false
bool isSuccess = false;
// write query to update
SqlConnection conn = new SqlConnection(myconnstring);
try
{
// write query to delete from database
string sql = "DELETE FROM tbl_products WHERE id=@id";
SqlCommand cmd = new SqlCommand(sql, conn);
//passing the values using cmd
cmd.Parameters.AddWithValue("@id", p.id);
// open connection
conn.Open();
// we need to create integer variable to execute querry
//creating int variable to execute query
int rows = cmd.ExecuteNonQuery();
// if query is successfully executed then the value will be greater than zero
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Update(productsBLL p)
{
//Create a boolean variable and set its Initial value to false
bool isSuccess = false;
//Creating SQL Connection for Database
SqlConnection conn = new SqlConnection(myconnectstring);
try
{
//SQL Query to Update the data in database
string sql = "UPDATE tbl_products SET name=@name, category=@category, description=@description, rate=@rate, added_date=@added_date, added_by=@added_by WHERE id=@id";
//Create SQL Command to pass value to query
SqlCommand cmd = new SqlCommand(sql, conn);
//Passing the values using parameters and cmd
cmd.Parameters.AddWithValue("@name", p.name);
cmd.Parameters.AddWithValue("@category", p.category);
cmd.Parameters.AddWithValue("@description", p.description);
cmd.Parameters.AddWithValue("@rate", p.rate);
cmd.Parameters.AddWithValue("@qty", p.qty);
cmd.Parameters.AddWithValue("@added_date", p.added_date);
cmd.Parameters.AddWithValue("@added_by", p.added_by);
cmd.Parameters.AddWithValue("@id", p.id);
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Update(productsBLL p)
{
//create boolean variable and set it to false
bool isSuccess = false;
// create sql connection to db
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
// sql query to update data to db
String sql = "Update tbl_products set name =@name, category=@category, description=@description, rate=@rate, added_date=@added_date, added_by=@added_by where id =@id";
// create sql command the value to query
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@name", p.name);
cmd.Parameters.AddWithValue("@category", p.category);
cmd.Parameters.AddWithValue("@description", p.description);
cmd.Parameters.AddWithValue("@rate", p.rate);
cmd.Parameters.AddWithValue("@qty", p.rate);
cmd.Parameters.AddWithValue("@added_date", p.added_date);
cmd.Parameters.AddWithValue("@added_by", p.added_by);
cmd.Parameters.AddWithValue("@id", p.id);
//open db connection
conn.Open();
// create integer variable to check if the query is excuted successfully or not
int rows = cmd.ExecuteNonQuery();
// if query is excuted successfully then value pf rows will be > 0, else < 0
if (rows > 0)
{
//query excuted successfully
isSuccess = true;
}
else
{
//query failed to excute successfully
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Update(productsBLL p)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myConnstring);
try
{
string sql = "UPDATE tbl_produto SET name=@name, " +
"category=@category, " +
"description=@description, " +
"preco=@preco, " +
"qtd=@qtd, " +
"added_date=@added_date, " +
"added_by=@added_by " +
"WHERE id=@id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@name", p.name);
cmd.Parameters.AddWithValue("@category", p.category);
cmd.Parameters.AddWithValue("@description", p.description);
cmd.Parameters.AddWithValue("@preco", p.rate);
cmd.Parameters.AddWithValue("@qtd", p.qty);
cmd.Parameters.AddWithValue("@added_date", p.added_date);
cmd.Parameters.AddWithValue("@added_by", p.added_by);
cmd.Parameters.AddWithValue("@id", p.id);
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public IList <productsBLL> GetProductsForTransaction(string keyword)
{
//Create an object of productsBLL and return it
productsBLL p = new productsBLL();
List <productsBLL> pl = new List <productsBLL>();
//SqlConnection
SqlConnection conn = new SqlConnection(myconnstrng);
//Datatable to store data temporarily
DataTable dt = new DataTable();
try
{
//Write the Query to Get the detaisl
string sql = "SELECT name, description, rate, qty FROM tbl_products WHERE name = '" + keyword + "'";
//Create Sql Data Adapter to Execute the query
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
//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)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
p.name = dt.Rows[0]["name"].ToString();
p.description = dt.Rows[0]["description"].ToString();
p.rate = decimal.Parse(dt.Rows[0]["rate"].ToString());
p.qty = decimal.Parse(dt.Rows[0]["qty"].ToString());
pl.Add(p);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//Close Database Connection
conn.Close();
}
return(pl);
}
public bool Insert(productsBLL p)
{
bool isSuccess = false;
//Sql connection for database
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
// SQL Query to insert product in to database
string sql = "insert into tbl_products(name, category, description, rate, qty, added_date, added_by ) values (@name, @category, @description, @rate, @qty, @added_date, @added_by)";
//creating SQL command to pass the values
SqlCommand cmd = new SqlCommand(sql, conn);
//passing the values through parameters
cmd.Parameters.AddWithValue("@name", p.name);
cmd.Parameters.AddWithValue("@category", p.category);
cmd.Parameters.AddWithValue("@description", p.description);
cmd.Parameters.AddWithValue("@rate", p.rate);
cmd.Parameters.AddWithValue("@qty", p.qty);
cmd.Parameters.AddWithValue("@added_date", p.added_date);
cmd.Parameters.AddWithValue("@added_by", p.added_by);
//opening dB connection
conn.Open();
int rows = cmd.ExecuteNonQuery();
// ifthe query is excuted then the value of rows will be > 0 else <0
if (rows > 0)
{
// Query excuted successfully
isSuccess = true;
}
else
{
// Query failed
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Update(productsBLL p)
{
//create a boolean variable and set its dafault value to false
bool isSuccess = false;
//connecting to database
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
//query to update category
string sql = "UPDATE tbl_products SET name=@name, category=@category, Sub_Category=@Sub_Category, description=@description, rate=@rate, added_date=@added_date, added_by=@added_by WHERE id=@id";
//creating sql command to pass valuesin our query
SqlCommand cmd = new SqlCommand(sql, conn);
//passing values through parameters
cmd.Parameters.AddWithValue("@name", p.name);
cmd.Parameters.AddWithValue("@category", p.category);
cmd.Parameters.AddWithValue("@Sub_Category", p.Sub_Category);
cmd.Parameters.AddWithValue("@description", p.description);
cmd.Parameters.AddWithValue("@rate", p.rate);
cmd.Parameters.AddWithValue("@qty", p.qty);
cmd.Parameters.AddWithValue("@added_date", p.added_date);
cmd.Parameters.AddWithValue("@added_by", p.added_by);
cmd.Parameters.AddWithValue("@id", p.id);
//open database connection
conn.Open();
//creating the int variable to execute query
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Insert(productsBLL p)
{
//Creating Boolean Variable and set its default value to false
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnectstring);
try
{
//SQL Query to Insert products into Database
string sql = "INSERT INTO tbl_products(name, category, description, rate, qty, added_date, added_by)VALUES (@name, @category, @description, @rate, @qty, @added_date, @added_by)";
//Creating SQL Command to pass the values
SqlCommand cmd = new SqlCommand(sql, conn);
//Passing the value through parameters
cmd.Parameters.AddWithValue("@name", p.name);
cmd.Parameters.AddWithValue("@category", p.category);
cmd.Parameters.AddWithValue("@description", p.description);
cmd.Parameters.AddWithValue("@rate", p.rate);
cmd.Parameters.AddWithValue("@qty", p.qty);
cmd.Parameters.AddWithValue("@added_date", p.added_date);
cmd.Parameters.AddWithValue("@added_by", p.added_by);
//Opening Database Connection
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Delete(productsBLL p)
{
//Create A Boolean variable and set it default value to false,
bool isSuccess = false;
//SQL connection to connect to database
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
//Write Quary to Delete products from Database
string sql = "DELETE FROM tbl_products WHERE id=@id";
//Sql command to pass the Value
SqlCommand cmd = new SqlCommand(sql, conn);
//passing the value using cmd.
cmd.Parameters.AddWithValue("@id", p.id);
//Open the Database
conn.Open();
int rows = cmd.ExecuteNonQuery();
//If the Quary is executed successfully the value of rows will be greater than 0 else it will less than 0.
if (rows > 0)
{
//Quary executed Successfully
isSuccess = true;
}
else
{
//failed to execute quary
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Delete(productsBLL p)
{
//Create Boolean Variable and Set its default value to false
bool isSuccess = false;
//SQL Connection for DB connection
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
//Write Query Product from DAtabase
String sql = "DELETE FROM tbl_products WHERE id=@id and branchId=@branchId";
//Sql Command to Pass the Value
SqlCommand cmd = new SqlCommand(sql, conn);
//Passing the values using cmd
cmd.Parameters.AddWithValue("@id", p.id);
cmd.Parameters.AddWithValue("@branchId", p.branchId);
//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 productsBLL GetProductsForTransaction(string keywords)
{
//Creating an object for ProductsBLL class and return it;
productsBLL p = new productsBLL();
//Sql connection for Database Connection
SqlConnection conn = new SqlConnection(myconnstrng);
//Creating Datatable to hold Data from Database temporarily
DataTable dt = new DataTable();
try
{
//Sql Quarey to search from Database
string sql = "SELECT name, rate, qty FROM tbl_products WHERE id LIKE '%" + keywords + "%' OR name LIKE '%" + keywords + "%'";
//Creating SqlCommand to Execute the Quary
//SqlCommand cmd = new SqlCommand(sql, conn);
//Getting Data from database
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
//opening Database connection
conn.Open();
//passing value from adapter to DataTable dt
adapter.Fill(dt);
//if we have the value of dt we need to save int dealercustmer BLL
if (dt.Rows.Count > 0)
{
p.name = dt.Rows[0]["name"].ToString();
p.qty = decimal.Parse(dt.Rows[0]["qty"].ToString());
p.rate = decimal.Parse(dt.Rows[0]["rate"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(p);
}
public bool Insert(productsBLL p)
{
//create a boolean variable and set its dafault value to false
bool isSuccess = false;
//connecting to database
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
//writing query to add new category
string sql = "INSERT INTO tbl_products(name, category, Sub_Category, description, rate, qty, added_date, added_by) VALUES(@name, @category,@Sub_Category, @description, @rate, @qty, @added_date, @added_by)";
//creating sql command to pass valuesin our query
SqlCommand cmd = new SqlCommand(sql, conn);
//passing values through parameters
cmd.Parameters.AddWithValue("@name", p.name);
cmd.Parameters.AddWithValue("@category", p.category);
cmd.Parameters.AddWithValue("@Sub_Category", p.Sub_Category);
cmd.Parameters.AddWithValue("@description", p.description);
cmd.Parameters.AddWithValue("@rate", p.rate);
cmd.Parameters.AddWithValue("@qty", p.qty);
cmd.Parameters.AddWithValue("@added_date", p.added_date);
cmd.Parameters.AddWithValue("@added_by", p.added_by);
//open database connection
conn.Open();
//creating the int variable to execute query
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
