Feature Request: Bulk Insert support

[tmsmith]

Feature Request: Bulk Insert support

[JMan7777]

Hi, below a small piece of code I use for Bulk Inserts with a MS SQL DB.
I stripped down the code a bit as my original code is making use of automatic retries using Polly.
So the code below is working for me but untested in this stripped down version. Feel free to use it as inspiration ;).

I also created a BulkDeleteAsync. If anyone interested just PM me.

public static Task BulkInsertAsync<T>(IEnumerable<T> entities, IDbTransaction transaction, int? commandTimeout = null)
{
	SqlTransaction sqlTransaction = null;

	if (transaction is SqlTransaction aSQLTransaction)
	{
		sqlTransaction = aSQLTransaction;
	}
	else
	{
		if (transaction != null)
		{
			throw new InvalidOperationException("Connection is not to a MS SQL Database");
		}
	}

	//Custom Bulk Insert using SqlBulkCopy
	using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlTransaction.Connection, SqlBulkCopyOptions.Default, sqlTransaction))
	{
		IClassMapper classMapper = DapperExtensionsConfiguration.GetMap<T>();
		bulkCopy.BatchSize = 25000;
		bulkCopy.NotifyAfter = 25000;
		bulkCopy.SqlRowsCopied += (sender, eventArgs) => Logger.Info($"BulkInsertAsync wrote {eventArgs.RowsCopied} records of { entities.Count()} {typeof(T).Name}'s.");
		bulkCopy.DestinationTableName = $"{classMapper.SchemaName}.{classMapper.TableName}";
		bulkCopy.BulkCopyTimeout = commandTimeout ?? sqlTransaction.Connection.ConnectionTimeout;

		DataTable newInserts = new DataTable("NewInserts");
		List<DataColumn> keys = new List<DataColumn>();
		classMapper.Properties.ToList().ForEach(prop =>
		{
			if (!prop.Ignored)
			{
				DataColumn dataColumn = new DataColumn();

				Type type = System.Nullable.GetUnderlyingType(prop.PropertyInfo.PropertyType);
				if (type == null)
				{
					type = prop.PropertyInfo.PropertyType;
				}

				dataColumn.DataType = type;
				dataColumn.ColumnName = prop.ColumnName;
				dataColumn.AutoIncrement = prop.KeyType.Equals(KeyType.Identity);
				if (!prop.KeyType.Equals(KeyType.NotAKey))
				{
					keys.Add(dataColumn);
					if (!prop.KeyType.Equals(KeyType.Identity))
					{
						dataColumn.AllowDBNull = false;
					}
				}
				// We need to specify the column mapping as the column sequence in the Dapper mapping 
				// file might be different than the DB table column sequence
				bulkCopy.ColumnMappings.Add(dataColumn.ColumnName, dataColumn.ColumnName);
				newInserts.Columns.Add(dataColumn);
			}
		});

		// Create an array for DataColumn objects.
		newInserts.PrimaryKey = keys.ToArray();

		// Add some new rows to the collection. 
		entities.ToList().ForEach(entity =>
		{
			DataRow row = newInserts.NewRow();
			classMapper.Properties.ToList().ForEach(prop =>
			{
				if (!prop.Ignored)
				{
					var value = prop.PropertyInfo.GetValue(entity);
					row[prop.ColumnName] = value ?? DBNull.Value;
				}
			});
			Logger.Trace($"Preparing '{bulkCopy.DestinationTableName}' bulk insert for '{typeof(T).Name}'");
			newInserts.Rows.Add(row);
		});

		//Commit to the internal data table
		newInserts.AcceptChanges();

		Stopwatch stopwatch = new Stopwatch();
		stopwatch.Start();
		try
		{
			// Write from the source to the destination.
			await bulkCopy.WriteToServerAsync(newInserts).ConfigureAwait(false);
			Logger.Trace($"BulkInsertAsync for {entities.Count()} {typeof(T).Name}'s successful. Elapsed: {stopwatch.ElapsedMilliseconds:0} ms.");
		}
		catch (Exception ex)
		{
			Logger.Error(ex, $"BulkInsertAsync for {entities.Count()} {typeof(T).Name}'s failed. Elapsed: {stopwatch.ElapsedMilliseconds:0} ms.");
			throw ex;
		}
		finally
		{
			stopwatch.Stop();
		}
	}
}

[pagebrooks]

I just added an Insert overload that takes advantage of Dapper's multi-insert. This isn't a true bulk insert, but it's better than singular Insert calls.

[dotnetchris]

I could possibly help out on working on a real solution that uses SqlBulkCopy but my knowledge of that is extremely limited

[dotnetchris]

Saw Sql Bulk Copy brought up on stackoverflow, figured link is very relevant here for future reference: http://stackoverflow.com/a/8638878/37055

[pagebrooks]

Saw this today, it builds the column mappings for SQL Bulk Copy reflectively.

http://elegantcode.com/2012/01/26/sqlbulkcopy-for-generic-listt-useful-for-entity-framework-nhibernate/

[dotnetchris]

Fantastic find Page!

[dotnetchris]

Simple example of using SqlCopy and Dapper together http://stackoverflow.com/a/9947259/37055

[erlis]

We should implement this in Dapper-Extensions... I'll work on something and send you a pull request.

Here we can see how the NPoco guys did it:
https://github.com/schotime/NPoco/blob/master/src/NPoco/SqlBulkCopyHelper.cs#L30