public DataTable ShowHistoryReturn() { string sql = "Select * from HistReturnItem order by id asc"; DataTable histReturnDatatable = new DataTable(); SqlCommand cmd = new SqlCommand(sql, DbClass.con); // open database connection //sql data adapter to hold the value from database tempororily try { DbClass.openConnection(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(histReturnDatatable); } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(histReturnDatatable); }
public DataTable ShowAll() { DataTable temp = new DataTable(); string sql = @"select customer_id as 'Id', customer_name ,credit_amount,phone_number,added_date as 'joined_date' from creditCustomer where active = '1'"; SqlCommand cmd = new SqlCommand(sql, DbClass.con); try { DbClass.openConnection(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(temp); } catch (Exception ex) { MessageBox.Show("Credit Customer not found!", "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(temp); }
public DataTable SearchReturnHist(int billNo) { string sql = @"Select * from HistReturnItem where bill_number = @bill_number "; DataTable billDatatable = new DataTable(); SqlCommand cmd = new SqlCommand(sql, DbClass.con); cmd.Parameters.AddWithValue("@bill_number", billNo); // open database connection //sql data adapter to hold the value from database tempororily try { DbClass.openConnection(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(billDatatable); } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(billDatatable); }
public int CountRows(string productCode) { int rowCount = 0; try { //check if more than one row is present for that product code // if more than one row is present then get the it with highest remaining item // check if the quantity >= or <= than highest remaining item // then that determines if we do 1 update or multiple updates //get it with highest remaining item string sql = @"select COUNT(*) from ProductTable where product_code = " + "'" + productCode + "'"; SqlCommand cmd = new SqlCommand(sql, DbClass.con); DbClass.openConnection(); object obj = cmd.ExecuteScalar(); rowCount = int.Parse(obj.ToString()); } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(rowCount); }
public DataTable GetProductWithHighestRemainingUnits(string productCode) { DataTable data = new DataTable(); try { //get highest remaining item string sql = "select * from ProductTable WHERE product_code = " + "'" + productCode + "'" + " order by remaining_unit desc"; SqlCommand cmd = new SqlCommand(sql, DbClass.con); DbClass.openConnection(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(data); } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(data); }
public float SumOfRemainingUnit(string keyword) { float sumOfUnits = 0; DataTable data = new DataTable(); try { string sql = @"Select sum(remaining_unit) as sum_units FROM ProductTable WHERE product_code = " + "'" + keyword + "'"; SqlCommand cmd = new SqlCommand(sql, DbClass.con); DbClass.openConnection(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(data); } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } sumOfUnits = float.Parse(data.Rows[0]["sum_units"].ToString()); return(sumOfUnits); }
public DataTable BuildProductSchema() { string sql = "Select * from productTable where 1=0"; DataTable productDatatable = new DataTable(); SqlCommand cmd = new SqlCommand(sql, DbClass.con); // open database connection //sql data adapter to hold the value from database tempororily try { DbClass.openConnection(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(productDatatable); } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(productDatatable); }
public DataTable SearchInventoryStock() { string sql = @"Select product_code,product_type,remaining_unit,total_cost_per_unit,selling_price FROM ProductTable"; SqlCommand cmd = new SqlCommand(sql, DbClass.con); DataTable data = new DataTable(); try { DbClass.openConnection(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(data); if (data.Rows.Count > 0) { } else { // may need to add icon and button like other messagebox MessageBox.Show("Error,no products available"); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(data); }
public DataTable searchPurchaseTransaction(string startDate, string endDate) { DataTable data = new DataTable(); try { string sql = @"Select product_type,remaining_unit,selling_price FROM ProductTable WHERE product_code = "; SqlCommand cmd = new SqlCommand(sql, DbClass.con); DbClass.openConnection(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(data); if (data.Rows.Count > 0) { } //else //{ // // may need to add icon and button like other messagebox // MessageBox.Show("Product not found"); //} } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(data); }
public bool delete(Product product) { bool isSuccess = false; try { string sql = @"DELETE from productTable where Id=@Id"; SqlCommand cmd = new SqlCommand(sql, DbClass.con); cmd.Parameters.AddWithValue("@Id", product.Id); DbClass.openConnection(); // ExecuteNonQuery returns the affected rows by the command, used for insert,delete,update etc operations. int rows = cmd.ExecuteNonQuery(); if (rows > 0) { isSuccess = true; } else { isSuccess = false; } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(isSuccess); }
public float GetCurrentBalance(string bank) { float value = 0; try { string sql = @"Select balance from BankInfo where bank_name = @bankName"; SqlCommand cmd = new SqlCommand(sql, DbClass.con); cmd.Parameters.AddWithValue("@bankName", bank); DbClass.openConnection(); var temp = cmd.ExecuteScalar(); value = float.Parse(temp.ToString()); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { DbClass.closeConnection(); } return(value); }
public string SearchLatestBillNumber() { string billNo = ""; string sql = @"Select top 1 bill_number from TransactionTable order by transaction_date desc"; SqlCommand cmd = new SqlCommand(sql, DbClass.con); DbClass.openConnection(); // ExecuteScalar returns the value of the first column of the first row, if executed successfully the value wont be null object obj = cmd.ExecuteScalar(); if (obj != null) { billNo = obj.ToString(); } else { billNo = ""; } DbClass.closeConnection(); return(billNo); }
public DataTable Search(int billNo) { string sql = @"Select td.Id,td.bill_number,td.product_code,td.product_type,td.unit_selling_price,td.quantity,td.total_selling_price,t.discount as 'total_bill_discount',t.total_amount as 'total_bill_amount',( ( t.discount / (t.total_amount + t.discount) )*100 ) as 'percentage_discount' from TransactionDetail td left join TransactionTable t on td.bill_number=t.bill_number where td.bill_number = @bill_number "; DataTable billDatatable = new DataTable(); SqlCommand cmd = new SqlCommand(sql, DbClass.con); cmd.Parameters.AddWithValue("@bill_number", billNo); // open database connection //sql data adapter to hold the value from database tempororily try { DbClass.openConnection(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(billDatatable); } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(billDatatable); }
public DataTable SearchHistBills(int billNo) { string sql = @"select td.bill_number,td.product_code,td.product_type, td.quantity ,td.unit_selling_price , td.total_selling_price, tt.transaction_date,tt.discount as total_discount,tt.payment_method, tt.total_amount as total_bill_amount from TransactionTable tt inner join TransactionDetail td on tt.bill_number = td.bill_number where td.bill_number = @bill_number order by td.bill_number "; DataTable billDatatable = new DataTable(); SqlCommand cmd = new SqlCommand(sql, DbClass.con); cmd.Parameters.AddWithValue("@bill_number", billNo); // open database connection //sql data adapter to hold the value from database tempororily try { DbClass.openConnection(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(billDatatable); } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(billDatatable); }
public DataTable getDetailedSalesByDate(DateTime BeginningDate, DateTime EndingDate) { DataTable data = new DataTable(); string sql = @"with a as ( select product_code, unit_selling_price,sum(quantity) as sum_qty from transactionDetail where bill_number in (select bill_number from transactionTable where CAST(transaction_date as date) BETWEEN CAST(@beginningDate as date) AND CAST(@endingDate as date)) group by product_code,unit_selling_price ),b as ( select MAX(total_cost_per_unit) as cost_per_unit,product_code from ProductTable where total_unit_in <> remaining_unit --i.e only sold products group by product_code ) select distinct a.product_code,a.unit_selling_price as selling_price_per_unit,b.cost_per_unit,a.sum_qty from a left join b on a.product_code =b.product_code "; SqlCommand cmd = new SqlCommand(sql, DbClass.con); cmd.Parameters.AddWithValue("@beginningDate", BeginningDate); cmd.Parameters.AddWithValue("@endingDate", EndingDate); try { DbClass.openConnection(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(data); if (data.Rows.Count > 0) { } else { // may need to add icon and button like other messagebox MessageBox.Show("Sales Data Not Available!", "Error", MessageBoxButton.OK, MessageBoxImage.Error); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(data); }
public bool insert(TransactionDetail transactionDetail) { bool IsSuccess = false; try { string sql = @"Insert into TransactionDetail (product_code,unit_selling_price,quantity,total_selling_price,bill_number,product_type) VALUES(@product_code, @unit_selling_price, @quantity, @total_selling_price, @bill_number, @product_type)"; SqlCommand cmd = new SqlCommand(sql, DbClass.con); cmd.Parameters.AddWithValue("@product_code", transactionDetail.productCode); cmd.Parameters.AddWithValue("@unit_selling_price", transactionDetail.unitSellingPrice); cmd.Parameters.AddWithValue("@quantity", transactionDetail.quantity); cmd.Parameters.AddWithValue("@total_selling_price", transactionDetail.totalSellingPrice); cmd.Parameters.AddWithValue("@bill_number", transactionDetail.billNumber); cmd.Parameters.AddWithValue("@product_type", transactionDetail.productType ?? ""); DbClass.openConnection(); int rows = cmd.ExecuteNonQuery(); if (rows > 0) { IsSuccess = true; } else { IsSuccess = false; } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(IsSuccess); }
public Product searchTransaction(string keyword) { Product product = new Product(); DataTable data = new DataTable(); try { string sql = @"Select product_type,remaining_unit,selling_price FROM ProductTable WHERE product_code = " + "'" + keyword + "'"; SqlCommand cmd = new SqlCommand(sql, DbClass.con); DbClass.openConnection(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(data); if (data.Rows.Count == 1) { product.productType = data.Rows[0]["product_type"].ToString(); product.remainingUnit = float.Parse(data.Rows[0]["remaining_unit"].ToString()); product.sellingPrice = float.Parse(data.Rows[0]["selling_price"].ToString()); } if (data.Rows.Count > 1) { product.productType = data.Rows[0]["product_type"].ToString(); // for remaining unit use sum agrregate for condition where there are more than one entry of product code product.remainingUnit = SumOfRemainingUnit(keyword); product.sellingPrice = float.Parse(data.Rows[0]["selling_price"].ToString()); } //else //{ // // may need to add icon and button like other messagebox // MessageBox.Show("Product not found"); //} } catch (Exception ex) { MessageBox.Show(ex.Message, "Error!,check the input data", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(product); }
public bool histInsert(int billNumber, string productCode, float returnQuantity, float returnAmount) { bool success = false; //id bill_number return_quantity return_amount return_date string sql = @"insert into HistReturnItem(bill_number,product_code,return_quantity,return_amount,return_date) values (@bill_number,@product_code,@return_quantity,@return_amount,@return_date) "; SqlCommand cmd = new SqlCommand(sql, DbClass.con); cmd.Parameters.AddWithValue("@bill_number", billNumber); cmd.Parameters.AddWithValue("@product_code", productCode); cmd.Parameters.AddWithValue("@return_quantity", returnQuantity); cmd.Parameters.AddWithValue("@return_amount", returnAmount); cmd.Parameters.AddWithValue("@return_date", DateTime.Now); try { DbClass.openConnection(); int rows = cmd.ExecuteNonQuery(); if (rows > 0) { success = true; } else { success = false; } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(success); }
public bool AddCreditCustomer(CreditCustomer creditCustomer) { bool success = false; string sql = @"insert into CreditCustomer(customer_name,phone_number,credit_amount,added_date,active) values (@fullName,@phoneNumber,@creditAmount,@addedDate,1) "; SqlCommand cmd = new SqlCommand(sql, DbClass.con); cmd.Parameters.AddWithValue("@fullName", creditCustomer.customerName); cmd.Parameters.AddWithValue("@phoneNumber", creditCustomer.phoneNumber); cmd.Parameters.AddWithValue("@creditAmount", creditCustomer.creditAmount); cmd.Parameters.AddWithValue("@addedDate", creditCustomer.addedDate); try { DbClass.openConnection(); int rows = cmd.ExecuteNonQuery(); if (rows > 0) { success = true; } else { success = false; } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(success); }
public bool Update(int billNo, string id, float returnQty, float creditAmount, float returnDiscount) { bool success = false; //NOTE:- transactionDetail table is filtered by ID and transactionTable is filtered by billNo string sql = @"update TransactionDetail set total_selling_price=unit_selling_price*(quantity - @returnQty),quantity = quantity - @returnQty where Id= @Id update TransactionTable set total_amount=total_amount - @creditAmount ,discount=discount - @returnDiscount,total_qty=total_qty - @returnQty where bill_number= @billNumber "; SqlCommand cmd = new SqlCommand(sql, DbClass.con); cmd.Parameters.AddWithValue("@billNumber", billNo); cmd.Parameters.AddWithValue("@Id", Int32.Parse(id)); cmd.Parameters.AddWithValue("@returnQty", returnQty); cmd.Parameters.AddWithValue("@creditAmount", creditAmount); cmd.Parameters.AddWithValue("@returnDiscount", returnDiscount); try { DbClass.openConnection(); int rows = cmd.ExecuteNonQuery(); if (rows > 0) { success = true; } else { success = false; } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(success); }
public DataTable search(string keyword) { DataTable temp = new DataTable(); try { string sql = "Select * from ProductTable where " + "product_type like '%" + keyword + "%' OR " + "brand_code like '%" + keyword + "%' OR " + "product_code like '%" + keyword + "%' OR " + "delivery_agent like '%" + keyword + "%' OR " + "vendor like '%" + keyword + "%' OR " + "unit_price_INR like '%" + keyword + "%' OR " + "unit_price_NPR like '%" + keyword + "%' OR " + "total_unit_in like '%" + keyword + "%' OR " + "carrier_charge_unit like '%" + keyword + "%' OR " + "total_cost_per_unit like '%" + keyword + "%' OR " + "selling_price like '%" + keyword + "%' "; SqlCommand cmd = new SqlCommand(sql, DbClass.con); DbClass.openConnection(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(temp); } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(temp); }
public bool UpdateQuantity(string productCode, float updatedQty) { bool success = false; try { string sql = @"UPDATE ProductTable SET remaining_unit = @remaining_unit WHERE product_code = @product_code"; SqlCommand cmd = new SqlCommand(sql, DbClass.con); cmd.Parameters.AddWithValue("@remaining_unit", updatedQty); cmd.Parameters.AddWithValue("@product_code", productCode); DbClass.openConnection(); int rows = cmd.ExecuteNonQuery(); if (rows > 0) { success = true; } else { success = false; } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(success); }
public bool IsCrCustomerPresent(string customerName, int customerId) { bool success = false; string sql = @"select COUNT(*) from CreditCustomer where customer_name= @customerName and customer_id = @customerId and active = 1"; SqlCommand cmd = new SqlCommand(sql, DbClass.con); cmd.Parameters.AddWithValue("@customerName", customerName); cmd.Parameters.AddWithValue("@customerId", customerId); try { DbClass.openConnection(); object obj = cmd.ExecuteScalar(); if (obj != null) { if (int.Parse(obj.ToString()) == 1) { success = true; } } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(success); }
public bool IncreaseBankBalance(float amount, string bankName) { bool success = false; try { string sql = @"Update BankInfo SET balance = @balance WHERE bank_name = @bankName"; SqlCommand cmd = new SqlCommand(sql, DbClass.con); float curBalance = GetCurrentBalance(bankName); float incBalance = curBalance + amount; cmd.Parameters.AddWithValue("@balance", incBalance); cmd.Parameters.AddWithValue("@bankName", bankName); DbClass.openConnection(); int rows = cmd.ExecuteNonQuery(); if (rows > 0) { success = true; } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { DbClass.closeConnection(); } return(success); }
public bool UpdateCredit(float amount, int Id) { bool success = false; string sql = @"update CreditCustomer set credit_amount = credit_amount + @amount where customer_id =@id and active ='1' "; SqlCommand cmd = new SqlCommand(sql, DbClass.con); cmd.Parameters.AddWithValue("@amount", amount); cmd.Parameters.AddWithValue("@id", Id); try { DbClass.openConnection(); int rows = cmd.ExecuteNonQuery(); if (rows > 0) { success = true; } else { success = false; } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(success); }
public float GetDiscountPercent(int billno) { string sql = "select discount from TransactionTable where bill_number = '@bill_number' "; SqlCommand cmd = new SqlCommand(sql, DbClass.con); cmd.Parameters.AddWithValue("@bill_number", billno); DbClass.openConnection(); // ExecuteScalar returns the value of the first column of the first row, if executed successfully the value wont be null object obj = cmd.ExecuteScalar(); if (obj == null) { MessageBox.Show("Discount for the given bill number not found"); return(0); } float discountPercent = float.Parse(obj.ToString()); DbClass.closeConnection(); return(discountPercent); }
public DataTable SearchSpecificInventoryStock(string productCode) { string sql = @"Select product_code,product_type,remaining_unit,total_cost_per_unit,selling_price FROM ProductTable WHERE product_code = @productCode"; SqlCommand cmd = new SqlCommand(sql, DbClass.con); cmd.Parameters.AddWithValue("@productCode", productCode); DataTable data = new DataTable(); try { DbClass.openConnection(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(data); if (data.Rows.Count > 0) { } else { // may need to add icon and button like other messagebox MessageBox.Show("Input product not avaialble!", "Error", MessageBoxButton.OK, MessageBoxImage.Error); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(data); }
public bool insert(BillingTransaction transaction, out int transactionID) { bool isSuccess = false; transactionID = -1; try { string sql = @"INSERT INTO TransactionTable ([customer_type],[customer_name],[customer_id],[bill_number],[transaction_date],[total_amount],[discount],[VAT] ) VALUES (@customer_type, @customer_name, @customer_id, @bill_number, @transaction_date, @total_amount, @discount, @VAT )"; // creating sql command to pass the value SqlCommand cmd = new SqlCommand(sql, DbClass.con); //passing data to parameter //NOTE ?? operator gives assigns the value of the right side if the ledt side value is null. cmd.Parameters.AddWithValue("@customer_type", transaction.customerType ?? string.Empty); cmd.Parameters.AddWithValue("@customer_name", transaction.customerName ?? string.Empty); cmd.Parameters.AddWithValue("@customer_id", transaction.customerId ?? string.Empty); cmd.Parameters.AddWithValue("@bill_number", transaction.billNumber); cmd.Parameters.AddWithValue("@transaction_date", transaction.transactionDate); cmd.Parameters.AddWithValue("@total_amount", transaction.totalAmount); cmd.Parameters.AddWithValue("@discount", transaction.discount); cmd.Parameters.AddWithValue("@VAT", transaction.VAT); DbClass.openConnection(); //SqlDataAdapter adapter = new SqlDataAdapter(cmd); //// ExecuteScalar returns the value of the first column of the first row, if executed successfully the value wont be null //object obj = cmd.ExecuteScalar(); //if (obj != null) //{ // transactionID = int.Parse(obj.ToString()); // isSuccess = true; //} //else //{ // isSuccess = false; //} int rows = cmd.ExecuteNonQuery(); if (rows > 0) { isSuccess = true; } else { isSuccess = false; } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(isSuccess); }
public bool update(Product product) { bool isSuccess = false; try { string sql = @"update productTable SET product_type = @product_type, brand_code = @brand_code , product_code = @product_code , delivery_agent = @delivery_agent , vendor = @vendor , unit_price_INR = @unit_price_INR, unit_price_NPR = @unit_price_NPR , total_unit_in = @total_unit_in , carrier_charge_unit = @carrier_charge_unit, total_cost_per_unit =@total_cost_per_unit , selling_price= @selling_price, remaining_unit= @remaining_unit WHERE Id = @Id" ; SqlCommand cmd = new SqlCommand(sql, DbClass.con); cmd.Parameters.AddWithValue("@product_type", product.productType); cmd.Parameters.AddWithValue("@brand_code", product.brandCode); cmd.Parameters.AddWithValue("@product_code", product.productCode); cmd.Parameters.AddWithValue("@delivery_agent", product.deliveryAgent); cmd.Parameters.AddWithValue("@vendor", product.vendor); cmd.Parameters.AddWithValue("@unit_price_INR", product.unitPriceINR); cmd.Parameters.AddWithValue("@unit_price_NPR", product.unitPriceNPR); cmd.Parameters.AddWithValue("@total_unit_in", product.totalUnitIn); cmd.Parameters.AddWithValue("@carrier_charge_unit", product.carrierChargePerUnit); cmd.Parameters.AddWithValue("@total_cost_per_unit", product.totalCostPerUnit); cmd.Parameters.AddWithValue("@selling_price", product.sellingPrice); cmd.Parameters.AddWithValue("@Id", product.Id); cmd.Parameters.AddWithValue("@remaining_unit", product.remainingUnit); DbClass.openConnection(); // ExecuteNonQuery returns the affected rows by the command, used for insert,delete,update etc operations. int rows = cmd.ExecuteNonQuery(); if (rows > 0) { isSuccess = true; } else { isSuccess = false; } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(isSuccess); }
public bool insert(Product product) { bool isSuccess = false; try { string sql = @"INSERT INTO productTable (product_type,brand_code,product_code,delivery_agent,vendor,unit_price_INR,unit_price_NPR,total_unit_in,carrier_charge_unit,total_cost_per_unit,selling_price,added_date,remaining_unit) VALUES (@product_type, @brand_code, @product_code, @delivery_agent, @vendor, @unit_price_INR, @unit_price_NPR, @total_unit_in, @carrier_charge_unit, @total_cost_per_unit, @selling_price, @added_date, @remaining_unit )"; // creating sql command to pass the value SqlCommand cmd = new SqlCommand(sql, DbClass.con); //passing data to parameter cmd.Parameters.AddWithValue("@product_type", product.productType); cmd.Parameters.AddWithValue("@brand_code", product.brandCode); cmd.Parameters.AddWithValue("@product_code", product.productCode); cmd.Parameters.AddWithValue("@delivery_agent", product.deliveryAgent); cmd.Parameters.AddWithValue("@vendor", product.vendor); cmd.Parameters.AddWithValue("@unit_price_INR", product.unitPriceINR); cmd.Parameters.AddWithValue("@unit_price_NPR", product.unitPriceNPR); cmd.Parameters.AddWithValue("@total_unit_in", product.totalUnitIn); cmd.Parameters.AddWithValue("@carrier_charge_unit", product.carrierChargePerUnit); cmd.Parameters.AddWithValue("@total_cost_per_unit", product.totalCostPerUnit); cmd.Parameters.AddWithValue("@selling_price", product.sellingPrice); cmd.Parameters.AddWithValue("@added_date", product.addedDate); cmd.Parameters.AddWithValue("@remaining_unit", product.remainingUnit); DbClass.openConnection(); //SqlDataAdapter adapter = new SqlDataAdapter(cmd); // ExecuteNonQuery executes the query and returns the affected rows by the command, used for insert,delete,update etc operations. int rows = cmd.ExecuteNonQuery(); if (rows > 0) { isSuccess = true; } else { isSuccess = false; } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { DbClass.closeConnection(); } return(isSuccess); }