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) + BulkOperationsHelper.GetOutputIdentityCmd(_identityColumn, _outputIdentity, Constants.TempOutputTableName, OperationType.Update) + "; " + "DROP TABLE " + Constants.TempTableName + ";"; return(comm); }
private string GetQuery(SqlConnection connection) { string 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); string batchQtyStart = _batchQuantity != null ? "UpdateMore:\n" : string.Empty; string batchQty = _batchQuantity != null ? $"TOP ({_batchQuantity}) " : string.Empty; string batchQtyRepeat = _batchQuantity != null ? $"\nIF @@ROWCOUNT != 0\ngoto UpdateMore" : string.Empty; string comm = $"{batchQtyStart}UPDATE {batchQty}{fullQualifiedTableName} " + $"{BulkOperationsHelper.BuildUpdateSet(_columns, null, _identityColumn)}" + $"{BulkOperationsHelper.BuildPredicateQuery(concatenatedQuery, _collationColumnDic, _customColumnMappings)}{batchQtyRepeat}"; 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(), _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 + ";"; 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> /// /// </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(); } } } }
/// <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); }
int ITransaction.CommitTransaction(string connectionName, SqlCredential credentials, SqlConnection connection) { int affectedRows = 0; if (_singleEntity == null) { return(affectedRows); } BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _whereConditions); BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _orConditions); BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _andConditions); BulkOperationsHelper.AddSqlParamsForUpdateQuery(_parameters, _columns, _singleEntity); var concatenatedQuery = _whereConditions.Concat(_andConditions).Concat(_orConditions).OrderBy(x => x.SortOrder); using (SqlConnection conn = BulkOperationsHelper.GetSqlConnection(connectionName, credentials, connection)) { conn.Open(); using (SqlTransaction transaction = conn.BeginTransaction()) { try { SqlCommand command = conn.CreateCommand(); command.Connection = conn; command.Transaction = transaction; command.CommandTimeout = _sqlTimeout; string fullQualifiedTableName = BulkOperationsHelper.GetFullQualifyingTableName(conn.Database, _schema, _tableName); string comm = $"UPDATE {fullQualifiedTableName} " + $"{BulkOperationsHelper.BuildUpdateSet(_columns, fullQualifiedTableName)}" + $"{BulkOperationsHelper.BuildPredicateQuery(concatenatedQuery)}"; command.CommandText = comm; if (_parameters.Count > 0) { command.Parameters.AddRange(_parameters.ToArray()); } affectedRows = command.ExecuteNonQuery(); transaction.Commit(); return(affectedRows); } catch (Exception) { transaction.Rollback(); throw; } finally { conn.Close(); } } } }