private void txtu_Name_TextChanged(object sender, EventArgs e) { //Get the keyword fro the text box string keyword = txtu_Name.Text; if (keyword == "") { //Clear all the textboxes txtu_Name.Text = ""; txtu_Contact.Text = ""; txtcompany.Text = ""; textqry.Text = ""; return; } custBLL dc = tDAL.searchcustumer(keyword); //Write the code to get the details and set the value on text boxes //queryBLL q = new queryBLL(); //Now transfer or set the value from DeCustBLL to textboxes // dc.name= txtu_Name.Text; txtu_Contact.Text = dc.mobile; int Cid = dc.cid; companysBLL c = pDAL.Searchfromid(Cid); txtcompany.Text = c.c_name; txtemail.Text = dc.email; }
public bool Increasenoofquery(custBLL p) { //Creating Boolean Variable and set its default value to false bool isSuccess = false; //Sql Connection for DAtabase SqlConnection conn = new SqlConnection(myconnstrng); try { int id = p.id; //SQL Query to insert company into database String sql = "UPDATE tbl_cust SET name=@name, email=@email, type=@type, mobile=@mobile, nquery=@nquery, cid=@cid WHERE id=@id"; //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("@id", p.id); cmd.Parameters.AddWithValue("@email", p.email); cmd.Parameters.AddWithValue("@mobile", p.mobile); cmd.Parameters.AddWithValue("@type", p.type); int i = p.nquery; i++; p.nquery = i; cmd.Parameters.AddWithValue("@nquery", p.nquery); cmd.Parameters.AddWithValue("@cid", p.cid); //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 custBLL searchcustumer(string keyword) { //Create an object of companysBLL and return it custBLL p = new custBLL(); // companysBLL q = new companysBLL(); //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 * FROM tbl_cust WHERE name LIKE '%" + 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 companysBLL if (dt.Rows.Count > 0) { p.name = dt.Rows[0]["name"].ToString(); p.mobile = dt.Rows[0]["mobile"].ToString(); p.id = int.Parse(dt.Rows[0]["id"].ToString()); p.email = dt.Rows[0]["email"].ToString(); p.type = dt.Rows[0]["type"].ToString(); p.nquery = int.Parse(dt.Rows[0]["nquery"].ToString()); p.cid = int.Parse(dt.Rows[0]["cid"].ToString()); } ; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { //Close Database Connection conn.Close(); } return(p); }
public bool chkcustumer(string keyword) { //Create an object of companysBLL and return it custBLL p = new custBLL(); bool sucess = false; // companysBLL q = new companysBLL(); //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 id,name,mobile,email,type,nquery FROM tbl_cust WHERE id LIKE '%" + keyword + "%' OR name LIKE '%" + keyword + "%' OR mobile LIKE '%" + 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 companysBLL if (dt.Rows.Count > 0) { sucess = true; } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { //Close Database Connection conn.Close(); } return(sucess); }
public bool Update(custBLL u) { bool isSuccess = false; SqlConnection conn = new SqlConnection(myconnstrng); try { String sql = "UPDATE tbl_cust SET cust_name=@cust_name, cust_updtd_date=@cust_updtd_date, cust_updtd_by=@cust_updtd_by WHERE cust_id=@cust_id"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@cust_id", u.cust_id); cmd.Parameters.AddWithValue("@cust_name", u.cust_name); cmd.Parameters.AddWithValue("@cust_updtd_date", u.cust_updtd_date); cmd.Parameters.AddWithValue("@cust_updtd_by", u.cust_updtd_by); conn.Open(); int rows = cmd.ExecuteNonQuery(); //if the query is executed successfully then the rows' value = 0 if (rows > 0) { //query successful isSuccess = true; } else { //Query falled isSuccess = false; } } catch (Exception ex) { Module.Tool tool = new Module.Tool(); tool.saveToText(ex); } finally { conn.Close(); } return(isSuccess); }
private void button1_Click(object sender, EventArgs e) { companysBLL tc = new companysBLL(); custBLL c = new custBLL(); string keyword = textcompany.Text; bool sucess = pDAL.chkcompanybyname(keyword); if (sucess == true) { MessageBox.Show("company found"); tc = pDAL.GetcompanysForTransaction(keyword); int id = tc.id; c.cid = id; c.nquery = 0; } else { addcompanys ca = new addcompanys(); ca.Show(); } c.name = txtu_Name.Text; c.mobile = textmobile.Text; c.type = texttyp.Text; c.email = textemail.Text; tc = pDAL.Search(textcompany.Text); sucess = tDAL.Insert(c); if (sucess == true) { MessageBox.Show("costumer added sucessfully"); clear(); } else { MessageBox.Show("costumer can not be added"); } }
private void button3_Click(object sender, EventArgs e) { string keyword = txtu_Name.Text; queryBLL q = new queryBLL(); bool qsucess = tDAL.chkcustumer(keyword); custBLL dc = tDAL.searchcustumer(keyword); if (qsucess == true) { keyword = txtcompany.Text; companysBLL c = pDAL.Search(keyword); q.ques = textqry.Text; q.soln = textsoln.Text; q.cu_id = dc.id; q.c_id = dc.cid; q.u_name = dc.name; emailname = q.u_name; q.u_mobile = dc.mobile; dc.nquery = dc.nquery++; bool incquery = tDAL.Increasenoofquery(dc); if (incquery == true) { MessageBox.Show("user and query updated"); } else { MessageBox.Show("user and query not updated"); } q.status = 1; bool success = dcDAL.Insert(q); q = dcDAL.Getqueryforsearch(q); qid = q.q_id; qid1 = q.q_id; } if (qsucess == false) { keyword = txtcompany.Text; bool csucess = pDAL.chkcompanybyname(keyword); if (csucess == true) { keyword = txtcompany.Text; companysBLL c = pDAL.Search(keyword); custBLL ca = new custBLL(); ca.name = txtu_Name.Text; emailname = q.u_name; ca.mobile = txtu_Contact.Text; string type = "user"; ca.type = type; ca.email = txtemail.Text; bool isadded = false; int id = c.id; ca.cid = id; ca.nquery = 0; isadded = tDAL.Insert(ca); if (isadded == true) { MessageBox.Show("new user added"); } else { MessageBox.Show("new user can not be added"); } } else { MessageBox.Show("plz add new company"); addcompanys ca = new addcompanys(); ca.Show(); } //space to add new user(custumer) //addnewcostumer costumer = new addnewcostumer(); //costumer.Show(); //this.Hide(); //MessageBox.Show("plz add new costumer"); //Failed to Add New product //MessageBox.Show("new user added"); } //if the product is added successfully then the value of success will be true else it will be false if (qsucess == true) { //Product Inserted Successfully MessageBox.Show("query Added Successfully"); emailsBLL email = new emailsBLL(); email.to = txtemail.Text; email.subject = "solidworks support query " + qid; string body = "<h3>" + "query- \n " + "</h3>" + "<b>" + textqry.Text + "</b>" + "<h3>" + " \n\n solution- \n" + "</h3>" + "<b>" + textsoln.Text + "</b>" + "\n\n\n\n\n"; email.body = body; email.name = txtu_Name.Text; bool sucess = eDAL.sendemail(email); if (sucess == true) { MessageBox.Show("email sent sucessfully"); } else { MessageBox.Show("email not sent"); } //Calling the Clear Method } }
private void button4_Click(object sender, EventArgs e) { string keyword = txtu_Name.Text; queryBLL q = new queryBLL(); bool qsucess = tDAL.chkcustumer(keyword);//chking costumer custBLL dc = tDAL.searchcustumer(keyword); if (qsucess == true)// if costumer is found { keyword = txtcompany.Text; companysBLL c = pDAL.Search(keyword); q.ques = textqry.Text; q.soln = textsoln.Text; q.cu_id = dc.id; q.c_id = dc.cid; q.u_name = dc.name; emailname = q.u_name; q.u_mobile = dc.mobile; dc.nquery = dc.nquery++; q.status = 0; q.datetime = DateTime.Now; bool success = dcDAL.Insert(q); bool incquery = tDAL.Increasenoofquery(dc); if (incquery == true) { MessageBox.Show("user and query updated"); } else { MessageBox.Show("user and query not updated"); } emailname = q.u_name; q = dcDAL.Getqueryforsearch(q); qid = q.q_id; if (success == true) { //Product Inserted Successfully MessageBox.Show("query Added Successfully"); emailsBLL email = new emailsBLL(); email.to = txtemail.Text; email.subject = "solidworks support query " + qid; string body = "<h3>" + "SR NO." + DateTime.Today + " " + qid + "query- \n " + "</h3>" + "<b>" + textqry.Text + "</b>" + "<h3>" + " \n\n solution- \n" + "</h3>" + "<b>" + textsoln.Text + "</b>" + "\n\n\n\n\n"; email.body = body; email.name = txtu_Name.Text; bool sucess = eDAL.sendemail(email); if (sucess == true) { MessageBox.Show("email sent sucessfully"); } else { MessageBox.Show("email not sent"); } //Calling the Clear Method } } if (qsucess == false) { keyword = txtcompany.Text; bool csucess = pDAL.chkcompanybyname(keyword); if (csucess == true) { keyword = txtcompany.Text; companysBLL c = pDAL.Search(keyword); custBLL ca = new custBLL(); ca.name = txtu_Name.Text; ca.mobile = txtu_Contact.Text; string type = "user"; ca.type = type; ca.email = txtemail.Text; bool isadded = false; int id = c.id; ca.cid = id; ca.nquery = 0; isadded = tDAL.Insert(ca); if (isadded == true) { MessageBox.Show("new user added"); } else { MessageBox.Show("new user can not be added"); } } else { MessageBox.Show("plz add new company"); addcompanys ca = new addcompanys(); ca.Show(); } } }
private void button2_Click(object sender, EventArgs e) { custBLL c = new custBLL(); custDAL cd = new custDAL(); queryBLL q = new queryBLL(); queryDAL qd = new queryDAL(); companysBLL b = new companysBLL(); companysDAL dc = new companysDAL(); #region user details //int id; string email; string name = ""; string mobile; //int nquery; #endregion #region query details string ques; string ans; int status = 1; int cid; string cname; int i = dataGridView1.Rows.Count; i--; #endregion for (int j = 1; j < i; j++) { #region get data from table name = dataGridView1.Rows[j].Cells[4].Value.ToString(); mobile = dataGridView1.Rows[j].Cells[5].Value.ToString(); ans = dataGridView1.Rows[j].Cells[3].Value.ToString(); email = dataGridView1.Rows[j].Cells[6].Value.ToString(); cname = dataGridView1.Rows[j].Cells[1].Value.ToString(); ques = dataGridView1.Rows[j].Cells[2].Value.ToString(); #endregion #region fill the query q.u_name = name; q.u_mobile = mobile; q.ques = ques; q.soln = ans; q.status = status; b = dc.GetcompanysForTransaction(cname); cid = b.id; q.c_id = cid; bool isuser1 = cd.chkcustumer(name); if (isuser1 == true) { c = cd.searchcustumer(name); } else { MessageBox.Show("user not found"); } q.cu_id = c.id; q.status = status; bool qadded = qd.Insert(q); if (qadded == false) { MessageBox.Show("query not added"); } #endregion } }
private void button1_Click(object sender, EventArgs e) { #region dal and bll custBLL c = new custBLL(); custDAL cd = new custDAL(); queryBLL q = new queryBLL(); queryDAL qd = new queryDAL(); companysBLL b = new companysBLL(); companysDAL dc = new companysDAL(); #endregion #region query variables string ques; string ans; int cid; string cname; #endregion #region user details //int id; string type = "user"; string email; string name = ""; string mobile; //int nquery; #endregion int i = dataGridView1.Rows.Count; i--; for (int j = 1; j < i; j++) { cname = dataGridView1.Rows[j].Cells[4].Value.ToString(); bool sucess = cd.chkcustumer(cname); if (sucess == false) { #region get data from table name = dataGridView1.Rows[j].Cells[4].Value.ToString(); mobile = dataGridView1.Rows[j].Cells[5].Value.ToString(); ans = dataGridView1.Rows[j].Cells[3].Value.ToString(); email = dataGridView1.Rows[j].Cells[6].Value.ToString(); cname = dataGridView1.Rows[j].Cells[1].Value.ToString(); ques = dataGridView1.Rows[j].Cells[2].Value.ToString(); #endregion #region adding the user bool isuser = cd.chkcustumer(name); if (isuser == false) { c.name = name; c.mobile = mobile; c.email = email; c.type = type; b = dc.GetcompanysForTransaction(cname); cid = b.id; c.cid = cid; c.nquery = 0; bool useradded = cd.Insert(c); bool qinc = cd.Increasenoofquery(c); if (useradded == false) { MessageBox.Show("user adding failed"); } } #endregion } } }