public DataTable Search(string keyword) { //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); //TO hold the data from database DataTable dt = new DataTable(); try { //SQL Query to search Data from DAtabase string query = "SELECT* FROM tbl_users WHERE id Like '%" + keyword + "%' OR user_name like '%" + keyword + "%' OR user_type like '%" + keyword + "%' OR adress like '%" + keyword + "%' "; //For Executing Command SqlCommand cmd = new SqlCommand(query, conn); //Getting DAta from dAtabase SqlDataAdapter adapter = new SqlDataAdapter(cmd); //Database Connection Open conn.Open(); //Fill Data in our DataTable adapter.Fill(dt); } catch (Exception ex) { //Throw Message if any error occurs MessageBox.Show(ex.Message); } finally { //Closing Connection conn.Close(); } //Return the value in DataTable return(dt); }
public bool DecreaseProduct(int id, decimal quantity) { //Create Boolean Variable and SEt its Value to false bool success = false; //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); try { //Get the Current product Quantity decimal currentQty = GetProductQty(id); //Decrease the Product Quantity based on product sales decimal NewQty = currentQty - quantity; //Update Product in Database success = UpdateQuantity(id, NewQty); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } return(success); }
public DataTable MonthlyAndDailyData(string d1, string d2) { //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); //TO hold the data from database DataTable dt = new DataTable(); //string d1 = DateTime.Now.ToString(); try { //SQL Query to insert Data in DAtabase string query = "SELECT* FROM tbl_productdetails WHERE added_date BETWEEN '" + d2 + "' AND '" + d1 + "' "; //For Executing Command SqlCommand cmd = new SqlCommand(query, conn); //Getting DAta from dAtabase SqlDataAdapter adapter = new SqlDataAdapter(cmd); //Database Connection Open conn.Open(); //Fill Data in our DataTable adapter.Fill(dt); } catch (Exception ex) { //Throw Message if any error occurs MessageBox.Show(ex.Message); } finally { //Closing Connection conn.Close(); } return(dt); }
public bool IncreaseProduct(int id, decimal IncreaseQty) { //Create a Boolean Variable and SEt its value to False bool success = false; //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); try { //Get the Current Qty From dAtabase based on id decimal currentQty = GetProductQty(id); //Increase the Current Quantity by the qty purchased from Dealer decimal NewQty = currentQty + IncreaseQty; //Update the Prudcty Quantity Now success = UpdateQuantity(id, NewQty); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } return(success); }
public DataTable select() { //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); //TO hold the data from database DataTable dt = new DataTable(); try { //SQL Query to Get Data From DAtabase string query = "select* from tbl_users"; //For Executing Command SqlCommand cmd = new SqlCommand(query, conn); //Getting DAta from dAtabase SqlDataAdapter adapter = new SqlDataAdapter(cmd); //Database Connection Open conn.Open(); //Fill Data in our DataTable adapter.Fill(dt); } catch (Exception ex) { //Throw Message if any error occurs MessageBox.Show(ex.Message); } finally { //Closing Connection conn.Close(); } //Return the value in DataTable return(dt); }
public bool update(product_detailsbll u) { //Create a boolean variable and set its value to false and return it bool issucess = false; //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); try { //SQL Query to insert Data in DAtabase string query = "UPDATE tbl_productdetails set product_id=@product_id,product_name=@product_name,price=@price,quantity=@quantity,discount=@discount,total=@total,purchase_price=@purchase_price,inv_no=@inv_no,type=@type,added_by=@added_by,added_date=@added_date,code=@code WHERE id=@id"; //For Executing Command SqlCommand cmd = new SqlCommand(query, conn); //Passing Values to the Variables cmd.Parameters.AddWithValue("@id", u.id); cmd.Parameters.AddWithValue("@product_id", u.product_id); cmd.Parameters.AddWithValue("@product_name", u.product_name); cmd.Parameters.AddWithValue("@price", u.price); cmd.Parameters.AddWithValue("@quantity", u.quantity); cmd.Parameters.AddWithValue("@discount", u.discount); cmd.Parameters.AddWithValue("@total", u.total); cmd.Parameters.AddWithValue("@purchase_price", u.purchase_price); cmd.Parameters.AddWithValue("@inv_no", u.inv_no); cmd.Parameters.AddWithValue("@type", u.type); cmd.Parameters.AddWithValue("@added_by", u.added_by); cmd.Parameters.AddWithValue("@added_date", u.added_date); cmd.Parameters.AddWithValue("@code", u.code); //Database Connection Open conn.Open(); //To execute non query int rows = cmd.ExecuteNonQuery(); //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0 if (rows > 0) { //Query Sucessfull issucess = true; } else { //Query Failed issucess = false; } } catch (Exception ex) { //Throw Message if any error occurs MessageBox.Show(ex.Message); } finally { //Closing Connection conn.Close(); } return(issucess); }
public bool update(invoicebll u) { //Create a boolean variable and set its value to false and return it bool issucess = false; //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); try { //SQL Query to insert Data in DAtabase string query = "UPDATE tbl_invoice set inv_no=@inv_no, customer_name=@customer_name,total_payable=@total_payable,paid_amount=@paid_amount,discount=@discount,due_amount=@due_amount,change_amount=@change_amount,added_by=@added_by,sales_date=@sales_date WHERE id=@id"; //For Executing Command SqlCommand cmd = new SqlCommand(query, conn); //Passing Values to the Variables cmd.Parameters.AddWithValue("@id", u.id); cmd.Parameters.AddWithValue("@inv_no", u.inv_no); cmd.Parameters.AddWithValue("@customer_name", u.customer_name); cmd.Parameters.AddWithValue("@total_payable", u.total_payable); cmd.Parameters.AddWithValue("@paid_amount", u.paid_amount); cmd.Parameters.AddWithValue("@discount", u.discount); cmd.Parameters.AddWithValue("@due_amount", u.due_amount); cmd.Parameters.AddWithValue("@change_amount", u.change_amount); cmd.Parameters.AddWithValue("@added_by", u.added_by); cmd.Parameters.AddWithValue("@sales_date", u.sales_date); //Database Connection Open conn.Open(); //To execute non query int row = cmd.ExecuteNonQuery(); //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0 if (row > 0) { //Query Sucessfull issucess = true; } else { //Query Failed issucess = false; } } catch (Exception ex) { //Throw Message if any error occurs MessageBox.Show(ex.Message); } finally { //Closing Connection conn.Close(); } return(issucess); }
public bool insert(Manage_Productsbll u) { //Create a boolean variable and set its value to false and return it bool issucess = false; //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); try { //SQL Query to insert Data in DAtabase string query = "Insert into tbl_stock(product_name,colour_code,supplier,catagory,purchase_price,retail_price,type,quantity,added_by,added_date)Values(@product_name,@colour_code,@supplier,@catagory,@purchase_price,@retail_price,@type,@quantity,@added_by,@added_date)"; //For Executing Command SqlCommand cmd = new SqlCommand(query, conn); //Passing Values to the Variables cmd.Parameters.AddWithValue("@product_name", u.product_name); cmd.Parameters.AddWithValue("@colour_code", u.colour_code); cmd.Parameters.AddWithValue("@supplier", u.supplier); cmd.Parameters.AddWithValue("@catagory", u.catagory); cmd.Parameters.AddWithValue("@purchase_price", u.purchase_price); cmd.Parameters.AddWithValue("@retail_price", u.retail_price); cmd.Parameters.AddWithValue("@type", u.type); cmd.Parameters.AddWithValue("@quantity", u.quantity); cmd.Parameters.AddWithValue("@added_by", u.added_by); cmd.Parameters.AddWithValue("@added_date", u.added_date); //Database Connection Open conn.Open(); //To execute non query int row = cmd.ExecuteNonQuery(); //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0 if (row > 0) { //Query Sucessfull issucess = true; } else { //Query Failed issucess = false; } } catch (Exception ex) { //Throw Message if any error occurs MessageBox.Show(ex.Message); } finally { //Closing Connection conn.Close(); } return(issucess); }
public bool insert(usersbll u) { //Create a boolean variable and set its value to false and return it bool issucess = false; //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); try { //SQL Query to insert Data in DAtabase string query = "Insert into tbl_users(user_name,user_type,password,email,cnic,adress,phone_no,added_by,added_date)Values(@user_name,@user_type,@password,@email,@cnic,@adress,@phone_no,@added_by,@added_date)"; //For Executing Command SqlCommand cmd = new SqlCommand(query, conn); //Passing Values to the Variables cmd.Parameters.AddWithValue("@user_name", u.user_name); cmd.Parameters.AddWithValue("@user_type", u.user_type); cmd.Parameters.AddWithValue("@password", u.password); cmd.Parameters.AddWithValue("@email", u.email); cmd.Parameters.AddWithValue("@cnic", u.cnic); cmd.Parameters.AddWithValue("@adress", u.adress); cmd.Parameters.AddWithValue("@phone_no", u.phone_no); cmd.Parameters.AddWithValue("@added_by", u.added_by); cmd.Parameters.AddWithValue("@added_date", u.added_date); //Database Connection Open conn.Open(); //To execute non query int row = cmd.ExecuteNonQuery(); //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0 if (row > 0) { //Query Sucessfull issucess = true; } else { //Query Failed issucess = false; } } catch (Exception ex) { //Throw Message if any error occurs MessageBox.Show(ex.Message); } finally { //Closing Connection conn.Close(); } return(issucess); }
public bool exist(Manage_Productsbll u) { //Create a boolean variable and set its value to false and return it bool issuccess = false; //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); try { //SQL Query to selecte Data from DAtabase string query = "select * from tbl_stock where product_name=@product_name AND supplier=@supplier AND catagory=@catagory AND colour_code=@colour_code AND type=@type"; //For Executing Command SqlCommand cmd = new SqlCommand(query, conn); //Passing Values to the Variables cmd.Parameters.AddWithValue("@type", u.type); cmd.Parameters.AddWithValue("@product_name", u.product_name); cmd.Parameters.AddWithValue("@supplier", u.supplier); cmd.Parameters.AddWithValue("@catagory", u.catagory); cmd.Parameters.AddWithValue("@colour_code", u.colour_code); //Getting DAta from dAtabase SqlDataAdapter adapter = new SqlDataAdapter(cmd); //TO hold the data from database DataTable dt = new DataTable(); //Database Connection Open conn.Open(); //Fill Data in our DataTable adapter.Fill(dt); //Checking The rows in DataTable if (dt.Rows.Count > 0) { //Data Exist issuccess = true; } else { //Data not exist issuccess = false; } } catch (Exception ex) { //Throw Message if any error occurs MessageBox.Show(ex.Message); } finally { //Closing Connection conn.Close(); } return(issuccess); }
public bool exist(usersbll u) { //Create a boolean variable and set its value to false and return it bool issuccess = false; //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); try { //SQL Query to selecte Data from DAtabase string query = "select * from tbl_users where user_name=@user_name AND cnic=@cnic AND email=@email AND phone_no=@phone_no "; //For Executing Command SqlCommand cmd = new SqlCommand(query, conn); //Passing Values to the Variables cmd.Parameters.AddWithValue("@user_name", u.user_name); cmd.Parameters.AddWithValue("@cnic", u.cnic); cmd.Parameters.AddWithValue("@email", u.email); cmd.Parameters.AddWithValue("@phone_no", u.phone_no); //Getting DAta from dAtabase SqlDataAdapter adapter = new SqlDataAdapter(cmd); //TO hold the data from database DataTable dt = new DataTable(); //Database Connection Open conn.Open(); //Fill Data in our DataTable adapter.Fill(dt); //Checking The rows in DataTable if (dt.Rows.Count > 0) { //Data Exist issuccess = true; } else { //Data not exist issuccess = false; } } catch (Exception ex) { //Throw Message if any error occurs MessageBox.Show(ex.Message); } finally { //Closing Connection conn.Close(); } return(issuccess); }
public bool update(suppliersbll u) { //Create a boolean variable and set its value to false and return it bool issucess = false; //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); try { //SQL Query to update Data in DAtabase string query = "UPDATE tbl_suppliers set user_name=@user_name,email=@email,company=@company,adress=@adress,phone_no=@phone_no WHERE id=@id"; //For Executing Command SqlCommand cmd = new SqlCommand(query, conn); //Passing Values to the Variables cmd.Parameters.AddWithValue("@id", u.id); cmd.Parameters.AddWithValue("@user_name", u.user_name); cmd.Parameters.AddWithValue("@email", u.email); cmd.Parameters.AddWithValue("@company", u.company); cmd.Parameters.AddWithValue("@adress", u.adress); cmd.Parameters.AddWithValue("@phone_no", u.phone_no); //Database Connection Open conn.Open(); //To execute non query int row = cmd.ExecuteNonQuery(); //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0 if (row > 0) { //Query Sucessfull issucess = true; } else { //Query Failed issucess = false; } } catch (Exception ex) { //Throw Message if any error occurs MessageBox.Show(ex.Message); } finally { //Closing Connection conn.Close(); } return(issucess); }
public bool login(loginbll u) { //Create a boolean variable and set its value to false and return it bool issuccess = false; //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); try { //SQL Query to check login string query = "select * from tbl_users where user_name=@user_name AND password=@password AND user_type=@user_type"; //Creating SQL Command to pass value SqlCommand cmd = new SqlCommand(query, conn); //Passing Values to the Variables cmd.Parameters.AddWithValue("@user_name", u.user_name); cmd.Parameters.AddWithValue("@password", u.password); cmd.Parameters.AddWithValue("@user_type", u.user_type); //Getting DAta from dAtabase SqlDataAdapter adapter = new SqlDataAdapter(cmd); //TO hold the data from database DataTable dt = new DataTable(); //Database Connection Open conn.Open(); //Fill Data in our DataTable adapter.Fill(dt); //Checking The rows in DataTable if (dt.Rows.Count > 0) { //Login Sucessful issuccess = true; } else { //Login Failed issuccess = false; } } catch (Exception ex) { //Throw Message if any error occurs MessageBox.Show(ex.Message); } finally { //Closing Connection conn.Close(); } return(issuccess); }
public bool UpdateQuantity(int id, decimal quantity) { //Create a Boolean Variable and Set its value to false bool success = false; //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); try { //Write the SQL Query to Update Qty string sql = "UPDATE tbl_productdetails SET quantity=@quantity WHERE id=@id"; //Create SQL Command to Pass the calue into Queyr SqlCommand cmd = new SqlCommand(sql, conn); //Passing the VAlue trhough parameters cmd.Parameters.AddWithValue("@quantity", quantity); cmd.Parameters.AddWithValue("@id", id); //Open Database Connection conn.Open(); //Create Int Variable and Check whether the query is executed Successfully or not int rows = cmd.ExecuteNonQuery(); //Lets check if the query is executed Successfully or not if (rows > 0) { //Query Executed Successfully success = true; } else { //Failed to Execute Query success = false; } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } return(success); }
public decimal GetProductQty(int id) { //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); //Create a Decimal Variable and set its default value to 0 decimal quantity = 0; //Create Data Table to save the data from database temporarily DataTable dt = new DataTable(); try { //Write WQL Query to Get Quantity from Database string sql = "SELECT quantity FROM tbl_productdetails WHERE id = " + id; //Cerate A SqlCommand SqlCommand cmd = new SqlCommand(sql, conn); //Create a SQL Data Adapter to Execute the query SqlDataAdapter adapter = new SqlDataAdapter(cmd); //open DAtabase Connection conn.Open(); //PAss the calue from Data Adapter to DataTable adapter.Fill(dt); //Lets check if the datatable has value or not if (dt.Rows.Count > 0) { quantity = decimal.Parse(dt.Rows[0]["quantity"].ToString()); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { //Close Database Connection conn.Close(); } return(quantity); }
public bool delete(usersbll u) { //Create a boolean variable and set its value to false and return it bool issucess = false; //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); try { //SQL Query to delete Data in DAtabase string query = "DELETE FROM tbl_users WHERE id=@id"; //For Executing Command SqlCommand cmd = new SqlCommand(query, conn); //Passing Values to the Variables cmd.Parameters.AddWithValue("@id", u.id); //Database Connection Open conn.Open(); //To execute non query int row = cmd.ExecuteNonQuery(); //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0 if (row > 0) { //Query Sucessfull issucess = true; } else { //Query Failed issucess = false; } } catch (Exception ex) { //Throw Message if any error occurs MessageBox.Show(ex.Message); } finally { //Closing Connection conn.Close(); } return(issucess); }
public usersbll GetIDFromUsername(string username) { //Connecting user BLL for getting id usersbll u = new usersbll(); //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); //TO hold the data from database DataTable dt = new DataTable(); try { //SQL Query to get Data from DAtabase string sql = "SELECT id FROM tbl_users WHERE user_name='" + username + "'"; //Getting DAta from dAtabase SqlDataAdapter adapter = new SqlDataAdapter(sql, conn); //Database Connection Open conn.Open(); //Fill Data in our DataTable adapter.Fill(dt); //Checking The rows in DataTable if (dt.Rows.Count > 0) { //Getting id from DataTable u.id = int.Parse(dt.Rows[0]["id"].ToString()); } } catch (Exception ex) { //Throw Message if any error occurs MessageBox.Show(ex.Message); } finally { //Closing Connection conn.Close(); } return(u); }
public Manage_Productsbll GetProductpriceFromId(string id) { //First Create an Object of BLL and REturn it Manage_Productsbll p = new Manage_Productsbll(); //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); //Data TAble to Holdthe data temporarily DataTable dt = new DataTable(); try { //SQL Query to Get id based on Name string sql = "SELECT purchase_price FROM tbl_stock WHERE id='" + id + "'"; //Create the SQL Data Adapter to Execute the Query SqlDataAdapter adapter = new SqlDataAdapter(sql, conn); conn.Open(); //Passing the VAlue from Adapter to DAtatable adapter.Fill(dt); if (dt.Rows.Count > 0) { //Pass the value from dt to variable p.purchase_price = dt.Rows[0]["purchase_price"].ToString(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } return(p); }
public Manage_Productsbll GetProductIDFromName(string ProductName, string type, string code) { //First Create an Object of BLL and REturn it Manage_Productsbll p = new Manage_Productsbll(); //MEthod to connect Database connclass c = new connclass(); SqlConnection conn = new SqlConnection(c.connection); //Data TAble to Holdthe data temporarily DataTable dt = new DataTable(); try { //SQL Query to Get id based on Name string sql = "SELECT id FROM tbl_stock WHERE product_name='" + ProductName + "' AND type = '" + type + "' AND colour_code = '" + code + "'"; //Create the SQL Data Adapter to Execute the Query SqlDataAdapter adapter = new SqlDataAdapter(sql, conn); 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(p); }