Ejemplo n.º 1
0
        public async Task <Guid> AddAsync(Email email, List <String> tags)
        {
            await using var connection = new SqlConnection(AdoHelper.ConnectionString);
            await connection.OpenAsync();

            await using SqlTransaction transaction = connection.BeginTransaction();

            try
            {
                var tagIds = await GetTagIds(tags, transaction);

                Guid emailId = await AddEmail(email, transaction);

                await AddEmailTags(emailId, tagIds, transaction);

                await transaction.CommitAsync();

                return(emailId);
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw new Exception("Error was occured. Transaction declined.", ex);
            }
        }
Ejemplo n.º 2
0
 /// <summary>
 /// Asynchronously commits active transaction.
 /// </summary>
 public async Task CommitTransactionAsync()
 {
     if (transaction != null && transaction.Connection != null)
     {
         await transaction.CommitAsync();
     }
 }
Ejemplo n.º 3
0
        public async Task CommitAsync_should_commit_transaction()
        {
            var transaction = NSubstitute.Substitute.For <IDbContextTransaction>();
            var sut         = new SqlTransaction(transaction);
            await sut.CommitAsync();

            transaction.Received(1).CommitAsync(Arg.Any <CancellationToken>());
        }
Ejemplo n.º 4
0
        private async Task DiscardMessageAsync(SqlConnection connection, SqlTransaction transaction, string discardMessage, CancellationToken cancellationToken)
        {
            await transaction?.CommitAsync(cancellationToken);

            transaction?.Dispose();
            connection?.Dispose();
            _logger.LogTrace(discardMessage);
        }
Ejemplo n.º 5
0
        public async Task <string> ExecSqlAsync(List <ParamSql> lst)
        {
            string result;

            try
            {
                using (this.conn = new SqlConnection(this._connectionString))
                {
                    bool flag = this.conn.State != ConnectionState.Open;
                    if (flag)
                    {
                        await this.conn.OpenAsync();
                    }
                    SqlTransaction sqlTransaction = this.conn.BeginTransaction();
                    try
                    {
                        this.cmd             = new SqlCommand();
                        this.cmd.CommandType = CommandType.Text;
                        this.cmd.Connection  = this.conn;
                        this.cmd.Transaction = sqlTransaction;
                        foreach (ParamSql paramSql in lst)
                        {
                            this.cmd.CommandText = paramSql.Sql;
                            foreach (SqlHelperParameter sqlHelperParameter in paramSql.Params)
                            {
                                this.cmd.Parameters.Add(this.GetParameter(sqlHelperParameter));
                            }
                            await this.cmd.ExecuteNonQueryAsync();
                        }
                        await sqlTransaction.CommitAsync();

                        result = string.Empty;
                    }
                    catch (Exception ex)
                    {
                        await sqlTransaction.RollbackAsync();

                        result = ex.Message;
                    }
                    finally
                    {
                        await sqlTransaction.DisposeAsync();

                        await this.cmd.DisposeAsync();

                        await this.conn.CloseAsync();
                    }
                }
            }
            catch (Exception ex2)
            {
                result = ex2.Message;
            }
            return(result);
        }
Ejemplo n.º 6
0
        public async Task <string> ExecSqlAsync(ArrayList sqlList)
        {
            string result;

            using (this.conn = new SqlConnection(this._connectionString))
            {
                bool flag = this.conn.State != ConnectionState.Open;
                if (flag)
                {
                    await this.conn.OpenAsync();
                }
                bool flag2 = sqlList.Count == 1;
                if (flag2)
                {
                    result = await this.ExecSqlAsync(sqlList[0].ToString());
                }
                else
                {
                    SqlTransaction sqlTransaction = this.conn.BeginTransaction();
                    try
                    {
                        this.cmd             = new SqlCommand();
                        this.cmd.CommandType = CommandType.Text;
                        this.cmd.Connection  = this.conn;
                        this.cmd.Transaction = sqlTransaction;
                        foreach (object obj in sqlList)
                        {
                            string commandText = (string)obj;
                            this.cmd.CommandText = commandText;
                            await this.cmd.ExecuteNonQueryAsync();
                        }
                        await sqlTransaction.CommitAsync();

                        result = string.Empty;
                    }
                    catch (Exception ex)
                    {
                        await sqlTransaction.RollbackAsync();

                        result = ex.Message;
                    }
                    finally
                    {
                        await sqlTransaction.DisposeAsync();
                    }
                }
            }
            return(result);
        }
