public productsbll getpid(string name) { productsbll p = new productsbll(); SqlConnection conn = new SqlConnection(myconnstring); DataTable dt = new DataTable(); try { String sql = "SELECT id FROM products_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) { p.id = int.Parse(dt.Rows[0]["id"].ToString()); } } catch (Exception e) { MessageBox.Show(e.Message); } finally { conn.Close(); } return(p); }
public bool Delete(productsbll u) { bool issuccess = false; SqlConnection conn = new SqlConnection(myconnstring); try { String sql = "DELETE FROM products_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 productsbll Search_products_transaction(String keywords) { productsbll dc = new productsbll(); SqlConnection conn = new SqlConnection(myconnstring); DataTable dt = new DataTable(); try { String sql = "SELECT Name,Rate,Qty FROM products_tbl WHERE Name LIKE '%" + keywords + "%' OR Name LIKE '%" + keywords + "%' OR Category LIKE '%" + keywords + "%' OR Rate 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.Category = dt.Rows[0]["Category"].ToString(); dc.Rate = Decimal.Parse(dt.Rows[0]["Rate"].ToString()); dc.Qty = Decimal.Parse(dt.Rows[0]["Qty"].ToString()); } } catch (Exception e) { MessageBox.Show(e.Message); } finally { conn.Close(); } return(dc); }
private void psearch_TextChanged(object sender, EventArgs e) { string keyword = psearch.Text; if (keyword == "") { pname.Text = ""; pcat.Text = "0"; prate.Text = "0"; pqty.Text = "0"; } productsbll d = pdal.Search_products_transaction(keyword); pname.Text = d.Name; pcat.Text = d.Qty.ToString(); prate.Text = d.Rate.ToString(); }
public bool Update(productsbll u) { bool issuccess = false; SqlConnection conn = new SqlConnection(myconnstring); try { String sql = "UPDATE products_tbl SET Name=@Name,Category=@Category,Description=@Description,Rate=@Rate,Qty=@Qty,added_date=@added_date,added_by=@added_by WHERE id=@id"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@Name", u.Name); cmd.Parameters.AddWithValue("@Category", u.Category); cmd.Parameters.AddWithValue("@Description", u.Description); cmd.Parameters.AddWithValue("@Rate", u.Rate); cmd.Parameters.AddWithValue("@Qty" + "", u.Qty); 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(productsbll u) { bool issuccess = false; SqlConnection conn = new SqlConnection(myconnstring); try { String sql = "INSERT INTO products_tbl(Name,Category,Description,Rate,Qty,added_date,added_by) VALUES (@Name,@Category,@Description,@Rate,@Qty,@added_date,@added_by)"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@Name", u.Name); cmd.Parameters.AddWithValue("@Category", u.Category); cmd.Parameters.AddWithValue("@Description", u.Description); cmd.Parameters.AddWithValue("@Rate", u.Rate); cmd.Parameters.AddWithValue("@Qty", u.Qty); 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"); } } }