/// <summary>
/// Specify a condition.
/// </summary>
/// <param name="expression"></param>
/// <param name="collation">Only explicitly set the collation if there is a collation conflict.</param>
/// <returns></returns>
/// <exception cref="SqlBulkToolsException"></exception>
public DeleteQueryReady <T> Where(Expression <Func <T, bool> > expression, string collation)
{
// _whereConditions list will only ever contain one element.
BulkOperationsHelper.AddPredicate(expression, PredicateType.Where, _whereConditions, _parameters,
_conditionSortOrder, Constants.UniqueParamIdentifier);
_conditionSortOrder++;
string leftName = BulkOperationsHelper.GetExpressionLeftName(expression, PredicateType.Or, "Collation");
_collationColumnDic.Add(leftName, collation);
return(new DeleteQueryReady <T>(_tableName, _schema, _conditionSortOrder,
_whereConditions, _parameters, _collationColumnDic));
}
/// <summary>
/// Specify a condition.
/// </summary>
/// <param name="expression"></param>
/// <param name="collation">Only explicitly set the collation if there is a collation conflict.</param>
/// <returns></returns>
/// <exception cref="SqlBulkToolsException"></exception>
public QueryUpdateReady <T> Where(Expression <Func <T, bool> > expression, string collation)
{
// _whereConditions list will only ever contain one element.
BulkOperationsHelper.AddPredicate(expression, PredicateType.Where, _whereConditions, _sqlParams,
_conditionSortOrder, Constants.UniqueParamIdentifier);
_conditionSortOrder++;
string leftName = BulkOperationsHelper.GetExpressionLeftName(expression, PredicateType.Or, "Collation");
_collationColumnDic.Add(BulkOperationsHelper.GetActualColumn(_customColumnMappings, leftName), collation);
return(new QueryUpdateReady <T>(_singleEntity, _tableName, _schema, _columns, _customColumnMappings,
_conditionSortOrder, _whereConditions, _sqlParams, _collationColumnDic, _propertyInfoList));
}
private string GetCommand(SqlConnection connection)
{
string comm = "MERGE INTO " + BulkOperationsHelper.GetFullQualifyingTableName(connection.Database, _schema, _tableName) + $" WITH ({_tableHint}) AS Target " +
"USING " + Constants.TempTableName + " AS Source " +
BulkOperationsHelper.BuildJoinConditionsForInsertOrUpdate(_matchTargetOn.ToArray(),
Constants.SourceAlias, Constants.TargetAlias, base._collationColumnDic, _nullableColumnDic) +
"WHEN MATCHED " + BulkOperationsHelper.BuildPredicateQuery(_matchTargetOn.ToArray(), _updatePredicates, Constants.TargetAlias, base._collationColumnDic) +
"THEN UPDATE " +
BulkOperationsHelper.BuildUpdateSet(_columns, Constants.SourceAlias, Constants.TargetAlias, _identityColumn) +
BulkOperationsHelper.GetOutputIdentityCmd(_identityColumn, _outputIdentity, Constants.TempOutputTableName,
OperationType.Update) + "; " +
"DROP TABLE " + Constants.TempTableName + ";";
return(comm);
}
private string GetQuery(SqlConnection connection)
{
var concatenatedQuery = _whereConditions.Concat(_andConditions).Concat(_orConditions).OrderBy(x => x.SortOrder);
string fullQualifiedTableName = BulkOperationsHelper.GetFullQualifyingTableName(connection.Database, _schema,
_tableName);
string batchQtyStart = _batchQuantity != null ? "DeleteMore:\n" : string.Empty;
string batchQty = _batchQuantity != null ? $"TOP ({_batchQuantity}) " : string.Empty;
string batchQtyRepeat = _batchQuantity != null ? $"\nIF @@ROWCOUNT != 0\ngoto DeleteMore" : string.Empty;
string comm = $"{batchQtyStart}DELETE {batchQty}FROM {fullQualifiedTableName} " +
$"{BulkOperationsHelper.BuildPredicateQuery(concatenatedQuery, _collationColumnDic, _customColumnMappings)}{batchQtyRepeat}";
return(comm);
}
private string GetMatchedTargetCmd()
{
// If user manually excludes every column, it's effectively the same as calling ExcludeAllColumnsFromUpdate() once.
if (_excludeFromUpdate.Count == _columns.Count)
{
_excludeAllColumnsFromUpdate = true;
}
if (_excludeAllColumnsFromUpdate)
{
return(string.Empty);
}
return("WHEN MATCHED " + BulkOperationsHelper.BuildPredicateQuery(_matchTargetOn.ToArray(), _updatePredicates, Constants.TargetAlias, base._collationColumnDic) +
"THEN UPDATE " +
BulkOperationsHelper.BuildUpdateSet(_columns, Constants.SourceAlias, Constants.TargetAlias, _identityColumn, _excludeFromUpdate, _bulkCopySettings));
}
/// <summary>
/// Exclude a property from the update statement. Useful for when you want to include CreatedDate or Guid for inserts only.
/// </summary>
/// <param name="columnName"></param>
/// <returns></returns>
public BulkInsertOrUpdate <T> ExcludeColumnFromUpdate(Expression <Func <T, object> > columnName)
{
var propertyName = BulkOperationsHelper.GetPropertyName(columnName);
if (propertyName == null)
{
throw new SqlBulkToolsException("ExcludeColumnFromUpdate column name can't be null");
}
if (!_columns.Contains(propertyName))
{
throw new SqlBulkToolsException("ExcludeColumnFromUpdate could not exclude column from update because column could not " +
"be recognised. Call AddAllColumns() or AddColumn() for this column first.");
}
_excludeFromUpdate.Add(propertyName);
return(this);
}
/// <summary>
/// At least one MatchTargetOn is required for correct configuration. MatchTargetOn is the matching clause for evaluating
/// each row in table. This is usally set to the unique identifier in the table (e.g. Id). Multiple MatchTargetOn members are allowed
/// for matching composite relationships.
/// </summary>
/// <param name="columnName"></param>
/// <returns></returns>
public SimpleUpsertQueryReady <T> MatchTargetOn(Expression <Func <T, object> > columnName)
{
var propertyName = BulkOperationsHelper.GetPropertyName(columnName);
if (propertyName == null)
{
throw new NullReferenceException("MatchTargetOn column name can't be null.");
}
_matchTargetOnSet.Add(propertyName);
if (!_columns.Contains(propertyName))
{
_columns.Add(propertyName);
}
return(this);
}
/// <summary>
/// Removes a column that you want to be excluded.
/// </summary>
/// <param name="columnName"></param>
/// <returns></returns>
/// <exception cref="SqlBulkToolsException"></exception>
public BulkAddColumnList <T> RemoveColumn(Expression <Func <T, object> > columnName)
{
var propertyName = BulkOperationsHelper.GetPropertyName(columnName);
if (_columns.Contains(propertyName))
{
_columns.Remove(propertyName);
}
else
{
throw new SqlBulkToolsException("Could not remove the column with name "
+ columnName +
". This could be because it's not a value or string type and therefore not included.");
}
return(this);
}
/// <summary>
///
/// </summary>
/// <param name="list"></param>
/// <param name="tableName"></param>
/// <param name="ext"></param>
public Table(IEnumerable <T> list, string tableName, BulkOperations ext)
{
_bulkCopyBatchSize = null;
_bulkCopyNotifyAfter = null;
_bulkCopyEnableStreaming = false;
_helper = new BulkOperationsHelper();
_tableName = tableName;
_sqlTimeout = 600;
_bulkCopyTimeout = 600;
_ext = ext;
_list = list;
_schema = Constants.DefaultSchemaName;
Columns = new HashSet <string>();
UpdateOnList = new List <string>();
DeleteOnList = new List <string>();
CustomColumnMappings = new Dictionary <string, string>();
_sqlBulkCopyOptions = SqlBulkCopyOptions.Default;
}
/// <summary>
/// Commits a transaction to database asynchronously. A valid setup must exist for the operation to be
/// successful.
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
public async Task <int> CommitAsync(SqlConnection connection)
{
int affectedRows = 0;
if (_singleEntity == null)
{
return(affectedRows);
}
if (connection.State == ConnectionState.Closed)
{
await connection.OpenAsync();
}
SqlCommand command = connection.CreateCommand();
command.Connection = connection;
command.CommandTimeout = _sqlTimeout;
string fullQualifiedTableName = BulkOperationsHelper.GetFullQualifyingTableName(connection.Database, _schema,
_tableName);
BulkOperationsHelper.AddSqlParamsForQuery(_sqlParams, _columns, _singleEntity, customColumns: _customColumnMappings);
var concatenatedQuery = _whereConditions.Concat(_andConditions).Concat(_orConditions).OrderBy(x => x.SortOrder);
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _columns);
string comm = $"UPDATE {fullQualifiedTableName} " +
$"{BulkOperationsHelper.BuildUpdateSet(_columns, null, _identityColumn)}" +
$"{BulkOperationsHelper.BuildPredicateQuery(concatenatedQuery, _collationColumnDic)}";
command.CommandText = comm;
if (_sqlParams.Count > 0)
{
command.Parameters.AddRange(_sqlParams.ToArray());
}
affectedRows = await command.ExecuteNonQueryAsync();
return(affectedRows);
}
/// <summary>
///
/// </summary>
/// <param name="columnName"></param>
/// <exception cref="SqlBulkToolsException"></exception>
protected void SetIdentity(Expression <Func <T, object> > columnName)
{
var propertyName = BulkOperationsHelper.GetPropertyName(columnName);
if (propertyName == null)
{
throw new SqlBulkToolsException("SetIdentityColumn column name can't be null");
}
if (_identityColumn == null)
{
_identityColumn = propertyName;
}
else
{
throw new SqlBulkToolsException("Can't have more than one identity column");
}
}
private string GetQuery(SqlConnection connection)
{
var fullQualifiedTableName = BulkOperationsHelper.GetFullQualifyingTableName(connection.Database, _schema,
_tableName);
BulkOperationsHelper.AddSqlParamsForQuery(_propertyInfoList, _sqlParams, _columns, _singleEntity, customColumns: _customColumnMappings);
var concatenatedQuery = _whereConditions.Concat(_andConditions).Concat(_orConditions).OrderBy(x => x.SortOrder);
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _columns);
var batchQtyStart = _batchQuantity != null ? "UpdateMore:\n" : string.Empty;
var batchQty = _batchQuantity != null ? $"TOP ({_batchQuantity}) " : string.Empty;
var batchQtyRepeat = _batchQuantity != null ? "\nIF @@ROWCOUNT != 0\ngoto UpdateMore" : string.Empty;
var comm = $"{batchQtyStart}UPDATE {batchQty}{fullQualifiedTableName} " +
$"{BulkOperationsHelper.BuildUpdateSet(_columns, null, _identityColumn)}" +
$"{BulkOperationsHelper.BuildPredicateQuery(concatenatedQuery, _collationColumnDic, _customColumnMappings)}{batchQtyRepeat}";
return(comm);
}
/// <summary>
/// Sets the identity column for the table.
/// </summary>
/// <param name="columnName"></param>
/// <returns></returns>
public QueryUpdateReady <T> SetIdentityColumn(Expression <Func <T, object> > columnName)
{
var propertyName = BulkOperationsHelper.GetPropertyName(columnName);
if (propertyName == null)
{
throw new SqlBulkToolsException("SetIdentityColumn column name can't be null");
}
if (_identityColumn == null)
{
_identityColumn = BulkOperationsHelper.GetActualColumn(_customColumnMappings, propertyName);
}
else
{
throw new SqlBulkToolsException("Can't have more than one identity column");
}
return(this);
}
/// <summary>
/// At least one MatchTargetOn is required for correct configuration. MatchTargetOn is the matching clause for evaluating
/// each row in table. This is usally set to the unique identifier in the table (e.g. Id). Multiple MatchTargetOn members are allowed
/// for matching composite relationships.
/// </summary>
/// <param name="columnName"></param>
/// <param name="collation">Only explicitly set the collation if there is a collation conflict.</param>
/// <returns></returns>
public QueryUpsertReady <T> MatchTargetOn(Expression <Func <T, object> > columnName, string collation)
{
var propertyName = BulkOperationsHelper.GetPropertyName(columnName);
if (propertyName == null)
{
throw new NullReferenceException("MatchTargetOn column name can't be null.");
}
_matchTargetOn.Add(propertyName);
if (collation == null)
{
throw new SqlBulkToolsException("Collation can't be null");
}
_collationColumnDic.Add(BulkOperationsHelper.GetActualColumn(_customColumnMappings, propertyName), collation);
return(this);
}
#pragma warning restore CS1591 // Missing XML comment for publicly visible type or member
/// <summary>
///
/// </summary>
/// <param name="list"></param>
/// <param name="tableName"></param>
/// <param name="columns"></param>
/// <param name="schema"></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>
protected AbstractColumnSelect(IEnumerable <T> list, string tableName, HashSet <string> columns, string schema,
int sqlTimeout, int bulkCopyTimeout, bool bulkCopyEnableStreaming, int?bulkCopyNotifyAfter, int?bulkCopyBatchSize, SqlBulkCopyOptions sqlBulkCopyOptions,
BulkOperations ext)
{
_helper = new BulkOperationsHelper();
_disableAllIndexes = false;
_disableIndexList = new HashSet <string>();
_customColumnMappings = new Dictionary <string, string>();
_list = list;
_tableName = tableName;
_columns = columns;
_schema = schema;
_sqlTimeout = sqlTimeout;
_bulkCopyTimeout = bulkCopyTimeout;
_bulkCopyEnableStreaming = bulkCopyEnableStreaming;
_bulkCopyNotifyAfter = bulkCopyNotifyAfter;
_bulkCopyBatchSize = bulkCopyBatchSize;
_sqlBulkCopyOptions = sqlBulkCopyOptions;
_ext = ext;
}
/// <summary>
/// Sets the identity column for the table.
/// </summary>
/// <param name="columnName"></param>
/// <returns></returns>
public SimpleUpsertQueryReady <T> SetIdentityColumn(Expression <Func <T, object> > columnName)
{
var propertyName = BulkOperationsHelper.GetPropertyName(columnName);
if (propertyName == null)
{
throw new SqlBulkToolsException("SetIdentityColumn column name can't be null");
}
if (_identityColumn == null)
{
_identityColumn = propertyName;
}
else
{
throw new SqlBulkToolsException("Can't have more than one identity column");
}
return(this);
}
private string GetCommand(SqlConnection connection)
{
string comm =
GetSetIdentityCmd(on: true) +
"MERGE INTO " + BulkOperationsHelper.GetFullQualifyingTableName(connection.Database, _schema, _tableName) +
$" WITH ({_tableHint}) AS Target " +
"USING " + Constants.TempTableName + " AS Source " +
BulkOperationsHelper.BuildJoinConditionsForInsertOrUpdate(_matchTargetOn.ToArray(),
Constants.SourceAlias, Constants.TargetAlias, base._collationColumnDic, _nullableColumnDic) +
GetMatchedTargetCmd() +
"WHEN NOT MATCHED BY TARGET THEN " +
BulkOperationsHelper.BuildMergeInsert(_columns, Constants.SourceAlias, _identityColumn, _bulkCopySettings) +
(_deleteWhenNotMatchedFlag ? " WHEN NOT MATCHED BY SOURCE " + BulkOperationsHelper.BuildPredicateQuery(_matchTargetOn.ToArray(),
_deletePredicates, Constants.TargetAlias, base._collationColumnDic) +
"THEN DELETE " : " ") +
BulkOperationsHelper.GetOutputIdentityCmd(_identityColumn, _outputIdentity, Constants.TempOutputTableName,
OperationType.InsertOrUpdate) + "; " +
GetSetIdentityCmd(on: false) +
"DROP TABLE " + Constants.TempTableName + ";";
return(comm);
}
/// <summary>
/// Commits a transaction to database asynchronously. A valid setup must exist for the operation to be
/// successful.
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
public async Task <int> CommitAsync(SqlConnection connection, SqlTransaction transaction)
{
int affectedRows = 0;
if (!_list.Any())
{
return(affectedRows);
}
DataTable dt = BulkOperationsHelper.CreateDataTable <T>(_propertyInfoList, _columns, _customColumnMappings, _ordinalDic, _matchTargetOn, _outputIdentity);
dt = BulkOperationsHelper.ConvertListToDataTable(_propertyInfoList, dt, _list, _columns, _ordinalDic);
// Must be after ToDataTable is called.
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _columns, _matchTargetOn);
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync();
}
DataTable dtCols = null;
if (_outputIdentity == ColumnDirectionType.InputOutput)
{
dtCols = BulkOperationsHelper.GetDatabaseSchema(bulk, connection, _schema, _tableName);
}
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection, _bulkCopySettings.SqlBulkCopyOptions, transaction))
{
bulkcopy.DestinationTableName = BulkOperationsHelper.GetFullQualifyingTableName(connection.Database, _schema, _tableName);
BulkOperationsHelper.MapColumns(bulkcopy, _columns, _customColumnMappings);
BulkOperationsHelper.SetSqlBulkCopySettings(bulkcopy, _bulkCopySettings);
SqlCommand command = connection.CreateCommand();
command.Connection = connection;
command.CommandTimeout = _sqlTimeout;
command.Transaction = transaction;
if (_disableAllIndexes)
{
command.CommandText = BulkOperationsHelper.GetIndexManagementCmd(Constants.Disable, _tableName,
_schema, connection);
await command.ExecuteNonQueryAsync();
}
// If InputOutput identity is selected, must use staging table.
if (_outputIdentity == ColumnDirectionType.InputOutput && dtCols != null)
{
command.CommandText = BulkOperationsHelper.BuildCreateTempTable(_columns, dtCols, _outputIdentity);
await command.ExecuteNonQueryAsync();
BulkOperationsHelper.InsertToTmpTable(connection, dt, _bulkCopySettings, transaction);
command.CommandText = BulkOperationsHelper.GetInsertIntoStagingTableCmd(connection, _schema, _tableName,
_columns, _identityColumn, _outputIdentity);
await command.ExecuteNonQueryAsync();
BulkOperationsHelper.LoadFromTmpOutputTable(command, _identityColumn, _outputIdentityDic, OperationType.Insert, _list);
}
else
{
await bulkcopy.WriteToServerAsync(dt);
}
if (_disableAllIndexes)
{
command.CommandText = BulkOperationsHelper.GetIndexManagementCmd(Constants.Rebuild, _tableName,
_schema, connection);
await command.ExecuteNonQueryAsync();
}
bulkcopy.Close();
affectedRows = dt.Rows.Count;
return(affectedRows);
}
}
/// <summary>
/// Commits a transaction to database asynchronously. A valid setup must exist for the operation to be
/// successful.
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
/// <exception cref="SqlBulkToolsException"></exception>
/// <exception cref="IdentityException"></exception>
public async Task <int> CommitAsync(SqlConnection connection)
{
int affectedRows = 0;
if (!_list.Any())
{
return(affectedRows);
}
if (!_deleteWhenNotMatchedFlag && _deletePredicates.Count > 0)
{
throw new SqlBulkToolsException($"{BulkOperationsHelper.GetPredicateMethodName(PredicateType.Delete)} only usable on BulkInsertOrUpdate " +
$"method when 'DeleteWhenNotMatched' is set to true.");
}
base.IndexCheck();
base.MatchTargetCheck();
DataTable dt = BulkOperationsHelper.CreateDataTable <T>(_columns, _customColumnMappings, _matchTargetOn, _outputIdentity);
dt = BulkOperationsHelper.ConvertListToDataTable(dt, _list, _columns, _outputIdentityDic);
// Must be after ToDataTable is called.
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _columns, _matchTargetOn);
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _deletePredicates);
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _updatePredicates);
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync();
}
var dtCols = BulkOperationsHelper.GetDatabaseSchema(connection, _schema, _tableName);
try
{
SqlCommand command = connection.CreateCommand();
command.Connection = connection;
command.CommandTimeout = _sqlTimeout;
//Creating temp table on database
command.CommandText = BulkOperationsHelper.BuildCreateTempTable(_columns, dtCols, _outputIdentity);
await command.ExecuteNonQueryAsync();
BulkOperationsHelper.InsertToTmpTable(connection, dt, _bulkCopyEnableStreaming, _bulkCopyBatchSize,
_bulkCopyNotifyAfter, _bulkCopyTimeout, _sqlBulkCopyOptions, _bulkCopyDelegates);
if (_disableIndexList != null && _disableIndexList.Any())
{
command.CommandText = BulkOperationsHelper.GetIndexManagementCmd(Constants.Disable, _tableName,
_schema, connection, _disableIndexList, _disableAllIndexes);
await command.ExecuteNonQueryAsync();
}
string comm = BulkOperationsHelper.GetOutputCreateTableCmd(_outputIdentity, Constants.TempOutputTableName,
OperationType.InsertOrUpdate, _identityColumn);
if (!string.IsNullOrWhiteSpace(comm))
{
command.CommandText = comm;
await command.ExecuteNonQueryAsync();
}
comm =
"MERGE INTO " + BulkOperationsHelper.GetFullQualifyingTableName(connection.Database, _schema, _tableName) +
" WITH (HOLDLOCK) AS Target " +
"USING " + Constants.TempTableName + " AS Source " +
BulkOperationsHelper.BuildJoinConditionsForInsertOrUpdate(_matchTargetOn.ToArray(),
Constants.SourceAlias, Constants.TargetAlias, base._collationColumnDic) +
"WHEN MATCHED " + BulkOperationsHelper.BuildPredicateQuery(_matchTargetOn.ToArray(), _updatePredicates, Constants.TargetAlias, base._collationColumnDic) +
"THEN UPDATE " +
BulkOperationsHelper.BuildUpdateSet(_columns, Constants.SourceAlias, Constants.TargetAlias, _identityColumn, _excludeFromUpdate) +
"WHEN NOT MATCHED BY TARGET THEN " +
BulkOperationsHelper.BuildInsertSet(_columns, Constants.SourceAlias, _identityColumn) +
(_deleteWhenNotMatchedFlag ? " WHEN NOT MATCHED BY SOURCE " + BulkOperationsHelper.BuildPredicateQuery(_matchTargetOn.ToArray(),
_deletePredicates, Constants.TargetAlias, base._collationColumnDic) +
"THEN DELETE " : " ") +
BulkOperationsHelper.GetOutputIdentityCmd(_identityColumn, _outputIdentity, Constants.TempOutputTableName,
OperationType.InsertOrUpdate) + "; " +
"DROP TABLE " + Constants.TempTableName + ";";
command.CommandText = comm;
if (_parameters.Count > 0)
{
command.Parameters.AddRange(_parameters.ToArray());
}
affectedRows = await command.ExecuteNonQueryAsync();
if (_disableIndexList != null && _disableIndexList.Any())
{
command.CommandText = BulkOperationsHelper.GetIndexManagementCmd(Constants.Rebuild,
_tableName, _schema, connection, _disableIndexList);
await command.ExecuteNonQueryAsync();
}
if (_outputIdentity == ColumnDirectionType.InputOutput)
{
await BulkOperationsHelper.LoadFromTmpOutputTableAsync(command, _identityColumn, _outputIdentityDic, OperationType.InsertOrUpdate, _list);
}
return(affectedRows);
}
catch (SqlException e)
{
for (int i = 0; i < e.Errors.Count; i++)
{
// Error 8102 is identity error.
if (e.Errors[i].Number == 8102)
{
// Expensive but neccessary to inform user of an important configuration setup.
throw new IdentityException(e.Errors[i].Message);
}
}
throw;
}
}
/// <summary>
/// Commits a transaction to database. A valid setup must exist for the operation to be
/// successful.
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
/// <exception cref="IdentityException"></exception>
public int Commit(SqlConnection connection, SqlTransaction transaction)
{
int affectedRows = 0;
if (!_list.Any())
{
return(affectedRows);
}
base.MatchTargetCheck();
DataTable dt = BulkOperationsHelper.CreateDataTable <T>(_propertyInfoList, _columns, _customColumnMappings, _ordinalDic, _matchTargetOn, _outputIdentity);
dt = BulkOperationsHelper.ConvertListToDataTable(_propertyInfoList, dt, _list, _columns, _ordinalDic, _outputIdentityDic);
// Must be after ToDataTable is called.
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _columns, _matchTargetOn);
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _updatePredicates);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
var dtCols = BulkOperationsHelper.GetDatabaseSchema(bulk, connection, _schema, _tableName);
try
{
SqlCommand command = connection.CreateCommand();
command.Connection = connection;
command.CommandTimeout = _sqlTimeout;
command.Transaction = transaction;
_nullableColumnDic = BulkOperationsHelper.GetNullableColumnDic(dtCols);
//Creating temp table on database
command.CommandText = BulkOperationsHelper.BuildCreateTempTable(_columns, dtCols, _outputIdentity);
command.ExecuteNonQuery();
//Bulk insert into temp table
BulkOperationsHelper.InsertToTmpTable(connection, dt, _bulkCopySettings, transaction);
string comm = BulkOperationsHelper.GetOutputCreateTableCmd(_outputIdentity, Constants.TempOutputTableName,
OperationType.InsertOrUpdate, _identityColumn);
if (!string.IsNullOrWhiteSpace(comm))
{
command.CommandText = comm;
command.ExecuteNonQuery();
}
comm = GetCommand(connection);
command.CommandText = comm;
if (_parameters.Count > 0)
{
command.Parameters.AddRange(_parameters.ToArray());
}
affectedRows = command.ExecuteNonQuery();
if (_outputIdentity == ColumnDirectionType.InputOutput)
{
BulkOperationsHelper.LoadFromTmpOutputTable(command, _identityColumn, _outputIdentityDic, OperationType.InsertOrUpdate, _list);
}
return(affectedRows);
}
catch (SqlException e)
{
for (int i = 0; i < e.Errors.Count; i++)
{
// Error 8102 is identity error.
if (e.Errors[i].Number == 8102)
{
// Expensive call but neccessary to inform user of an important configuration setup.
throw new IdentityException(e.Errors[i].Message);
}
}
throw;
}
}
/// <summary>
/// Specify an additional condition to match on.
/// </summary>
/// <param name="expression">Only explicitly set the collation if there is a collation conflict.</param>
/// <returns></returns>
/// <exception cref="SqlBulkToolsException"></exception>
public QueryUpdateReady <T> And(Expression <Func <T, bool> > expression)
{
BulkOperationsHelper.AddPredicate(expression, PredicateType.And, _andConditions, _sqlParams, _conditionSortOrder, appendParam: Constants.UniqueParamIdentifier);
_conditionSortOrder++;
return(this);
}
DataTable IDataTableTransaction.BuildDataTable()
{
return(BulkOperationsHelper.ConvertListToDataTable(_propertyInfoList, _dt, _list, _columns, _ordinalDic));
}
/// <summary>
/// Commits a transaction to database asynchronously. A valid setup must exist for the operation to be
/// successful.
/// </summary>
/// <param name="conn"></param>
/// <returns></returns>
/// <exception cref="NullReferenceException"></exception>
/// <exception cref="IdentityException"></exception>
public async Task <int> CommitAsync(SqlConnection conn)
{
int affectedRows = 0;
if (_singleEntity == null)
{
return(affectedRows);
}
if (_matchTargetOnSet.Count == 0)
{
throw new NullReferenceException("MatchTargetOn is a mandatory for upsert operation");
}
try
{
BulkOperationsHelper.AddSqlParamsForQuery(_sqlParams, _columns, _singleEntity, _identityColumn, _outputIdentity, _customColumnMappings);
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _columns);
if (conn.State == ConnectionState.Closed)
{
await conn.OpenAsync();
}
SqlCommand command = conn.CreateCommand();
command.Connection = conn;
command.CommandTimeout = _sqlTimeout;
string fullQualifiedTableName = BulkOperationsHelper.GetFullQualifyingTableName(conn.Database, _schema, _tableName);
command.CommandText = $"UPDATE {fullQualifiedTableName} {BulkOperationsHelper.BuildUpdateSet(_columns, _excludeFromUpdate, _identityColumn)}" +
$"{(_outputIdentity == ColumnDirectionType.InputOutput ? $", @{_identityColumn} = [{_identityColumn}] " : string.Empty)} " +
$"{BulkOperationsHelper.BuildMatchTargetOnList(_matchTargetOnSet, _collationColumnDic)} " +
$"IF (@@ROWCOUNT = 0) BEGIN " +
$"{BulkOperationsHelper.BuildInsertIntoSet(_columns, _identityColumn, fullQualifiedTableName)} " +
$"VALUES{BulkOperationsHelper.BuildValueSet(_columns, _identityColumn)}" +
$"{(_outputIdentity == ColumnDirectionType.InputOutput ? $" SET @{_identityColumn} = SCOPE_IDENTITY()" : string.Empty)} END";
if (_sqlParams.Count > 0)
{
command.Parameters.AddRange(_sqlParams.ToArray());
}
affectedRows = await command.ExecuteNonQueryAsync();
if (_outputIdentity == ColumnDirectionType.InputOutput)
{
foreach (var x in _sqlParams)
{
if (x.Direction == ParameterDirection.InputOutput &&
x.ParameterName == $"@{_identityColumn}")
{
if (x.Value is DBNull)
{
break;
}
PropertyInfo propertyInfo = _singleEntity.GetType().GetProperty(_identityColumn);
propertyInfo.SetValue(_singleEntity, x.Value);
break;
}
}
}
return(affectedRows);
}
catch (SqlException e)
{
for (int i = 0; i < e.Errors.Count; i++)
{
// Error 8102 and 544 is identity error.
if (e.Errors[i].Number == 544 || e.Errors[i].Number == 8102)
{
// Expensive but neccessary to inform user of an important configuration setup.
throw new IdentityException(e.Errors[i].Message);
}
}
throw;
}
}
/// <summary>
/// Commits a transaction to database asynchronously. A valid setup must exist for the operation to be
/// successful.
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
/// <exception cref="SqlBulkToolsException"></exception>
/// <exception cref="IdentityException"></exception>
public async Task <int> CommitAsync(SqlConnection connection)
{
int affectedRows = 0;
if (!_list.Any())
{
return(affectedRows);
}
if (!_deleteWhenNotMatchedFlag && _deletePredicates.Count > 0)
{
throw new SqlBulkToolsException($"{BulkOperationsHelper.GetPredicateMethodName(PredicateType.Delete)} only usable on BulkInsertOrUpdate " +
$"method when 'DeleteWhenNotMatched' is set to true.");
}
base.MatchTargetCheck();
DataTable dt = BulkOperationsHelper.CreateDataTable <T>(_propertyInfoList, _columns, _customColumnMappings, _ordinalDic, _matchTargetOn, _outputIdentity);
dt = BulkOperationsHelper.ConvertListToDataTable(_propertyInfoList, dt, _list, _columns, _ordinalDic, _outputIdentityDic);
// Must be after ToDataTable is called.
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _columns, _matchTargetOn);
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _deletePredicates);
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _updatePredicates);
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _excludeFromUpdate);
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync();
}
var dtCols = BulkOperationsHelper.GetDatabaseSchema(connection, _schema, _tableName);
try
{
SqlCommand command = connection.CreateCommand();
command.Connection = connection;
command.CommandTimeout = _sqlTimeout;
_nullableColumnDic = BulkOperationsHelper.GetNullableColumnDic(dtCols);
//Creating temp table on database
command.CommandText = BulkOperationsHelper.BuildCreateTempTable(_columns, dtCols, _outputIdentity);
await command.ExecuteNonQueryAsync();
BulkOperationsHelper.InsertToTmpTable(connection, dt, _bulkCopySettings);
string comm = BulkOperationsHelper.GetOutputCreateTableCmd(_outputIdentity, Constants.TempOutputTableName,
OperationType.InsertOrUpdate, _identityColumn);
if (!string.IsNullOrWhiteSpace(comm))
{
command.CommandText = comm;
await command.ExecuteNonQueryAsync();
}
comm = GetCommand(connection);
command.CommandText = comm;
if (_parameters.Count > 0)
{
command.Parameters.AddRange(_parameters.ToArray());
}
affectedRows = await command.ExecuteNonQueryAsync();
if (_outputIdentity == ColumnDirectionType.InputOutput)
{
await BulkOperationsHelper.LoadFromTmpOutputTableAsync(command, _identityColumn, _outputIdentityDic, OperationType.InsertOrUpdate, _list);
}
return(affectedRows);
}
catch (SqlException e)
{
for (int i = 0; i < e.Errors.Count; i++)
{
// Error 8102 is identity error.
if (e.Errors[i].Number == 8102)
{
// Expensive but neccessary to inform user of an important configuration setup.
throw new IdentityException(e.Errors[i].Message);
}
}
throw;
}
}
/// <summary>
/// Returns a data table to be used in a stored procedure.
/// </summary>
/// <returns></returns>
/// <exception cref="NotImplementedException"></exception>
public DataTable PrepareDataTable()
{
_ext.SetBulkExt(this, _columns, CustomColumnMappings, typeof(T), _removedColumns);
_dt = BulkOperationsHelper.CreateDataTable <T>(_propertyInfoList, _columns, CustomColumnMappings, _ordinalDic);
return(_dt);
}
/// <summary>
/// Commits a transaction to database asynchronously. A valid setup must exist for the operation to be
/// successful.
/// </summary>
/// <param name="connection"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
/// <exception cref="IdentityException"></exception>
public async Task <int> CommitAsync(SqlConnection connection, int commandTimeout = 0)
{
try
{
int affectedRows = 0;
if (!_list.Any())
{
return(affectedRows);
}
base.MatchTargetCheck();
DataTable dt = BulkOperationsHelper.CreateDataTable <T>(_propertyInfoList, _columns, _customColumnMappings, _ordinalDic, _matchTargetOn, _outputIdentity);
dt = BulkOperationsHelper.ConvertListToDataTable(_propertyInfoList, dt, _list, _columns, _ordinalDic, _outputIdentityDic);
// Must be after ToDataTable is called.
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _columns, _matchTargetOn);
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _updatePredicates);
if (connection.State == ConnectionState.Closed)
{
await connection.OpenAsync();
}
BulkOperationsHelper.ValidateMsSqlVersion(connection, OperationType.Update);
var dtCols = BulkOperationsHelper.GetDatabaseSchema(connection, _schema, _tableName);
SqlCommand command = connection.CreateCommand();
command.Connection = connection;
command.CommandTimeout = commandTimeout;
//Creating temp table on database
var schemaDetail = BulkOperationsHelper.BuildCreateTempTable(_columns, dtCols, _outputIdentity);
command.CommandText = schemaDetail.BuildCreateTableQuery;
await command.ExecuteNonQueryAsync();
_nullableColumnDic = schemaDetail.NullableDic;
//Insert into temp table
if (BulkOperationsHelper.GetBulkInsertStrategyType(dt, _columns) ==
BulkInsertStrategyType.MultiValueInsert)
{
var tempTableSetup = BulkOperationsHelper.BuildInsertQueryFromDataTable(_customColumnMappings, dt, _identityColumn, _columns,
_bulkCopySettings, schemaDetail, Constants.TempTableName, keepIdentity: true, keepInternalId: true);
command.CommandText = tempTableSetup.InsertQuery;
command.Parameters.AddRange(tempTableSetup.SqlParameterList.ToArray());
await command.ExecuteNonQueryAsync();
command.Parameters.Clear();
}
else
{
await BulkOperationsHelper.InsertToTmpTableWithBulkCopyAsync(connection, dt, _bulkCopySettings);
}
string comm = BulkOperationsHelper.GetOutputCreateTableCmd(_outputIdentity, Constants.TempOutputTableName,
OperationType.Update, _identityColumn);
if (!string.IsNullOrWhiteSpace(comm))
{
command.CommandText = comm;
await command.ExecuteNonQueryAsync();
}
comm = GetCommand(connection);
command.CommandText = comm;
if (_parameters.Count > 0)
{
command.Parameters.AddRange(_parameters.ToArray());
}
affectedRows = await command.ExecuteNonQueryAsync();
if (_outputIdentity == ColumnDirectionType.InputOutput)
{
await BulkOperationsHelper.LoadFromTmpOutputTableAsync(command, _identityColumn, _outputIdentityDic, OperationType.InsertOrUpdate, _list);
}
return(affectedRows);
}
catch (SqlException e)
{
for (int i = 0; i < e.Errors.Count; i++)
{
// Error 8102 is identity error.
if (e.Errors[i].Number == 544 || e.Errors[i].Number == 8102)
{
// Expensive call but neccessary to inform user of an important configuration setup.
throw new IdentityException(e.Errors[i].Message);
}
}
throw;
}
}
/// <summary>
/// Adds all properties in model that are either value, string, char[] or byte[] type.
/// </summary>
/// <returns></returns>
public DataTableAllColumnSelect <T> AddAllColumns()
{
Columns = BulkOperationsHelper.GetAllValueTypeAndStringColumns(typeof(T));
return(new DataTableAllColumnSelect <T>(_ext, _list, Columns));
}
int ITransaction.CommitTransaction(string connectionName, SqlCredential credentials, SqlConnection connection)
{
int affectedRows = 0;
if (!_list.Any())
{
return(affectedRows);
}
base.IndexCheck();
DataTable dt = BulkOperationsHelper.CreateDataTable <T>(_columns, _customColumnMappings, _matchTargetOn, _outputIdentity);
dt = BulkOperationsHelper.ConvertListToDataTable(dt, _list, _columns);
// Must be after ToDataTable is called.
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _columns, _matchTargetOn);
using (SqlConnection conn = BulkOperationsHelper.GetSqlConnection(connectionName, credentials, connection))
{
conn.Open();
DataTable dtCols = null;
if (_outputIdentity == ColumnDirection.InputOutput)
{
dtCols = BulkOperationsHelper.GetDatabaseSchema(conn, _schema, _tableName);
}
using (SqlTransaction transaction = conn.BeginTransaction())
{
//Bulk insert into temp table
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, _sqlBulkCopyOptions, transaction))
{
try
{
bulkcopy.DestinationTableName = BulkOperationsHelper.GetFullQualifyingTableName(conn.Database, _schema, _tableName);
BulkOperationsHelper.MapColumns(bulkcopy, _columns, _customColumnMappings);
BulkOperationsHelper.SetSqlBulkCopySettings(bulkcopy, _bulkCopyEnableStreaming, _bulkCopyBatchSize,
_bulkCopyNotifyAfter, _bulkCopyTimeout, _bulkCopyDelegates);
SqlCommand command = conn.CreateCommand();
command.Connection = conn;
command.Transaction = transaction;
if (_disableAllIndexes || (_disableIndexList != null && _disableIndexList.Any()))
{
command.CommandText = BulkOperationsHelper.GetIndexManagementCmd(IndexOperation.Disable, _tableName,
_schema, conn, _disableIndexList, _disableAllIndexes);
command.ExecuteNonQuery();
}
// If InputOutput identity is selected, must use staging table.
if (_outputIdentity == ColumnDirection.InputOutput && dtCols != null)
{
command.CommandText = BulkOperationsHelper.BuildCreateTempTable(_columns, dtCols, _outputIdentity);
command.ExecuteNonQuery();
BulkOperationsHelper.InsertToTmpTable(conn, transaction, dt, _bulkCopyEnableStreaming, _bulkCopyBatchSize,
_bulkCopyNotifyAfter, _bulkCopyTimeout, _sqlBulkCopyOptions, _bulkCopyDelegates);
command.CommandText = BulkOperationsHelper.GetInsertIntoStagingTableCmd(command, conn, _schema, _tableName,
_columns, _identityColumn, _outputIdentity);
command.ExecuteNonQuery();
BulkOperationsHelper.LoadFromTmpOutputTable(command, _identityColumn, _outputIdentityDic, OperationType.Insert, _list);
}
else
{
bulkcopy.WriteToServer(dt);
}
if (_disableAllIndexes || (_disableIndexList != null && _disableIndexList.Any()))
{
command.CommandText = BulkOperationsHelper.GetIndexManagementCmd(IndexOperation.Rebuild, _tableName,
_schema, conn, _disableIndexList, _disableAllIndexes);
command.ExecuteNonQuery();
}
transaction.Commit();
bulkcopy.Close();
affectedRows = dt.Rows.Count;
return(affectedRows);
}
catch (Exception)
{
transaction.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}
}
}
/// <summary>
///
/// </summary>
/// <param name="connectionName"></param>
/// <param name="credentials"></param>
/// <param name="connection"></param>
/// <returns></returns>
async Task <int> ITransaction.CommitTransactionAsync(string connectionName, SqlCredential credentials, SqlConnection connection)
{
int affectedRows = 0;
if (!_list.Any())
{
return(affectedRows);
}
base.IndexCheck();
base.MatchTargetCheck();
DataTable dt = BulkOperationsHelper.CreateDataTable <T>(_columns, _customColumnMappings, _matchTargetOn, _outputIdentity);
dt = BulkOperationsHelper.ConvertListToDataTable(dt, _list, _columns, _outputIdentityDic);
// Must be after ToDataTable is called.
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _columns);
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _deletePredicates);
using (SqlConnection conn = BulkOperationsHelper.GetSqlConnection(connectionName, credentials, connection))
{
await conn.OpenAsync();
var dtCols = BulkOperationsHelper.GetDatabaseSchema(conn, _schema, _tableName);
using (SqlTransaction transaction = conn.BeginTransaction())
{
try
{
SqlCommand command = conn.CreateCommand();
command.Connection = conn;
command.Transaction = transaction;
command.CommandTimeout = _sqlTimeout;
//Creating temp table on database
command.CommandText = BulkOperationsHelper.BuildCreateTempTable(_columns, dtCols, _outputIdentity);
await command.ExecuteNonQueryAsync();
await BulkOperationsHelper.InsertToTmpTableAsync(conn, transaction, dt, _bulkCopyEnableStreaming, _bulkCopyBatchSize,
_bulkCopyNotifyAfter, _bulkCopyTimeout, _sqlBulkCopyOptions, _bulkCopyDelegates);
if (_disableIndexList != null && _disableIndexList.Any())
{
command.CommandText = BulkOperationsHelper.GetIndexManagementCmd(IndexOperation.Disable, _tableName,
_schema, conn, _disableIndexList, _disableAllIndexes);
await command.ExecuteNonQueryAsync();
}
string comm = BulkOperationsHelper.GetOutputCreateTableCmd(_outputIdentity, Constants.TempOutputTableName,
OperationType.InsertOrUpdate, _identityColumn);
if (!string.IsNullOrWhiteSpace(comm))
{
command.CommandText = comm;
command.ExecuteNonQuery();
}
// Updating destination table, and dropping temp table
comm = "MERGE INTO " + BulkOperationsHelper.GetFullQualifyingTableName(conn.Database, _schema, _tableName) + " WITH (HOLDLOCK) AS Target " +
"USING " + Constants.TempTableName + " AS Source " +
BulkOperationsHelper.BuildJoinConditionsForUpdateOrInsert(_matchTargetOn.ToArray(),
Constants.SourceAlias, Constants.TargetAlias) +
"WHEN MATCHED " + BulkOperationsHelper.BuildPredicateQuery(_matchTargetOn.ToArray(), _deletePredicates, Constants.TargetAlias) +
"THEN DELETE " +
BulkOperationsHelper.GetOutputIdentityCmd(_identityColumn, _outputIdentity, Constants.TempOutputTableName,
OperationType.Delete) + "; " +
"DROP TABLE " + Constants.TempTableName + ";";
command.CommandText = comm;
if (_parameters.Count > 0)
{
command.Parameters.AddRange(_parameters.ToArray());
}
affectedRows = await command.ExecuteNonQueryAsync();
if (_disableIndexList != null && _disableIndexList.Any())
{
command.CommandText = BulkOperationsHelper.GetIndexManagementCmd(IndexOperation.Rebuild, _tableName,
_schema, conn, _disableIndexList);
await command.ExecuteNonQueryAsync();
}
if (_outputIdentity == ColumnDirection.InputOutput)
{
await
BulkOperationsHelper.LoadFromTmpOutputTableAsync(command, _identityColumn, _outputIdentityDic,
OperationType.Delete, _list);
}
transaction.Commit();
return(affectedRows);
}
catch (Exception)
{
transaction.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}
}
DataTable IDataTableTransaction.BuildDataTable()
{
return(BulkOperationsHelper.ConvertListToDataTable(_dt, _list, _columns));
}