Async for Bulk copy

Question

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

Have you tried setting a debugger on the line `console.write(e);`? In my experience async methods often swallows exceptions. — smoksnes, Aug 04 '16 at 06:07

score 5 · Accepted Answer · edited Aug 04 '16 at 12:42

5

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.

edited Aug 04 '16 at 12:42

Jonathan

49
1
9

answered Aug 04 '16 at 06:29

Damien_The_Unbeliever

234,701
27
340
448

_return await is an anti-pattern_ - Very interesting. Could you provide some more information regarding this? – smoksnes Aug 04 '16 at 06:37
2

@smoksnes - `await` is a way of pausing the current method until some other piece of code has done its job. If all that your method is going to do when it's resumed is to say "I'm done" then why did you go to all of the effort? That being said, the actual anti-pattern is `return await` if that's the *only* `await` in the method (as it is here) and it's not within a `try` block with a `finally` or a `using` block (in which case there is additional code to run after `return`) – Damien_The_Unbeliever Aug 04 '16 at 06:42
1

@smoksnes - there's even a [github issue](https://github.com/dotnet/roslyn/issues/1981) asking for the compiler to optimize it out. – Damien_The_Unbeliever Aug 04 '16 at 06:43
Thanks for the explaination. – smoksnes Aug 04 '16 at 06:44
Do I still need to return when your task do not have a object expression? I have this error Task has no Object expression error with return – Jonathan Aug 04 '16 at 09:57
@Jonathan - I'm not sure I understand the question. – Damien_The_Unbeliever Aug 04 '16 at 09:58

Zein Makki · Answer 2 · 2016-08-04T06:16:35.387

0

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.

edited Aug 04 '16 at 06:16

answered Aug 04 '16 at 06:07

Zein Makki

29,485
6
52
63

It works after I added in the .Wait(); However, now I am getting connection open and close problem. How to allow one connection cater for multiple bulkcopy? – Jonathan Aug 04 '16 at 06:25
@Jonathan you might need to create a separate connection for each task. or close the connection after the `Task.WhenAll(insert1, insert2).Wait();` line of code. – Zein Makki Aug 04 '16 at 06:28

Async for Bulk copy

2 Answers2

Linked