CODEWITHSUNDEEP
c#asynchronoussqlbulkcopy
Jonathan
Jonathan

Reputation: 59

Async for Bulk copy

I have quite a few datatable to bulkinsert into databasetable Due to its large size, one table took 5 min to complete the insert. 2 tables took me 10 min

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 am now trying to do an async for Bulk insert, but there is neither any data inserted nor giving me error. How to capture the exception?

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

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

Upvotes: 2

Views: 10065

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

The Task.Runs are adding nothing useful here. Also, don't try to share a single connection object between the two runs of your method. Something like:

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 an anti-pattern, but here you want to use it so that the using statement doesn't close your connection until after the bulk load is complete.

Also, I switched to using Task.WaitAll which actually waits, which is more idiomatic than using Task.WhenAll and then immediately calling Wait on it.

Upvotes: 5

Zein Makki
Zein Makki

Reputation: 30022

Task.WhenAll returns a Task object that needs to be awaited or the code that follows continues its normal execution and the main method exits immediately.

Since this is a console application and the main can't be marked as async, you can use this:

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

The normal usage is: await (Task.WhenAll(...)) but you can't mark Main as an async method.

Upvotes: 0

Related Questions