public DCbll getid(string name) { DCbll dc = new DCbll(); SqlConnection conn = new SqlConnection(myconnstring); DataTable dt = new DataTable(); try { String sql = "SELECT id FROM dealer_custmr_tbl WHERE Name='" + name + "'"; SqlCommand cmd = new SqlCommand(sql, conn); SqlDataAdapter adapter = new SqlDataAdapter(cmd); conn.Open(); adapter.Fill(dt); if (dt.Rows.Count > 0) { dc.id = int.Parse(dt.Rows[0]["id"].ToString()); } } catch (Exception e) { MessageBox.Show(e.Message); } finally { conn.Close(); } return(dc); }
public bool Delete(DCbll u) { bool issuccess = false; SqlConnection conn = new SqlConnection(myconnstring); try { String sql = "DELETE FROM dealer_custmr_tbl where id=@id"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@id", u.id); conn.Open(); int rows = cmd.ExecuteNonQuery(); if (rows > 0) { issuccess = true; } else { issuccess = false; } } catch (Exception e) { MessageBox.Show(e.Message); } finally { conn.Close(); } return(issuccess); }
public DCbll Search_dea_cust_transaction(String keywords) { DCbll dc = new DCbll(); SqlConnection conn = new SqlConnection(myconnstring); DataTable dt = new DataTable(); try { String sql = "SELECT Name,Email,Contact,Address FROM dealer_custmr_tbl WHERE Type LIKE '%" + keywords + "%' OR id LIKE '%" + keywords + "%' OR Name LIKE '%" + keywords + "%' OR Email LIKE '%" + keywords + "%' OR Contact LIKE '%" + keywords + "%' OR Address LIKE '%" + keywords + "%' "; SqlCommand cmd = new SqlCommand(sql, conn); SqlDataAdapter adapter = new SqlDataAdapter(cmd); conn.Open(); adapter.Fill(dt); 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 e) { MessageBox.Show(e.Message); } finally { conn.Close(); } return(dc); }
private void dcsearch_TextChanged(object sender, EventArgs e) { string keyword = dcsearch.Text; if (keyword == "") { dcname.Text = ""; dcemail.Text = ""; dccontact.Text = ""; dcaddress.Text = ""; } DCbll d = dcdal.Search_dea_cust_transaction(keyword); dcname.Text = d.Name; dcemail.Text = d.Email; dccontact.Text = d.Contact; dcaddress.Text = d.Address; }
public bool Update(DCbll u) { bool issuccess = false; SqlConnection conn = new SqlConnection(myconnstring); try { String sql = "UPDATE dealer_custmr_tbl SET Type=@Type,Name=@Name,Email=@Email,Contact=@Contact,Address=@Address,added_date=@added_date,added_by=@added_by WHERE id=@id"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@Type", u.Type); cmd.Parameters.AddWithValue("@Name", u.Name); cmd.Parameters.AddWithValue("@Email", u.Email); cmd.Parameters.AddWithValue("@Contact", u.Contact); cmd.Parameters.AddWithValue("@Address", u.Address); 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) { issuccess = true; } else { issuccess = false; } } catch (Exception e) { MessageBox.Show(e.Message); } finally { conn.Close(); } return(issuccess); }
public bool Insert(DCbll u) { bool issuccess = false; SqlConnection conn = new SqlConnection(myconnstring); try { String sql = "INSERT INTO dealer_custmr_tbl(Type,Name,Email,Contact,Address,added_date,added_by) VALUES (@Type,@Name,@Email,@Contact,@Address,@added_date,@added_by)"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@Type", u.Type); cmd.Parameters.AddWithValue("@Name", u.Name); cmd.Parameters.AddWithValue("@Email", u.Email); cmd.Parameters.AddWithValue("@Contact", u.Contact); cmd.Parameters.AddWithValue("@Address", u.Address); cmd.Parameters.AddWithValue("@added_date", u.added_date); cmd.Parameters.AddWithValue("@added_by", u.added_by); conn.Open(); int rows = cmd.ExecuteNonQuery(); if (rows > 0) { issuccess = true; } else { issuccess = false; } } catch (Exception e) { MessageBox.Show(e.Message); } finally { conn.Close(); } return(issuccess); }
private void button2_Click(object sender, EventArgs e) { Transactionbll transaction = new Transactionbll(); transaction.Type = label1.Text; string dea_csname = dcname.Text; DCbll dc = dcdal.getid(dea_csname); transaction.dea_cust_id = dc.id; transaction.Grand_total = Math.Round(decimal.Parse(grandtotal.Text), 2); transaction.transaction_date = DateTime.Now; transaction.tax = decimal.Parse(vat.Text); transaction.discount = decimal.Parse(discount.Text); string usrname = Formlogin.loggdin; userbll u = udal.getid(usrname); transaction.added_by = u.id; transaction.transactiondetails = transactiondt; bool success = false; //inserting transaction and transaction details using (TransactionScope scope = new TransactionScope()) { int transactionid = -1; bool w = tdal.Insert(transaction, out transactionid); for (int i = 0; i < transactiondt.Rows.Count; i++) { trnsctiondetailbll tddetail = new trnsctiondetailbll(); String prdname = transactiondt.Rows[i][0].ToString(); productsbll p = pdal.getpid(prdname); tddetail.pid = p.id; tddetail.rate = decimal.Parse(transactiondt.Rows[i][1].ToString()); tddetail.qty = decimal.Parse(transactiondt.Rows[i][2].ToString()); tddetail.Total = Math.Round(decimal.Parse(transactiondt.Rows[i][3].ToString()), 2); tddetail.dea_cust_id = dc.id; tddetail.added_date = DateTime.Now; tddetail.added_by = u.id; //INC OR DEC QTY string ttype = label1.Text; bool x = false; if (ttype == "PURCHASE") { x = pdal.increaseqty(tddetail.pid, tddetail.qty); } else if (ttype == "SALES") { x = pdal.decreaseqty(tddetail.pid, tddetail.qty); } bool y = tddal.Insert(tddetail); success = w && y && x; } if (success == true) { scope.Complete(); DGVPrinter print = new DGVPrinter(); print.Title = "\r\n\r\n MIKE'S STORES PVT LTD \r\n"; print.SubTitle = "KOCHI ,KERALA ,phone - +919537583616\r\n\r\n"; print.SubTitleFormatFlags = StringFormatFlags.LineLimit | StringFormatFlags.NoClip; print.PageNumbers = true; print.PageNumberInHeader = false; print.PorportionalColumns = true; print.HeaderCellAlignment = StringAlignment.Near; print.Footer = "Discount :" + discount.Text + "% \r\n" + "vat :" + vat.Text + "%\r\n" + "GRAND_TOTAL:" + grandtotal.Text + "\r\n" + "Date & time of purchase:" + dateTimePicker1.Value + "\r\n\r" + " THANK YOU"; print.FooterSpacing = 40; print.PrintDataGridView(dgvproducts); MessageBox.Show("TRANSACTION C0MPLETED SUCCESSFULLY"); dgvproducts.DataSource = null; dgvproducts.Rows.Clear(); dgvproducts.DataSource = null; dgvproducts.Rows.Clear(); dcsearch.Text = ""; dcname.Text = ""; dcemail.Text = ""; dccontact.Text = ""; dcaddress.Text = ""; clear(); sbtotal.Text = ""; discount.Text = "0"; grandtotal.Text = "0"; vat.Text = "0"; PA.Text = "0"; RA.Text = "0"; } else { MessageBox.Show("TRANSACTION FAILED"); } } }