Ejemplo n.º 7
0
        public async Task PopulateAsync(DbSchema[] schemas)
        {
            using (var conn = new SqlConnection(Connection.ConnectionString))
            {
                await conn.OpenAsync();

                this.Logger.Information($"Opened connection to {conn.Database} on {conn.DataSource}");
                using (SqlTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        foreach (var schema in schemas)
                        {
                            var cleanCmd = new SqlCommand($"TRUNCATE TABLE {schema.TableName}", conn, tran);

                            await cleanCmd.ExecuteNonQueryAsync();

                            this.Logger.Information($"table {schema.TableName} truncated");
                            using (var bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran))
                            {
                                bulkCopy.DestinationTableName = $"[dbo].{schema.TableName}";

                                foreach (var col in schema.Columns)
                                {
                                    bulkCopy.ColumnMappings.Add(col.Name, col.Name);
                                }

                                await bulkCopy.WriteToServerAsync(schema.Records);
                            }
                            this.Logger.Information($"{schema.Records.Rows.Count} records have been imported into {schema.TableName}");
                        }

                        await tran.CommitAsync();

                        this.Logger.Information($"All transactions have been committed.");
                    }
                    catch (Exception ex)
                    {
                        this.Logger.Error(ex, $"An error occured while doing operations.  Rolling back all changes to {conn.Database} on {conn.DataSource}");
                        await tran.RollbackAsync();

                        throw;
                    }
                }
                await conn.CloseAsync();
            }
        }
Ejemplo n.º 8
0
        public async Task CommitAsync()
        {
            try
            {
                await _transaction.CommitAsync();
            }
            catch (Exception)
            {
                await _transaction.RollbackAsync();

                throw;
            }
            finally
            {
                Dispose();
            }
            await BeginTransactionAsync();
        }
Ejemplo n.º 9
0
        public async Task CreateOrUpdateUsers(params User[] users)
        {
            if (!users.Any())
            {
                return;
            }

            if (users.Length == 1)
            {
                await CreateOrUpdateUser(users[0]);

                return;
            }

            using var connection = new SqlConnection(ConnectionString);
            await connection.OpenAsync();

            SqlTransaction trans = connection.BeginTransaction();

            await connection.ExecuteAsync(@$ "
CREATE TABLE #TempUsers (Id bigint, Username nvarchar(60), JoinChannel bit) 
", transaction : trans);

            await connection.ExecuteAsync(@$ "
INSERT INTO #TempUsers (Id, Username, JoinChannel) VALUES (@Id, @Username, @JoinChannel)
", users, trans);

            await connection.ExecuteAsync(@$ "
MERGE Users us
USING #TempUsers tus
ON us.Id = tus.Id
WHEN MATCHED THEN
    UPDATE 
    SET us.Username = tus.Username, 
        us.JoinChannel = IIF(us.JoinChannel = 1, us.JoinChannel, tus.JoinChannel)
WHEN NOT MATCHED THEN
    INSERT (Id, Username, JoinChannel)
    VALUES (tus.Id, tus.Username, tus.JoinChannel);
", transaction : trans);

            await trans.CommitAsync();
        }
Ejemplo n.º 10
0
        public async Task <StatisticResult> ExecuteAsync(string sqlExpression)
        {
            List <Dictionary <string, object> > results = new List <Dictionary <string, object> >();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(sqlExpression, connection);
                await connection.OpenAsync();

                SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable);
                command.Transaction = transaction;

                try
                {
                    SqlDataReader reader = await command.ExecuteReaderAsync();

                    while (await reader.ReadAsync())
                    {
                        Dictionary <string, object> row = new Dictionary <string, object>();

                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            row.Add(reader.GetName(i), reader.GetValue(i));
                        }

                        results.Add(row);
                    }
                    await reader.CloseAsync();

                    await transaction.CommitAsync();
                }
                catch (Exception)
                {
                    await transaction.RollbackAsync();
                }

                return(new StatisticResult(results));
            }
        }
