private string GetCommand(SqlConnection connection)
{
if (_updatePredicates?.Count > 0 && !string.IsNullOrEmpty(updateWhen))
{
throw new Exception("两种updateWhen不可同时使用");
}
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) +
" " + updateWhen + " " +
"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 + ";";
return(comm);
}
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(), _deletePredicates, Constants.TargetAlias, base._collationColumnDic) +
"THEN DELETE " +
BulkOperationsHelper.GetOutputIdentityCmd(_identityColumn, _outputIdentity, Constants.TempOutputTableName,
OperationType.Delete) + "; " +
"DROP TABLE " + Constants.TempTableName + ";";
return(comm);
}
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>
/// <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 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 affectedRecords = 0;
if (!_list.Any())
{
return(affectedRecords);
}
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);
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync();
}
var dtCols = BulkOperationsHelper.GetDatabaseSchema(connection, _schema, _tableName);
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(), _deletePredicates, Constants.TargetAlias, base._collationColumnDic) +
"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());
}
affectedRecords = command.ExecuteNonQuery();
if (_disableIndexList != null && _disableIndexList.Any())
{
command.CommandText = BulkOperationsHelper.GetIndexManagementCmd(Constants.Rebuild, _tableName,
_schema, connection, _disableIndexList);
await command.ExecuteNonQueryAsync();
}
if (_outputIdentity == ColumnDirectionType.InputOutput)
{
BulkOperationsHelper.LoadFromTmpOutputTable(command, _identityColumn, _outputIdentityDic, OperationType.Delete, _list);
}
return(affectedRecords);
}
