/// <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)); }