void ITransaction.CommitTransaction(SqlConnection connection)
{
if (!_list.Any())
{
return;
}
if (_disableAllIndexes && (_disableIndexList != null && _disableIndexList.Any()))
{
throw new InvalidOperationException("Invalid setup. If \'TmpDisableAllNonClusteredIndexes\' is invoked, you can not use the \'AddTmpDisableNonClusteredIndex\' method.");
}
DataTable dt = _helper.ToDataTable(_list, _columns, _customColumnMappings);
// Must be after ToDataTable is called.
_helper.DoColumnMappings(_customColumnMappings, _columns, _updateOnList);
using (SqlConnection conn = _helper.GetSqlConnection(connection))
{
conn.Open();
using (SqlTransaction transaction = conn.BeginTransaction())
{
//Bulk insert into temp table
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, _sqlBulkCopyOptions, transaction))
{
try
{
bulkcopy.DestinationTableName = _helper.GetFullQualifyingTableName(conn.Database, _schema, _tableName);
_helper.MapColumns(bulkcopy, _columns, _customColumnMappings);
_helper.SetSqlBulkCopySettings(bulkcopy, _bulkCopyEnableStreaming, _bulkCopyBatchSize,
_bulkCopyNotifyAfter, _bulkCopyTimeout);
SqlCommand command = conn.CreateCommand();
command.Connection = conn;
command.Transaction = transaction;
if (_disableAllIndexes || (_disableIndexList != null && _disableIndexList.Any()))
{
command.CommandText = _helper.GetIndexManagementCmd(IndexOperation.Disable, _tableName, _disableIndexList, _disableAllIndexes);
command.ExecuteNonQuery();
}
bulkcopy.WriteToServer(dt);
if (_disableAllIndexes || (_disableIndexList != null && _disableIndexList.Any()))
{
command.CommandText = _helper.GetIndexManagementCmd(IndexOperation.Rebuild, _tableName, _disableIndexList, _disableAllIndexes);
command.ExecuteNonQuery();
}
transaction.Commit();
bulkcopy.Close();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}
}
}
void ITransaction.CommitTransaction(SqlConnection connection)
{
if (!_list.Any())
{
return;
}
if (_disableAllIndexes && (_disableIndexList != null && _disableIndexList.Any()))
{
throw new InvalidOperationException("Invalid setup. If \'TmpDisableAllNonClusteredIndexes\' is invoked, you can not use the \'AddTmpDisableNonClusteredIndex\' method.");
}
if (_matchTargetOn.Count == 0)
{
throw new InvalidOperationException("MatchTargetOn list is empty when it's required for this operation. This is usually the primary key of your table but can also be more than one column depending on your business rules.");
}
DataTable dt = _helper.ToDataTable(_list, _columns, _customColumnMappings, _matchTargetOn);
// Must be after ToDataTable is called.
_helper.DoColumnMappings(_customColumnMappings, _columns);
using (SqlConnection conn = _helper.GetSqlConnection(connection))
{
conn.Open();
var dtCols = _helper.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 = _helper.BuildCreateTempTable(_columns, dtCols);
command.ExecuteNonQuery();
_helper.InsertToTmpTable(conn, transaction, dt, _bulkCopyEnableStreaming, _bulkCopyBatchSize, _bulkCopyNotifyAfter, _bulkCopyTimeout, _sqlBulkCopyOptions);
// Updating destination table, and dropping temp table
if (_disableIndexList != null && _disableIndexList.Any())
{
command.CommandText = _helper.GetIndexManagementCmd(IndexOperation.Disable, _tableName, _disableIndexList, _disableAllIndexes);
command.ExecuteNonQuery();
}
string comm = "MERGE INTO " + _helper.GetFullQualifyingTableName(conn.Database, _schema, _tableName) + " WITH (HOLDLOCK) AS Target " +
"USING #TmpTable AS Source " +
_helper.BuildJoinConditionsForUpdateOrInsert(_matchTargetOn.ToArray(),
_sourceAlias, _targetAlias) +
"WHEN MATCHED THEN DELETE; " +
"DROP TABLE #TmpTable;";
command.CommandText = comm;
command.ExecuteNonQuery();
if (_disableIndexList != null && _disableIndexList.Any())
{
command.CommandText = _helper.GetIndexManagementCmd(IndexOperation.Rebuild, _tableName, _disableIndexList);
command.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}
}
void ITransaction.CommitTransaction(SqlConnection connection)
{
if (!_list.Any())
{
return;
}
if (_disableAllIndexes && (_disableIndexList != null && _disableIndexList.Any()))
{
throw new InvalidOperationException("Invalid setup. If \'TmpDisableAllNonClusteredIndexes\' is invoked, you can not use the \'AddTmpDisableNonClusteredIndex\' method.");
}
if (_matchTargetOn.Count == 0)
{
throw new InvalidOperationException("MatchTargetOn list is empty when it's required for this operation. " +
"This is usually the primary key of your table but can also be more than one column depending on your business rules.");
}
DataTable dt = _helper.ToDataTable(_list, _columns, _customColumnMappings, _matchTargetOn, _outputIdentity, _outputIdentityDic);
// Must be after ToDataTable is called.
_helper.DoColumnMappings(_customColumnMappings, _columns, _matchTargetOn);
using (SqlConnection conn = _helper.GetSqlConnection(connection))
{
conn.Open();
var dtCols = _helper.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 = _helper.BuildCreateTempTable(_columns, dtCols, _outputIdentity);
command.ExecuteNonQuery();
_helper.InsertToTmpTable(conn, transaction, dt, _bulkCopyEnableStreaming, _bulkCopyBatchSize,
_bulkCopyNotifyAfter, _bulkCopyTimeout, _sqlBulkCopyOptions);
if (_disableIndexList != null && _disableIndexList.Any())
{
command.CommandText = _helper.GetIndexManagementCmd(IndexOperation.Disable, _tableName, _disableIndexList, _disableAllIndexes);
command.ExecuteNonQuery();
}
//if (_outputIdentity)
//{
// command.CommandText = _helper.GetOutputCreateTableCmd(_outputIdentity, "#TmpOutput",
// OperationType.Insert);
// command.ExecuteNonQuery();
//}
string comm =
_helper.GetOutputCreateTableCmd(_outputIdentity, "#TmpOutput", OperationType.Insert) +
"MERGE INTO " + _helper.GetFullQualifyingTableName(conn.Database, _schema, _tableName) +
" WITH (HOLDLOCK) AS Target " +
"USING #TmpTable AS Source " +
_helper.BuildJoinConditionsForUpdateOrInsert(_matchTargetOn.ToArray(),
_sourceAlias, _targetAlias) +
"WHEN MATCHED THEN " +
_helper.BuildUpdateSet(_columns, _sourceAlias, _targetAlias, _identityColumn) +
"WHEN NOT MATCHED BY TARGET THEN " +
_helper.BuildInsertSet(_columns, _sourceAlias, _identityColumn) +
(_deleteWhenNotMatchedFlag ? " WHEN NOT MATCHED BY SOURCE THEN DELETE " : " ") +
_helper.GetOutputIdentityCmd(_identityColumn, _outputIdentity, "#TmpOutput",
OperationType.Insert) +
"DROP TABLE #TmpTable;";
command.CommandText = comm;
command.ExecuteNonQuery();
if (_disableIndexList != null && _disableIndexList.Any())
{
command.CommandText = _helper.GetIndexManagementCmd(IndexOperation.Rebuild, _tableName, _disableIndexList);
command.ExecuteNonQuery();
}
if (_outputIdentity)
{
command.CommandText = "SELECT InternalId, Id FROM #TmpOutput;";
using (SqlDataReader reader = command.ExecuteReader())
{
var list = _list.ToList();
while (reader.Read())
{
var test = reader[0];
var test2 = reader[1];
T item;
if (_outputIdentityDic.TryGetValue((int)reader[0], out item))
{
Type type = item.GetType();
PropertyInfo prop = type.GetProperty(_identityColumn);
prop.SetValue(item, reader[1], null);
}
}
}
}
transaction.Commit();
}
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);
}
}
transaction.Rollback();
throw;
}
catch (Exception)
{
transaction.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}
}