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