private void btnUpdateCust_Click(object sender, EventArgs e)
{
dc.id = int.Parse(txtCustID.Text);
dc.type = cmbtype.Text;
dc.name = txtName.Text;
dc.email = txtEMail.Text;
dc.contact = txtContact.Text;
dc.address = txtAddress.Text;
dc.added_date = DateTime.Now;
string loggedIn = Login.loggedInUser;
UserBLL user = udal.getIdFromUserName(loggedIn);
dc.added_by = user.id;
bool success = dcDal.Update(dc);
if (success == true)
{
Mymessage mymessage = new Mymessage("Data Updated Successfully!");
mymessage.Show();
Clear();
DataTable dt = dcDal.Select();
GDVCustomer.DataSource = dt;
}
else
{
Mymessage mymessage = new Mymessage("Error While updating Data!");
mymessage.Show();
}
}
private void btnAddPrdct_Click(object sender, EventArgs e)
{
if (txtPrdctQty.Text == "")
{
Mymessage mymessage = new Mymessage("Add Product Quantity First!");
mymessage.Show();
return;
}
string productName = txtPrdctName.Text;
decimal rate = decimal.Parse(txtProductRate.Text);
decimal Qty = decimal.Parse(txtPrdctQty.Text);
decimal Total = rate * Qty;
decimal subtotal = decimal.Parse(txtSubTotal.Text);
subtotal = Total + subtotal;
if (productName == "")
{
Mymessage mymessage = new Mymessage("Select a Product First!");
mymessage.Show();
return;
}
else
{
txtSubTotal.Text = subtotal.ToString();
transactionDT.Rows.Add(productName, rate, Qty, Total);
GDVCustomer.DataSource = transactionDT;
txtPrdctSearch.Text = "";
txtPrdctQty.Text = "";
txtPrdctName.Text = "";
txtPrdctInventory.Text = "";
txtProductRate.Text = "";
}
}
private void btnUpdateprdct_Click(object sender, EventArgs e)
{
if (txtPrdctID.Text.Length == 0)
{
Mymessage mymessage = new Mymessage("Product ID is required!");
mymessage.Show();
return;
}
if (txtName.Text.Length == 0)
{
Mymessage mymessage = new Mymessage("Product Name is required!");
mymessage.Show();
return;
}
if (txtDesc.Text.Length == 0)
{
Mymessage mymessage = new Mymessage("Product Description is required!");
mymessage.Show();
return;
}
if (txtProductRate.Text.Length == 0)
{
Mymessage mymessage = new Mymessage("Product Rate is required!");
mymessage.Show();
return;
}
if (cmbCategory.Text.Length == 0)
{
Mymessage mymessage = new Mymessage("Product Category is required!");
mymessage.Show();
return;
}
p.id = int.Parse(txtPrdctID.Text);
p.name = txtName.Text;
p.description = txtDesc.Text;
p.category = cmbCategory.Text;
p.rate = decimal.Parse(txtProductRate.Text);
p.added_date = DateTime.Now;
string loggedInUser = Login.loggedInUser;
UserBLL user = userDAL.getIdFromUserName(loggedInUser);
p.added_by = user.id;
bool success = pdal.Update(p);
if (success == true)
{
Mymessage mymessage = new Mymessage("Sucessfully Updated!");
mymessage.Show();
Clear();
DataTable dt = pdal.Select();
GDVProduct.DataSource = dt;
}
else
{
Mymessage mymessage = new Mymessage("Product updation failed!");
mymessage.Show();
}
}
public bool Delete(UserBLL u)
{
bool issuccess = false;
SqlCeConnection con = new SqlCeConnection(myconnstrng);
try
{
string comm = "DELETE FROM tbl_users where id=@id";
SqlCeCommand cmd = new SqlCeCommand(comm, con);
cmd.Parameters.AddWithValue("@id", u.id);
con.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
issuccess = true;
}
else
{
issuccess = false;
}
}
catch (Exception ex)
{
Mymessage myMessageBox = new Mymessage(ex.Message);
myMessageBox.Show();
}
finally
{
con.Close();
}
return(issuccess);
}
private void btnDeletePrdct_Click(object sender, EventArgs e)
{
if (txtPrdctID.Text.Length == 0)
{
Mymessage mymessage = new Mymessage("Product ID is required for updation!");
mymessage.Show();
return;
}
else
{
p.id = int.Parse(txtPrdctID.Text);
}
bool success = pdal.Delete(p);
if (success == true)
{
Mymessage mymessage = new Mymessage("Product Successfully Deleted");
mymessage.Show();
Clear();
DataTable dt = pdal.Select();
GDVProduct.DataSource = dt;
}
else
{
Mymessage mymessage = new Mymessage("Product Deletion Failed!");
mymessage.Show();
}
}
public bool DecreaseProduct(int ProductID, decimal Qty)
{
//Create Boolean Variable and SEt its Value to false
bool success = false;
SqlCeConnection conn = new SqlCeConnection(myconnstrng);
try
{
//Get the Current product Quantity
decimal currentQty = GetProductQty(ProductID);
//Decrease the Product Quantity based on product sales
decimal NewQty = currentQty - Qty;
//Update Product in Database
success = UpdateQuantity(ProductID, NewQty);
}
catch (Exception ex)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
conn.Close();
}
return(success);
}
private void btnDeleteUser_Click(object sender, EventArgs e)
{
if (txtUserId.Text.Length == 0)
{
Mymessage mymessage = new Mymessage("UserID is required!");
mymessage.Show();
return;
}
u1.id = Convert.ToInt32(txtUserId.Text);
bool success = dal.Delete(u1);//= dal.Delete(u1);
if (success == true)
{
Mymessage mymessage = new Mymessage("Data Succussfully Deleted!");
mymessage.Show();
clearUser();
}
else
{
Mymessage myMessageBox = new Mymessage("There is Error while Deleting data in the DataBase");
myMessageBox.Show();
}
DataTable dt = dal.select();//dal.select();
GDVusers.DataSource = dt;
GDVusers.Refresh();
}
public DataTable DisplayProductsByCategory(string category)
{
//Sql Connection First
SqlCeConnection conn = new SqlCeConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
//SQL Query to Display Product Based on CAtegory
string sql = "SELECT * FROM tbl_products WHERE category='" + category + "'";
SqlCeCommand cmd = new SqlCeCommand(sql, conn);
SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd);
//Open Database Connection Here
conn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
conn.Close();
}
return(dt);
}
public UserBLL getIdFromUserName(string username)
{
UserBLL u = new UserBLL();
SqlCeConnection con = new SqlCeConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
string query = "SELECT id from tbl_users WHERE username='******'";
SqlCeDataAdapter adapter = new SqlCeDataAdapter(query, con);
con.Open();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
u.id = int.Parse(dt.Rows[0]["id"].ToString());
}
}catch (Exception ex)
{
Mymessage mymessage = new Mymessage(ex.Message);
}
finally
{
con.Close();
}
return(u);
}
public bool IncreaseProduct(int ProductID, decimal IncreaseQty)
{
//Create a Boolean Variable and SEt its value to False
bool success = false;
//Create SQL Connection To Connect DAtabase
SqlCeConnection conn = new SqlCeConnection(myconnstrng);
try
{
//Get the Current Qty From dAtabase based on id
decimal currentQty = GetProductQty(ProductID);
//Increase the Current Quantity by the qty purchased from Dealer
decimal NewQty = currentQty + IncreaseQty;
//Update the Prudcty Quantity Now
success = UpdateQuantity(ProductID, NewQty);
}
catch (Exception ex)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
conn.Close();
}
return(success);
}
public DataTable Search(string keyword)
{
//Create a Sql Connection
SqlCeConnection conn = new SqlCeConnection(myconnstrng);
//Creating a Data TAble and returnign its value
DataTable dt = new DataTable();
try
{
//Write the Query to Search Dealer or Customer Based in id, type and name
string sql = "SELECT * FROM tbl_dea_cust WHERE id LIKE '%" + keyword + "%' OR type LIKE '%" + keyword + "%' OR name LIKE '%" + keyword + "%'";
//Sql Command to Execute the Query
SqlCeCommand cmd = new SqlCeCommand(sql, conn);
//Sql Dat Adapeter to hold tthe data from dataase temporarily
SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd);
//Open DAta Base Connection
conn.Open();
//Pass the value from adapter to data table
adapter.Fill(dt);
}
catch (Exception ex)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
conn.Close();
}
return(dt);
}
public DataTable Search(string keywords)
{
//SQL Connection fro DB Connection
SqlCeConnection conn = new SqlCeConnection(myconnstrng);
//Creating DAtaTable to hold value from dAtabase
DataTable dt = new DataTable();
try
{
//SQL query to search product
string sql = "SELECT * FROM tbl_products WHERE id LIKE '%" + keywords + "%' OR name LIKE '%" + keywords + "%' OR category LIKE '%" + keywords + "%'";
//Sql Command to execute Query
SqlCeCommand cmd = new SqlCeCommand(sql, conn);
//SQL Data Adapter to hold the data from database temporarily
SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd);
//Open Database Connection
conn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
conn.Close();
}
return(dt);
}
public DataTable DisplayTransactionByType(string type)
{
//Create SQL Connection
SqlCeConnection conn = new SqlCeConnection(myconnstrng);
//Create a DataTable
DataTable dt = new DataTable();
try
{
//Write SQL Query
string sql = "SELECT * FROM tbl_transactions WHERE type='" + type + "'";
//SQL Command to Execute Query
SqlCeCommand cmd = new SqlCeCommand(sql, conn);
//SQlDataAdapter to hold the data from database
SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd);
//Open DAtabase Connection
conn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
conn.Close();
}
return(dt);
}
static string myconnstrng = Program.CS;//ConfigurationManager.ConnectionStrings["connstrng"].ConnectionString;
#region Insert Transaction Method
public bool Insert_Transaction(TransactionsBLL t, out int transactionID)
{
//Create a boolean value and set its default value to false
bool isSuccess = false;
//Set the out transactionID value to negative 1 i.e. -1
transactionID = -1;
//Create a SqlCeConnection first
SqlCeConnection conn = new SqlCeConnection(myconnstrng);
try
{
//SQL Query to Insert Transactions
string sql = "INSERT INTO tbl_transactions (type, dea_cust_id, grandTotal, transaction_date, tax, discount, added_by) VALUES (@type, @dea_cust_id, @grandTotal, @transaction_date, @tax, @discount, @added_by); SELECT @@IDENTITY;";
//Sql Commandto pass the value in sql query
SqlCeCommand cmd = new SqlCeCommand(sql, conn);
//Passing the value to sql query using cmd
cmd.Parameters.AddWithValue("@type", t.type);
cmd.Parameters.AddWithValue("@dea_cust_id", t.dea_cust_id);
cmd.Parameters.AddWithValue("@grandTotal", t.grandTotal);
cmd.Parameters.AddWithValue("@transaction_date", t.transaction_date);
cmd.Parameters.AddWithValue("@tax", t.tax);
cmd.Parameters.AddWithValue("@discount", t.discount);
cmd.Parameters.AddWithValue("@added_by", t.added_by);
//Open Database Connection
conn.Open();
//Execute the Query
object o = cmd.ExecuteScalar();
//If the query is executed successfully then the value will not be null else it will be null
if (o != null)
{
//Query Executed Successfully
transactionID = int.Parse(o.ToString());
isSuccess = true;
}
else
{
//failed to execute query
isSuccess = false;
}
}
catch (Exception ex)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
//Close the connection
conn.Close();
}
return(isSuccess);
}
public bool Update(ProductsBLL p)
{
//create a boolean variable and set its initial value to false
bool isSuccess = false;
//Create SQL Connection for DAtabase
SqlCeConnection conn = new SqlCeConnection(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
SqlCeCommand cmd = new SqlCeCommand(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)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
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
SqlCeConnection conn = new SqlCeConnection(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
SqlCeCommand cmd = new SqlCeCommand(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)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Insert(DeaCustBLL dc)
{
//Creting SQL Connection First
SqlCeConnection conn = new SqlCeConnection(myconnstrng);
//Create and Boolean value and set its default value to false
bool isSuccess = false;
try
{
//Write SQL Query to Insert Details of Dealer or Customer
string sql = "INSERT INTO tbl_dea_cust (type, name, email, contact, address, added_date, added_by) VALUES (@type, @name, @email, @contact, @address, @added_date, @added_by)";
//SQl Command to Pass the values to query and execute
SqlCeCommand cmd = new SqlCeCommand(sql, conn);
//Passing the calues using Parameters
cmd.Parameters.AddWithValue("@type", dc.type);
cmd.Parameters.AddWithValue("@name", dc.name);
cmd.Parameters.AddWithValue("@email", dc.email);
cmd.Parameters.AddWithValue("@contact", dc.contact);
cmd.Parameters.AddWithValue("@address", dc.address);
cmd.Parameters.AddWithValue("@added_date", dc.added_date);
cmd.Parameters.AddWithValue("@added_by", dc.added_by);
//Open DAtabaseConnection
conn.Open();
//Int variable to check whether 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 0
if (rows > 0)
{
//Query Executed Successfully
isSuccess = true;
}
else
{
//Failed to Execute Query
isSuccess = false;
}
}
catch (Exception ex)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
conn.Close();
}
return(isSuccess);
}
static string myconnstrng = Program.CS;//ConfigurationManager.ConnectionStrings["connstrng"].ConnectionString;
#region Insert Method for Transaction Detail
public bool InsertTransactionDetail(transactionDetailBLL td)
{
//Create a boolean value and set its default value to false
bool isSuccess = false;
//Create a database connection here
SqlCeConnection conn = new SqlCeConnection(myconnstrng);
try
{
//Sql Query to Insert Transaction detais
string sql = "INSERT INTO tbl_transaction_detail (product_id, rate, qty, total, dea_cust_id, added_date, added_by) VALUES (@product_id, @rate, @qty, @total, @dea_cust_id, @added_date, @added_by)";
//Passing the value to the SQL Query
SqlCeCommand cmd = new SqlCeCommand(sql, conn);
//Passing the values using cmd
cmd.Parameters.AddWithValue("@product_id", td.product_id);
cmd.Parameters.AddWithValue("@rate", td.rate);
cmd.Parameters.AddWithValue("@qty", td.qty);
cmd.Parameters.AddWithValue("@total", td.total);
cmd.Parameters.AddWithValue("@dea_cust_id", td.dea_cust_id);
cmd.Parameters.AddWithValue("@added_date", td.added_date);
cmd.Parameters.AddWithValue("@added_by", td.added_by);
//Open Database connection
conn.Open();
//declare the int variable and execute the query
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
//Query Executed Successfully
isSuccess = true;
}
else
{
//FAiled to Execute Query
isSuccess = false;
}
}
catch (Exception ex)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
//Close Database Connection
conn.Close();
}
return(isSuccess);
}
public bool update(CategoryBLL c)
{
//Creating Boolean variable and set its default value to false
bool isSuccess = false;
//Creating SQL Connection
SqlCeConnection conn = new SqlCeConnection(myconnstrng);
try
{
//Query to Update Category
string sql = "UPDATE tbl_categories SET title=@title, description=@description, added_date=@added_date, added_by=@added_by WHERE id=@id";
//SQl Command to Pass the Value on Sql Query
SqlCeCommand cmd = new SqlCeCommand(sql, conn);
//Passing Value using cmd
cmd.Parameters.AddWithValue("@title", c.title);
cmd.Parameters.AddWithValue("@description", c.description);
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 then the value will be grater than zero
if (rows > 0)
{
//Query Executed Successfully
isSuccess = true;
}
else
{
//Failed to Execute Query
isSuccess = false;
}
}
catch (Exception ex)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Update(DeaCustBLL dc)
{
//SQL Connection for Database Connection
SqlCeConnection conn = new SqlCeConnection(myconnstrng);
//Create Boolean variable and set its default value to false
bool isSuccess = false;
try
{
//SQL Query to update data in database
string sql = "UPDATE tbl_dea_cust SET type=@type, name=@name, email=@email, contact=@contact, address=@address, added_date=@added_date, added_by=@added_by WHERE id=@id";
//Create SQL Command to pass the value in sql
SqlCeCommand cmd = new SqlCeCommand(sql, conn);
//Passing the values through parameters
cmd.Parameters.AddWithValue("@type", dc.type);
cmd.Parameters.AddWithValue("@name", dc.name);
cmd.Parameters.AddWithValue("@email", dc.email);
cmd.Parameters.AddWithValue("@contact", dc.contact);
cmd.Parameters.AddWithValue("@address", dc.address);
cmd.Parameters.AddWithValue("@added_date", dc.added_date);
cmd.Parameters.AddWithValue("@added_by", dc.added_by);
cmd.Parameters.AddWithValue("@id", dc.id);
//open the Database Connection
conn.Open();
//Int varialbe to check if the query executed successfully or not
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
//Query Executed Successfully
isSuccess = true;
}
else
{
//Failed to Execute Query
isSuccess = false;
}
}
catch (Exception ex)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Update(UserBLL u)
{
SqlCeConnection con = new SqlCeConnection(myconnstrng);
bool isSuccess = false;
SqlCeConnection conn = new SqlCeConnection(myconnstrng);
try
{
string sql = "UPDATE tbl_users SET first_name=@first_name, last_name=@last_name, email=@email, username=@username, password=@password, contact=@contact, address=@address, gender=@gender, user_type=@user_type, added_date=@added_date, added_by=@added_by WHERE id=@id";
SqlCeCommand cmd = new SqlCeCommand(sql, conn);
cmd.Parameters.AddWithValue("@first_name", u.First_Name);
cmd.Parameters.AddWithValue("@last_name", u.Last_Name);
cmd.Parameters.AddWithValue("@email", u.EMail);
cmd.Parameters.AddWithValue("@username", u.UserName);
cmd.Parameters.AddWithValue("@password", u.Password);
cmd.Parameters.AddWithValue("@contact", u.Contact);
cmd.Parameters.AddWithValue("@address", u.Address);
cmd.Parameters.AddWithValue("@gender", u.Gender);
cmd.Parameters.AddWithValue("@user_type", u.UserType);
cmd.Parameters.AddWithValue("@added_date", u.Added_Date);
cmd.Parameters.AddWithValue("@added_by", u.Added_By);
cmd.Parameters.AddWithValue("@id", u.id);
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
//Query Successfull
isSuccess = true;
}
else
{
//Query Failed
isSuccess = false;
}
}
catch (Exception ex)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
conn.Close();
}
return(isSuccess);
}
private void btnUpdateCat_Click(object sender, EventArgs e)
{
if (txtCatID.Text.Length == 0)
{
Mymessage mymessage = new Mymessage("Category ID is required!");
mymessage.Show();
return;
}
if (txtTitle.Text.Length == 0)
{
Mymessage mymessage = new Mymessage("Category Title is required!");
mymessage.Show();
return;
}
if (txtDesc.Text.Length == 0)
{
Mymessage mymessage = new Mymessage("Category Description is required!");
mymessage.Show();
return;
}
c.id = int.Parse(txtCatID.Text);
c.title = txtTitle.Text;
c.description = txtDesc.Text;
c.added_date = DateTime.Now;
string addedBy = Login.loggedInUser;
UserBLL user = userDAL.getIdFromUserName(addedBy);
c.added_by = user.id;
bool success = dal.update(c);
if (success == true)
{
Mymessage my = new Mymessage("Category Updated SuccessFully!");
my.Show();
Clear();
DataTable dt = dal.Select();
GDVCategory.DataSource = dt;
}
else
{
Mymessage mymessage = new Mymessage("Failed in Updation Category");
mymessage.Show();
}
}
public bool UpdateQuantity(int ProductID, decimal Qty)
{
//Create a Boolean Variable and Set its value to false
bool success = false;
//SQl Connection to Connect Database
SqlCeConnection conn = new SqlCeConnection(myconnstrng);
try
{
//Write the SQL Query to Update Qty
string sql = "UPDATE tbl_products SET qty=@qty WHERE id=@id";
//Create SQL Command to Pass the calue into Queyr
SqlCeCommand cmd = new SqlCeCommand(sql, conn);
//Passing the VAlue trhough parameters
cmd.Parameters.AddWithValue("@qty", Qty);
cmd.Parameters.AddWithValue("@id", ProductID);
//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)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
conn.Close();
}
return(success);
}
private void txtDiscount_OnValueChanged(object sender, EventArgs e)
{
if (txtDiscount.Text == "")
{
Mymessage mymessage = new Mymessage(" First!");
// mymessage.Show();
return;
}
decimal sunTotal = decimal.Parse(txtSubTotal.Text);
decimal Discount = decimal.Parse(txtDiscount.Text);
decimal GradTotal = ((100 - Discount) / 100) * sunTotal;
txtGrandTotal.Text = GradTotal.ToString();
}
public bool Delete(ProductsBLL p)
{
//Create Boolean Variable and Set its default value to false
bool isSuccess = false;
//SQL Connection for DB connection
SqlCeConnection conn = new SqlCeConnection(myconnstrng);
try
{
//Write Query Product from DAtabase
String sql = "DELETE FROM tbl_products WHERE id=@id";
//Sql Command to Pass the Value
SqlCeCommand cmd = new SqlCeCommand(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)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
conn.Close();
}
return(isSuccess);
}
public DeaCustBLL SearchDealerCustomerForTransaction(string keyword)
{
//Create an object for DeaCustBLL class
DeaCustBLL dc = new DeaCustBLL();
//Create a DAtabase Connection
SqlCeConnection conn = new SqlCeConnection(myconnstrng);
//Create a DAta Table to hold the value temporarily
DataTable dt = new DataTable();
try
{
//Write a SQL Query to Search Dealer or Customer Based on Keywords
string sql = "SELECT name, email, contact, address from tbl_dea_cust WHERE id LIKE '%" + keyword + "%' OR name LIKE '%" + keyword + "%'";
//Create a Sql Data Adapter to Execute the Query
SqlCeDataAdapter adapter = new SqlCeDataAdapter(sql, conn);
//Open the DAtabase Connection
conn.Open();
//Transfer the data from SqlData Adapter to DAta Table
adapter.Fill(dt);
//If we have values on dt we need to save it in dealerCustomer BLL
if (dt.Rows.Count > 0)
{
dc.name = dt.Rows[0]["name"].ToString();
dc.email = dt.Rows[0]["email"].ToString();
dc.contact = dt.Rows[0]["contact"].ToString();
dc.address = dt.Rows[0]["address"].ToString();
}
}
catch (Exception ex)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
//Close Database connection
conn.Close();
}
return(dc);
}
public decimal GetProductQty(int ProductID)
{
//SQl Connection First
SqlCeConnection conn = new SqlCeConnection(myconnstrng);
//Create a Decimal Variable and set its default value to 0
decimal qty = 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 qty FROM tbl_products WHERE id = " + ProductID;
//Cerate A SqlCommand
SqlCeCommand cmd = new SqlCeCommand(sql, conn);
//Create a SQL Data Adapter to Execute the query
SqlCeDataAdapter adapter = new SqlCeDataAdapter(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)
{
qty = decimal.Parse(dt.Rows[0]["qty"].ToString());
}
}
catch (Exception ex)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
//Close Database Connection
conn.Close();
}
return(qty);
}
public bool Delete(DeaCustBLL dc)
{
//SQlConnection for Database Connection
SqlCeConnection conn = new SqlCeConnection(myconnstrng);
//Create a Boolean Variable and set its default value to false
bool isSuccess = false;
try
{
//SQL Query to Delete Data from dAtabase
string sql = "DELETE FROM tbl_dea_cust WHERE id=@id";
//SQL command to pass the value
SqlCeCommand cmd = new SqlCeCommand(sql, conn);
//Passing the value
cmd.Parameters.AddWithValue("@id", dc.id);
//Open DB Connection
conn.Open();
//integer variable
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
//Query Executed Successfully
isSuccess = true;
}
else
{
//Failed to Execute Query
isSuccess = false;
}
}
catch (Exception ex)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Insert(UserBLL u)
{
bool isSuccess = false;
SqlCeConnection con = new SqlCeConnection(myconnstrng);
try
{
string comm = "INSERT into tbl_users (first_name,last_name,email,username,password,contact,address,gender,user_type,added_date,added_by) VALUES (@first_name,@last_name,@email,@username,@password,@contact,@address,@gender,@user_type,@added_date,@added_by)";
SqlCeCommand cmd = new SqlCeCommand(comm, con);
cmd.Parameters.AddWithValue("@first_name", u.First_Name);
cmd.Parameters.AddWithValue("@last_name", u.Last_Name);
cmd.Parameters.AddWithValue("@email", u.EMail);
cmd.Parameters.AddWithValue("@username", u.UserName);
cmd.Parameters.AddWithValue("@password", u.Password);
cmd.Parameters.AddWithValue("@contact", u.Contact);
cmd.Parameters.AddWithValue("@address", u.Address);
cmd.Parameters.AddWithValue("@gender", u.Gender);
cmd.Parameters.AddWithValue("@user_type", u.UserType);
cmd.Parameters.AddWithValue("@added_date", u.Added_Date);
cmd.Parameters.AddWithValue("@added_by", u.Added_By);
con.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
Mymessage myMessageBox = new Mymessage(ex.Message);
myMessageBox.Show();
}
finally
{
con.Close();
}
return(isSuccess);
}
public bool Delete(CategoryBLL c)
{
//Create a Boolean variable and set its value to false
bool isSuccess = false;
SqlCeConnection conn = new SqlCeConnection(myconnstrng);
try
{
//SQL Query to Delete from Database
string sql = "DELETE FROM tbl_categories WHERE id=@id";
SqlCeCommand cmd = new SqlCeCommand(sql, conn);
//Passing the value using cmd
cmd.Parameters.AddWithValue("@id", c.id);
//Open SqlConnection
conn.Open();
int rows = cmd.ExecuteNonQuery();
//If the query is executd successfully then the value of rows will be greater than zero else it will be less than 0
if (rows > 0)
{
//Query Executed Successfully
isSuccess = true;
}
else
{
//Faied to Execute Query
isSuccess = false;
}
}
catch (Exception ex)
{
Mymessage mymessage = new Mymessage(ex.Message);
mymessage.Show();
}
finally
{
conn.Close();
}
return(isSuccess);
}
