question

SushilAgarwal-8271 avatar image
0 Votes"
SushilAgarwal-8271 asked SushilAgarwal-8271 edited

Best way to enter 2000 or more records

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


windows-forms
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SushilAgarwal-8271 avatar image
0 Votes"
SushilAgarwal-8271 answered SushilAgarwal-8271 edited

Thanks karenpayneoregon,

I got to learn good about async/await .

i implimented the concept due to your sample project, thanks a lot for it.

myth about async/await in my mind , i was guessing great speed enhancement was wrong.

aync/await for my scenario yielded very slow working. earlier i was facing Blocking and pumping issuing for the process running long around 2 hours niw with diffrent apporch can be done in just 5 minuts without async/await.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

Basic structure to perform task with asynchronous operation

 public async Task MakeDirectBillTask()
 {
     await Task.Run(async () =>
     {
         await ...
    
     });
 }

Call it

 await MakeDirectRdBill();

Alternate options to insert

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SushilAgarwal-8271 avatar image
0 Votes"
SushilAgarwal-8271 answered SushilAgarwal-8271 edited

Thanks karenpayneoregon,

I tried, with ide inteligance suggetion the button click event got changed to async task,


 private async Task btnMakeBills_ClickAsync(object sender, EventArgs e)

the long running process called liked this

  await Task.Run(() =>
                 {
                     MakeDirectRdBill();
    
                 });

and the log running process method is

 ![private void MakeDirectRdBill()][1]

but now i am getting attched error.

can you please guide what would have gone wrong ?
[1]: /answers/storage/attachments/88770-asyncawait.png


asyncawait.png (114.8 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SushilAgarwal-8271,
Event handlers need to be async void methods, so you need to change to your code as below:

     private async void  button1_Click(object sender, EventArgs e)
     {
         await clickAsync(sender, e);
     }
        
     public async Task clickAsync(object sender, EventArgs e)
     {
         await Task.Run(() =>
         {
                 MakeDirectRdBill();
        
         });
 }

Best Regards,
Daniel Zhang

0 Votes 0 ·

Thanks Daniel Zhang.

Your suggetion could help remove the error.

but i did not mark it answer, becuase i saw Ms. karenpayneoregon solution its seems to be the thing i was deaming to do, yet i have not tried that.

thanks a lot for your valauble time and effort.

0 Votes 0 ·

Greetings @SushilAgarwal-8271

I have created a sample project to show how to call a method asynchronously which you can use as a model for other async operations and includes how to use events for monitoring said operation..

89116-figure1.png


0 Votes 0 ·
figure1.png (21.3 KiB)