int ITransaction.CommitTransaction(string connectionName, SqlCredential credentials, SqlConnection connection)
{
var rowsAffected = 0;
if (!_list.Any())
{
return(rowsAffected);
}
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, _matchTargetOn);
using (SqlConnection conn = _helper.GetSqlConnection(connectionName, credentials, 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();
//Bulk insert into temp table
_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 " +
_helper.BuildUpdateSet(_columns, _sourceAlias, _targetAlias, _identityColumn) +
"; DROP TABLE #TmpTable;";
command.CommandText = comm;
rowsAffected = command.ExecuteNonQuery();
if (_disableIndexList != null && _disableIndexList.Any())
{
command.CommandText = _helper.GetIndexManagementCmd(IndexOperation.Rebuild, _tableName, _disableIndexList);
command.ExecuteNonQuery();
}
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();
}
}
}
return(rowsAffected);
}
int ITransaction.CommitTransaction(string connectionName, SqlCredential credentials, SqlConnection connection)
{
var rowsAffected = 0;
if (!_list.Any())
{
return(rowsAffected);
}
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(connectionName, credentials, 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;
rowsAffected = 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();
}
}
}
return(rowsAffected);
}
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();
}
}
}
}