c# - Bulk insert using transactions is blocking UI on async process -

trying insert multiple records mysql database using async/await , transactions, still causing ui become frozen/unresponsive while in loop.

looking @ following code, doing wrong or how can achieved ui still responsive during process.

the async method

public static async task addrecords() {     foreach ( string month in months ) {         await maketable( month );         string query="insert `"+month+"` ( caller, started, dialed, durationsec, durationmin, cost, location, switch ) values (@caller, @started, @dialed, @durationsec, @durationmin, @cost, @location, @switch);";         using ( mysqlconnection cn=new mysqlconnection( connectionstring.tostring() ) ) {             await cn.openasync();             using ( mysqltransaction trans=cn.begintransaction() ) {                 using ( mysqlcommand cmd=new mysqlcommand( query, cn, trans ) ) {                     cmd.commandtype=commandtype.text;                     foreach ( record r in cdr.records ) {                         if ( r.started.tostring( "yyyy-mm" )==month ) {                             cmd.parameters.clear();                             cmd.parameters.addwithvalue( "@caller", r.caller );                             cmd.parameters.addwithvalue( "@started", r.started );                             cmd.parameters.addwithvalue( "@dialed", r.dialed );                             cmd.parameters.addwithvalue( "@durationsec", r.duration );                             cmd.parameters.addwithvalue( "@durationmin", math.ceiling( r.duration/60 ) );                             cmd.parameters.addwithvalue( "@cost", r.cost );                             cmd.parameters.addwithvalue( "@location", r.location );                             cmd.parameters.addwithvalue( "@switch", r.switch.tostring() );                             cmd.executenonquery();                         }                     }                     trans.commit();                 }             }             await cn.closeasync();         }     } } 

a snippet on how being called :

    private async void button1_click( object sender, eventargs e ) {          this.text = "adding records";          await addrecords();          this.text = "completed";     } 

as aside, when ui blocking, shouldn't block after prior code has been executed. example, in above button click method, first 'this.text' not set because await addrecords(); executes, happens before ui has chance finish updating, , doesn't complete until after blocking finished results in this.text - "completed" being noticed @ ui level.

---

update

ui still blocking after modifying cmd.executenonquery(); await cmd.executenonqueryasync(); ( recommended yuval itzchakov ) leads me believe blocking on trans.commit(); line or how transaction being built.

updated code

public static async task addrecords() {     foreach ( string month in months ) {         await maketable( month );         string query="insert `"+month+"` ( caller, started, dialed, durationsec, durationmin, cost, location, switch ) values (@caller, @started, @dialed, @durationsec, @durationmin, @cost, @location, @switch);";         using ( mysqlconnection cn=new mysqlconnection( connectionstring.tostring() ) ) {             await cn.openasync();             using ( mysqltransaction trans=cn.begintransaction() ) {                 using ( mysqlcommand cmd=new mysqlcommand( query, cn, trans ) ) {                     cmd.commandtype=commandtype.text;                     foreach ( record r in cdr.records ) {                         if ( r.started.tostring( "yyyy-mm" )==month ) {                             cmd.parameters.clear();                             cmd.parameters.addwithvalue( "@caller", r.caller );                             cmd.parameters.addwithvalue( "@started", r.started );                             cmd.parameters.addwithvalue( "@dialed", r.dialed );                             cmd.parameters.addwithvalue( "@durationsec", r.duration );                             cmd.parameters.addwithvalue( "@durationmin", math.ceiling( r.duration/60 ) );                             cmd.parameters.addwithvalue( "@cost", r.cost );                             cmd.parameters.addwithvalue( "@location", r.location );                             cmd.parameters.addwithvalue( "@switch", r.switch.tostring() );                             await cmd.executenonqueryasync();                         }                     }                     trans.commit();                 }             }             await cn.closeasync();         }     } } 

i think of time spend in commit operation. problem each await in addrecords function synchronizes ui thread function called from. main problem.

the easiest way disconnect synchronizationcontext causes trouble force addrecords run in threadpool-thread.

doing easy:

private async void button1_click( object sender, eventargs e ) {      this.text = "adding records";      await task.run(() => addrecords());      this.text = "completed"; } 

this run database stuff in threadpool , task created here synchronizes ui once done.

the down side of is, can't access ui within addrecords method anymore. in code didn't this, guess fine.