Jonathan Jonathan - 4 months ago 65
C# Question

Async for Bulk copy

I have quite a few datatable to bulkinsert into databasetable
Due to large size, one table took 5 min to complete 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 a 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, 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;
}

Answer

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.