Scroll to top

Dapper.NET : Bulk inserts


Curious Bot - December 1, 2018 - 0 comments

Async Bulk Copy

This sample uses a ToDataReader method described here Creating a Generic List DataReader for SqlBulkCopy.

This can also be done using non-async methods.

public class Widget
{
public int WidgetId {get;set;}
public string Name {get;set;}
public int Quantity {get;set;}
}

public async Task AddWidgets(IEnumerable<Widget> widgets)
{
using(var conn = new SqlConnection("{connection string}")) {
await conn.OpenAsync();

using(var bulkCopy = new SqlBulkCopy(conn)) {
bulkCopy.BulkCopyTimeout = SqlTimeoutSeconds;
bulkCopy.BatchSize = 500;
bulkCopy.DestinationTableName = "Widgets";
bulkCopy.EnableStreaming = true;

using(var dataReader = widgets.ToDataReader())
{
await bulkCopy.WriteToServerAsync(dataReader);
}
}
}
}

Bulk Copy

This sample uses a ToDataReader method described here Creating a Generic List DataReader for SqlBulkCopy.

This can also be done using async methods.

public class Widget
{
public int WidgetId {get;set;}
public string Name {get;set;}
public int Quantity {get;set;}
}

public void AddWidgets(IEnumerable<Widget> widgets)
{
using(var conn = new SqlConnection("{connection string}")) {
conn.Open();

using(var bulkCopy = new SqlBulkCopy(conn)) {
bulkCopy.BulkCopyTimeout = SqlTimeoutSeconds;
bulkCopy.BatchSize = 500;
bulkCopy.DestinationTableName = "Widgets";
bulkCopy.EnableStreaming = true;

using(var dataReader = widgets.ToDataReader())
{
bulkCopy.WriteToServer(dataReader);
}
}
}
}

Remarks

The WriteToServer and WriteToServerAsync have overloads that accept IDataReader (seen in the examples), DataTable, and DataRow arrays (DataRow[]) as the source of the data for the Bulk Copy.

Related posts