/// <summary>
/// Logic used to perform bulk inserts with SqlServer's BulkCopy
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="sqlSyntaxProvider"></param>
/// <param name="pd"></param>
/// <param name="collection"></param>
/// <returns></returns>
internal static int BulkInsertRecordsSqlServer <T>(Database db, SqlServerSyntaxProvider sqlSyntaxProvider,
Database.PocoData pd, IEnumerable <T> collection)
{
//NOTE: We need to use the original db.Connection here to create the command, but we need to pass in the typed
// connection below to the SqlBulkCopy
using (var cmd = db.CreateCommand(db.Connection, string.Empty))
{
using (var copy = new SqlBulkCopy(
GetTypedConnection <SqlConnection>(db.Connection),
SqlBulkCopyOptions.Default,
GetTypedTransaction <SqlTransaction>(cmd.Transaction))
{
BulkCopyTimeout = 10000,
DestinationTableName = pd.TableInfo.TableName
})
{
//var cols = pd.Columns.Where(x => IncludeColumn(pd, x)).Select(x => x.Value).ToArray();
using (var bulkReader = new PocoDataDataReader <T, SqlServerSyntaxProvider>(collection, pd, sqlSyntaxProvider))
{
copy.WriteToServer(bulkReader);
return(bulkReader.RecordsAffected);
}
}
}
}
/// <summary>
/// Bulk-insert records using SqlServer BulkCopy method.
/// </summary>
/// <typeparam name="T">The type of the records.</typeparam>
/// <param name="database">The database.</param>
/// <param name="pocoData">The PocoData object corresponding to the record's type.</param>
/// <param name="records">The records.</param>
/// <returns>The number of records that were inserted.</returns>
internal static int BulkInsertRecordsSqlServer <T>(IUmbracoDatabase database, PocoData pocoData, IEnumerable <T> records)
{
// create command against the original database.Connection
using (var command = database.CreateCommand(database.Connection, CommandType.Text, string.Empty))
{
// use typed connection and transactionf or SqlBulkCopy
var tConnection = GetTypedConnection <SqlConnection>(database.Connection);
var tTransaction = GetTypedTransaction <SqlTransaction>(command.Transaction);
var tableName = pocoData.TableInfo.TableName;
var syntax = database.SqlContext.SqlSyntax as SqlServerSyntaxProvider;
if (syntax == null)
{
throw new NotSupportedException("SqlSyntax must be SqlServerSyntaxProvider.");
}
using (var copy = new SqlBulkCopy(tConnection, SqlBulkCopyOptions.Default, tTransaction)
{
BulkCopyTimeout = 10000, DestinationTableName = tableName
})
using (var bulkReader = new PocoDataDataReader <T, SqlServerSyntaxProvider>(records, pocoData, syntax))
{
copy.WriteToServer(bulkReader);
return(bulkReader.RecordsAffected);
}
}
}
/// <summary>
/// Bulk-insert records using SqlServer BulkCopy method.
/// </summary>
/// <typeparam name="T">The type of the records.</typeparam>
/// <param name="database">The database.</param>
/// <param name="pocoData">The PocoData object corresponding to the record's type.</param>
/// <param name="records">The records.</param>
/// <returns>The number of records that were inserted.</returns>
internal static int BulkInsertRecordsSqlServer <T>(IUmbracoDatabase database, PocoData pocoData, IEnumerable <T> records)
{
// create command against the original database.Connection
using (var command = database.CreateCommand(database.Connection, CommandType.Text, string.Empty))
{
// use typed connection and transactionf or SqlBulkCopy
var tConnection = GetTypedConnection <SqlConnection>(database.Connection);
var tTransaction = GetTypedTransaction <SqlTransaction>(command.Transaction);
var tableName = pocoData.TableInfo.TableName;
var syntax = database.SqlContext.SqlSyntax as SqlServerSyntaxProvider;
if (syntax == null)
{
throw new NotSupportedException("SqlSyntax must be SqlServerSyntaxProvider.");
}
using (var copy = new SqlBulkCopy(tConnection, SqlBulkCopyOptions.Default, tTransaction)
{
BulkCopyTimeout = 10000, DestinationTableName = tableName
})
using (var bulkReader = new PocoDataDataReader <T, SqlServerSyntaxProvider>(records, pocoData, syntax))
{
//we need to add column mappings here because otherwise columns will be matched by their order and if the order of them are different in the DB compared
//to the order in which they are declared in the model then this will not work, so instead we will add column mappings by name so that this explicitly uses
//the names instead of their ordering.
foreach (var col in bulkReader.ColumnMappings)
{
copy.ColumnMappings.Add(col.DestinationColumn, col.DestinationColumn);
}
copy.WriteToServer(bulkReader);
return(bulkReader.RecordsAffected);
}
}
}
/// <summary>
/// Bulk-insert records using SqlServer BulkCopy method.
/// </summary>
/// <typeparam name="T">The type of the records.</typeparam>
/// <param name="database">The database.</param>
/// <param name="pocoData">The PocoData object corresponding to the record's type.</param>
/// <param name="records">The records.</param>
/// <returns>The number of records that were inserted.</returns>
internal static int BulkInsertRecordsSqlServer <T>(IUmbracoDatabase database, PocoData pocoData, IEnumerable <T> records)
{
// TODO: The main reason this exists is because the NPoco InsertBulk method doesn't return the number of items.
// It is worth investigating the performance of this vs NPoco's because we use a custom BulkDataReader
// which in theory should be more efficient than NPocos way of building up an in-memory DataTable.
// create command against the original database.Connection
using (var command = database.CreateCommand(database.Connection, CommandType.Text, string.Empty))
{
// use typed connection and transactionf or SqlBulkCopy
var tConnection = GetTypedConnection <SqlConnection>(database.Connection);
var tTransaction = GetTypedTransaction <SqlTransaction>(command.Transaction);
var tableName = pocoData.TableInfo.TableName;
var syntax = database.SqlContext.SqlSyntax as SqlServerSyntaxProvider;
if (syntax == null)
{
throw new NotSupportedException("SqlSyntax must be SqlServerSyntaxProvider.");
}
using (var copy = new SqlBulkCopy(tConnection, SqlBulkCopyOptions.Default, tTransaction)
{
BulkCopyTimeout = 0, // 0 = no bulk copy timeout. If a timeout occurs it will be an connection/command timeout.
DestinationTableName = tableName,
// be consistent with NPoco: https://github.com/schotime/NPoco/blob/5117a55fde57547e928246c044fd40bd00b2d7d1/src/NPoco.SqlServer/SqlBulkCopyHelper.cs#L50
BatchSize = 4096
})
using (var bulkReader = new PocoDataDataReader <T, SqlServerSyntaxProvider>(records, pocoData, syntax))
{
//we need to add column mappings here because otherwise columns will be matched by their order and if the order of them are different in the DB compared
//to the order in which they are declared in the model then this will not work, so instead we will add column mappings by name so that this explicitly uses
//the names instead of their ordering.
foreach (var col in bulkReader.ColumnMappings)
{
copy.ColumnMappings.Add(col.DestinationColumn, col.DestinationColumn);
}
copy.WriteToServer(bulkReader);
return(bulkReader.RecordsAffected);
}
}
}
