/// <summary>
/// Commits a transaction to database asynchronously. A valid setup must exist for the operation to be
/// successful.
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
public async Task <int> CommitAsync(SqlConnection connection)
{
int affectedRows = 0;
if (_singleEntity == null)
{
return(affectedRows);
}
if (connection.State == ConnectionState.Closed)
{
await connection.OpenAsync();
}
SqlCommand command = connection.CreateCommand();
command.Connection = connection;
command.CommandTimeout = _sqlTimeout;
string fullQualifiedTableName = BulkOperationsHelper.GetFullQualifyingTableName(connection.Database, _schema,
_tableName);
BulkOperationsHelper.AddSqlParamsForQuery(_sqlParams, _columns, _singleEntity, customColumns: _customColumnMappings);
var concatenatedQuery = _whereConditions.Concat(_andConditions).Concat(_orConditions).OrderBy(x => x.SortOrder);
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _columns);
string comm = $"UPDATE {fullQualifiedTableName} " +
$"{BulkOperationsHelper.BuildUpdateSet(_columns, null, _identityColumn)}" +
$"{BulkOperationsHelper.BuildPredicateQuery(concatenatedQuery, _collationColumnDic)}";
command.CommandText = comm;
if (_sqlParams.Count > 0)
{
command.Parameters.AddRange(_sqlParams.ToArray());
}
affectedRows = await command.ExecuteNonQueryAsync();
return(affectedRows);
}
private string GetQuery(SqlConnection connection)
{
var fullQualifiedTableName = BulkOperationsHelper.GetFullQualifyingTableName(connection.Database, _schema,
_tableName);
BulkOperationsHelper.AddSqlParamsForQuery(_propertyInfoList, _sqlParams, _columns, _singleEntity, customColumns: _customColumnMappings);
var concatenatedQuery = _whereConditions.Concat(_andConditions).Concat(_orConditions).OrderBy(x => x.SortOrder);
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _columns);
var batchQtyStart = _batchQuantity != null ? "UpdateMore:\n" : string.Empty;
var batchQty = _batchQuantity != null ? $"TOP ({_batchQuantity}) " : string.Empty;
var batchQtyRepeat = _batchQuantity != null ? "\nIF @@ROWCOUNT != 0\ngoto UpdateMore" : string.Empty;
var comm = $"{batchQtyStart}UPDATE {batchQty}{fullQualifiedTableName} " +
$"{BulkOperationsHelper.BuildUpdateSet(_columns, null, _identityColumn)}" +
$"{BulkOperationsHelper.BuildPredicateQuery(concatenatedQuery, _collationColumnDic, _customColumnMappings)}{batchQtyRepeat}";
return(comm);
}
/// <summary>
/// Commits a transaction to database asynchronously. A valid setup must exist for the operation to be
/// successful.
/// </summary>
/// <param name="conn"></param>
/// <returns></returns>
/// <exception cref="NullReferenceException"></exception>
/// <exception cref="IdentityException"></exception>
public async Task <int> CommitAsync(SqlConnection conn)
{
int affectedRows = 0;
if (_singleEntity == null)
{
return(affectedRows);
}
if (_matchTargetOnSet.Count == 0)
{
throw new NullReferenceException("MatchTargetOn is a mandatory for upsert operation");
}
try
{
BulkOperationsHelper.AddSqlParamsForQuery(_sqlParams, _columns, _singleEntity, _identityColumn, _outputIdentity, _customColumnMappings);
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _columns);
if (conn.State == ConnectionState.Closed)
{
await conn.OpenAsync();
}
SqlCommand command = conn.CreateCommand();
command.Connection = conn;
command.CommandTimeout = _sqlTimeout;
string fullQualifiedTableName = BulkOperationsHelper.GetFullQualifyingTableName(conn.Database, _schema, _tableName);
command.CommandText = $"UPDATE {fullQualifiedTableName} {BulkOperationsHelper.BuildUpdateSet(_columns, _excludeFromUpdate, _identityColumn)}" +
$"{(_outputIdentity == ColumnDirectionType.InputOutput ? $", @{_identityColumn} = [{_identityColumn}] " : string.Empty)} " +
$"{BulkOperationsHelper.BuildMatchTargetOnList(_matchTargetOnSet, _collationColumnDic)} " +
$"IF (@@ROWCOUNT = 0) BEGIN " +
$"{BulkOperationsHelper.BuildInsertIntoSet(_columns, _identityColumn, fullQualifiedTableName)} " +
$"VALUES{BulkOperationsHelper.BuildValueSet(_columns, _identityColumn)}" +
$"{(_outputIdentity == ColumnDirectionType.InputOutput ? $" SET @{_identityColumn} = SCOPE_IDENTITY()" : string.Empty)} END";
if (_sqlParams.Count > 0)
{
command.Parameters.AddRange(_sqlParams.ToArray());
}
affectedRows = await command.ExecuteNonQueryAsync();
if (_outputIdentity == ColumnDirectionType.InputOutput)
{
foreach (var x in _sqlParams)
{
if (x.Direction == ParameterDirection.InputOutput &&
x.ParameterName == $"@{_identityColumn}")
{
if (x.Value is DBNull)
{
break;
}
PropertyInfo propertyInfo = _singleEntity.GetType().GetProperty(_identityColumn);
propertyInfo.SetValue(_singleEntity, x.Value);
break;
}
}
}
return(affectedRows);
}
catch (SqlException e)
{
for (int i = 0; i < e.Errors.Count; i++)
{
// Error 8102 and 544 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>
/// <returns></returns>
/// <exception cref="IdentityException"></exception>
public async Task <int> CommitAsync(SqlConnection connection)
{
int affectedRows = 0;
if (_singleEntity == null)
{
return(affectedRows);
}
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync();
}
try
{
BulkOperationsHelper.AddSqlParamsForQuery(_sqlParams, _columns, _singleEntity, _identityColumn, _outputIdentity, _customColumnMappings);
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _columns);
SqlCommand command = connection.CreateCommand();
command.Connection = connection;
command.CommandTimeout = _sqlTimeout;
string fullQualifiedTableName = BulkOperationsHelper.GetFullQualifyingTableName(connection.Database, _schema,
_tableName);
StringBuilder sb = new StringBuilder();
sb.Append($"{BulkOperationsHelper.BuildInsertIntoSet(_columns, _identityColumn, fullQualifiedTableName)} " +
$"VALUES{BulkOperationsHelper.BuildValueSet(_columns, _identityColumn)} ");
if (_outputIdentity == ColumnDirectionType.InputOutput)
{
sb.Append($"SET @{_identityColumn}=SCOPE_IDENTITY();");
}
command.CommandText = sb.ToString();
if (_sqlParams.Count > 0)
{
command.Parameters.AddRange(_sqlParams.ToArray());
}
affectedRows = await command.ExecuteNonQueryAsync();
if (_outputIdentity == ColumnDirectionType.InputOutput)
{
foreach (var x in _sqlParams)
{
if (x.Direction == ParameterDirection.InputOutput &&
x.ParameterName == $"@{_identityColumn}")
{
PropertyInfo propertyInfo = _singleEntity.GetType().GetProperty(_identityColumn);
propertyInfo.SetValue(_singleEntity, x.Value);
break;
}
}
}
return(affectedRows);
}
catch (SqlException e)
{
for (int i = 0; i < e.Errors.Count; i++)
{
// Error 8102 and 544 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="conn"></param>
/// <returns></returns>
/// <exception cref="NullReferenceException"></exception>
/// <exception cref="IdentityException"></exception>
public async Task <int> CommitAsync(SqlConnection conn, SqlTransaction transaction)
{
int affectedRows = 0;
if (_singleEntity == null)
{
return(affectedRows);
}
if (_matchTargetOn.Count == 0)
{
throw new NullReferenceException("MatchTargetOn is a mandatory for upsert operation");
}
try
{
BulkOperationsHelper.AddSqlParamsForQuery(_propertyInfoList, _sqlParams, _columns, _singleEntity, _identityColumn, _outputIdentity, _customColumnMappings);
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _columns);
if (conn.State == ConnectionState.Closed)
{
await conn.OpenAsync();
}
SqlCommand command = conn.CreateCommand();
command.Connection = conn;
command.Transaction = transaction;
string fullQualifiedTableName = BulkOperationsHelper.GetFullQualifyingTableName(conn.Database, _schema, _tableName);
command.CommandText = GetCommand(fullQualifiedTableName);
if (_sqlParams.Count > 0)
{
command.Parameters.AddRange(_sqlParams.ToArray());
}
affectedRows = await command.ExecuteNonQueryAsync();
if (_outputIdentity == ColumnDirectionType.InputOutput)
{
foreach (var x in _sqlParams)
{
if (x.Direction == ParameterDirection.InputOutput &&
x.ParameterName == $"@{_identityColumn}")
{
if (x.Value is DBNull)
{
break;
}
PropertyInfo propertyInfo = _singleEntity.GetType().GetProperty(_identityColumn);
propertyInfo.SetValue(_singleEntity, x.Value);
break;
}
}
}
return(affectedRows);
}
catch (SqlException e)
{
for (int i = 0; i < e.Errors.Count; i++)
{
// Error 8102 and 544 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;
}
}