protected void GridView1_RowDeleting(object sender, System.Web.UI.WebControls.GridViewDeleteEventArgs e) { int id = 0; var gridView1DataKey = GridView1.DataKeys[e.RowIndex]; if (gridView1DataKey != null) { id = Int32.Parse(gridView1DataKey.Value.ToString()); } int bid = 0; using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["InventoryConnectionString"].ConnectionString)) { connection.Open(); SqlCommand command = new SqlCommand("select b.BillID,c.Name,c.CustomerID,c.Address,b.BillDate,b.GrandTotal,b.ShippingCharge,b.IsCanceled,ci.City,s.State,co.Country from Bill b,Customer c,City ci,Country co,State s where b.CustomerID=c.CustomerID and b.BillID='" + id + "' and c.CityID=ci.CityID and ci.StateID=s.StateID and s.CountryID=co.CountryID", connection); SqlDataAdapter adp = new SqlDataAdapter(command); DataTable dataTable = new DataTable(); adp.Fill(dataTable); foreach (DataRow dr in dataTable.Rows) { BillClass bc = new BillClass() { BillId = int.Parse(dr["BillID"].ToString()), CustomerId = int.Parse(dr["CustomerID"].ToString()), CustomerName = dr["Name"].ToString(), Address = dr["Address"].ToString(), City = dr["City"].ToString(), Country = dr["Country"].ToString(), State = dr["State"].ToString(), ShippingCharge = float.Parse(dr["ShippingCharge"].ToString()), GrandTotal = float.Parse(dr["GrandTotal"].ToString()) }; ConfirmOrderForm.BillList.Add(bc); bid = (int)dr["BillID"]; if (dr["IsCanceled"].ToString() == "True") { Session["Order"] = "Already Canceled"; } else { Session["Order"] = "Not Canceled"; } break; } SqlCommand command1 = new SqlCommand("select p.ProductID,p.Name,b.Quantity,b.Price,b.DiscountPercentage,b.DiscountValue,b.ProductTotal from BillDetail b,Product p where b.BillID='" + bid + "' and b.ProductID=p.ProductID", connection); SqlDataAdapter adp1 = new SqlDataAdapter(command1); DataTable dataTable1 = new DataTable(); adp1.Fill(dataTable1); foreach (DataRow dr in dataTable1.Rows) { BillDetailClass bdc = new BillDetailClass() { ProductId = int.Parse(dr["ProductID"].ToString()), ProductName = dr["Name"].ToString(), Quantity = int.Parse(dr["Quantity"].ToString()), Price = float.Parse(dr["Price"].ToString()), DisPercentage = float.Parse(dr["DiscountPercentage"].ToString()), DisValue = float.Parse(dr["DiscountValue"].ToString()), Total = float.Parse(dr["ProductTotal"].ToString()) }; OrderWebForm.BillDetailList.Add(bdc); } connection.Close(); Response.Redirect("~/InvoiceForm.aspx"); } }
protected void btnBill_Click(object sender, EventArgs e) { // ReSharper disable once TooWideLocalVariableScope int bid; if (ddlCustomer.SelectedValue != "Select Customer") { using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["InventoryConnectionString"].ConnectionString)) { connection.Open(); SqlTransaction sqlTransaction = connection.BeginTransaction(); SqlCommand command = connection.CreateCommand(); command.Transaction = sqlTransaction; try { txtGrandTotal.Text = (float.Parse(txtTotal.Text) + float.Parse(txtShippingCharge.Text)).ToString(CultureInfo.InvariantCulture); // Query Type Transaction command.CommandText = "INSERT INTO Bill(CustomerID,BillDate,ShippingCharge,GrandTotal,IsCanceled) OUTPUT INSERTED.BillID VALUES ('" + ddlCustomer.SelectedValue + "','" + DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss") + "','" + txtShippingCharge.Text + "','" + txtGrandTotal.Text + "','0') "; bid = Int32.Parse(command.ExecuteScalar().ToString()); //Response.Write(command.ExecuteScalar().ToString()); BillClass bc = new BillClass { BillId = bid, CustomerName = ddlCustomer.SelectedItem.ToString(), Address = txtAddress.Text, City = txtCity.Text, State = txtState.Text, Country = txtCountry.Text, CustomerId = int.Parse(ddlCustomer.SelectedValue), GrandTotal = float.Parse(txtGrandTotal.Text), ShippingCharge = float.Parse(txtShippingCharge.Text) }; BillList.Add(bc); foreach (var data in OrderWebForm.BillDetailList) { command.CommandText = "select Quantity from Product where ProductID='" + data.ProductId + "'"; var quantity = int.Parse(command.ExecuteScalar().ToString()); quantity = quantity - data.Quantity; command.CommandText = "update Product set Quantity='" + quantity + "' where ProductID='" + data.ProductId + "'"; command.ExecuteNonQuery(); command.CommandText = "insert into BillDetail(BillID,ProductID,Quantity,Price,DiscountPercentage,DiscountValue,ProductTotal) values('" + bid + "','" + data.ProductId + "','" + data.Quantity + "','" + data.Price + "','" + data.DisPercentage + "','" + data.DisValue + "','" + data.Total + "')"; command.ExecuteNonQuery(); } sqlTransaction.Commit(); Session["Order"] = "Not Canceled"; Response.Redirect("~/InvoiceForm.aspx"); Thread.ResetAbort(); } catch (Exception ex) { if (ex.Message != "Thread was being aborted.") { sqlTransaction.Rollback(); } Response.Write(ex); } connection.Close(); }//if bracket } }