public bool InsertCustomer(helperDB cust)
{
bool isSuccess = false;
SqlConnection con = new SqlConnection(myconnstring);
try
{
String sql = "INSERT INTO tbCustomer VALUES (@FName,@LName,@Cust_Contact)";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@FName", cust.fname);
cmd.Parameters.AddWithValue("@LName", cust.lname);
cmd.Parameters.AddWithValue("@Cust_Contact", cust.contact);
con.Open();
if (cmd.ExecuteNonQuery() > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
con.Close();
}
return(isSuccess);
}
public bool UpdateProductOrder(helperDB pro)
{
bool isSuccess = false;
SqlConnection con = new SqlConnection(myconnstring);
try
{
String sql = "spInsertUpdateDeleteProduct";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@P_ID", pro.id);
cmd.Parameters.AddWithValue("@P_Dec", pro.fname); //fname used as description
cmd.Parameters.AddWithValue("@P_price", pro.price);
cmd.Parameters.AddWithValue("@P_quantity", pro.quantity);
cmd.Parameters.AddWithValue("@StatementType", "Update");
con.Open();
if (cmd.ExecuteNonQuery() > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception error)
{
MessageBox.Show(error.Message);
}
finally
{
con.Close();
}
return(isSuccess);
}
// helper methods to entertain multitable queries
public bool InsertUsername(helperDB emp)
{
SqlConnection con = new SqlConnection(myconnstring);
int userid = 0;
try
{
con.Open();
// gets the ID of the employee that is inserted latest
SqlCommand maxID = new SqlCommand("Select MAX(Emp_ID) from tbEmployee", con);
userid = (int)maxID.ExecuteScalar();
// insert in the login table
String sql1 = "spInsertUpdateDeleteLogins";
SqlCommand cmd1 = new SqlCommand(sql1, con);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@emp_id", userid);
cmd1.Parameters.AddWithValue("@username", emp.username);
cmd1.Parameters.AddWithValue("@password", emp.fname + emp.contact);
cmd1.Parameters.AddWithValue("@StatementType", "Insert");
///using userid as row checker
userid = cmd1.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
con.Close();
}
return((userid > 0) ? true : false);
}
public int selectSupplierID(helperDB delivery)
{
SqlConnection con = new SqlConnection(myconnstring);
try
{
SqlCommand cmd = new SqlCommand("Select Supp_ID from tbDelivery WHERE Dev_ID = @devID", con);
cmd.Parameters.AddWithValue("@devID", delivery.devID);
con.Open();
if (cmd.ExecuteScalar() != null)
{
return((int)cmd.ExecuteScalar());
}
else
{
return(0);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
con.Close();
}
return(0);
;
}
// devID as custID
// id as P_ID
// suppID as empID
public bool InsertOrderDec(helperDB order)
{
bool isSuccess = false;
SqlConnection con = new SqlConnection(myconnstring);
try
{
String sql = "INSERT INTO tbOrderDec VALUES (@O_ID,@P_ID,@Quantity)";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@O_ID", order.devID);
cmd.Parameters.AddWithValue("@P_ID", order.id);
cmd.Parameters.AddWithValue("@Quantity", order.quantity);
con.Open();
if (cmd.ExecuteNonQuery() > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
con.Close();
}
return(isSuccess);
}
public int authenticate(helperDB person)
{
SqlConnection con = new SqlConnection(myconnstring);
int found = 0;
try
{
String sql = "SELECT dbo.fn_Login(@user, @password)";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@user", person.username);
cmd.Parameters.AddWithValue("@password", person.password);
con.Open();
cmd.CommandType = CommandType.Text;
if (!(cmd.ExecuteScalar() == null))
{
found = (int)cmd.ExecuteScalar();
}
else
{
found = 0;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
con.Close();
}
return(found);
}
public bool deleteProduct(helperDB pro)
{
bool isSuccess = false;
SqlConnection con = new SqlConnection(myconnstring);
try
{
String sql = "SELECT COUNT([P_ID]) FROM tbOrderDec WHERE([P_ID]) = @P_ID";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@P_ID", pro.id);
con.Open();
int proExist = (int)cmd.ExecuteScalar();
if (proExist == 0)
{
try
{
//delete from emloyee table
sql = "spInsertUpdateDeleteProduct";
cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@P_id", pro.id);
cmd.Parameters.AddWithValue("@StatementType", "Delete");
int row = cmd.ExecuteNonQuery();
// if query executed then value of row is greater than 0
if (row > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
con.Close();
}
return(isSuccess);
}
public bool deleteSupplier(helperDB supplier)
{
bool isSuccess = false;
SqlConnection con = new SqlConnection(myconnstring);
try
{
String sql = "SELECT COUNT([Supp_ID]) FROM tbDelivery WHERE([Supp_ID]) = @Supp_ID";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@Supp_ID", supplier.id);
con.Open();
int SupplerExist = (int)cmd.ExecuteScalar();
if (SupplerExist == 0)
{
try
{
String sql1 = "spInsertUpdateDeleteSupplier";
SqlCommand cmd1 = new SqlCommand(sql1, con);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@supp_id", supplier.id);
cmd1.Parameters.AddWithValue("@StatementType", "Delete");
int row = cmd1.ExecuteNonQuery();
// if query executed then value of row is greater than 0
if (row > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
con.Close();
}
return(isSuccess);
}
private void logIn_button_Click(object sender, EventArgs e)
{
helperDB log = new helperDB();
log.username = UserName_loginForm.Text.Trim(); // removes the spaces at start and end
log.password = Password_loginForm.Text.Trim();
loginID = log.authenticate(log); // calling the sql function
if (loginID != 0)
{
if (adminRadiobtn.Checked)
{
this.Hide(); // hides the login form
frmMain f1 = new frmMain();
f1.Show();
f1.controlAccess(false);
foreach (Control c in panel1.Controls) // clear the username $ password textbox
{
if (c is TextBox)
{
c.Text = "";
}
}
}
else if (userRadioBtn.Checked)
{
this.Hide();
frmMain f1 = new frmMain();
f1.Show();
f1.controlAccess(true);
foreach (Control c in panel1.Controls) // clear the username $ password textbox
{
if (c is TextBox)
{
c.Text = "";
}
}
}
else
{
MessageBox.Show(panel1, "Please! check whether you are USER or ADMIN !", "Incomplete Input", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
else
{
MessageBox.Show(panel1, "Invalid Username or Password !", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public bool UpdateEmployee(helperDB emp)
{
bool isSuccess = false;
SqlConnection con = new SqlConnection(myconnstring);
try
{
String sql = "spInsertUpdateDeleteEmployee";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@emp_ID", emp.id);
cmd.Parameters.AddWithValue("@fname", emp.fname);
cmd.Parameters.AddWithValue("@lname", emp.lname);
cmd.Parameters.AddWithValue("@emp_contact", emp.contact);
cmd.Parameters.AddWithValue("@emp_designation", emp.designation);
cmd.Parameters.AddWithValue("@StatementType", "Update");
con.Open();
int row = cmd.ExecuteNonQuery();
//update username
sql = "spInsertUpdateDeleteLogins";
cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@emp_id", emp.id);
cmd.Parameters.AddWithValue("@username", emp.username);
int row1 = cmd.ExecuteNonQuery();
// if query executed then value of row is greater than 0
if (row > 0 && row1 > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception e)
{
}
finally
{
con.Close();
}
return(isSuccess);
}
public bool UpdateProduct(helperDB pro)
{
bool isSuccess = false;
SqlConnection con = new SqlConnection(myconnstring);
try
{
String sql = "spInsertUpdateDeleteProduct";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@P_ID", pro.id);
cmd.Parameters.AddWithValue("@P_Dec", pro.fname); //fname used as description
cmd.Parameters.AddWithValue("@P_price", pro.price);
cmd.Parameters.AddWithValue("@P_quantity", pro.quantity);
cmd.Parameters.AddWithValue("@StatementType", "Update");
con.Open();
int row1 = cmd.ExecuteNonQuery();
//update username
sql = "UPDATE tbDeliveryDec SET pro_quantity = @p_quantity WHERE P_ID = @P_ID";
cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@P_ID", pro.id);
cmd.Parameters.AddWithValue("@p_quantity", pro.quantity);
int row = cmd.ExecuteNonQuery();
// if query executed then value of row is greater than 0
if (row > 0 && row1 > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
con.Close();
}
return(isSuccess);
}
public bool InsertEmployee(helperDB emp)
{
bool isSuccess = false;
SqlConnection con = new SqlConnection(myconnstring);
try
{
String sql = "spInsertUpdateDeleteEmployee";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@fname", emp.fname);
cmd.Parameters.AddWithValue("@lname", emp.lname);
cmd.Parameters.AddWithValue("@emp_contact", emp.contact);
cmd.Parameters.AddWithValue("@emp_designation", emp.designation);
cmd.Parameters.AddWithValue("@StatementType", "Insert");
con.Open();
int row = cmd.ExecuteNonQuery();
// if query executed then value of row is greater than 0
if (row > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
con.Close();
}
return((InsertUsername(emp) && isSuccess) ? true : false);
}
// devID as custID
// id as O_ID
// suppID as empID
public bool InsertOrder(helperDB order)
{
bool isSuccess = false;
SqlConnection con = new SqlConnection(myconnstring);
try
{
frmMain timepick = new frmMain();
String sql = "INSERT INTO tbOrder VALUES (@Cust_ID,@Emp_ID,@Amount,@Date,@Time)";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@Cust_ID", order.devID);
cmd.Parameters.AddWithValue("@Emp_ID", order.suppID);
cmd.Parameters.AddWithValue("@Amount", order.price);
cmd.Parameters.AddWithValue("@Date", SqlDbType.Date).Value = timepick.dateTimePicker3.Value.Date;
cmd.Parameters.AddWithValue("@TIME", SqlDbType.Time).Value = DateTime.Now;
con.Open();
if (cmd.ExecuteNonQuery() > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
con.Close();
}
return(isSuccess);
}
public bool UpdateSupplier(helperDB supplier)
{
bool isSuccess = false;
SqlConnection con = new SqlConnection(myconnstring);
try
{
String sql = "spInsertUpdateDeleteSupplier";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Supp_ID", supplier.id);
cmd.Parameters.AddWithValue("@fname", supplier.fname);
cmd.Parameters.AddWithValue("@lname", supplier.lname);
cmd.Parameters.AddWithValue("@supp_contact", supplier.contact);
cmd.Parameters.AddWithValue("@StatementType", "Update");
con.Open();
int row = cmd.ExecuteNonQuery();
// if query executed then value of row is greater than 0
if (row > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
con.Close();
}
return(isSuccess);
}
public bool InsertProduct(helperDB product)
{
bool isSuccess = false;
SqlConnection con = new SqlConnection(myconnstring);
try
{
String sql = "SELECT Dev_ID from tbDelivery WHERE Dev_ID = @devID";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@devID", product.devID); // fname used as Product description
con.Open();
int row = 0;
if (cmd.ExecuteScalar() != null)
{
row = (int)cmd.ExecuteScalar();
}
if (row == 0)
{
frmMain dateTimeObject = new frmMain();
sql = "Insert into tbdelivery VALUES(@dev_ID,@Supp_ID,@Dev_Date,@Dev_TIME)";
cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@dev_ID", product.devID);
cmd.Parameters.AddWithValue("@Supp_ID", product.suppID);
cmd.Parameters.AddWithValue("@Dev_Date", SqlDbType.Date).Value = dateTimeObject.date_addDetails_inventory_popup_txt.Value.Date;
cmd.Parameters.AddWithValue("@Dev_TIME", SqlDbType.Time).Value = DateTime.Now;
cmd.ExecuteNonQuery();
}
sql = "spInsertUpdateDeleteProduct";
cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@P_dec", product.fname); // fname used as Product description
cmd.Parameters.AddWithValue("@P_price", product.price);
cmd.Parameters.AddWithValue("@P_quantity", product.quantity);
cmd.Parameters.AddWithValue("@StatementType", "Insert");
int row2 = cmd.ExecuteNonQuery();
sql = "Select MAX(P_ID) from tbProduct";
cmd = new SqlCommand(sql, con);
int pid = (int)cmd.ExecuteScalar();
sql = "spInsertUpdateDeleteDeliveryDec";
cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@dev_id", product.devID);
cmd.Parameters.AddWithValue("@p_id", pid);
cmd.Parameters.AddWithValue("@quantity", product.quantity);
cmd.Parameters.AddWithValue("@StatementType", "Insert");
int row1 = cmd.ExecuteNonQuery();
if (row1 > 0 && row2 > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
con.Close();
}
return(isSuccess);
}
