Hello experts,
From and excel file having 2000-5000 rows, i want to fill two tables with some bussiness logic and save them
what would be the best way to do it.
i tried Async/Await, invocation of traget handle error is occuring.
private void btnMakeBills_Click(object sender, EventArgs e)
{
if (cmbMakeBillGL.SelectedValue == null
|| cmbMakeBillGL.SelectedValue == DBNull.Value)
{
MessageBox.Show("Sale Head:" + salebillgl.ToString() + " Invalid");
cmbMakeBillGL.Focus();
return;
}
string billtype = cmbMakeBillGL.SelectedValue.ToString() + ":" +
cmbMakeBillGL.Text + " ?";
if (DialogResult.Yes == MessageBox.Show("Cancell Bill Generate ?", "Bills G.L. Series: " + billtype + " Wrong ?", MessageBoxButtons.YesNo))
{
cmbMakeBillGL.Focus();
return;
}
var watch = System.Diagnostics.Stopwatch.StartNew();
bindNavigator1.btnUndo.PerformClick();
//cmbSaleBook.SelectedValue = cmbMakeBillGL.SelectedValue;
//int pos = bs.Find("doc_gl", cmbMakeBillGL.SelectedValue.ToString());
//if (pos >= 0)
// bs.Position = pos;
DataRowView drvMakeBillItems = null;
billtype = "";
int noofbills = dgvMakeBills.SelectedRows.Count-1;
if (noofbills < 0)
return;
//Properties.Settings.Default.BillAddDebug = true;
bool bos = _BillnoOnSave;
_BillnoOnSave = true;
//
billtype = dgvMakeBills.SelectedRows[noofbills].Cells["typeofbill"].Value.ToString();
if (!saleHeadAccountBookNo.Equals("BA"))
{
//pgAutoGenBills.Maximum = 100;
//pgAutoGenBills.Minimum = 0;
//pgAutoGenBills.Value = 0;
//pgAutoGenBills.Step = 1;
//timer2.Enabled=true;
//await MakeDirectRdBill();
MakeDirectRdBill();
//timer2.Enabled = false;
//pgAutoGenBills.Value = pgAutoGenBills.Maximum;
}
}
private void MakeDirectRdBill()
{
DataRow[] drmaingl = ds.Tables["maingl"].Select("gl_code='" + cmbMakeBillGL.SelectedValue.ToString() + "'");
int dnn = Convert.ToInt32(newbillno(drmaingl[0]["connect2"].ToString()));
for (int i = dgvMakeBills.SelectedRows.Count - 1; i >= 0; i--)
{
//lblBookEdit1.Text = "Pending:" + i.ToString();
DataGridViewRow dgvrAdvice = dgvMakeBills.SelectedRows[i];
if (dgvrAdvice.Cells["id"].Value == null)
continue;
decimal diffrate = 0;
try
{
decimal.TryParse(dgvrAdvice.Cells["rate"].Value.ToString(), out diffrate);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
if (diffrate <= 0)
continue;
if (dgvrAdvice.Cells["qty"].Value == DBNull.Value
|| Convert.ToDecimal(dgvrAdvice.Cells["qty"].Value).Equals(0))
continue;
string billtype = dgvrAdvice.Cells["typeofbill"].Value.ToString();
//see if r.d.bill is maid against this debit note advice
using (SqlConnection con = new SqlConnection(Appvaribales.ConnectionString))
{
//SQL Function Example
SqlCommand cmd = new SqlCommand("select dbo.fnadvicebilled(@id,@ba_da)", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@id", dgvrAdvice.Cells["id"].Value.ToString()));
cmd.Parameters.Add(new SqlParameter("@ba_da", billtype));
try
{
con.Open();
string billnumber = (string)cmd.ExecuteScalar();
con.Close();
if (!string.IsNullOrWhiteSpace(billnumber))
{
dgvMakeBills.Rows.Remove(dgvrAdvice);
continue;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
int HourDiff = DateTime.Now.Hour - dtpBilldate.Value.Hour;
int MinutsDiff = DateTime.Now.Minute - dtpBilldate.Value.Minute;
int SecondsDiff = DateTime.Now.Second - dtpBilldate.Value.Second;
DataRow drMakeBill = ds.Tables["bill"].NewRow();
drMakeBill["ui"] = Guid.NewGuid();
drMakeBill["doc_dt"] = dtpBilldate.Value.AddHours(HourDiff).AddMinutes(MinutsDiff).AddSeconds(SecondsDiff);
drMakeBill["due_dt"] = dtpBilldate.Value.AddDays(1);
drMakeBill["finyear"] = finYear;
drMakeBill["doc_gl"] = cmbMakeBillGL.SelectedValue.ToString();
drMakeBill["doc_no"] = dnn;
dnn++;
drMakeBill["mainsl_ui"] = dgvrAdvice.Cells["mainsl_ui"].Value;
drMakeBill["delivery_ui"] = dgvrAdvice.Cells["delivery_ui"].Value;
drMakeBill["BillReferencesBy"] = dgvrAdvice.Cells["BillReferencesBy"].Value;
//For Clubbed Old Bills Old refrence is not required
drMakeBill["oldrate"] = dgvrAdvice.Cells["oldrate"].Value;
drMakeBill["allpo"] = dgvrAdvice.Cells["allpo"].Value;
if (!cbDnByClubbedOB.Checked)
drMakeBill["RdOnBill_ui"] = dgvrAdvice.Cells["RdOnBill_ui"].Value.ToString();
drMakeBill["DebitNoteAdvice_id"] = dgvrAdvice.Cells["id"].Value;
DateTime frmDate, todate;
if (DateTime.TryParse(dgvrAdvice.Cells["fromdate"].Value.ToString(), out frmDate))
{
drMakeBill["fromdate"] = frmDate;
//dtpFromDate.Value = frmDate;
}
if (DateTime.TryParse(dgvrAdvice.Cells["todate"].Value.ToString(), out todate))
{
//dtpToDate.Value = todate;
drMakeBill["todate"] = todate;
}
drMakeBill["connect2"] = drmaingl[0]["connect2"];
DataRow drMakeBillItems = ds.Tables["billitem"].NewRow();
drMakeBillItems["ui"] = Guid.NewGuid();
drMakeBillItems["bill_ui"] = drMakeBill["ui"];
drMakeBillItems["srl"] = 1;
drMakeBillItems["item_ui"] = dgvrAdvice.Cells["item_ui"].Value;
drMakeBillItems["qty"] = dgvrAdvice.Cells["qty"].Value;
drMakeBillItems["oaitem_ui"] = dgvrAdvice.Cells["oaitem_ui"].Value;
drMakeBillItems["rate"] = diffrate;
drMakeBill["oldrate"] = dgvrAdvice.Cells["oldrate"].Value;
drMakeBill["allpo"] = dgvrAdvice.Cells["allpo"].Value;
decimal tds_rt = 0, gstrate = 0;
DataRow[] dRowsItem = ds.Tables["Item"].Select("ui='" + drMakeBillItems["item_ui"].ToString() + "'");
DataRow[] drHsn = ds.Tables["CETSHUOM"].Select("Trim(ex_chptrno)='" + dRowsItem[0]["ex_chptrno"].ToString() + "'");
if (drHsn.Length >= 1)
{
decimal.TryParse(drHsn[0]["tds_rt"].ToString(), out tds_rt);
decimal.TryParse(drHsn[0]["gst_rt"].ToString(), out gstrate);
}
drMakeBillItems["tds_rt"] = tds_rt;
DataRow[] dRowsMainsl = ds.Tables["mainsl"].Select("ui='" + drMakeBill["mainsl_ui"].ToString() + "'");
if (dRowsMainsl.Length > 0)
{
string custstate = dRowsMainsl[0]["lst_no"].ToString().Substring(0, 2);
string compsatate = ErpMdi1.drCompanyRow[0]["StateVatNo"].ToString().Substring(0, 2);
if (custstate == compsatate)
{
drMakeBillItems["bed_rt"] = gstrate / 2;
drMakeBillItems["SaleTax_rt"] = gstrate / 2;
}
else
{
drMakeBillItems["bed_rt"] = gstrate;
drMakeBillItems["SaleTax_rt"] = 0;
}
}
decimal.TryParse(dgvrAdvice.Cells["qty"].Value.ToString(), out decimal qty);
decimal.TryParse(dgvrAdvice.Cells["rate"].Value.ToString(), out decimal rate);
decimal Amount = Math.Round(rate * qty, 2, MidpointRounding.AwayFromZero);
switch (Convert.ToSByte(drmaingl[0]["roundamt"]))
{
case 1:
Amount = Math.Round(Amount, MidpointRounding.AwayFromZero);
break;
}
drMakeBillItems["amount"] = Amount;
drMakeBillItems["finyear"] = finYear;
Dictionary<string, object> cmd1para = new Dictionary<string, object>();
cmd1para.Add("oaitem_ui", dgvrAdvice.Cells["oaitem_ui"].Value.ToString());
string cmd1 = "select * from oaitem where ui=@oaitem_ui";
DataTable oaitem = CursorAdapter.ExecuteDataTableBySqlString(cmd1, cmd1para);
decimal packingRt = 0, assessableAmtPP = 0, Tdamt = 0, PackingAmt = 0;
if (oaitem.Rows.Count > 0)
{
if (Amount > 0)
{
//[OAitemRow]
decimal.TryParse(oaitem.Rows[0]["td_rt"].ToString(), out decimal tdRt);
if (tdRt>0)
Tdamt = Math.Round((tdRt * Amount / 100), 2, MidpointRounding.AwayFromZero);
drMakeBillItems["td_amt"] = Tdamt;
}
decimal.TryParse(oaitem.Rows[0]["Packing_rt"].ToString(), out packingRt);
drMakeBillItems["packing_rt"] = packingRt;
PackingAmt = packingRt * qty;
drMakeBillItems["PackingAmt"] = PackingAmt;
decimal.TryParse(oaitem.Rows[0]["assessableAmtPP"].ToString(), out assessableAmtPP);
drMakeBillItems["assessableAmtPP"] = assessableAmtPP;
}
//AssessableValue = qty * assessableAmtPP;
//drMakeBillItems["AssessableValue"] = AssessableValue;
decimal HomeAmt = Amount - Tdamt + PackingAmt;
drMakeBillItems["AssessableValue"] = HomeAmt;
decimal.TryParse(drMakeBillItems["Bed_rt"].ToString(), out decimal bedRt);
decimal.TryParse(drMakeBillItems["SaleTax_rt"].ToString(), out decimal SaletaxRt);
decimal Bed_amt = Math.Round((HomeAmt * bedRt / 100), 2, MidpointRounding.AwayFromZero);
decimal SaletaxAmt = Math.Round((HomeAmt * SaletaxRt / 100), 2, MidpointRounding.AwayFromZero);
drMakeBillItems["bed_Amt"] = Bed_amt;
drMakeBillItems["lstamt"] = SaletaxAmt;
drMakeBillItems["bed_on_amt"] = HomeAmt;
decimal Amt = HomeAmt + Bed_amt + SaletaxAmt;
decimal TdsAmt = Math.Round(Amt * tds_rt / 100, 2, MidpointRounding.AwayFromZero);
drMakeBillItems["tds_amt"] = TdsAmt;
drMakeBillItems["log_date"] = DateTime.Now.ToString();
drMakeBillItems["host_name"] = Environment.MachineName;
drMakeBillItems["nt_username"] = Environment.UserName;
//
drMakeBill["amount"] = Amount;
drMakeBill["packingAmt"] = PackingAmt;
drMakeBill["bed_amt"] = Bed_amt;
drMakeBill["lstamt"] = SaletaxAmt;
drMakeBill["tds_amt"] = TdsAmt;
drMakeBill["td_amt"] = Tdamt;
drMakeBill["misc_charg"] = 0;
drMakeBill["amt"] = Amt;
drMakeBill["balanceamt"] = Amt;
drMakeBill["log_date"] = DateTime.Now.ToString();
drMakeBill["host_name"] = Environment.MachineName;
drMakeBill["nt_username"] = Environment.UserName;
ds.Tables["bill"].Rows.Add(drMakeBill);
ds.Tables["billitem"].Rows.Add(drMakeBillItems);
//Update Debit Note Advice that D.N.Added
dgvrAdvice.Cells["bill_ui"].Value = drMakeBill["ui"];
Dictionary<string, object> param = new Dictionary<string, object>();
param.Add("bill_ui", drMakeBill["ui"]);
param.Add("id", dgvrAdvice.Cells["id"].Value);
CursorAdapter.ExecuteNonQueryUsingSQLStatment(@"update debitnoteadvice
set bill_ui=@bill_ui
where id=@id", param);
dgvMakeBills.Rows.Remove(dgvrAdvice);
}
bindNavigator1_tblUpdate();
//return null;
}
