public SqlBulkCopy (string connectionString, SqlBulkCopyOptions copyOptions)
{
this.connection = new SqlConnection (connectionString);
this.copyOptions = copyOptions;
isLocalConnection = true;
throw new NotImplementedException ();
}
public static void BulkInsert(string connection, SqlBulkCopyOptions options, DataTable data)
{
//var retry = RetryPolicyFactory.GetDefaultSqlConnectionRetryPolicy();
var retry = RetryPolicy.DefaultExponential;
retry.ExecuteAction(() => ReliableSqlBulkCopy.Action(connection, options, data));
}
private static void Action(string connection, SqlBulkCopyOptions options, DataTable data)
{
using (var bulk = new SqlBulkCopy(connection, options))
{
bulk.DestinationTableName = data.TableName;
bulk.WriteToServer(data);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="SqlBulkCopyInternal"/> class.
/// </summary>
/// <param name="connectionString">Database connection string.</param>
/// <param name="copyOptions">Bulk copy options.</param>
public SqlBulkCopyInternal(string connectionString, SqlBulkCopyOptions copyOptions)
{
this.bulkCopy = new SqlBulkCopy(connectionString, copyOptions);
// Changing this to false causes all the data to be loaded into memory before
// sending to the server which is not a good idea for large data sets.
this.bulkCopy.EnableStreaming = true;
this.bulkCopy.BulkCopyTimeout = 0;
}
public MsSqlBulkCopy(string connectionString, SqlBulkCopyOptions options)
{
_inner = new SqlBulkCopy(connectionString, options);
_inner.SqlRowsCopied += (s, e) =>
{
BulkCopyEventArgs args = new BulkCopyEventArgs {Count = e.RowsCopied, Type = CopyEventType.Active};
OnRowsInserted(args);
e.Abort = args.Abort;
};
}
private void Insert(DataTable dt, SqlTransaction transaction, SqlBulkCopyOptions options, int batchSize)
{
using (dt)
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(transaction.Connection, options, transaction))
{
sqlBulkCopy.BatchSize = batchSize;
sqlBulkCopy.DestinationTableName = dt.TableName;
sqlBulkCopy.WriteToServer(dt);
}
}
}
public SqlBulkCopy(SqlConnection connection, SqlBulkCopyOptions copyOptions, SqlTransaction externalTransaction) : this(connection)
{
this._copyOptions = copyOptions;
if ((externalTransaction != null) && this.IsCopyOption(SqlBulkCopyOptions.UseInternalTransaction))
{
throw SQL.BulkLoadConflictingTransactionOption();
}
if (!this.IsCopyOption(SqlBulkCopyOptions.UseInternalTransaction))
{
this._externalTransaction = externalTransaction;
}
}
public SqlEntityBulkInserter(OutputContext output) {
_output = output;
_bulkCopyOptions = SqlBulkCopyOptions.Default;
TurnOptionOn(SqlBulkCopyOptions.TableLock);
TurnOptionOn(SqlBulkCopyOptions.UseInternalTransaction);
TurnOptionOff(SqlBulkCopyOptions.CheckConstraints);
TurnOptionOff(SqlBulkCopyOptions.FireTriggers);
TurnOptionOn(SqlBulkCopyOptions.KeepNulls);
_keys = output.Entity.GetPrimaryKey();
_outputKeysReader = new SqlEntityMatchingKeysReader(output, _keys);
_sqlUpdater = new SqlEntityUpdater(output);
}
/// <summary>
/// 批量插入
/// </summary>
private static void BulkCopy( SqlConnection connection, SqlTransaction transaction, string destTableName,
DataTable source, int batchSize, SqlBulkCopyOptions options ) {
using(
var sqlBulkCopy = new SqlBulkCopy( connection, options, transaction ) {
BulkCopyTimeout = 600,
NotifyAfter = source.Rows.Count,
BatchSize = batchSize,
DestinationTableName = destTableName
} ) {
foreach( DataColumn column in source.Columns )
sqlBulkCopy.ColumnMappings.Add( column.ColumnName, column.ColumnName );
sqlBulkCopy.WriteToServer( source );
transaction.Commit();
}
}
public SqlBulkCopy (string connectionString, SqlBulkCopyOptions copyOptions)
{
if (connectionString == null) {
throw new ArgumentNullException ("connectionString");
}
this.connection = new SqlConnection (connectionString);
isLocalConnection = true;
switch (copyOptions) {
case SqlBulkCopyOptions.Default:
this.copyOptions = copyOptions;
break;
default:
throw new NotImplementedException ("We don't know how to process non-default copyOptions.");
}
}
/// <summary>
/// Transfer data into a destination table.
/// </summary>
/// <param name="tableName">
/// The name of the table that will be written to.
/// If a query is not provided this will also be the name of the table that data is read from.</param>
/// <param name="sqlBulkCopyOptions">Options used by <see cref="SqlBulkCopy"/>.</param>
/// <param name="query">An optional query that will be used as the source of data.</param>
/// <param name="queryParams">Optional parameters that are used by the source data query.</param>
/// <param name="sqlTransaction">Optional <see cref="SqlTransaction"/> object that will be used to construct the <see cref="SqlBulkCopy"/>.</param>
/// <param name="sqlBulkCopyCustomizer">Optional delegate that can customize the <see cref="SqlBulkCopy"/> object.</param>
/// <param name="smartColumnMapping">
/// Passing true will cause source columns to be mapped to destination columns by name and computed columns to be ignored.
/// A value of false will result in ordinal based mapping of all columns. Defaults to true.
/// </param>
public void TransferData(string tableName, string query = null, SqlParameter[] queryParams = null, SqlBulkCopyOptions sqlBulkCopyOptions = SqlBulkCopyOptions.Default, SqlTransaction sqlTransaction = null, Action<SqlBulkCopy> sqlBulkCopyCustomizer = null, bool smartColumnMapping = true)
{
if (tableName == null)
throw new ArgumentNullException(nameof(tableName));
if (!tableName.Contains("["))
tableName = tableName.Split('.').Select(n => "[" + n + "]").Join(".");
using (var source = new SqlConnection(_sourceConnString))
using (var dest = new SqlConnection(_destConnString))
using (var bcp = new SqlBulkCopy(dest, sqlBulkCopyOptions, null))
using (var sourceCmd = source.CreateCommand())
using (source.Connect())
using (dest.Connect())
{
query = query.IsNullOrWhiteSpace() ? $"SELECT * FROM {tableName}" : query;
bcp.EnableStreaming = true;
bcp.DestinationTableName = tableName;
bcp.BulkCopyTimeout = (int)TransferTimeout.TotalSeconds;
sourceCmd.CommandType = CommandType.Text;
sourceCmd.CommandText = query;
sourceCmd.CommandTimeout = (int)TransferTimeout.TotalSeconds;
if (queryParams != null && queryParams.Any())
sourceCmd.Parameters.AddRange(queryParams);
using (var reader = sourceCmd.ExecuteReader())
{
if (smartColumnMapping)
{
var schema = reader.GetSchemaTable();
MapColumns(schema, bcp, dest);
}
if (sqlBulkCopyCustomizer != null)
sqlBulkCopyCustomizer(bcp);
bcp.WriteToServer(reader);
}
}
}
/// <summary>
/// 批量插入
/// </summary>
/// <param name="connection">连接</param>
/// <param name="destTableName">目标表名</param>
/// <param name="source">数据源</param>
/// <param name="batchSize">批大小</param>
/// <param name="options">批量选项</param>
public static int BulkCopy( SqlConnection connection, string destTableName, DataTable source,
int batchSize = 1000,SqlBulkCopyOptions options = SqlBulkCopyOptions.Default ) {
try {
if ( connection.State == ConnectionState.Closed )
connection.Open();
var transaction = connection.BeginTransaction();
try {
BulkCopy( connection, transaction, destTableName, source, batchSize, options );
}
catch( Exception ) {
transaction.Rollback();
throw;
}
}
finally {
connection.Close();
}
return source.Rows.Count;
}
/// <summary>
///
/// </summary>
/// <param name="copyOptions"></param>
/// <returns></returns>
public SqlBulkCopyContext CreateSqlBulkCopyContext(SqlBulkCopyOptions copyOptions)
{
if (this.Connection == null)
{
return new SqlBulkCopyContext(this.ConnectionString, copyOptions);
}
return new SqlBulkCopyContext(this.Connection as SqlConnection, copyOptions, this.Transaction as SqlTransaction);
}
private bool IsCopyOption(SqlBulkCopyOptions copyOption) {
return (_copyOptions & copyOption) == copyOption;
}
public SqlBulkCopy (SqlConnection connection, SqlBulkCopyOptions copyOptions, SqlTransaction externalTransaction)
{
throw new PlatformNotSupportedException (EXCEPTION_MESSAGE);
}
internal async Task InsertToTmpTableAsync(SqlConnection conn, SqlTransaction transaction, DataTable dt, bool bulkCopyEnableStreaming, int?bulkCopyBatchSize, int?bulkCopyNotifyAfter, int bulkCopyTimeout, SqlBulkCopyOptions sqlBulkCopyOptions)
{
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, sqlBulkCopyOptions, transaction))
{
bulkcopy.DestinationTableName = "#TmpTable";
SetSqlBulkCopySettings(bulkcopy, bulkCopyEnableStreaming,
bulkCopyBatchSize,
bulkCopyNotifyAfter, bulkCopyTimeout);
await bulkcopy.WriteToServerAsync(dt);
}
}
private static BulkInsertResult <T> BulkInsert <T>(IEnumerable <T> entities, BulkOptions options, TableMapping tableMapping, SqlConnection dbConnection, SqlTransaction transaction, string tableName,
string[] inputColumns = null, SqlBulkCopyOptions bulkCopyOptions = SqlBulkCopyOptions.Default, bool useInteralId = false)
{
var dataReader = new EntityDataReader <T>(tableMapping, entities, useInteralId);
var sqlBulkCopy = new SqlBulkCopy(dbConnection, bulkCopyOptions, transaction)
{
DestinationTableName = tableName,
BatchSize = options.BatchSize
};
if (options.CommandTimeout.HasValue)
{
sqlBulkCopy.BulkCopyTimeout = options.CommandTimeout.Value;
}
foreach (var column in dataReader.TableMapping.Columns)
{
if (inputColumns == null || (inputColumns != null && inputColumns.Contains(column.Column.Name)))
{
sqlBulkCopy.ColumnMappings.Add(column.Property.Name, column.Column.Name);
}
}
if (useInteralId)
{
sqlBulkCopy.ColumnMappings.Add(Constants.Guid_ColumnName, Constants.Guid_ColumnName);
}
sqlBulkCopy.WriteToServer(dataReader);
return(new BulkInsertResult <T> {
RowsAffected = Convert.ToInt32(sqlBulkCopy.GetPrivateFieldValue("_rowsCopied")),
EntityMap = dataReader.EntityMap
});
}
protected internal override void BulkCopy(DataTable dt, ObjectName destinationTable, SqlBulkCopyOptions options)
{
throw new NotImplementedException();
}
/// <summary>
///
/// </summary>
/// <param name="list"></param>
/// <param name="tableName"></param>
/// <param name="schema"></param>
/// <param name="columns"></param>
/// <param name="disableAllIndexes"></param>
/// <param name="customColumnMappings"></param>
/// <param name="sqlTimeout"></param>
/// <param name="bulkCopyTimeout"></param>
/// <param name="bulkCopyEnableStreaming"></param>
/// <param name="bulkCopyNotifyAfter"></param>
/// <param name="bulkCopyBatchSize"></param>
/// <param name="sqlBulkCopyOptions"></param>
/// <param name="disableIndexList"></param>
/// <param name="bulkCopyDelegates"></param>
public BulkDelete(IEnumerable <T> list, string tableName, string schema, HashSet <string> columns, HashSet <string> disableIndexList,
bool disableAllIndexes,
Dictionary <string, string> customColumnMappings, int sqlTimeout, int bulkCopyTimeout,
bool bulkCopyEnableStreaming, int?bulkCopyNotifyAfter, int?bulkCopyBatchSize, SqlBulkCopyOptions sqlBulkCopyOptions,
IEnumerable <SqlRowsCopiedEventHandler> bulkCopyDelegates)
:
base(list, tableName, schema, columns, disableIndexList, disableAllIndexes, customColumnMappings, sqlTimeout,
bulkCopyTimeout, bulkCopyEnableStreaming, bulkCopyNotifyAfter, bulkCopyBatchSize, sqlBulkCopyOptions,
bulkCopyDelegates)
{
_deletePredicates = new List <PredicateCondition>();
_parameters = new List <SqlParameter>();
_conditionSortOrder = 1;
}
/// <summary>
/// 大量批次新增(限對象為Sql Server)，使用SqlHelper的預設連線
/// </summary>
/// <param name="dtSource">資料來源的DataTable</param>
/// <param name="destDataTableName">目標資料庫的表格名稱</param>
/// <param name="optionSqlBulk">匯入時的選項</param>
public void SqlBulkCopyFromDataTable(DataTable dtSource, string destDBTableName, SqlBulkCopyOptions optionSqlBulk = SqlBulkCopyOptions.Default)
{
SqlBulkCopyFromDataTable(connectionString, dtSource, destDBTableName, optionSqlBulk);
}
/// <summary>
/// 大量批次新增(限對象為Sql Server)，工程師自行指定DB連線
/// </summary>
/// <param name="connectionString">DB連線字串</param>
/// <param name="dtSource">資料來源的DataTable</param>
/// <param name="destDataTableName">目標資料庫的表格名稱</param>
/// <param name="optionSqlBulk">匯入時的選項</param>
public void SqlBulkCopyFromDataTable(string connectionString, DataTable dtSource, string destDBTableName, SqlBulkCopyOptions optionSqlBulk = SqlBulkCopyOptions.Default)
{
if (string.IsNullOrEmpty(destDBTableName))
{
throw new Exception("缺少目標資料庫的表格名稱");
}
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open(); //開啟連線
//開始交易
SqlTransaction tran = conn.BeginTransaction();
//宣告SqlBulkCopy
using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn, optionSqlBulk, tran))
{
//設定一個批次量寫入多少筆資料
sqlBC.BatchSize = 1000;
//設定逾時的秒數
sqlBC.BulkCopyTimeout = 30;
//設定要寫入的資料表
sqlBC.DestinationTableName = destDBTableName;
foreach (DataColumn dataCol in dtSource.Columns)
{
//對應資料行
sqlBC.ColumnMappings.Add(dataCol.ColumnName, dataCol.ColumnName);
}//end foreach
//開始寫入新增
try
{
sqlBC.WriteToServer(dtSource);
tran.Commit();//交易提交
}
catch (SqlException ex)
{
tran.Rollback();//交易Rollback
throw ex;
}
} //end using
} //end using
}
/// <summary>
/// Updates the specified entities into the database asynchronous.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="connection">The connection to the database. This can either be open or closed.</param>
/// <param name="entities">The entity to be inserted.</param>
/// <param name="transaction">Optional transaction for the command.</param>
/// <param name="sqlBulkCopyOptions">The SQL bulk copy options.</param>
/// <param name="bulkCopyTimeout">The bulk copy timeout.</param>
/// <param name="bulkCopyBatchSize">Size of the bulk copy batch.</param>
/// <param name="bulkCopyNotifyAfter">The bulk copy notify after.</param>
/// <param name="bulkCopyEnableStreaming">if set to <c>true</c> [bulk copy enable streaming].</param>
/// <returns>
/// The id of the inserted entity.
/// </returns>
public static async Task BulkUpdatetAsync <TEntity>(
this IDbConnection connection,
IEnumerable <TEntity> entities,
IDbTransaction transaction = null,
SqlBulkCopyOptions sqlBulkCopyOptions = SqlBulkCopyOptions.Default,
int bulkCopyTimeout = 600,
int bulkCopyBatchSize = 5000,
int bulkCopyNotifyAfter = 1000,
bool bulkCopyEnableStreaming = false
) where TEntity : class
{
if (!entities.Any())
{
return;
}
var dataTable = GetTemporaryDataTable(typeof(TEntity), out var bulkMetadata, false).Shred(entities, bulkMetadata, null);
var sqlConnection = (SqlConnection)connection;
sqlConnection.Open();
if (transaction == null)
{
transaction = connection.BeginTransaction();
}
using (var trans = (SqlTransaction)transaction)
{
try
{
var command = sqlConnection.CreateCommand();
command.Connection = sqlConnection;
command.Transaction = trans;
command.CommandTimeout = 600;
CheckTemporaryTableQuery(connection, transaction, bulkMetadata, false);
//Creating temp table on database
command.CommandText = bulkMetadata.TempTableQuery;
await command.ExecuteNonQueryAsync();
//Bulk copy into temp table
await BulkCopyAsync <TEntity>(sqlConnection, (SqlTransaction)transaction, dataTable, sqlBulkCopyOptions,
bulkMetadata.Name, bulkCopyTimeout, bulkCopyBatchSize, bulkCopyNotifyAfter, bulkCopyEnableStreaming);
// Updating destination table, and dropping temp table
CheckUpdateTableQuery(bulkMetadata);
command.CommandText = bulkMetadata.UpdateQuery;
await command.ExecuteNonQueryAsync();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
finally
{
sqlConnection.Close();
}
}
}
private void LogInserting(SqlBulkCopyOptions options, SqlBulkCopy bulkCopy, string columns)
{
_logger.Logger.LogInformation(EventIds.Inserting, @"Executing DbCommand [SqlBulkCopyOptions={SqlBulkCopyOptions}, BulkCopyTimeout={BulkCopyTimeout}, BatchSize={BatchSize}, EnableStreaming={EnableStreaming}]
INSERT BULK {table} ({columns})", options, bulkCopy.BulkCopyTimeout, bulkCopy.BatchSize, bulkCopy.EnableStreaming,
bulkCopy.DestinationTableName, columns);
}
/// <summary>
///
/// </summary>
/// <param name="list"></param>
/// <param name="tableName"></param>
/// <param name="columns"></param>
/// <param name="schema"></param>
/// <param name="sourceAlias"></param>
/// <param name="targetAlias"></param>
/// <param name="sqlTimeout"></param>
/// <param name="bulkCopyTimeout"></param>
/// <param name="bulkCopyEnableStreaming"></param>
/// <param name="bulkCopyNotifyAfter"></param>
/// <param name="bulkCopyBatchSize"></param>
/// <param name="sqlBulkCopyOptions"></param>
/// <param name="ext"></param>
public AllColumnSelect(IEnumerable <T> list, string tableName, HashSet <string> columns, string schema, string sourceAlias, string targetAlias,
int sqlTimeout, int bulkCopyTimeout, bool bulkCopyEnableStreaming, int?bulkCopyNotifyAfter, int?bulkCopyBatchSize, SqlBulkCopyOptions sqlBulkCopyOptions,
BulkOperations ext)
{
_helper = new BulkOperationsHelpers();
_disableAllIndexes = false;
_disableIndexList = new HashSet <string>();
_list = list;
_columns = columns;
_tableName = tableName;
_schema = schema;
_sourceAlias = sourceAlias;
_targetAlias = targetAlias;
_sqlTimeout = sqlTimeout;
_bulkCopyTimeout = bulkCopyTimeout;
_bulkCopyEnableStreaming = bulkCopyEnableStreaming;
_bulkCopyNotifyAfter = bulkCopyNotifyAfter;
_bulkCopyBatchSize = bulkCopyBatchSize;
_sqlBulkCopyOptions = sqlBulkCopyOptions;
_ext = ext;
CustomColumnMappings = new Dictionary <string, string>();
_customColumnCollationMappings = new Dictionary <string, string>();
}
/// <summary>
/// Prepares the bulk copy operation.
/// </summary>
/// <param name="connection">The connection to use.</param>
/// <param name="tableName">The name of the table.</param>
/// <param name="reader">The reader to read objects from.</param>
/// <param name="configure">A callback method to configure the bulk copy object.</param>
/// <param name="options">Options for initializing the bulk copy object.</param>
/// <param name="transaction">An optional transaction to participate in.</param>
/// <returns>The configured bulk copy object.</returns>
private static SqlInsightBulkCopy PrepareBulkCopy(IDbConnection connection, string tableName, IDataReader reader, Action <InsightBulkCopy> configure, InsightBulkCopyOptions options, IDbTransaction transaction)
{
if (reader == null)
{
throw new ArgumentNullException("reader");
}
SqlBulkCopyOptions sqlOptions = SqlBulkCopyOptions.Default;
if (options.HasFlag(InsightBulkCopyOptions.KeepIdentity))
{
sqlOptions |= SqlBulkCopyOptions.KeepIdentity;
}
if (options.HasFlag(InsightBulkCopyOptions.FireTriggers))
{
sqlOptions |= SqlBulkCopyOptions.FireTriggers;
}
if (options.HasFlag(InsightBulkCopyOptions.CheckConstraints))
{
sqlOptions |= SqlBulkCopyOptions.CheckConstraints;
}
if (options.HasFlag(InsightBulkCopyOptions.TableLock))
{
sqlOptions |= SqlBulkCopyOptions.TableLock;
}
if (options.HasFlag(InsightBulkCopyOptions.KeepNulls))
{
sqlOptions |= SqlBulkCopyOptions.KeepNulls;
}
if (options.HasFlag(InsightBulkCopyOptions.UseInternalTransaction))
{
sqlOptions |= SqlBulkCopyOptions.UseInternalTransaction;
}
SqlBulkCopy bulk = null;
SqlInsightBulkCopy insightBulk = null;
try
{
bulk = new SqlBulkCopy((SqlConnection)connection, sqlOptions, (SqlTransaction)transaction);
bulk.DestinationTableName = tableName;
bulk.EnableStreaming = true;
// map the columns by name, in case we skipped a readonly column
for (int i = 0; i < reader.FieldCount; i++)
{
string fieldName = reader.GetName(i);
bulk.ColumnMappings.Add(fieldName, fieldName);
}
insightBulk = new SqlInsightBulkCopy(bulk);
bulk = null;
if (configure != null)
{
configure(insightBulk);
}
return(insightBulk);
}
catch
{
if (insightBulk != null)
{
insightBulk.Dispose();
}
if (bulk != null)
{
((IDisposable)bulk).Dispose();
}
throw;
}
}
public static void BulkCopy(DataTable dt, List <IColumn> column, ObjectName destinationTable, SqlBulkCopyOptions options, int?timeout)
{
Connector.Current.BulkCopy(dt, column, destinationTable, options, timeout);
}
public BulkOptions(int? batchSize = null, int? bulkCopyTimeout = null, SqlBulkCopyOptions sqlBulkCopyOptions = SqlBulkCopyOptions.Default,
FieldsSelector fieldsSelector = FieldsSelector.Source, bool? caseSensitive = null, bool? enableStreaming = null,
bool createTable = false, ColumnDefinitionOptions columnDefinitionOptions = null, bool? ignoreDataReaderSchemaTable = null,
bool? checkTableIfNotExistsBeforeCreation = null)
{
this.BatchSize = batchSize;
this.BulkCopyTimeout = bulkCopyTimeout;
this.SqlBulkCopyOptions = sqlBulkCopyOptions;
this.FieldsSelector = fieldsSelector;
this.CaseSensitive = caseSensitive;
this.EnableStreaming = enableStreaming;
this.CreateTable = createTable;
this.ColumnDefinitionOptions = columnDefinitionOptions;
this.IgnoreDataReaderSchemaTable = ignoreDataReaderSchemaTable;
this.CheckTableIfNotExistsBeforeCreation = checkTableIfNotExistsBeforeCreation;
}
public void Run <T>(IEnumerable <T> entities, IDbTransaction transaction, SqlBulkCopyOptions options, int batchSize)
{
Run(entities, (TTransaction)transaction, options, batchSize);
}
public SqlBulkCopy(string connectionString, SqlBulkCopyOptions copyOptions) : this(connectionString)
{
this._copyOptions = copyOptions;
}
//public abstract string ProviderName { get; }
public abstract void Run <T>(IEnumerable <T> entities, TTransaction transaction, SqlBulkCopyOptions options, int batchSize);
public SqlBulkCopy SetupSqlBulkCopy(IDev2DataListEvaluateIterator batchItr, IDev2IteratorCollection parametersIteratorCollection, IDev2DataListEvaluateIterator timeoutItr, DbSource runtimeDatabase, SqlBulkCopyOptions copyOptions)
{
var batchSize = -1;
var timeout = -1;
GetParameterValuesForBatchSizeAndTimeOut(batchItr, parametersIteratorCollection, timeoutItr, ref batchSize, ref timeout);
var sqlBulkCopy = new SqlBulkCopy(runtimeDatabase.ConnectionString, copyOptions)
{
DestinationTableName = TableName
};
if (batchSize != -1)
{
sqlBulkCopy.BatchSize = batchSize;
}
if (timeout != -1)
{
sqlBulkCopy.BulkCopyTimeout = timeout;
}
return(sqlBulkCopy);
}
private static SqlBulkCopy GetSqlBulkCopy(SqlConnection sqlConnection, IDbContextTransaction transaction, SqlBulkCopyOptions sqlBulkCopyOptions = SqlBulkCopyOptions.Default)
{
if (transaction == null)
{
return(new SqlBulkCopy(sqlConnection, sqlBulkCopyOptions, null));
}
else
{
var sqlTransaction = (SqlTransaction)transaction.GetDbTransaction();
return(new SqlBulkCopy(sqlConnection, sqlBulkCopyOptions, sqlTransaction));
}
}
public bool BulkCopy(IDataReader reader, string tableName, string command = null, SqlBulkCopyOptions options = SqlBulkCopyOptions.Default)
{
using (SqlConnection m_Conn = new SqlConnection(DataHelper.ConnectString))
{
SqlTransaction sqlT = null;
try
{
if (m_Conn.State == ConnectionState.Closed)
{
m_Conn.Open();
}
sqlT = m_Conn.BeginTransaction();
if (!string.IsNullOrEmpty(command))
{
SqlCommand cmd = new SqlCommand(command, m_Conn);
cmd.Transaction = sqlT;
cmd.ExecuteNonQuery();
}
SqlBulkCopy copy = new SqlBulkCopy(m_Conn, options, sqlT);
copy.DestinationTableName = tableName;
copy.BulkCopyTimeout = 100000;
//copy.BatchSize = _capacity;
copy.WriteToServer(reader); //如果写入失败，考虑不能无限增加线程数
//Clear();
sqlT.Commit();
m_Conn.Close();
return(true);
}
catch (Exception e)
{
if (sqlT != null)
{
sqlT.Rollback();
}
m_Conn.Close();
CallException(e.Message);
return(false);
}
}
}
/// <summary>
/// Creates the SQL bulk copy.
/// </summary>
/// <param name="copyOptions">The copy options.</param>
/// <returns></returns>
public SqlBulkCopy CreateSqlBulkCopy(SqlBulkCopyOptions copyOptions)
{
return(_connection.CreateSqlBulkCopy(copyOptions));
}
protected internal override void BulkCopy(DataTable dt, ObjectName destinationTable, SqlBulkCopyOptions options, int?timeout)
{
using (SqlConnection con = EnsureConnection())
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
options.HasFlag(SqlBulkCopyOptions.UseInternalTransaction) ? con : (SqlConnection)Transaction.CurrentConnection,
options,
options.HasFlag(SqlBulkCopyOptions.UseInternalTransaction) ? null : (SqlTransaction)Transaction.CurrentTransaccion))
using (HeavyProfiler.Log("SQL", () => destinationTable.ToString() + " Rows:" + dt.Rows.Count))
{
bulkCopy.BulkCopyTimeout = timeout ?? Connector.ScopeTimeout ?? this.CommandTimeout ?? bulkCopy.BulkCopyTimeout;
foreach (DataColumn c in dt.Columns)
{
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName));
}
bulkCopy.DestinationTableName = destinationTable.ToString();
bulkCopy.WriteToServer(dt);
}
}
/// <summary>
/// Bulk insert a list of data entity objects into the database.
/// </summary>
/// <typeparam name="TEntity">The type of the data entity object.</typeparam>
/// <param name="connection">The connection object to be used.</param>
/// <param name="entities">The list of the data entities to be bulk-inserted.</param>
/// <param name="mappings">The list of the columns to be used for mappings. If this parameter is not set, then all columns will be used for mapping.</param>
/// <param name="options">The bulk-copy options to be used.</param>
/// <param name="bulkCopyTimeout">The timeout in seconds to be used.</param>
/// <param name="batchSize">The size per batch to be used.</param>
/// <param name="transaction">The transaction to be used.</param>
/// <returns>The number of rows affected by the execution.</returns>
public int BulkInsert <TEntity>(IDbConnection connection,
IEnumerable <TEntity> entities,
IEnumerable <BulkInsertMapItem> mappings = null,
SqlBulkCopyOptions options = SqlBulkCopyOptions.Default,
int?bulkCopyTimeout = null,
int?batchSize = null,
IDbTransaction transaction = null)
where TEntity : class
{
// Validate the objects
ValidateConnection(connection);
ValidateTransaction(transaction);
DbConnectionExtension.ValidateTransactionConnectionObject(connection, transaction);
// Variables for the operation
var result = 0;
// Before Execution Time
var beforeExecutionTime = DateTime.UtcNow;
// Actual Execution
using (var reader = new DataEntityDataReader <TEntity>(entities, connection))
{
using (var sqlBulkCopy = new SqlBulkCopy((SqlConnection)connection, options, (SqlTransaction)transaction))
{
// Set the destinationtable
sqlBulkCopy.DestinationTableName = ClassMappedNameCache.Get <TEntity>();
// Set the timeout
if (bulkCopyTimeout != null && bulkCopyTimeout.HasValue)
{
sqlBulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value;
}
// Set the batch szie
if (batchSize != null && batchSize.HasValue)
{
sqlBulkCopy.BatchSize = batchSize.Value;
}
// Add the mappings
if (mappings == null)
{
// Get the actual DB fields
var dbFields = DbFieldCache.Get(connection, ClassMappedNameCache.Get <TEntity>(), transaction);
var fields = reader.Properties.AsFields();
var filteredFields = new List <Tuple <string, string> >();
// To fix the casing problem of the bulk inserts
foreach (var dbField in dbFields)
{
var field = fields.FirstOrDefault(f => string.Equals(f.UnquotedName, dbField.UnquotedName, StringComparison.OrdinalIgnoreCase));
if (field != null)
{
filteredFields.Add(new Tuple <string, string>(field.UnquotedName, dbField.UnquotedName));
}
}
// Iterate the filtered fields
foreach (var field in filteredFields)
{
sqlBulkCopy.ColumnMappings.Add(field.Item1, field.Item2);
}
}
else
{
// Iterate the provided mappings
foreach (var mapItem in mappings)
{
sqlBulkCopy.ColumnMappings.Add(mapItem.SourceColumn, mapItem.DestinationColumn);
}
}
// Open the connection and do the operation
connection.EnsureOpen();
sqlBulkCopy.WriteToServer(reader);
// Hack the 'SqlBulkCopy' object
var copiedField = GetRowsCopiedField();
// Set the return value
result = copiedField != null ? (int)copiedField.GetValue(sqlBulkCopy) : reader.RecordsAffected;
}
}
// Result
return(result);
}
public static void BulkCopy(DataTable dt, ObjectName destinationTable, SqlBulkCopyOptions options, int? timeout)
{
Connector.Current.BulkCopy(dt, destinationTable, options, timeout);
}
/// <summary>
/// Bulk insert an instance of <see cref="DbDataReader"/> object into the database in an asynchronous way.
/// </summary>
/// <param name="connection">The connection object to be used.</param>
/// <param name="tableName">The target table for bulk-insert operation.</param>
/// <param name="reader">The <see cref="DbDataReader"/> object to be used in the bulk-insert operation.</param>
/// <param name="mappings">The list of the columns to be used for mappings. If this parameter is not set, then all columns will be used for mapping.</param>
/// <param name="options">The bulk-copy options to be used.</param>
/// <param name="bulkCopyTimeout">The timeout in seconds to be used.</param>
/// <param name="batchSize">The size per batch to be used.</param>
/// <param name="transaction">The transaction to be used.</param>
/// <returns>The number of rows affected by the execution.</returns>
public async Task <int> BulkInsertAsync(IDbConnection connection,
string tableName,
DbDataReader reader,
IEnumerable <BulkInsertMapItem> mappings = null,
SqlBulkCopyOptions options = SqlBulkCopyOptions.Default,
int?bulkCopyTimeout = null,
int?batchSize = null,
IDbTransaction transaction = null)
{
// Validate the objects
ValidateConnection(connection);
ValidateTransaction(transaction);
DbConnectionExtension.ValidateTransactionConnectionObject(connection, transaction);
// Variables for the operation
var result = 0;
// Before Execution Time
var beforeExecutionTime = DateTime.UtcNow;
// Actual Execution
using (var sqlBulkCopy = new SqlBulkCopy((SqlConnection)connection, options, (SqlTransaction)transaction))
{
// Set the destinationtable
sqlBulkCopy.DestinationTableName = tableName;
// Set the timeout
if (bulkCopyTimeout != null && bulkCopyTimeout.HasValue)
{
sqlBulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value;
}
// Set the batch szie
if (batchSize != null && batchSize.HasValue)
{
sqlBulkCopy.BatchSize = batchSize.Value;
}
// Add the mappings
if (mappings == null)
{
// Get the actual DB fields
var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction);
var fields = Enumerable.Range(0, reader.FieldCount).Select((index) => reader.GetName(index));
var filteredFields = new List <Tuple <string, string> >();
// To fix the casing problem of the bulk inserts
foreach (var dbField in dbFields)
{
var readerField = fields.FirstOrDefault(field => string.Equals(field, dbField.UnquotedName, StringComparison.OrdinalIgnoreCase));
if (!string.IsNullOrEmpty(readerField))
{
filteredFields.Add(new Tuple <string, string>(readerField, dbField.UnquotedName));
}
}
// Iterate the filtered fields
foreach (var field in filteredFields)
{
sqlBulkCopy.ColumnMappings.Add(field.Item1, field.Item2);
}
}
else
{
// Iterate the provided mappings
foreach (var mapItem in mappings)
{
sqlBulkCopy.ColumnMappings.Add(mapItem.SourceColumn, mapItem.DestinationColumn);
}
}
// Open the connection and do the operation
await connection.EnsureOpenAsync();
await sqlBulkCopy.WriteToServerAsync(reader);
// Hack the 'SqlBulkCopy' object
var copiedField = GetRowsCopiedField();
// Set the return value
result = copiedField != null ? (int)copiedField.GetValue(sqlBulkCopy) : reader.RecordsAffected;
}
// Result
return(result);
}
public SqlBulkCopy (string connectionString, SqlBulkCopyOptions copyOptions)
{
throw new PlatformNotSupportedException (EXCEPTION_MESSAGE);
}
/// <summary>Returns <c>true</c> if the <paramref name="option"/> is turned on, otherwise <c>false</c></summary>
/// <param name="option">The <see cref="SqlBulkCopyOptions"/> option to test for.</param>
/// <returns></returns>
protected bool IsOptionOn(SqlBulkCopyOptions option)
{
return (bulkCopyOptions & option) == option;
}
bool IsOptionOn(SqlBulkCopyOptions option)
{
return((_bulkCopyOptions & option) == option);
}
/// <summary>Turns the <paramref name="option"/> off.</summary>
/// <param name="option"></param>
protected void TurnOptionOff(SqlBulkCopyOptions option)
{
if (IsOptionOn(option))
bulkCopyOptions ^= option;
}
protected internal abstract void BulkCopy(DataTable dt, ObjectName destinationTable, SqlBulkCopyOptions options, int? timeout);
public SqlBulkCopy (SqlConnection connection, SqlBulkCopyOptions copyOptions, SqlTransaction externalTransaction)
{
if (connection == null) {
throw new ArgumentNullException ("connection");
}
this.connection = connection;
this.copyOptions = copyOptions;
if ((copyOptions & SqlBulkCopyOptions.UseInternalTransaction) == SqlBulkCopyOptions.UseInternalTransaction) {
if (externalTransaction != null)
throw new ArgumentException (transConflictMessage);
}
else
this.externalTransaction = externalTransaction;
if ((copyOptions & SqlBulkCopyOptions.UseInternalTransaction) == SqlBulkCopyOptions.UseInternalTransaction)
throw new NotImplementedException ("We don't know how to process UseInternalTransaction option.");
this.copyOptions = copyOptions;
}
private bool IsCopyOption(SqlBulkCopyOptions copyOption)
{
return ((this._copyOptions & copyOption) == copyOption);
}
//教育 BulkCopy
private void DoBulkCopy(SqlTransaction oTran, DataTable srcData)
{
SqlBulkCopyOptions setting = SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.TableLock;
using (SqlBulkCopy sqlBC = new SqlBulkCopy(oTran.Connection, setting, oTran))
{
sqlBC.BulkCopyTimeout = 600; ///設定逾時的秒數
//sqlBC.BatchSize = 1000; ///設定一個批次量寫入多少筆資料, 設定值太小會影響效能
////設定 NotifyAfter 屬性，以便在每複製 10000 個資料列至資料表後，呼叫事件處理常式。
//sqlBC.NotifyAfter = 10000;
///設定要寫入的資料庫
sqlBC.DestinationTableName = "Education";
/// 對應來源與目標資料欄位 左邊：C# DataTable欄位 右邊：資料庫Table欄位
sqlBC.ColumnMappings.Add("Edu_CityNo", "Edu_CityNo");
sqlBC.ColumnMappings.Add("Edu_CityName", "Edu_CityName");
sqlBC.ColumnMappings.Add("Edu_15upJSDownRateYear", "Edu_15upJSDownRateYear");
sqlBC.ColumnMappings.Add("Edu_15upJSDownRate", "Edu_15upJSDownRate");
sqlBC.ColumnMappings.Add("Edu_15upHSRateYear", "Edu_15upHSRateYear");
sqlBC.ColumnMappings.Add("Edu_15upHSRate", "Edu_15upHSRate");
sqlBC.ColumnMappings.Add("Edu_15upUSUpRateYear", "Edu_15upUSUpRateYear");
sqlBC.ColumnMappings.Add("Edu_15upUSUpRate", "Edu_15upUSUpRate");
sqlBC.ColumnMappings.Add("Edu_ESStudentDropOutRateYear", "Edu_ESStudentDropOutRateYear");
sqlBC.ColumnMappings.Add("Edu_ESStudentDropOutRate", "Edu_ESStudentDropOutRate");
sqlBC.ColumnMappings.Add("Edu_JSStudentDropOutRateYear", "Edu_JSStudentDropOutRateYear");
sqlBC.ColumnMappings.Add("Edu_JSStudentDropOutRate", "Edu_JSStudentDropOutRate");
sqlBC.ColumnMappings.Add("Edu_ESStudentsYear", "Edu_ESStudentsYear");
sqlBC.ColumnMappings.Add("Edu_ESStudents", "Edu_ESStudents");
sqlBC.ColumnMappings.Add("Edu_JSStudentsYear", "Edu_JSStudentsYear");
sqlBC.ColumnMappings.Add("Edu_JSStudents", "Edu_JSStudents");
sqlBC.ColumnMappings.Add("Edu_HSStudentsYear", "Edu_HSStudentsYear");
sqlBC.ColumnMappings.Add("Edu_HSStudents", "Edu_HSStudents");
sqlBC.ColumnMappings.Add("Edu_ESToHSIndigenousYear", "Edu_ESToHSIndigenousYear");
sqlBC.ColumnMappings.Add("Edu_ESToHSIdigenous", "Edu_ESToHSIdigenous");
sqlBC.ColumnMappings.Add("Edu_ESToHSIndigenousRateYear", "Edu_ESToHSIndigenousRateYear");
sqlBC.ColumnMappings.Add("Edu_ESToHSIndigenousRate", "Edu_ESToHSIndigenousRate");
sqlBC.ColumnMappings.Add("Edu_ESJSNewInhabitantsYear", "Edu_ESJSNewInhabitantsYear");
sqlBC.ColumnMappings.Add("Edu_ESJSNewInhabitants", "Edu_ESJSNewInhabitants");
sqlBC.ColumnMappings.Add("Edu_ESJSNewInhabitantsRateYear", "Edu_ESJSNewInhabitantsRateYear");
sqlBC.ColumnMappings.Add("Edu_ESToJSNewInhabitantsRate", "Edu_ESToJSNewInhabitantsRate");
sqlBC.ColumnMappings.Add("Edu_ESJSTeachersYear", "Edu_ESJSTeachersYear");
sqlBC.ColumnMappings.Add("Edu_ESJSTeachers", "Edu_ESJSTeachers");
sqlBC.ColumnMappings.Add("Edu_ESJSTeachersOfStudentRateYear", "Edu_ESJSTeachersOfStudentRateYear");
sqlBC.ColumnMappings.Add("Edu_ESJSTeachersOfStudentRate", "Edu_ESJSTeachersOfStudentRate");
sqlBC.ColumnMappings.Add("Edu_BudgetYear", "Edu_BudgetYear");
sqlBC.ColumnMappings.Add("Edu_Budget", "Edu_Budget");
sqlBC.ColumnMappings.Add("Edu_BudgetUpRateYearDesc", "Edu_BudgetUpRateYearDesc");
sqlBC.ColumnMappings.Add("Edu_BudgetUpRate", "Edu_BudgetUpRate");
sqlBC.ColumnMappings.Add("Edu_ESToHSAvgBudgetYear", "Edu_ESToHSAvgBudgetYear");
sqlBC.ColumnMappings.Add("Edu_ESToHSAvgBudget", "Edu_ESToHSAvgBudget");
sqlBC.ColumnMappings.Add("Edu_ESJSPCNumYear", "Edu_ESJSPCNumYear");
sqlBC.ColumnMappings.Add("Edu_ESJSPCNum", "Edu_ESJSPCNum");
sqlBC.ColumnMappings.Add("Edu_ESJSAvgPCNumYear", "Edu_ESJSAvgPCNumYear");
sqlBC.ColumnMappings.Add("Edu_ESJSAvgPCNum", "Edu_ESJSAvgPCNum");
sqlBC.ColumnMappings.Add("Edu_HighschoolDownRemoteAreasYear", "Edu_HighschoolDownRemoteAreasYear");
sqlBC.ColumnMappings.Add("Edu_HighschoolDownRemoteAreas", "Edu_HighschoolDownRemoteAreas");
sqlBC.ColumnMappings.Add("Edu_CreateDate", "Edu_CreateDate");
sqlBC.ColumnMappings.Add("Edu_CreateID", "Edu_CreateID");
sqlBC.ColumnMappings.Add("Edu_CreateName", "Edu_CreateName");
sqlBC.ColumnMappings.Add("Edu_Status", "Edu_Status");
sqlBC.ColumnMappings.Add("Edu_Version", "Edu_Version");
/// 開始寫入資料
sqlBC.WriteToServer(srcData);
}
}
void TurnOptionOn(SqlBulkCopyOptions option)
{
_bulkCopyOptions |= option;
}
private static void ExecuteSqlBulkCopy(SqlTransaction transaction, SqlConnection sourceConnection, SqlConnection destinationConnection, SqlBulkCopyOptions copyOptions, string tableName, IEnumerable <string> columnNames)
{
using var sourceCommand = sourceConnection.CreateCommand();
sourceCommand.CommandText = SQL_SELECT.FormatInvariant(tableName);
var sourceTableReader = sourceCommand.ExecuteReader();
using var sqlBulkCopy = new SqlBulkCopy(destinationConnection, copyOptions, transaction)
{
BatchSize = BULKCOPY_BATCHSIZE,
BulkCopyTimeout = 0,
DestinationTableName = tableName,
EnableStreaming = true,
};
foreach (var columnName in columnNames)
{
_ = sqlBulkCopy.ColumnMappings.Add(columnName, columnName);
}
sqlBulkCopy.WriteToServer(sourceTableReader);
sourceTableReader.Close();
}
public SqlServerInsertBulk InsertBulk <TObject>(string tableName, IEnumerable <TObject> objects, SqlBulkCopyOptions options = SqlBulkCopyOptions.Default) where TObject : class
{
return(InsertBulk <TObject>(new SqlServerObjectName(tableName), objects, options));
}
protected virtual async Task BulkInsert <TEntity>(List <TEntity> entities, string table, int?batchSize, int?bulkCopyTimeout, bool keepIdentity, bool @async)
{
PublicHelper.CheckNull(entities);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
DataTable dtToWrite = ConvertToSqlBulkCopyDataTable(entities, typeDescriptor);
bool shouldCloseConnection = false;
SqlConnection conn = this.Session.CurrentConnection as SqlConnection;
try
{
SqlTransaction externalTransaction = null;
if (this.Session.IsInTransaction)
{
externalTransaction = this.Session.CurrentTransaction as SqlTransaction;
}
SqlBulkCopyOptions sqlBulkCopyOptions = SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.FireTriggers;
if (keepIdentity)
{
sqlBulkCopyOptions = SqlBulkCopyOptions.KeepIdentity | sqlBulkCopyOptions;
}
SqlBulkCopy sbc = new SqlBulkCopy(conn, sqlBulkCopyOptions, externalTransaction);
using (sbc)
{
for (int i = 0; i < dtToWrite.Columns.Count; i++)
{
var column = dtToWrite.Columns[i];
sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
if (batchSize != null)
{
sbc.BatchSize = batchSize.Value;
}
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
sbc.DestinationTableName = AppendTableName(dbTable);
if (bulkCopyTimeout != null)
{
sbc.BulkCopyTimeout = bulkCopyTimeout.Value;
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
shouldCloseConnection = true;
}
if (@async)
{
await sbc.WriteToServerAsync(dtToWrite);
}
else
{
sbc.WriteToServer(dtToWrite);
}
}
}
finally
{
if (conn != null)
{
if (shouldCloseConnection && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
}
protected internal override void BulkCopy(DataTable dt, ObjectName destinationTable, SqlBulkCopyOptions options, int? timeout)
{
using (SqlConnection con = EnsureConnection())
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
options.HasFlag(SqlBulkCopyOptions.UseInternalTransaction) ? con : (SqlConnection)Transaction.CurrentConnection,
options,
options.HasFlag(SqlBulkCopyOptions.UseInternalTransaction) ? null : (SqlTransaction)Transaction.CurrentTransaccion))
using (HeavyProfiler.Log("SQL", () => destinationTable.ToString() + " Rows:" + dt.Rows.Count))
{
bulkCopy.BulkCopyTimeout = timeout ?? Connector.ScopeTimeout ?? this.CommandTimeout ?? bulkCopy.BulkCopyTimeout;
foreach (DataColumn c in dt.Columns)
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName));
bulkCopy.DestinationTableName = destinationTable.ToString();
bulkCopy.WriteToServer(dt);
}
}
/// <summary>
/// Inserts the batch of records using bulk insert.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="dataTable">The data table.</param>
/// <param name="options">The options.</param>
/// <returns>SqlServerInsertBulk.</returns>
public SqlServerInsertBulk InsertBulk(string tableName, DataTable dataTable, SqlBulkCopyOptions options = SqlBulkCopyOptions.Default)
{
return(InsertBulk(new SqlServerObjectName(tableName), dataTable, options));
}
/// <summary>Sets the Sql bulk copy options.</summary>
/// <param name="sqlBulkCopyOptions">The Sql bulk copy options.</param>
/// <returns>A DapperPlusActionSet.</returns>
public DapperPlusEntityMapper SqlBulkCopyOptions(SqlBulkCopyOptions sqlBulkCopyOptions)
{
_sqlBulkCopyOptions = sqlBulkCopyOptions;
return this;
}
/// <summary>
/// Inserts or updates the specified entities into the database.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="connection">The connection to the database. This can either be open or closed.</param>
/// <param name="entities">The entity to be inserted.</param>
/// <param name="transaction">Optional transaction for the command.</param>
/// <param name="sqlBulkCopyOptions">The SQL bulk copy options.</param>
/// <param name="bulkCopyTimeout">The bulk copy timeout.</param>
/// <param name="bulkCopyBatchSize">Size of the bulk copy batch.</param>
/// <param name="bulkCopyNotifyAfter">The bulk copy notify after.</param>
/// <param name="bulkCopyEnableStreaming">if set to <c>true</c> [bulk copy enable streaming].</param>
/// <param name="deleteWhenNotMatched">if set to <c>true</c> [delete when not matched].</param>
/// <param name="outputIdentity">The output identity.</param>
public static void BulkInsertOrUpdate <TEntity>(
this IDbConnection connection,
IEnumerable <TEntity> entities,
IDbTransaction transaction = null,
SqlBulkCopyOptions sqlBulkCopyOptions = SqlBulkCopyOptions.Default,
int bulkCopyTimeout = 600,
int bulkCopyBatchSize = 5000,
int bulkCopyNotifyAfter = 1000,
bool bulkCopyEnableStreaming = false,
bool deleteWhenNotMatched = false,
bool?outputIdentity = null
) where TEntity : class
{
if (!entities.Any())
{
return;
}
Dictionary <int, TEntity> outputIdentityDic = outputIdentity.HasValue && outputIdentity.Value ?
new Dictionary <int, TEntity>() : null;
var dataTable = GetTemporaryDataTable(typeof(TEntity), out var bulkMetadata, outputIdentity).Shred(entities, bulkMetadata, null, outputIdentityDic);
var sqlConnection = (SqlConnection)connection;
sqlConnection.Open();
if (transaction == null)
{
transaction = connection.BeginTransaction();
}
using (var trans = (SqlTransaction)transaction)
{
try
{
var command = sqlConnection.CreateCommand();
command.Connection = sqlConnection;
command.Transaction = trans;
command.CommandTimeout = 600;
CheckTemporaryTableQuery(connection, transaction, bulkMetadata, true);
//Creating temp table on database
command.CommandText = bulkMetadata.TempTableQuery;
command.ExecuteNonQuery();
//Bulk copy into temp table
BulkCopy <TEntity>(sqlConnection, trans, dataTable, sqlBulkCopyOptions,
bulkMetadata.Name, bulkCopyTimeout, bulkCopyBatchSize, bulkCopyNotifyAfter, bulkCopyEnableStreaming);
// Updating destination table, and dropping temp table
CheckInsertOrUpdateTableQuery(bulkMetadata);
string outputCreateTableQuery = outputIdentity.HasValue && outputIdentity.Value ?
$"CREATE TABLE #{bulkMetadata.Name}InsertOutput(InternalId int, Id int); " : "";
string outputIdentityQuery = outputIdentity.HasValue && outputIdentity.Value ?
$"OUTPUT Source.InternalId, INSERTED.{bulkMetadata.IdentityColumn.Item1} INTO #{bulkMetadata.Name}InsertOutput(InternalId, {bulkMetadata.IdentityColumn.Item1}); " : ";";
string deleteWhenNotMatchedQuery = deleteWhenNotMatched ?
" WHEN NOT MATCHED BY SOURCE THEN DELETE " : " ";
var query = $"{outputCreateTableQuery} {bulkMetadata.UpdateQuery} {deleteWhenNotMatchedQuery} {outputIdentityQuery} DROP TABLE #{bulkMetadata.Name};";
command.CommandText = query;
command.ExecuteNonQuery();
if (outputIdentity.HasValue && outputIdentity.Value)
{
command.CommandText = $"SELECT InternalId, Id FROM #{bulkMetadata.Name}InsertOutput;";
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (outputIdentityDic.TryGetValue((int)reader[0], out var item))
{
Type type = item.GetType();
PropertyInfo prop = type.GetProperty(bulkMetadata.IdentityColumn.Item1);
prop.SetValue(item, reader[1], null);
}
}
}
}
command.CommandText = $"DROP TABLE #{bulkMetadata.Name}InsertOutput;";
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
finally
{
sqlConnection.Close();
}
}
}
/// <summary>Turns a <see cref="bulkCopyOptions"/> on or off depending on the value of <paramref name="on"/></summary>
/// <param name="option">The <see cref="SqlBulkCopyOptions"/> to turn on or off.</param>
/// <param name="on"><c>true</c> to set the <see cref="SqlBulkCopyOptions"/> <paramref name="option"/> on otherwise <c>false</c> to turn the <paramref name="option"/> off.</param>
protected void ToggleOption(SqlBulkCopyOptions option, bool on)
{
if (on)
{
TurnOptionOn(option);
}
else
{
TurnOptionOff(option);
}
}
public SqlBulkCopyContext(String connectionString, SqlBulkCopyOptions copyOptions)
{
ConnectionString = connectionString;
SqlBulkCopy = new SqlBulkCopy(connectionString, copyOptions);
}
/// <summary>Turns the <paramref name="option"/> on.</summary>
/// <param name="option"></param>
protected void TurnOptionOn(SqlBulkCopyOptions option)
{
bulkCopyOptions |= option;
}
public SqlBulkCopyContext(SqlConnection connection, SqlBulkCopyOptions copyOptions, SqlTransaction transaction)
{
Connection = connection;
Transaction = transaction;
SqlBulkCopy = new SqlBulkCopy(connection, copyOptions, transaction);
}
public SqlBulkCopy (string connectionString, SqlBulkCopyOptions copyOptions)
{
if (connectionString == null) {
throw new ArgumentNullException ("connectionString");
}
this.connection = new SqlConnection (connectionString);
isLocalConnection = true;
if ((copyOptions & SqlBulkCopyOptions.UseInternalTransaction) == SqlBulkCopyOptions.UseInternalTransaction)
throw new NotImplementedException ("We don't know how to process UseInternalTransaction option.");
this.copyOptions = copyOptions;
}
public static async Task TestAsync(string connStr, string dstTable, string dstTable2)
{
string sourceQuery = string.Format(sourceQueryTemplate, sourceTable);
string sourceQuery2 = string.Format(sourceQueryTemplate2, sourceTable2);
string initialQuery = string.Format(initialQueryTemplate, dstTable);
string initialQuery2 = string.Format(initialQueryTemplate2, dstTable2);
string getRowCountQuery = string.Format(getRowCountQueryTemplate, sourceTable);
string getRowCountQuery2 = string.Format(getRowCountQueryTemplate, sourceTable2);
using (SqlConnection dstConn = new SqlConnection(connStr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
await dstConn.OpenAsync();
Helpers.TryExecute(dstCmd, initialQuery);
Helpers.TryExecute(dstCmd, initialQuery2);
using (SqlConnection srcConn = new SqlConnection(connStr))
using (SqlCommand srcCmd = new SqlCommand(getRowCountQuery, srcConn))
{
await srcConn.OpenAsync();
try
{
int nRowsInSource = Convert.ToInt32(await srcCmd.ExecuteScalarAsync());
srcCmd.CommandText = getRowCountQuery2;
int nRowsInSource2 = Convert.ToInt32(await srcCmd.ExecuteScalarAsync());
srcCmd.CommandText = sourceQuery;
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(dstConn))
{
bulkCopy.DestinationTableName = dstTable;
// no hints
using (DbDataReader reader = await srcCmd.ExecuteReaderAsync())
{
await bulkCopy.WriteToServerAsync(reader);
}
// hint for 1 of 3 columns
using (DbDataReader reader = await srcCmd.ExecuteReaderAsync())
{
bulkCopy.ColumnOrderHints.Add("CustomerID", SortOrder.Ascending);
await bulkCopy.WriteToServerAsync(reader);
}
// hints for all 3 columns
// order of hints is not the same as column order in table
using (DbDataReader reader = await srcCmd.ExecuteReaderAsync())
{
bulkCopy.ColumnOrderHints.Add("ContactName", SortOrder.Descending);
bulkCopy.ColumnOrderHints.Add("CompanyName", SortOrder.Ascending);
await bulkCopy.WriteToServerAsync(reader);
}
// add column mappings
using (DbDataReader reader = await srcCmd.ExecuteReaderAsync())
{
bulkCopy.ColumnMappings.Add(0, 1);
bulkCopy.ColumnMappings.Add(1, 2);
bulkCopy.ColumnMappings.Add(2, 0);
await bulkCopy.WriteToServerAsync(reader);
}
}
// add copy options
SqlBulkCopyOptions copyOptions = SqlBulkCopyOptions.AllowEncryptedValueModifications
| SqlBulkCopyOptions.FireTriggers
| SqlBulkCopyOptions.KeepNulls;
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn, copyOptions, null))
{
bulkcopy.DestinationTableName = dstTable;
bulkcopy.ColumnOrderHints.Add("CustomerID", SortOrder.Ascending);
bulkcopy.ColumnOrderHints.Add("CompanyName", SortOrder.Ascending);
bulkcopy.ColumnOrderHints.Add("ContactName", SortOrder.Descending);
using (DbDataReader reader = await srcCmd.ExecuteReaderAsync())
{
await bulkcopy.WriteToServerAsync(reader);
}
const int nWriteToServerCalls = 5;
Helpers.VerifyResults(dstConn, dstTable, 3, nRowsInSource * nWriteToServerCalls);
// different tables
srcCmd.CommandText = sourceQuery2;
using (DbDataReader reader = await srcCmd.ExecuteReaderAsync())
{
bulkcopy.DestinationTableName = dstTable2;
bulkcopy.ColumnOrderHints.Clear();
bulkcopy.ColumnOrderHints.Add("LastName", SortOrder.Descending);
await bulkcopy.WriteToServerAsync(reader);
Helpers.VerifyResults(dstConn, dstTable2, 2, nRowsInSource2);
}
}
}
finally
{
DataTestUtility.DropTable(dstConn, dstTable);
DataTestUtility.DropTable(dstConn, dstTable2);
}
}
}
}
