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