Async for Bulk copy

asynchronous c# sqlbulkcopy

Question

I need to bulk-insert a number of datatables into a database table. One table took five minutes to insert due to its size. It took me ten minutes to complete two tables.

static void Main(string[] args)
{
    DataTableBulkInsert(DataTable1);
    DataTableBulkInsert(DataTable2);
}

public static void DataTableBulkInsert(DataTable Table){
    SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(myConnection);
    sqlBulkCopy.DestinationTableName = "dbo.DatabaseTable";
    myConnection.Open();                    
    sqlBulkCopy.WriteToServer(Table);
    myConnection.Close();
}

I'm now attempting to do an async for a bulk insert, but neither data nor errors are being produced. How may the exception be caught?

static void Main(string[] args)
{
     var insert1 = Task.Run(async () => await DataTableBulkInsert(DataTable1);
     var insert2 = Task.Run(async () => await DataTableBulkInsert(DataTable2);
     Task.WhenAll( insert1, insert 2);
}

public static async Task<Boolean> DataTableBulkInsert(DataTable Table)
{
      try
      {
          SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(myConnection);
          sqlBulkCopy.DestinationTableName = "dbo.DatabaseTable";
          myConnection.Open();                    
          await sqlBulkCopy.WriteToServerAsync(Table);
          myConnection.Close();
      }
      catch (Exception (e))
      {
        console.write(e);
      }
      return true;
 }
1
2
8/4/2016 6:04:37 AM

Accepted Answer

The Task.Run S aren't contributing anything helpful here. Additionally, avoid attempting to use the same connection (object) for both executions of your method. the following

static void Main(string[] args)
{
     var insert1 = DataTableBulkInsert(DataTable1);
     var insert2 = DataTableBulkInsert(DataTable2);
     Task.WaitAll( insert1, insert2);
}

public static async Task DataTableBulkInsert(DataTable Table)
{
      using(var localConnection = new SqlConnection(/* connection string */))
      {
          SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(localConnection );
          sqlBulkCopy.DestinationTableName = "dbo.DatabaseTable";
          localConnection.Open();                    
          await sqlBulkCopy.WriteToServerAsync(Table);
      }
 }

Normally return await is a contrarian pattern, but you want to use it here so that theusing Your connection is not terminated by the statement until the bulk load is finished.

Additionally, I changed from using to using Task.WaitAll, which is really waits and is more idiomatic.Task.WhenAll and then phoning right awayWait on it.

5
8/4/2016 12:42:46 PM

Popular Answer

Task.WhenAll produces a Task object that must be awaited in order for the main procedure to continue running normally and the following code to be executed.

The main cannot be designated as secondary since this is a console program.async you may employ this:

Task.WhenAll(insert1, insert2).Wait(); // wait for the returned Task object to Complete

Usage as usual is:await (Task.WhenAll(...)) yet you cannot designate Main as anasync method.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow