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);
}