/// <summary>
/// Inserts entities into table <typeparamref name="T"/>s (by default).
/// </summary>
/// <typeparam name="T">The type being inserted.</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="data">Entities to insert</param>
/// <param name="transaction">The transaction to run under, null (the default) if none</param>
/// <param name="batchSize">Number of bulk items inserted together, 0 (the default) if all</param>
/// <param name="bulkCopyTimeout">Number of seconds before bulk command execution timeout, 30 (the default)</param>
public static void BulkInsert <T>(this SqlConnection connection, IEnumerable <T> data, SqlTransaction transaction = null, int batchSize = 0, int bulkCopyTimeout = 30)
{
var type = typeof(T);
var tableName = TableMapper.GetTableName(type);
var allProperties = PropertiesCache.TypePropertiesCache(type);
var keyProperties = PropertiesCache.KeyPropertiesCache(type);
var computedProperties = PropertiesCache.ComputedPropertiesCache(type);
var allPropertiesExceptKeyAndComputed = allProperties.Except(keyProperties.Union(computedProperties)).ToList();
var allPropertiesExceptKeyAndComputedString = GetColumnsStringSqlServer(allPropertiesExceptKeyAndComputed);
var tempToBeInserted = $"#{tableName}_TempInsert";
connection.Execute($@"SELECT TOP 0 {allPropertiesExceptKeyAndComputedString} INTO {tempToBeInserted} FROM {tableName} target WITH(NOLOCK);", null, transaction);
using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
bulkCopy.BulkCopyTimeout = bulkCopyTimeout;
bulkCopy.BatchSize = batchSize;
bulkCopy.DestinationTableName = tempToBeInserted;
bulkCopy.WriteToServer(ToDataTable(data, tableName, allPropertiesExceptKeyAndComputed).CreateDataReader());
}
connection.Execute($@"
INSERT INTO {tableName}({allPropertiesExceptKeyAndComputedString})
SELECT {allPropertiesExceptKeyAndComputedString} FROM {tempToBeInserted}
DROP TABLE {tempToBeInserted};", null, transaction);
}
/// <summary>
/// Inserts entities into table <typeparamref name="T"/>s (by default) asynchronously.
/// </summary>
/// <typeparam name="T">The type being inserted.</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="data">Entities to insert</param>
/// <param name="transaction">The transaction to run under, null (the default) if none</param>
/// <param name="batchSize">Number of bulk items inserted together, 0 (the default) if all</param>
/// <param name="bulkCopyTimeout">Number of seconds before bulk command execution timeout, 30 (the default)</param>
public static async Task BulkInsertAsync <T>(this SqlConnection connection, IEnumerable <T> data, SqlTransaction transaction = null, int batchSize = 0, int bulkCopyTimeout = 30)
{
var type = typeof(T);
var tableName = TableMapper.GetTableName(type);
var allProperties = PropertiesCache.TypePropertiesCache(type);
var keyProperties = PropertiesCache.KeyPropertiesCache(type);
var computedProperties = PropertiesCache.ComputedPropertiesCache(type);
var columns = PropertiesCache.GetColumnNamesCache(type);
var allPropertiesExceptKeyAndComputed = allProperties.Except(keyProperties.Union(computedProperties)).ToList();
var allPropertiesExceptKeyAndComputedString = GetColumnsStringSqlServer(allPropertiesExceptKeyAndComputed, columns);
var tempToBeInserted = $"#TempInsert_{tableName}".Replace(".", string.Empty);
await connection.ExecuteAsync($@"SELECT TOP 0 {allPropertiesExceptKeyAndComputedString} INTO {tempToBeInserted} FROM {FormatTableName(tableName)} target WITH(NOLOCK);", null, transaction, commandTimeout : connection.ConnectionTimeout);
using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
bulkCopy.BulkCopyTimeout = bulkCopyTimeout;
bulkCopy.BatchSize = batchSize;
bulkCopy.DestinationTableName = tempToBeInserted;
await bulkCopy.WriteToServerAsync(ToDataTable(data, allPropertiesExceptKeyAndComputed).CreateDataReader());
}
await connection.ExecuteAsync($@"
INSERT INTO {FormatTableName(tableName)}({allPropertiesExceptKeyAndComputedString})
SELECT {allPropertiesExceptKeyAndComputedString} FROM {tempToBeInserted}
DROP TABLE {tempToBeInserted};", null, transaction, commandTimeout : connection.ConnectionTimeout);
}
/// <summary>
/// Inserts entities into table <typeparamref name="T"/>s (by default) returns inserted entities.
/// </summary>
/// <typeparam name="T">The element type of the array</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="data">Entities to insert</param>
/// <param name="transaction">The transaction to run under, null (the default) if none</param>
/// <param name="batchSize">Number of bulk items inserted together, 0 (the default) if all</param>
/// <param name="bulkCopyTimeout">Number of seconds before bulk command execution timeout, 30 (the default)</param>
/// <param name="identityInsert">Usage of db generated ids. By default DB generated IDs are used (identityInsert=false)</param>
/// <returns>Inserted entities</returns>
public static IEnumerable <T> BulkInsertAndSelect <T>(this SqlConnection connection, IEnumerable <T> data, SqlTransaction transaction = null, int batchSize = 0, int bulkCopyTimeout = 30, bool identityInsert = false)
{
var type = typeof(T);
var tableName = TableMapper.GetTableName(type);
var allProperties = PropertiesCache.TypePropertiesCache(type);
var keyProperties = PropertiesCache.KeyPropertiesCache(type);
var computedProperties = PropertiesCache.ComputedPropertiesCache(type);
var columns = PropertiesCache.GetColumnNamesCache(type);
if (keyProperties.Count == 0)
{
var dataList = data.ToList();
connection.BulkInsert(dataList, transaction, batchSize, bulkCopyTimeout);
return(dataList);
}
var insertProperties = allProperties.Except(computedProperties).ToList();
if (!identityInsert)
{
insertProperties = insertProperties.Except(keyProperties).ToList();
}
var(identityInsertOn, identityInsertOff, sqlBulkCopyOptions) = GetIdentityInsertOptions(identityInsert, tableName);
var keyPropertiesString = GetColumnsStringSqlServer(keyProperties, columns);
var keyPropertiesInsertedString = GetColumnsStringSqlServer(keyProperties, columns, "inserted.");
var insertPropertiesString = GetColumnsStringSqlServer(insertProperties, columns);
var allPropertiesString = GetColumnsStringSqlServer(allProperties, columns, "target.");
var tempToBeInserted = $"#TempInsert_{tableName}".Replace(".", string.Empty);
var tempInsertedWithIdentity = $"@TempInserted_{tableName}".Replace(".", string.Empty);
connection.Execute($"SELECT TOP 0 {insertPropertiesString} INTO {tempToBeInserted} FROM {FormatTableName(tableName)} target WITH(NOLOCK);", null, transaction);
using (var bulkCopy = new SqlBulkCopy(connection, sqlBulkCopyOptions, transaction))
{
bulkCopy.BulkCopyTimeout = bulkCopyTimeout;
bulkCopy.BatchSize = batchSize;
bulkCopy.DestinationTableName = tempToBeInserted;
bulkCopy.WriteToServer(ToDataTable(data, insertProperties).CreateDataReader());
}
var table = string.Join(", ", keyProperties.Select(k => $"[{k.Name }] bigint"));
var joinOn = string.Join(" AND ", keyProperties.Select(k => $"target.[{k.Name }] = ins.[{k.Name }]"));
return(connection.Query <T>($@"
{identityInsertOn}
DECLARE {tempInsertedWithIdentity} TABLE ({table})
INSERT INTO {FormatTableName(tableName)}({insertPropertiesString})
OUTPUT {keyPropertiesInsertedString} INTO {tempInsertedWithIdentity} ({keyPropertiesString})
SELECT {insertPropertiesString} FROM {tempToBeInserted}
{identityInsertOff}
SELECT {allPropertiesString}
FROM {FormatTableName(tableName)} target INNER JOIN {tempInsertedWithIdentity} ins ON {joinOn}
DROP TABLE {tempToBeInserted};", null, transaction));
}
/// <summary>
/// Inserts entities into table <typeparamref name="T"/>s (by default) asynchronously and returns inserted entities.
/// </summary>
/// <typeparam name="T">The type being inserted.</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="data">Entities to insert</param>
/// <param name="transaction">The transaction to run under, null (the default) if none</param>
/// <param name="batchSize">Number of bulk items inserted together, 0 (the default) if all</param>
/// <param name="bulkCopyTimeout">Number of seconds before bulk command execution timeout, 30 (the default)</param>
/// <returns>Inserted entities</returns>
public static async Task <IEnumerable <T> > BulkInsertAndSelectAsync <T>(this SqlConnection connection, IEnumerable <T> data, SqlTransaction transaction = null, int batchSize = 0, int bulkCopyTimeout = 30)
{
var type = typeof(T);
var tableName = TableMapper.GetTableName(type);
var allProperties = PropertiesCache.TypePropertiesCache(type);
var keyProperties = PropertiesCache.KeyPropertiesCache(type);
var computedProperties = PropertiesCache.ComputedPropertiesCache(type);
var columns = PropertiesCache.NewColumnNamesCache(type);
if (keyProperties.Count == 0)
{
var dataList = data.ToList();
await connection.BulkInsertAsync(dataList, transaction, batchSize, bulkCopyTimeout);
return(dataList);
}
var allPropertiesExceptKeyAndComputed = allProperties.Except(keyProperties.Union(computedProperties)).ToList();
var keyPropertiesString = GetColumnsStringSqlServer(keyProperties, columns);
var keyPropertiesInsertedString = GetColumnsStringSqlServer(keyProperties, columns, "inserted.");
var allPropertiesExceptKeyAndComputedString = GetColumnsStringSqlServer(allPropertiesExceptKeyAndComputed, columns);
var allPropertiesString = GetColumnsStringSqlServer(allProperties, columns, "target.");
var tempToBeInserted = $"#{tableName}_TempInsert".Replace(".", string.Empty);
var tempInsertedWithIdentity = $"@{tableName}_TempInserted".Replace(".", string.Empty);
await connection.ExecuteAsync($@"SELECT TOP 0 {allPropertiesExceptKeyAndComputedString} INTO {tempToBeInserted} FROM {tableName} target WITH(NOLOCK);", null, transaction);
using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
bulkCopy.BulkCopyTimeout = bulkCopyTimeout;
bulkCopy.BatchSize = batchSize;
bulkCopy.DestinationTableName = tempToBeInserted;
await bulkCopy.WriteToServerAsync(ToDataTable(data, tableName, allPropertiesExceptKeyAndComputed).CreateDataReader());
}
var table = string.Join(", ", keyProperties.Select(k => $"[{k.Name }] bigint"));
var joinOn = string.Join(" AND ", keyProperties.Select(k => $"target.[{k.Name }] = ins.[{k.Name }]"));
return(await connection.QueryAsync <T>($@"
DECLARE {tempInsertedWithIdentity} TABLE ({table})
INSERT INTO {tableName}({allPropertiesExceptKeyAndComputedString})
OUTPUT {keyPropertiesInsertedString} INTO {tempInsertedWithIdentity} ({keyPropertiesString})
SELECT {allPropertiesExceptKeyAndComputedString} FROM {tempToBeInserted}
SELECT {allPropertiesString}
FROM {tableName} target INNER JOIN {tempInsertedWithIdentity} ins ON {joinOn}
DROP TABLE {tempToBeInserted};", null, transaction));
}
/// <summary>
/// Inserts entities into table <typeparamref name="T"/>s (by default) asynchronously.
/// </summary>
/// <typeparam name="T">The type being inserted.</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="data">Entities to insert</param>
/// <param name="transaction">The transaction to run under, null (the default) if none</param>
/// <param name="batchSize">Number of bulk items inserted together, 0 (the default) if all</param>
/// <param name="bulkCopyTimeout">Number of seconds before bulk command execution timeout, 30 (the default)</param>
/// <param name="identityInsert">Usage of db generated ids. By default DB generated IDs are used (identityInsert=false)</param>
public static async Task BulkInsertAsync <T>(this SqlConnection connection, IEnumerable <T> data, SqlTransaction transaction = null, int batchSize = 0, int bulkCopyTimeout = 30, bool identityInsert = false)
{
var type = typeof(T);
var tableName = TableMapper.GetTableName(type);
var allProperties = PropertiesCache.TypePropertiesCache(type);
var keyProperties = PropertiesCache.KeyPropertiesCache(type);
var computedProperties = PropertiesCache.ComputedPropertiesCache(type);
var columns = PropertiesCache.GetColumnNamesCache(type);
var insertProperties = allProperties.Except(computedProperties).ToList();
if (!identityInsert)
{
insertProperties = insertProperties.Except(keyProperties).ToList();
}
var(identityInsertOn, identityInsertOff, sqlBulkCopyOptions) = GetIdentityInsertOptions(identityInsert, tableName);
var insertPropertiesString = GetColumnsStringSqlServer(insertProperties, columns);
var tempToBeInserted = $"#TempInsert_{tableName}".Replace(".", string.Empty);
await connection.ExecuteAsync($@"SELECT TOP 0 {insertPropertiesString} INTO {tempToBeInserted} FROM {FormatTableName(tableName)} target WITH(NOLOCK);", null, transaction);
using (var bulkCopy = new SqlBulkCopy(connection, sqlBulkCopyOptions, transaction))
{
bulkCopy.BulkCopyTimeout = bulkCopyTimeout;
bulkCopy.BatchSize = batchSize;
bulkCopy.DestinationTableName = tempToBeInserted;
await bulkCopy.WriteToServerAsync(ToDataTable(data, insertProperties).CreateDataReader());
}
await connection.ExecuteAsync($@"
{identityInsertOn}
INSERT INTO {FormatTableName(tableName)}({insertPropertiesString})
SELECT {insertPropertiesString} FROM {tempToBeInserted}
{identityInsertOff}
DROP TABLE {tempToBeInserted};", null, transaction);
}