private void showDataGridView() { MySqlConnection con = DBConnection.getConn(); try { con.Open(); string query = "Select FullName,FirstName,EmpID,NIC,Address,Shop,Type,Status,ContactNo from employee"; DataTable table = new DataTable(); MySqlDataAdapter adapter = new MySqlDataAdapter(query, connectionString); adapter.Fill(table); dataGridView1.DataSource = table; dataGridView1.ReadOnly = true; dataGridView1.Rows[0].Selected = false; //dataGridView1.Focus(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { con.Close(); DBConnection.returnConn(con); con = null; } }
public static String getQuestion() { String question = ""; string query = "Select SecurityQuestion from employee where EmpID = '" + User.LogObj.getUserName() + "'"; MySqlConnection con = DBConnection.getConn(); try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { question = reader.GetString(0); } } catch (Exception ex) { } finally { con.Close(); DBConnection.returnConn(con); con = null; } return(question); }
private void saveBtn_Click(object sender, EventArgs e) { MySqlConnection con = DBConnection.getConn(); try { string query = "update employee set Address ='" + addressTxt.Text + "',ContactNo='" + contactNoTxt.Text + "',Shop='" + shopCBox.Text + "',Type='" + typeCBox.Text + "',Status='" + statusCbx.Text + "' where EmpID = '" + getEmpID() + "'"; con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.ExecuteNonQuery(); MessageBox.Show("Update Success!"); groupBox1.Visible = false; showDataGridView(); selectRaw(getEmpID()); //Select updated row just after saving } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { con.Close(); DBConnection.returnConn(con); con = null; } }
private static bool isPasswordCorrect(String password) { bool correct = false; string query = "Select Password from employee where EmpID ='" + User.LogObj.getUserName() + "'"; MySqlConnection con = DBConnection.getConn(); try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { if (reader.GetString(0) == password) { correct = true; break; } } return(correct); } catch (Exception ex) { ChangePasswordInterface.showMessage("Catch exception"); return(correct); } finally { con.Close(); DBConnection.returnConn(con); con = null; } }
public static void changePassword(String oldPassword, String newPassword, String question, String answer) { if (isPasswordCorrect(oldPassword)) { string query = "update employee set password ='******', SecurityQuestion = '" + question + "', Answer = '" + answer + "' where EmpID = '" + User.LogObj.getUserName() + "'"; MySqlConnection con = DBConnection.getConn(); try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.ExecuteNonQuery(); User.ChangePassObj.clearText(); ChangePasswordInterface.showMessage("Password Changed successfully!"); } catch (Exception ex) { ChangePasswordInterface.showMessage("Error in doing, Please try again later" + ex.Message); } finally { con.Close(); DBConnection.returnConn(con); con = null; } } else { ChangePasswordInterface.showMessage("Current Password wrong!"); } }
public void addUserToDatabase(User user) { string query = "insert into employee (EmpID,FullName,FirstName,NIC,Address,ContactNo,Shop,Type,Status,Password) values ('" + user.empID + "','" + user.fullName + "','" + user.firstName + "','" + user.nic + "','" + user.address + "','" + user.contactNo + "','" + user.shop + "','" + user.type + "','" + user.status + "','" + user.password + "')"; MySqlConnection con = DBConnection.getConn(); try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.ExecuteNonQuery(); con.Close(); //addemp.clearAllText(); //addemp.showMessage("Successfully added the new Employee!"); User.addEmpObj.clearAllText(); User.addEmpObj.showMessage("Successfully added the new Employee!"); } catch (Exception ex) { User.addEmpObj.showMessage(ex.Message); } finally { con.Close(); DBConnection.returnConn(con); con = null; } }
private void textBox1_TextChanged(object sender, EventArgs e) { MySqlConnection con = DBConnection.getConn(); try { if (nameTxt.Text != "") { nicTxt.Enabled = false; employeeIdTxt.Enabled = false; /*con.Open(); * String query = "Select FirstName,LastName,EmpID,NIC,Address,Shop,Type,ContactNo from employee where FirstName like '%" + nameTxt.Text + "%'"; * DataTable table = new DataTable(); * MySqlDataAdapter ada; * //MySqlCommand cmd; * for (int i = 0; i < 2; i++) * { * //cmd = new MySqlCommand(query, con); * ada = new MySqlDataAdapter(query, con); * //ada.SelectCommand = cmd; * ada.Fill(table); * //ada.Update(table); * query = "Select FirstName,LastName,EmpID,NIC,Address,Shop,Type,ContactNo from employee where LastName like '%" + nameTxt.Text + "%'"; * } * //BindingSource bsource = new BindingSource(); * //bsource.DataSource = table; * dataGridView1.DataSource = table;*/ dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect; (dataGridView1.DataSource as DataTable).DefaultView.RowFilter = "FirstName LIKE '" + nameTxt.Text + "%'"; //(dataGridView1.DataSource as DataTable).DefaultView.RowFilter = "LastName LIKE '" + nameTxt.Text + "%'"; //(dataGridView1.DataSource as DataTable).DefaultView.RowFilter = string.Format(); /*foreach (DataGridViewRow row in dataGridView1.Rows) * { * if (row.Cells[0].Value.ToString().StartsWith(nameTxt.Text) || row.Cells[1].Value.ToString().StartsWith(nameTxt.Text)) * { * * } * }*/ } else { showDataGridView(); nicTxt.Enabled = true; employeeIdTxt.Enabled = true; } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { con.Close(); DBConnection.returnConn(con); con = null; } }
private void nicTxt_TextChanged(object sender, EventArgs e) { MySqlConnection con = DBConnection.getConn(); try { if (nicTxt.Text != "") { nameTxt.Enabled = false; employeeIdTxt.Enabled = false; /*con.Open(); * String query = "Select FirstName,LastName,EmpID,NIC,Address,Shop,Type,ContactNo from employee where NIC like '%" + nicTxt.Text + "%'"; * //MySqlCommand cmd = new MySqlCommand(query, con); * MySqlDataAdapter ada = new MySqlDataAdapter(query, con); * //ada.SelectCommand = cmd; * DataTable table = new DataTable(); * ada.Fill(table); * //BindingSource bsource = new BindingSource(); * //bsource.DataSource = table; * dataGridView1.DataSource = table; * //ada.Update(table);*/ dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect; (dataGridView1.DataSource as DataTable).DefaultView.RowFilter = "NIC LIKE '" + nicTxt.Text + "%'"; } else { showDataGridView(); nameTxt.Enabled = true; employeeIdTxt.Enabled = true; } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { con.Close(); DBConnection.returnConn(con); con = null; } }
public void execute(string username, int shop, DataGridView dataGridView3) { DateTime currentDateandTime = DateTime.Now; string date = ""; string time = ""; date = currentDateandTime.ToShortDateString(); time = currentDateandTime.ToShortTimeString(); MySqlConnection connection = DBConnection.getConn(); connection.Open(); string insertQuery = "INSERT INTO pharmacy_management.return_table VALUES (@invoice_number,@user,@shop,@details,@date,@time)"; MySqlCommand command = new MySqlCommand(insertQuery, connection); command.Parameters.AddWithValue("@invoice_number", invoice_number); command.Parameters.AddWithValue("@user", username); command.Parameters.AddWithValue("@shop", shop); command.Parameters.AddWithValue("@details", returnDetails); command.Parameters.AddWithValue("@date", date); command.Parameters.AddWithValue("@time", time); command.ExecuteNonQuery(); connection.Close(); foreach (DataGridViewRow row in dataGridView3.Rows) { string quantity = row.Cells[3].Value.ToString(); string currentstock = ""; MySqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "SELECT stock_shop_" + shop + " FROM pharmacy_management.product WHERE product_code = '" + Convert.ToString(row.Cells[1].Value) + "'"; try { connection.Open(); MySqlDataReader myReader = cmd.ExecuteReader(); while (myReader.Read()) { currentstock = Convert.ToInt32(myReader.GetString("stock_shop_" + shop)).ToString(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { connection.Close(); } string newstock = Convert.ToString(Convert.ToInt32(currentstock) + Convert.ToInt32(quantity)); string updatequery = "UPDATE pharmacy_management.product SET stock_shop_" + shop + " = '" + newstock + "' WHERE product_code = '" + Convert.ToString(row.Cells[1].Value) + "' "; MySqlCommand cmdsampleinventory = new MySqlCommand(updatequery, connection); try { connection.Open(); int myread2er = cmdsampleinventory.ExecuteNonQuery(); connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } } DBConnection.returnConn(connection); connection = null; }
public static void loginUsers(String userName, String password) { MySqlConnection con = DBConnection.getConn(); try { con.Open(); String query = "Select Type,Password,Status,Shop from employee where EmpID = '" + userName + "'"; MySqlCommand cmd = new MySqlCommand(query, con); MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { //string type = reader["Type"].ToString(); string type = reader.GetString(0); string status = reader.GetString(2); if (status == "Active") { if (reader.GetString(1) == password) { User.CurrentUserName = userName; User.UserShop = reader.GetString(3); User.UserType = type; if (type == "Owner") { /*AdminInterface owner = new AdminInterface(); * owner.Show(); * this.Hide();*/ Owner.login(); LogObj.Hide(); } else if (type == "Supervisor") { /*SupervisorInterface sp = new SupervisorInterface(); * sp.Show(); * this.Hide();*/ Supervisor.login(); LogObj.Hide(); } else if (type == "Cashier") { /*CashierInterface emp = new CashierInterface(); * emp.Show(); * this.Hide();*/ Cashier.login(); LogObj.Hide(); } else { //nameErLbl.Visible = true; User.LogObj.displayErr(); } } else { //nameErLbl.Visible = true; User.LogObj.displayErr(); User.LogObj.showForgotPassButton(); } } else { User.LogObj.inactiveErr(); } } if (!reader.HasRows) { //nameErLbl.Visible = true; User.LogObj.displayErr(); } } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { con.Close(); DBConnection.returnConn(con); con = null; } }
public void execute(DataGridView datagridview, DataGridView datagridview2, string amount, string user_name, int shop) { MySqlConnection connection = DBConnection.getConn(); for (int i = 0; i < datagridview.Rows.Count; i++) { string currentstock = ""; MySqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "SELECT stock_shop_" + shop + " FROM pharmacy_management.product WHERE product_code = '" + Convert.ToString(datagridview.Rows[i].Cells[1].Value) + "'"; try { connection.Open(); MySqlDataReader myReader = cmd.ExecuteReader(); while (myReader.Read()) { currentstock = Convert.ToString(Convert.ToInt32(myReader.GetString("stock_shop_" + shop))); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { connection.Close(); } /*string stock_requested = Convert.ToString(datagridview.Rows[i].Cells[3].Value); * MessageBox.Show(stock_requested); * MessageBox.Show(currentstock); * int value = Convert.ToInt32(currentstock) - Convert.ToInt32(stock_requested); * MessageBox.Show(Convert.ToString(value));*/ string newstock = Convert.ToString(Convert.ToInt32(currentstock) - Convert.ToInt32(datagridview.Rows[i].Cells[3].Value)); string updatequery = "UPDATE pharmacy_management.product SET stock_shop_" + shop + " = '" + newstock + "' WHERE product_code = '" + Convert.ToString(datagridview.Rows[i].Cells[1].Value) + "' "; MySqlCommand cmdsampleinventory = new MySqlCommand(updatequery, connection); try { connection.Open(); int myread2er = cmdsampleinventory.ExecuteNonQuery(); connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } } string details = ""; for (int i = 0; i < datagridview.Rows.Count; i++) { string product_code = ""; string product = ""; string quantity = ""; string price = ""; string total = ""; product_code = Convert.ToString(datagridview.Rows[i].Cells[1].Value); product = Convert.ToString(datagridview.Rows[i].Cells[2].Value); quantity = Convert.ToString(datagridview.Rows[i].Cells[3].Value); price = Convert.ToString(datagridview.Rows[i].Cells[4].Value); total = Convert.ToString(datagridview.Rows[i].Cells[5].Value); string newname = ""; try { string[] names = product.Split(); foreach (string item in names) { newname += item + "_"; } } catch { } string outcome = newname.Substring(0, newname.Length - 1); details += product_code + " " + outcome + " " + quantity + " " + price + " " + total + "/"; } try { DateTime current = DateTime.Now; string date = ""; string time = ""; date = current.ToShortDateString(); time = current.ToShortTimeString(); connection.Open(); string insertquery = "INSERT INTO pharmacy_management.invoice VALUES (null,@user,@shop,@details,@date,@time)"; MySqlCommand command = new MySqlCommand(insertquery, connection); command.Parameters.AddWithValue("@user", user_name); command.Parameters.AddWithValue("@shop", shop); command.Parameters.AddWithValue("@details", details); command.Parameters.AddWithValue("@date", date); command.Parameters.AddWithValue("@time", time); command.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { connection.Close(); } Invoice frm = new Invoice(user_name); for (int i = 0; i < datagridview.RowCount; i++) { frm.dataGridView3.Rows.Add(); for (int j = 2; j < 6; j++) { frm.dataGridView3.Rows[i].Cells[j - 2].Value = datagridview.Rows[i].Cells[j].Value; //MessageBox.Show(Convert.ToString(this.datagridview.Rows[i].Cells[j].Value)); } } MySqlCommand cmd2 = connection.CreateCommand(); cmd2.CommandText = "SELECT * FROM pharmacy_management.invoice WHERE invoice_number = (SELECT MAX(invoice_number) FROM pharmacy_management.invoice)"; string invoice_num = ""; try { connection.Open(); MySqlDataReader myReader2 = cmd2.ExecuteReader(); while (myReader2.Read()) { invoice_num = Convert.ToString(Convert.ToInt32(myReader2.GetString("Invoice_number"))); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { connection.Close(); } frm.invcnumtxtx.Text = invoice_num; frm.ttlbilltxt.Text = amount; string selectQuery = "SELECT product_code, name, description, category, stock_shop_" + shop + ",unit_price, re_order_size FROM pharmacy_management.product "; MySqlDataAdapter adapter = new MySqlDataAdapter(selectQuery, connection); DataTable dt = new DataTable(); adapter.Fill(dt); datagridview2.DataSource = dt; datagridview.Rows.Clear(); frm.Show(); DBConnection.returnConn(connection); connection = null; }