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