public async Task <List <T> > QueryAsync <T>(string sql, object param = null) where T : class { var connection = _sqlConnectionFactory.GetOpenConnection(); var query = await QueryAsync <T>(connection, sql, param); return(query.AsList()); }
public async Task <SearchQueryResult <HangfireJobQueryResult> > SearchJobs(SearchHangfireJobsParameter parameter, CancellationToken cancellationToken) { const string sql = "SELECT " + "[Id], " + "[InvocationData], " + "[StateName], " + "[CreatedAt] " + "FROM [HangFire].[Job] " + "ORDER BY [CreatedAt] DESC " + "OFFSET @startIndex ROWS " + "FETCH NEXT @count ROWS ONLY"; const string countSql = "SELECT COUNT(*) " + "FROM [HangFire].[Job] "; var connection = _sqlConnectionFactory.GetOpenConnection(); var count = await connection.QuerySingleAsync <int>(countSql); var result = await connection.QueryAsync <HangfireJobQueryResult>(sql, new { startIndex = parameter.StartIndex, count = parameter.Count }); return(new SearchQueryResult <HangfireJobQueryResult> { Content = result, Count = count, StartIndex = parameter.StartIndex }); }
public async Task <PersonEntity> Add(PersonEntity entity) { var connection = _sqlConnectionFactory.GetOpenConnection(); var inserted = await connection.QuerySingleAsync <PersonEntity>(DatabaseStoreProcedure.InsertPerson, new { entity.Age, entity.FirstName, entity.LastName }, commandType : System.Data.CommandType.StoredProcedure); return(inserted); }
public async Task <MedicalStuff> GetById(MedicalStuffId id) { var connection = _sqlConnectionFactory.GetOpenConnection(); const string sql = "SELECT * FROM [TestDb].[calendars].[MedicalStuffs] WHERE MedicalStuffs.Id = @Id"; var param = new { Id = id.Value }; var medicalStaff = await connection.QuerySingleAsync <MedicalStaffDto>(sql, param); return(medicalStaff.Mapp()); }
public Task <UserDto> GetUserByLoginAsync(string login) { using (var connection = _sqlConnectionFactory.GetOpenConnection()) { return(connection.QueryFirstAsync <UserDto>( "[User].[GetUserByLogin]", new { Login = login }, null, null, CommandType.StoredProcedure)); } }
public Task <IEnumerable <OutboxMessageDto> > GetUnprocessedMessages() { using (var connection = _sqlConnectionFactory.GetOpenConnection()) { return(connection.QueryAsync <OutboxMessageDto>( "[User].[GetUnprocessedOutboxMessages]", null, null, null, CommandType.StoredProcedure)); } }
public long?GetCheckpoint(SubscriptionCode subscriptionCode) { using var connection = _sqlConnectionFactory.GetOpenConnection(); var checkpoint = connection.QuerySingleOrDefault <long?>("SELECT " + "[SubscriptionCheckpoint].Position " + "FROM [payments].[SubscriptionCheckpoints] AS [SubscriptionCheckpoint] " + "WHERE [Code] = @Code", new { Code = subscriptionCode }); return(checkpoint); }
public Task <FeedQueryResult> Get(string feedId, CancellationToken cancellationToken) { const string sql = "SELECT [feeds].[Id] as [FeedId], " + "[feeds].[Title] as [FeedTitle], " + "[feeds].[UserId] as [UserId] " + "FROM [dbo].[Feeds] as [feeds] " + "where [feeds].[Id] = @feedId"; var connection = _sqlConnectionFactory.GetOpenConnection(); return(connection.QuerySingleOrDefaultAsync <FeedQueryResult>(sql, new { feedId })); }
public async Task <List <MeetingGroupProposalDto> > Handle(GetMemberMeetingGroupProposalsQuery query, CancellationToken cancellationToken) { var connection = _sqlConnectionFactory.GetOpenConnection(); string sql = "SELECT " + $"[MeetingGroupProposal].[Id] AS [{nameof(MeetingGroupProposalDto.Id)}], " + $"[MeetingGroupProposal].[Name] AS [{nameof(MeetingGroupProposalDto.Name)}], " + $"[MeetingGroupProposal].[ProposalUserId] AS [{nameof(MeetingGroupProposalDto.ProposalUserId)}], " + $"[MeetingGroupProposal].[LocationCity] AS [{nameof(MeetingGroupProposalDto.LocationCity)}], " + $"[MeetingGroupProposal].[LocationCountryCode] AS [{nameof(MeetingGroupProposalDto.LocationCountryCode)}], " + $"[MeetingGroupProposal].[Description] AS [{nameof(MeetingGroupProposalDto.Description)}], " + $"[MeetingGroupProposal].[ProposalDate] AS [{nameof(MeetingGroupProposalDto.ProposalDate)}], " + $"[MeetingGroupProposal].[StatusCode] AS [{nameof(MeetingGroupProposalDto.StatusCode)}] " + "FROM [meetings].[v_MeetingGroupProposals] AS [MeetingGroupProposal] " + "WHERE [MeetingGroupProposal].ProposalUserId = @MemberId " + "ORDER BY [MeetingGroupProposal].[Name]"; var meetingGroupProposals = await connection.QueryAsync <MeetingGroupProposalDto>( sql, new { MemberId = _memberContext.MemberId.Value }); return(meetingGroupProposals.AsList()); }
public async Task <OrderDetailsDto> Handle(GetCustomerOrderDetailsQuery request, CancellationToken cancellationToken) { IDbConnection connection = _sqlConnectionFactory.GetOpenConnection(); const string sql = "SELECT " + "[Order].[Id], " + "[Order].[IsRemoved], " + "[Order].[Value], " + "[Order].[Currency] " + "FROM orders.v_Orders AS [Order] " + "WHERE [Order].Id = @OrderId"; OrderDetailsDto order = await connection.QuerySingleOrDefaultAsync <OrderDetailsDto>(sql, new { request.OrderId }); const string sqlProducts = "SELECT " + "[Order].[ProductId] AS [Id], " + "[Order].[Quantity], " + "[Order].[Name], " + "[Order].[Value], " + "[Order].[Currency] " + "FROM orders.v_OrderProducts AS [Order] " + "WHERE [Order].OrderId = @OrderId"; IEnumerable <ProductDto> products = await connection.QueryAsync <ProductDto>(sqlProducts, new { request.OrderId }); order.Products = products.AsList(); return(order); }
public async Task <List <MeetingGroupProposalDto> > Handle(GetMeetingGroupProposalsQuery query, CancellationToken cancellationToken) { var connection = _sqlConnectionFactory.GetOpenConnection(); var parameters = new DynamicParameters(); var pageData = PagedQueryHelper.GetPageData(query); parameters.Add(nameof(PagedQueryHelper.Offset), pageData.Offset); parameters.Add(nameof(PagedQueryHelper.Next), pageData.Next); string sql = "SELECT " + $"[MeetingGroupProposal].[Id] AS [{nameof(MeetingGroupProposalDto.Id)}], " + $"[MeetingGroupProposal].[Name] AS [{nameof(MeetingGroupProposalDto.Name)}], " + $"[MeetingGroupProposal].[ProposalUserId] AS [{nameof(MeetingGroupProposalDto.ProposalUserId)}], " + $"[MeetingGroupProposal].[LocationCity] AS [{nameof(MeetingGroupProposalDto.LocationCity)}], " + $"[MeetingGroupProposal].[LocationCountryCode] AS [{nameof(MeetingGroupProposalDto.LocationCountryCode)}], " + $"[MeetingGroupProposal].[Description] AS [{nameof(MeetingGroupProposalDto.Description)}], " + $"[MeetingGroupProposal].[ProposalDate] AS [{nameof(MeetingGroupProposalDto.ProposalDate)}], " + $"[MeetingGroupProposal].[StatusCode] AS [{nameof(MeetingGroupProposalDto.StatusCode)}] " + "FROM [meetings].[v_MeetingGroupProposals] AS [MeetingGroupProposal] " + "ORDER BY [MeetingGroupProposal].[Name]"; sql = PagedQueryHelper.AppendPageStatement(sql); var meetingGroupProposals = await connection.QueryAsync <MeetingGroupProposalDto>(sql, parameters); return(meetingGroupProposals.AsList()); }
public async Task <MeetingDetailsDto> Handle(GetMeetingDetailsQuery query, CancellationToken cancellationToken) { var connection = _sqlConnectionFactory.GetOpenConnection(); return(await connection.QuerySingleAsync <MeetingDetailsDto>( "SELECT " + $"[MeetingDetails].[Id] AS [{nameof(MeetingDetailsDto.Id)}], " + $"[MeetingDetails].[MeetingGroupId] AS [{nameof(MeetingDetailsDto.MeetingGroupId)}], " + $"[MeetingDetails].[Title] AS [{nameof(MeetingDetailsDto.Title)}], " + $"[MeetingDetails].[TermStartDate] AS [{nameof(MeetingDetailsDto.TermStartDate)}], " + $"[MeetingDetails].[TermEndDate] AS [{nameof(MeetingDetailsDto.TermEndDate)}], " + $"[MeetingDetails].[Description] AS [{nameof(MeetingDetailsDto.Description)}], " + $"[MeetingDetails].[LocationName] AS [{nameof(MeetingDetailsDto.LocationName)}], " + $"[MeetingDetails].[LocationAddress] AS [{nameof(MeetingDetailsDto.LocationAddress)}], " + $"[MeetingDetails].[LocationPostalCode] AS [{nameof(MeetingDetailsDto.LocationPostalCode)}], " + $"[MeetingDetails].[LocationCity] AS [{nameof(MeetingDetailsDto.LocationCity)}], " + $"[MeetingDetails].[AttendeesLimit] AS [{nameof(MeetingDetailsDto.AttendeesLimit)}], " + $"[MeetingDetails].[GuestsLimit] AS [{nameof(MeetingDetailsDto.GuestsLimit)}], " + $"[MeetingDetails].[RSVPTermStartDate] AS [{nameof(MeetingDetailsDto.RSVPTermStartDate)}], " + $"[MeetingDetails].[RSVPTermEndDate] AS [{nameof(MeetingDetailsDto.RSVPTermEndDate)}], " + $"[MeetingDetails].[EventFeeValue] AS [{nameof(MeetingDetailsDto.EventFeeValue)}], " + $"[MeetingDetails].[EventFeeCurrency] AS [{nameof(MeetingDetailsDto.EventFeeCurrency)}] " + "FROM [meetings].[v_MeetingDetails] AS [MeetingDetails] " + "WHERE [MeetingDetails].[Id] = @MeetingId", new { query.MeetingId })); }
public async Task <Unit> Handle(ExpireSubscriptionPaymentsCommand request, CancellationToken cancellationToken) { const string sql = "SELECT " + "[SubscriptionPayment].PaymentId " + "FROM [payments].[SubscriptionPayments] AS [SubscriptionPayment] " + "WHERE [SubscriptionPayment].Date > @Date AND " + "[SubscriptionPayment].[Status] = @Status"; var connection = _sqlConnectionFactory.GetOpenConnection(); var timeForPayment = TimeSpan.FromMinutes(20); var date = SystemClock.Now.Add(timeForPayment); var expiredSubscriptionPaymentsIds = await connection.QueryAsync <Guid>(sql, new { Date = date, Status = SubscriptionPaymentStatus.WaitingForPayment.Code }); var expiredSubscriptionsPaymentsIdsList = expiredSubscriptionPaymentsIds.AsList(); foreach (var subscriptionPaymentId in expiredSubscriptionsPaymentsIdsList) { await _commandsScheduler.EnqueueAsync( new ExpireSubscriptionPaymentCommand(subscriptionPaymentId)); } return(Unit.Value); }
public async Task <List <MatchDto> > Handle(GetMatchesByTeamQuery request, CancellationToken cancellationToken) { var connection = _sqlConnectionFactory.GetOpenConnection(); const string sql = @"SELECT m.Id, homeTeam.Name AS HomeTeam, awayTeam.Name AS AwayTeam, m.Status, m.UtcDate, m.ScoreWinner, m.ScoreHomeTeam, m.ScoreAwayTeam, m.AwayTeamId, m.HomeTeamId, m.Season, m.Name, homeTeam.Logo AS HomeTeamLogo, awayTeam.Logo AS AwayTeamLogo FROM Match.Matches AS m INNER JOIN Match.Teams AS homeTeam ON m.HomeTeamId = homeTeam.Id INNER JOIN Match.Teams AS awayTeam ON m.AwayTeamId = awayTeam.Id WHERE ( m.HomeTeamId = @TeamId ) OR ( m.AwayTeamId = @TeamId) ORDER BY m.UtcDate DESC"; var response = await connection.QueryAsync <MatchDto>(sql, new { request.TeamId }); return(response.ToList()); }
public async Task <List <SubscriptionPaymentDto> > Handle( GetSubscriptionPaymentsQuery query, CancellationToken cancellationToken) { var connection = _sqlConnectionFactory.GetOpenConnection(); var subscriptionPayments = await connection.QueryAsync <SubscriptionPaymentDto>( "SELECT " + $"[SubscriptionPayment].[PaymentId] AS [{nameof(SubscriptionPaymentDto.PaymentId)}], " + $"[SubscriptionPayment].[PayerId] AS [{nameof(SubscriptionPaymentDto.PayerId)}], " + $"[SubscriptionPayment].[Status] AS [{nameof(SubscriptionPaymentDto.Status)}], " + $"[SubscriptionPayment].[MoneyCurrency] AS [{nameof(SubscriptionPaymentDto.MoneyCurrency)}], " + $"[SubscriptionPayment].[MoneyValue] AS [{nameof(SubscriptionPaymentDto.MoneyValue)}], " + $"[SubscriptionPayment].[Date] AS [{nameof(SubscriptionPaymentDto.Date)}], " + $"[SubscriptionPayment].[SubscriptionId] AS [{nameof(SubscriptionPaymentDto.SubscriptionId)}], " + $"[SubscriptionPayment].[Type] AS [{nameof(SubscriptionPaymentDto.Type)}], " + $"[SubscriptionPayment].[Period] AS [{nameof(SubscriptionPaymentDto.Period)}] " + "FROM [payments].[SubscriptionPayments] AS [SubscriptionPayment] " + "WHERE [SubscriptionPayment].PayerId = @PayerId", new { query.PayerId }); return(subscriptionPayments.AsList()); }
public async Task <TeamDto> Handle(GetTeamQuery request, CancellationToken cancellationToken) { var connection = _sqlConnectionFactory.GetOpenConnection(); const string sql = @"SELECT Id, Name, StadiumDescription, StadiumLocation, StadiumName, Country, Description, Facebook, FormedYear, Instagram, League, Logo, Manager, ExternalId FROM Match.Teams WHERE ( Id = @Id)"; var teams = await connection.QueryAsync <TeamDto>(sql, new { request.Id }); return(teams.FirstOrDefault()); }
public async Task <MatchDto> Handle(GetMatchByIdQuery request, CancellationToken cancellationToken) { var connection = _sqlConnectionFactory.GetOpenConnection(); const string sql = @"SELECT m.Id, homeTeam.Name AS HomeTeam, awayTeam.Name AS AwayTeam, m.Status, m.UtcDate, m.ScoreWinner, m.ScoreHomeTeam, m.ScoreAwayTeam, m.AwayTeamId, m.HomeTeamId, m.Season, m.Name, m.ExternalId, homeTeam.Logo AS HomeTeamLogo, awayTeam.Logo AS AwayTeamLogo FROM Match.Matches AS m LEFT OUTER JOIN Match.Teams AS homeTeam ON m.HomeTeamId = homeTeam.Id LEFT OUTER JOIN Match.Teams AS awayTeam ON m.AwayTeamId = awayTeam.Id WHERE ( m.Id = @Id )"; var response = await connection.QueryAsync <MatchDto>(sql, new { request.Id }); return(response.SingleOrDefault()); }
public async Task <List <MemberMeetingGroupDto> > Handle( GetAuthenticationMemberMeetingGroupsQuery query, CancellationToken cancellationToken) { var connection = _sqlConnectionFactory.GetOpenConnection(); var sql = "SELECT " + $"[MemberMeetingGroup].[Id] AS [{nameof(MemberMeetingGroupDto.Id)}], " + $"[MemberMeetingGroup].[Name] AS [{nameof(MemberMeetingGroupDto.Name)}], " + $"[MemberMeetingGroup].[Description] AS [{nameof(MemberMeetingGroupDto.Description)}], " + $"[MemberMeetingGroup].[LocationCountryCode] AS [{nameof(MemberMeetingGroupDto.LocationCountryCode)}], " + $"[MemberMeetingGroup].[LocationCity] AS [{nameof(MemberMeetingGroupDto.LocationCity)}], " + $"[MemberMeetingGroup].[MemberId] AS [{nameof(MemberMeetingGroupDto.MemberId)}], " + $"[MemberMeetingGroup].[RoleCode] AS [{nameof(MemberMeetingGroupDto.RoleCode)}] " + "FROM [meetings].[v_MemberMeetingGroups] AS [MemberMeetingGroup] " + "WHERE [MemberMeetingGroup].MemberId = @MemberId AND [MemberMeetingGroup].[IsActive] = 1"; var meetingGroups = await connection.QueryAsync <MemberMeetingGroupDto>( sql, new { MemberId = _executionContextAccessor.UserId }); return(meetingGroups.AsList()); }
public async Task <Unit> Handle(ExpireSubscriptionsCommand request, CancellationToken cancellationToken) { const string sql = "SELECT " + "[SubscriptionDetails].Id " + "FROM [payments].[SubscriptionDetails] AS [SubscriptionDetails] " + "WHERE [SubscriptionDetails].ExpirationDate < @Date"; var connection = _sqlConnectionFactory.GetOpenConnection(); var expiredSubscriptionsIds = await connection.QueryAsync <Guid>(sql, new { Date = SystemClock.Now }); var expiredSubscriptionsIdsList = expiredSubscriptionsIds.AsList(); foreach (var subscriptionId in expiredSubscriptionsIdsList) { await _commandsScheduler.EnqueueAsync( new ExpireSubscriptionCommand(subscriptionId)); } return(Unit.Value); }
public async Task <Guid> Handle(BuySubscriptionRenewalCommand command, CancellationToken cancellationToken) { var priceList = await PriceListProvider.GetPriceList(_sqlConnectionFactory.GetOpenConnection()); var subscriptionId = new SubscriptionId(command.SubscriptionId); var subscription = await _aggregateStore.Load(new SubscriptionId(command.SubscriptionId)); if (subscription == null) { throw new InvalidCommandException(new List <string> { "Subscription for renewal must exist." }); } var subscriptionRenewalPayment = SubscriptionRenewalPayment.Buy( _payerContext.PayerId, subscriptionId, SubscriptionPeriod.Of(command.SubscriptionTypeCode), command.CountryCode, MoneyValue.Of(command.Value, command.Currency), priceList); _aggregateStore.AppendChanges(subscriptionRenewalPayment); return(subscriptionRenewalPayment.Id); }
public async Task <Unit> Handle(SendMeetingGroupCreatedEmailCommand request, CancellationToken cancellationToken) { var connection = _sqlConnectionFactory.GetOpenConnection(); var meetingGroup = await connection.QuerySingleAsync <MeetingGroupDto>("SELECT " + "[MeetingGroup].[Name], " + "[MeetingGroup].[LocationCountryCode], " + "[MeetingGroup].[LocationCity] " + "FROM [meetings].[v_MeetingGroups] AS [MeetingGroup] " + "WHERE [MeetingGroup].[Id] = @Id", new { Id = request.MeetingGroupId.Value }); var member = await MembersQueryHelper.GetMember(request.CreatorId, connection); var email = new EmailMessage( member.Email, $"{meetingGroup.Name} created", $"{meetingGroup.Name} created at {meetingGroup.LocationCity}, {meetingGroup.LocationCountryCode}"); _emailSender.SendEmail(email); return(Unit.Value); }
public async Task <OrderDto> Handle(GetOrderDetailsQuery request, CancellationToken cancellationToken) { IDbConnection dbConnection = _sqlConnectionFactory.GetOpenConnection(); const string orderSql = "SELECT " + "OrderId, " + "Title " + "FROM " + "Orders " + "WHERE " + "OrderId = @OrderId"; OrderDto order = await dbConnection.QuerySingleOrDefaultAsync <OrderDto>(orderSql, new { request.OrderId }); const string orderProductsSql = "SELECT " + "OP.OrderProductId, " + "p.Name, " + "OP.Purchased, " + "OP.Quantity " + "FROM OrderProducts OP " + "INNER JOIN Products P ON OP.ProductId = P.ProductId " + "WHERE OrderId = @OrderId"; IEnumerable <OrderProductDto> orderProducts = await dbConnection.QueryAsync <OrderProductDto>(orderProductsSql, new { request.OrderId }); order.OrderProducts = orderProducts.AsList(); return(order); }
public async Task <IReadOnlyList <GetWeaponsDto> > Handle(GetWeaponsQuery request, CancellationToken cancellationToken) { const string sql = "SELECT * FROM Weapons"; var connection = _sqlConnectionFactory.GetOpenConnection(); var list = await connection.QueryAsync <GetWeaponsDto>(sql); return(list.AsList()); }
public async Task <Calendar> GetById(CalendarId id) { var connection = _sqlConnectionFactory.GetOpenConnection(); const string sql = "SELECT * FROM[TestDb].[calendars].[Calendars] WHERE calendars.Id = @Id"; var param = new { Id = id.Value }; try { var calendar = await connection.QuerySingleAsync <CalendarDto>(sql, param); return(calendar.Mapp()); } catch (Exception) { return(null); } }
public async Task EnqueueAsync(ICommand command) { var connection = _sqlConnectionFactory.GetOpenConnection(); const string sqlInsert = "INSERT INTO [Match].[InternalCommands] ([Id], [EnqueueDate] , [Type], [Data]) VALUES " + "(@Id, @EnqueueDate, @Type, @Data)"; await connection.ExecuteAsync(sqlInsert, new { command.Id, EnqueueDate = DateTime.UtcNow, Type = command.GetType().FullName, Data = JsonConvert.SerializeObject(command, new JsonSerializerSettings { ContractResolver = new DomainEventsDispatcher.AllPropertiesContractResolver() }) }); }
public async Task <List <ClinicsDto> > Handle(GetaAllClinicsQuery request, CancellationToken cancellationToken) { var connection = _sqlConnectionFactory.GetOpenConnection(); const string sql = "SELECT [Id], [Location], [Name] FROM [calendars].[Clinics]"; var clinics = await connection.QueryAsync <ClinicsDto>(sql); return(clinics.AsList()); }
public async Task <List <FutureBoxDto> > Handle(GetUserFutureBoxesQuery request, CancellationToken cancellationToken) { var connection = _sqlConnectionFactory.GetOpenConnection(); const string sql = "SELECT [Id], [Name] FROM [user].[FutureBoxes] WHERE UserID = @UserID"; var boxes = await connection.QueryAsync <FutureBoxDto>(sql, new { request.UserID }); return(boxes.AsList()); }
public Task <IEnumerable <DataSourceQueryResult> > Get(IEnumerable <string> datasourceIds, CancellationToken cancellationToken) { const string sql = "SELECT " + "[Id], " + "[Title], " + "[Description], " + "[Url], " + "[NbFollowers]," + "[NbStoriesPerMonth] " + "FROM [dbo].[DataSources] " + "where [Id] IN @ids"; var connection = _sqlConnectionFactory.GetOpenConnection(); return(connection.QueryAsync <DataSourceQueryResult>(sql, new { ids = datasourceIds })); }
public async Task <List <CountryDto> > Handle(GetAllCountriesQuery query, CancellationToken cancellationToken) { var connection = _sqlConnectionFactory.GetOpenConnection(); return((await connection.QueryAsync <CountryDto>( "SELECT " + $"[Country].[Code] AS [{nameof(CountryDto.Code)}], " + $"[Country].[Name] AS [{nameof(CountryDto.Name)}] " + "FROM [meetings].[v_Countries] AS [Country]")).AsList()); }
public async Task <List <UserDetailsDto> > Handle(GetUsersQuery request, CancellationToken cancellationToken) { var connection = _sqlConnectionFactory.GetOpenConnection(); const string pqTradeSql = "SELECT id, first_name, last_name, email FROM public.cqrs_user_get()"; var users = await connection.QueryAsync <UserDetailsDto>(pqTradeSql); return(users.AsList()); }