public async Task <Guid> SaveChangesAsync() { // Dispatch Domain Events collection. // Choices: // A) Right BEFORE committing data (EF SaveChanges) into the DB will make a single transaction including // side effects from the domain event handlers which are using the same DbContext with "InstancePerLifetimeScope" or "scoped" lifetime // B) Right AFTER committing data (EF SaveChanges) into the DB will make multiple transactions. // You will need to handle eventual consistency and compensatory actions in case of failures in any of the Handlers. await DispatchDomainEvents(); using var connection = await _connectionFactory.CreateConnectionAsync(); using var transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); var affectedRows = 0; var transactionId = Guid.NewGuid(); foreach (var change in _changes.Where(c => !c.Sent)) { change.Sent = true; switch (change.OperationType) { case OperationType.INSERT: change.DbEntity.Created = _dateTime.UtcNow; change.DbEntity.CreatedBy = _currentUserService.UserId; break; case OperationType.UPDATE: change.DbEntity.LastModified = _dateTime.UtcNow; change.DbEntity.LastModifiedBy = _currentUserService.UserId; break; } affectedRows += await connection.ExecuteAsync(change.Sql, change.DbEntity, transaction); } foreach (var entry in _integrationEvents) { entry.SetTransactionId(transactionId); affectedRows += await connection.ExecuteAsync(entry.BuildIntegrationEventLogEntryInsertStatement(), entry, transaction); } transaction.Commit(); return(transactionId); }
public async Task SeedDatabaseAsync() { using var connection = await _factory.CreateConnectionAsync(); var tables = await connection.QueryAsync <string>("SHOW TABLES"); if (tables.Any()) { return; } using var transaction = connection.BeginTransaction(); await CreateTables(connection, transaction); await SeedEnumTables(connection, transaction); transaction.Commit(); }
public async Task <Bug> GetByIdAsync(Guid id) { var sql = // WorkItem "SELECT Id, Title, AssignedTo, StateId, TeamId, SprintId, Description, " + "PriorityId, RepoLink, StateReasonId " + "FROM WorkItem Where Id = @Id;" + // Bug "SELECT EffortOriginalEstimate, EffortRemaining, EffortCompleted, IntegratedInBuild, " + "StoryPoints, SeverityId, ActivityId, SystemInfo, FoundInBuild " + "FROM Bug WHERE Id = @Id;" + // RelatedWorks "SELECT Id, LinkTypeId, Comment, Url, ReferencedWorkItemId, WorkItemId " + "FROM RelatedWork WHERE WorkItemId = @Id;" + // Tags "SELECT Text FROM Tag WHERE WorkItemId = @Id;" + // Attachments "SELECT Path, FileName, MimeType, Created FROM Attachment WHERE WorkItemId = @Id;"; using var connection = await _connectionFactory.CreateConnectionAsync(); using var multi = await connection.QueryMultipleAsync(sql, new { Id = id }); var workItemDao = multi.Read <WorkItemDao>().FirstOrDefault(); if (workItemDao == null) { return(null); } var bugDao = multi.Read <BugDao>().FirstOrDefault(); var relatedWorkDaos = multi.Read <RelatedWorkDao>(); var tagDaos = multi.Read <TagDao>(); var attachmentDaos = multi.Read <AttachmentDao>(); var bug = bugDao !.ToBug(workItemDao, tagDaos, attachmentDaos, relatedWorkDaos); return(bug); }
public async Task <IEnumerable <FilterOcorrenciasVm> > FilterOcorrenciasAsync(FilterOcorrenciasQuery query) { query.PrepareStringsForLikeOperation(); var sql = new StringBuilder( "SELECT DISTINCT o.id, identificador_ocorrencia as IdentificadorOcorrencia, tipo as Tipo, " + "delegacia_policia_apuracao as DelegaciaPoliciaApuracao, natureza as Natureza, data_hora_fato as DataHoraFato, " + "data_hora_comunicacao as DataHoraComunicacao, endereco_fato as EnderecoFato, praticado_por_menor as PraticadoPorMenor, " + "local_periciado as LocalPericiado, tipo_local as TipoLocal, objeto_meio_empregado as ObjetoMeioEmpregado, " + "o.created as Created, o.created_by as CreatedBy, " + "(SELECT COUNT(*) FROM pessoas_envolvidas p WHERE id_ocorrencia = o.id) 'PessoasEnvolvidas', " + "(SELECT COUNT(*) FROM unidades_moveis u WHERE id_ocorrencia = o.id) 'UnidadesMoveis' FROM ocorrencias o " + "LEFT JOIN pessoas_envolvidas p ON o.id = p.id_ocorrencia " + "LEFT JOIN unidades_moveis u on o.id = u.id_ocorrencia " + "WHERE 42 = 42"); if (!string.IsNullOrWhiteSpace(query.IdentificadorOcorrencia)) { sql.Append(" AND LOWER(identificador_ocorrencia) LIKE @IdentificadorOcorrencia"); } if (query.Natureza.HasValue) { sql.Append(" AND LOWER(natureza) = @Natureza"); } if (!string.IsNullOrWhiteSpace(query.DelegaciaPoliciaApuracao)) { sql.Append(" AND LOWER(delegacia_policia_apuracao) LIKE @DelegaciaPoliciaApuracao"); } if (query.PraticadoPorMenor.HasValue) { sql.Append(" AND praticado_por_menor = @PraticadoPorMenor"); } if (query.LocalPericiado.HasValue) { sql.Append(" AND local_periciado = @LocalPericiado"); } if (!string.IsNullOrWhiteSpace(query.TipoLocal)) { sql.Append(" AND LOWER(tipo_local) LIKE @TipoLocal"); } if (!string.IsNullOrWhiteSpace(query.ObjetoMeioEmpregado)) { sql.Append(" AND LOWER(objeto_meio_empregado) LIKE @ObjetoMeioEmpregado"); } if (query.CriadoDe.HasValue) { sql.Append(" AND o.created > @CriadoDe"); } if (query.CriadoAte.HasValue) { sql.Append(" AND o.created < @CriadoAte"); } if (!string.IsNullOrWhiteSpace(query.PessoaEnvolvidaNome)) { sql.Append(" AND LOWER(nome) LIKE @PessoaEnvolvidaNome;"); } if (!string.IsNullOrWhiteSpace(query.PessoaEnvolvidaEnvolvimento)) { sql.Append(" AND LOWER(envolvimento) LIKE @PessoaEnvolvidaEnvolvimento"); } if (!string.IsNullOrWhiteSpace(query.PessoaEnvolvidaCpf)) { sql.Append(" AND LOWER(cpf) LIKE @PessoaEnvolvidaCpf"); } if (!string.IsNullOrWhiteSpace(query.PessoaEnvolvidaProfissao)) { sql.Append(" AND LOWER(profissao) LIKE @PessoaEnvolvidaProfissao"); } if (!string.IsNullOrWhiteSpace(query.PessoaEnvolvidaGravidadeLesoes)) { sql.Append(" AND LOWER(gravidade_lesoes) LIKE @PessoaEnvolvidaGravidadeLesoes"); } if (!string.IsNullOrWhiteSpace(query.PessoaEnvolvidaRacaCor)) { sql.Append(" AND LOWER(raca_cor) LIKE @PessoaEnvolvidaRacaCor"); } if (!string.IsNullOrWhiteSpace(query.UnidadeMovelMatriculaResponsavel)) { sql.Append(" AND LOWER(matricula_responsavel) LIKE @UnidadeMovelMatriculaResponsavel"); } if (!string.IsNullOrWhiteSpace(query.UnidadeMovelUnidadeResponsavel)) { sql.Append(" AND LOWER(unidade_responsavel) LIKE @UnidadeMovelUnidadeResponsavel"); } if (!string.IsNullOrWhiteSpace(query.UnidadeMovelPrefixoVtr)) { sql.Append(" AND LOWER(prefixo_vtr) LIKE @UnidadeMovelPrefixoVtr"); } if (!string.IsNullOrWhiteSpace(query.UnidadeMovelResponsavel)) { sql.Append(" AND LOWER(responsavel) LIKE @UnidadeMovelResponsavel"); } if (query.PessoaEnvolvidaSexo.HasValue) { sql.Append($" AND LOWER(sexo) = LOWER(@PessoaEnvolvidaSexo)"); } using var connection = await _connectionFactory.CreateConnectionAsync(); return(await connection.QueryAsync <FilterOcorrenciasVm>(sql.ToString(), query)); }