Ejemplo n.º 11
0
        public async Task <T> ExecuteSqlCommand <T>(string sqlExpression, Execution <T> executionFunction, params SqlParameter[] parameters)
        {
            SqlCommand command = CreateCommand(sqlExpression, parameters);

            using (SqlConnection connection = command.Connection)
            {
                await connection.OpenAsync();

                SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable);
                command.Transaction = transaction;
                try
                {
                    T item = await executionFunction.Invoke(command);

                    await transaction.CommitAsync();

                    return(item);
                }
                catch (Exception ex)
                {
                    await transaction.RollbackAsync();

                    return(default);
Ejemplo n.º 12
0
        public async Task BulkInsert(List <LineItemDto> lineItemDtos, DateTime Date, string AccountID)
        {
            try{
                DataTable sourceDt = new DataTable();
                sourceDt.Columns.Add("Date", typeof(DateTime));
                sourceDt.Columns.Add("AccountPayerAccountId", typeof(string));
                sourceDt.Columns.Add("UsageAmount", typeof(decimal));
                sourceDt.Columns.Add("UnblendedRate", typeof(decimal));
                sourceDt.Columns.Add("UnblendedCost", typeof(decimal));
                sourceDt.Columns.Add("UsageStartDate", typeof(DateTime));
                sourceDt.Columns.Add("UsageEndDate", typeof(DateTime));
                sourceDt.Columns.Add("ProductionID", typeof(string));
                sourceDt.Columns.Add("LineItemType", typeof(string));

                foreach (LineItemDto dto in lineItemDtos)
                {
                    DataRow dr = sourceDt.NewRow();
                    dr["Date"] = dto.Date;
                    dr["AccountPayerAccountId"] = dto.UsageAccountId;
                    dr["UsageAmount"]           = dto.UsageAmount;
                    dr["UnblendedRate"]         = dto.UnblendedRate;
                    dr["UnblendedCost"]         = dto.UnblendedCost;
                    dr["UsageStartDate"]        = dto.UsageStartDate;
                    dr["UsageEndDate"]          = dto.UsageEndDate;
                    dr["ProductionID"]          = dto.ProductID;
                    dr["LineItemType"]          = dto.LineItemType;
                    sourceDt.Rows.Add(dr);
                }

                using (SqlConnection con = new SqlConnection(_connection.ConnectionString))
                {
                    con.Open();
                    using (SqlTransaction transaction = con.BeginTransaction())
                    {
                        try{
                            using (var cmd = con.CreateCommand())
                            {
                                cmd.CommandText = $"DELETE FROM LineItems_{Date.Month.ToString().PadLeft(2,'0')} WHERE Date = @Date AND AccountPayerAccountId=@AccountId";
                                cmd.Transaction = transaction;
                                cmd.Parameters.AddWithValue("@Date", Date);
                                cmd.Parameters.AddWithValue("@AccountId", AccountID);
                                await cmd.ExecuteNonQueryAsync();
                            }
                            using (SqlBulkCopy dest = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, transaction))
                            {
                                dest.DestinationTableName = $"dbo.LineItems_{Date.Month.ToString().PadLeft(2,'0')}";
                                dest.ColumnMappings.Add("Date", "Date");
                                dest.ColumnMappings.Add("AccountPayerAccountId", "AccountPayerAccountId");
                                dest.ColumnMappings.Add("UsageAmount", "UsageAmount");
                                dest.ColumnMappings.Add("UnblendedRate", "UnblendedRate");
                                dest.ColumnMappings.Add("UnblendedCost", "UnblendedCost");
                                dest.ColumnMappings.Add("UsageStartDate", "UsageStartDate");
                                dest.ColumnMappings.Add("UsageEndDate", "UsageEndDate");
                                dest.ColumnMappings.Add("ProductionID", "ProductionID");
                                dest.ColumnMappings.Add("LineItemType", "LineItemType");
                                await dest.WriteToServerAsync(sourceDt);
                            }
                            await transaction.CommitAsync();
                        }catch (Exception ex) {
                            await transaction.RollbackAsync();
                        }
                    }
                }
            }catch (Exception ex) {
                _logger.LogError($"Add Production got errors:{ex.Message}");
                throw ex;
            }
        }
Ejemplo n.º 13
0
 public async Task CommitAsync()
 {
     await transaction.CommitAsync();
 }
Ejemplo n.º 14
0
        public async Task <ImportResult> PersistAsync(IList <ConvertedData> aggregatedModelItems)
        {
            int batchOrder        = Interlocked.Increment(ref _batchOrder);
            int concurrentThreads = Interlocked.Increment(ref _concurrentThreads);

            _logger.LogInformation(
                "Start Persisting batch {batchNumber}. Concurrent persisting tasks {concurrentThreads}"
                , batchOrder
                , concurrentThreads
                );

            //await Task.Delay(10000);
            aggregatedModelItems ??= new List <ConvertedData>();
            int          itemCount = aggregatedModelItems.Count;
            ImportResult result    = new ImportResult()
            {
                Batch    = batchOrder,
                HasError = true,
                Data     = aggregatedModelItems
            };

            int answersCount = itemCount;
            IEnumerable <Category> categories;
            IEnumerable <Question> questions;
            IEnumerable <Answer>   answers;

            if (aggregatedModelItems.Count > 0)
            {
                //Remove duplicated items
                categories = aggregatedModelItems.Select <ConvertedData, Category>(converted => converted.Category).DistinctBy(x => x.Id);
                questions  = aggregatedModelItems.Select <ConvertedData, Question>(converted => converted.Question).DistinctBy(x => x.Id);

                //var aaa = aggregatedModelItems.Select<ConvertedData, Answer>(converted => converted.Answer).GroupBy(ans => ans.Id).Where(grp => grp.Count() > 1).Select(grp => grp);
                //Answers should be always different but ...
                answers      = aggregatedModelItems.Select <ConvertedData, Answer>(converted => converted.Answer).DistinctBy(x => x.Id);
                answersCount = answers.Count();

                _logger.LogDebug(
                    "Analyzed {itemCount} elements. {answers.Count()} distinct answers, {categories.Count()} distinct categories and {questions.Count()} distinct questions on Thread {Thread.CurrentThread.ManagedThreadId}"
                    , itemCount
                    , answersCount
                    , categories.Count()
                    , questions.Count()
                    , Thread.CurrentThread.ManagedThreadId
                    );

                try
                {
                    using (SqlConnection destinationConnection = new SqlConnection(_importSettings.DbConnectionString))
                    {
                        destinationConnection.Open();

                        using (SqlTransaction transaction =
                                   destinationConnection.BeginTransaction())
                        {
                            try
                            {
                                //Recreate Temp Tables
                                using (SqlCommand cmd = new SqlCommand(sqlDropAndRecreateTempTables, destinationConnection, transaction))
                                {
                                    cmd.CommandTimeout = 600;
                                    await cmd.ExecuteNonQueryAsync();
                                }
                                //Fill Temp Tables
                                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
                                           destinationConnection, SqlBulkCopyOptions.KeepIdentity,
                                           transaction))
                                {
                                    bulkCopy.BatchSize       = _importSettings.BatchSize;
                                    bulkCopy.BulkCopyTimeout = 600;

                                    //Insert Categories
                                    bulkCopy.DestinationTableName = "#Categories";
                                    bulkCopy.ColumnMappings.Add("Id", "Id");
                                    using (IDataReader reader = categories.AsDataReaderOfObjects())
                                    {
                                        try
                                        {
                                            await bulkCopy.WriteToServerAsync(reader);
                                        }
                                        finally
                                        {
                                            reader.Close();
                                        }
                                    }

                                    //Insert Questions
                                    bulkCopy.DestinationTableName = "#Questions";
                                    bulkCopy.ColumnMappings.Clear();
                                    bulkCopy.ColumnMappings.Add("Id", "Id");
                                    bulkCopy.ColumnMappings.Add("CategoryId", "CategoryId");
                                    bulkCopy.ColumnMappings.Add("Text", "Text");
                                    using (IDataReader reader = questions.AsDataReaderOfObjects())
                                    {
                                        await bulkCopy.WriteToServerAsync(reader);
                                    }

                                    //Insert Answers
                                    bulkCopy.DestinationTableName = "#Answers";
                                    bulkCopy.ColumnMappings.Clear();
                                    bulkCopy.ColumnMappings.Add("Id", "Id");
                                    bulkCopy.ColumnMappings.Add("QuestionId", "QuestionId");
                                    bulkCopy.ColumnMappings.Add("Text", "Text");
                                    bulkCopy.ColumnMappings.Add("Timestamp", "Timestamp");
                                    using (IDataReader reader = answers.AsDataReaderOfObjects())
                                    {
                                        await bulkCopy.WriteToServerAsync(reader);
                                    }
                                }

                                //Add indexes on temp tables
                                using (SqlCommand cmd = new SqlCommand(sqlCreateIndexes, destinationConnection, transaction))
                                {
                                    cmd.CommandTimeout = 600;
                                    await cmd.ExecuteNonQueryAsync();
                                }

                                //Insert new records and drop temp tables
                                using (SqlCommand cmd = new SqlCommand(sqlInsertNewRecords, destinationConnection, transaction))
                                {
                                    cmd.CommandTimeout = 1200;
                                    await cmd.ExecuteNonQueryAsync();
                                }
                                //_logger.LogDebug("Committing Transaction");
                                await transaction.CommitAsync();

                                result.HasError = false;
                            }
                            catch
                            {
                                if (transaction != null)
                                {
                                    transaction.Rollback();
                                }
                                throw;
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    result.HasError         = true;
                    result.OccuredException = ex;
                }
                finally
                {
                    Interlocked.Decrement(ref _concurrentThreads);
                }

                Interlocked.Add(ref _persisted, answersCount);
                Interlocked.Add(ref _total, itemCount);
            }
            _logger.LogInformation("Items sent to database till now: distinct in block {_persisted} / total {_total}", _persisted, _total);
            return(result);
        }