/// <summary> /// Commits a transaction to database. A valid setup must exist for the operation to be /// successful. /// </summary> /// <param name="connection"></param> /// <param name="commandTimeout"></param> /// <returns></returns> public int Commit(SqlConnection connection, int commandTimeout = 0) { try { 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.Closed) { connection.Open(); } BulkOperationsHelper.ValidateMsSqlVersion(connection, OperationType.Insert); DataTable dtCols = BulkOperationsHelper.GetDatabaseSchema(connection, _schema, _tableName); string destinationTableName = BulkOperationsHelper.GetFullQualifyingTableName(connection.Database, _schema, _tableName); var schemaDetail = BulkOperationsHelper.BuildCreateTempTable(_columns, dtCols, _outputIdentity); SqlCommand command = connection.CreateCommand(); command.Connection = connection; command.CommandTimeout = commandTimeout; if (_disableAllIndexes) { command.CommandText = BulkOperationsHelper.GetIndexManagementCmd(Constants.Disable, _tableName, _schema, connection); command.ExecuteNonQuery(); } // If InputOutput identity is selected, must use staging table. if (_outputIdentity == ColumnDirectionType.InputOutput && dtCols != null) { command.CommandText = schemaDetail.BuildCreateTableQuery; command.ExecuteNonQuery(); 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()); command.ExecuteNonQuery(); command.Parameters.Clear(); } else { BulkOperationsHelper.InsertToTmpTableWithBulkCopy(connection, dt, _bulkCopySettings); } command.CommandText = BulkOperationsHelper.GetInsertIntoStagingTableCmd(command, connection, _schema, _tableName, _columns, _identityColumn, _outputIdentity); command.ExecuteNonQuery(); BulkOperationsHelper.LoadFromTmpOutputTable(command, _identityColumn, _outputIdentityDic, OperationType.Insert, _list); } else if (BulkOperationsHelper.GetBulkInsertStrategyType(dt, _columns) == BulkInsertStrategyType.MultiValueInsert) { var tableSetup = BulkOperationsHelper.BuildInsertQueryFromDataTable(_customColumnMappings, dt, _identityColumn, _columns, _bulkCopySettings, schemaDetail, destinationTableName); command.CommandText = GetSetIdentityCmd(on: true); command.CommandText += tableSetup.InsertQuery; command.CommandText += " " + GetSetIdentityCmd(on: false); command.Parameters.AddRange(tableSetup.SqlParameterList.ToArray()); command.ExecuteNonQuery(); command.Parameters.Clear(); } else { using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection, _bulkCopySettings.SqlBulkCopyOptions, null)) { bulkcopy.DestinationTableName = destinationTableName; BulkOperationsHelper.MapColumns(bulkcopy, _columns, _customColumnMappings); BulkOperationsHelper.SetSqlBulkCopySettings(bulkcopy, _bulkCopySettings); bulkcopy.WriteToServer(dt); bulkcopy.Close(); } } if (_disableAllIndexes) { command.CommandText = BulkOperationsHelper.GetIndexManagementCmd(Constants.Rebuild, _tableName, _schema, connection); command.ExecuteNonQuery(); } affectedRows = dt.Rows.Count; 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 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> /// <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> /// Commits a transaction to database. A valid setup must exist for the operation to be /// successful. /// </summary> /// <param name="connection"></param> /// <param name="commandTimeout"></param> /// <returns></returns> public int Commit(SqlConnection connection, int commandTimeout = 0) { int affectedRecords = 0; if (!_list.Any()) { return(affectedRecords); } 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); BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _deletePredicates); if (connection.State == ConnectionState.Closed) { connection.Open(); } BulkOperationsHelper.ValidateMsSqlVersion(connection, OperationType.Delete); 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; command.ExecuteNonQuery(); _nullableColumnDic = schemaDetail.NullableDic; 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()); command.ExecuteNonQuery(); command.Parameters.Clear(); } else { BulkOperationsHelper.InsertToTmpTableWithBulkCopy(connection, dt, _bulkCopySettings); } string comm = BulkOperationsHelper.GetOutputCreateTableCmd(_outputIdentity, Constants.TempOutputTableName, OperationType.Delete, _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()); } affectedRecords = command.ExecuteNonQuery(); if (_outputIdentity == ColumnDirectionType.InputOutput) { BulkOperationsHelper.LoadFromTmpOutputTable(command, _identityColumn, _outputIdentityDic, OperationType.Delete, _list); } return(affectedRecords); }