public async Task TestMultiClosedConnAsync() { using (SqlMapper.GridReader multi = await connection.QueryMultipleAsync("select 1; select 2")) { multi.ReadAsync <int>().Result.Single().IsEqualTo(1); multi.ReadAsync <int>().Result.Single().IsEqualTo(2); } }
public async Task TestMultiConversionAsync() { using (SqlMapper.GridReader multi = await connection.QueryMultipleAsync("select Cast(1 as BigInt) Col1; select Cast(2 as BigInt) Col2").ConfigureAwait(false)) { Assert.Equal(1, multi.ReadAsync <int>().Result.Single()); Assert.Equal(2, multi.ReadAsync <int>().Result.Single()); } }
public async Task TestMultiClosedConnAsync() { using (SqlMapper.GridReader multi = await connection.QueryMultipleAsync("select 1; select 2").ConfigureAwait(false)) { Assert.Equal(1, multi.ReadAsync <int>().Result.Single()); Assert.Equal(2, multi.ReadAsync <int>().Result.Single()); } }
public async Task <IEnumerable <SystemEntity> > GetByUser(int UserID) { try { using (SqlConnection connection = new SqlConnection(_coreDbContext.Database.GetDbConnection().ConnectionString)) { DynamicParameters parameters = new DynamicParameters(); parameters.Add("@UserId", UserID); string query = "[dbo].[usp_sel_system_module_menu]"; await connection.OpenAsync(); using (SqlMapper.GridReader _result_QueryMultipleAsync = await connection.QueryMultipleAsync(sql: query, param: parameters, commandType: CommandType.StoredProcedure)) { var systems = (await _result_QueryMultipleAsync.ReadAsync <SystemEntity>()).ToList(); var modules = (await _result_QueryMultipleAsync.ReadAsync <ModuleEntity>()).ToList(); var menus = (await _result_QueryMultipleAsync.ReadAsync <MenuEntity>()).ToList(); if (systems != null) { foreach (var module in modules) { module.Menus = new List <MenuEntity>(); foreach (var menu in menus) { if (module.ID == menu.ModuleID) { module.Menus.Add(menu); } } } foreach (var system in systems) { system.Modules = new List <ModuleEntity>(); foreach (var module in modules) { if (system.ID == module.SystemID) { system.Modules.Add(module); } } } } return(systems); } } } catch (SqlException sqlEx) { throw sqlEx; } catch (Exception ex) { throw ex; } }
internal static async Task <SubscriptionDtoManager> ReadAsync(SqlMapper.GridReader reader) { var manager = new SubscriptionDtoManager(); manager.Subscriptions.AddRange(await reader.ReadAsync <SubscriptionDto>()); manager.Parameters.AddRange(await reader.ReadAsync <ParameterDto>()); manager.ParameterValues.AddRange(await reader.ReadAsync <ParameterValueDto>()); return(manager); }
public async Task TestMultiClosedConnAsyncViaFirstOrDefault() { using (SqlMapper.GridReader multi = await connection.QueryMultipleAsync("select 1; select 2; select 3; select 4; select 5;").ConfigureAwait(false)) { Assert.Equal(1, multi.ReadFirstOrDefaultAsync <int>().Result); Assert.Equal(2, multi.ReadAsync <int>().Result.Single()); Assert.Equal(3, multi.ReadFirstOrDefaultAsync <int>().Result); Assert.Equal(4, multi.ReadAsync <int>().Result.Single()); Assert.Equal(5, multi.ReadFirstOrDefaultAsync <int>().Result); } }
public async Task TestMultiClosedConnAsyncViaFirstOrDefault() { using (SqlMapper.GridReader multi = await connection.QueryMultipleAsync("select 1; select 2; select 3; select 4; select 5;")) { multi.ReadFirstOrDefaultAsync <int>().Result.IsEqualTo(1); multi.ReadAsync <int>().Result.Single().IsEqualTo(2); multi.ReadFirstOrDefaultAsync <int>().Result.IsEqualTo(3); multi.ReadAsync <int>().Result.Single().IsEqualTo(4); multi.ReadFirstOrDefaultAsync <int>().Result.IsEqualTo(5); } }
public async Task <GetPersonDetailResult> Handle( GetPersonDetailRequest request, CancellationToken cancellationToken) { await _validator.ValidateAndThrowAsync(request, null, cancellationToken); List <string> sqlClauses = new List <string> { SqlClauseForQueryingPerson(), SqlClauseForQueryingCatalogCategory() }; using var connection = await this._connectionFactory.GetConnection(cancellationToken); SqlMapper.GridReader multiQueries = await connection.QueryMultipleAsync(string.Join("; ", sqlClauses), new { request.PersonId } ); var product = await multiQueries.ReadFirstOrDefaultAsync <GetPersonDetailResult.PersonDetailResult>(); var catalogCategories = await multiQueries.ReadAsync <GetPersonDetailResult.CatalogCategoryResult>(); var result = new GetPersonDetailResult { Person = product ?? new GetPersonDetailResult.PersonDetailResult(), CatalogCategories = catalogCategories ?? Enumerable.Empty <GetPersonDetailResult.CatalogCategoryResult>() }; return(result); }
public async Task <PagingModel <ProfileDto> > GetProfiles(ProfileFilter filter, int page, int pageSize) { Query baseQuery = new Query().From("Profiles"); if (filter != null) { baseQuery.WhereFilter(filter); } Query profilesQuery = baseQuery.Clone().Select("FirstName", "LastName", "MiddleName", "Birthday"); profilesQuery.Offset((page - 1) * pageSize); profilesQuery.Limit(pageSize); Query totalQuery = baseQuery.Clone().AsCount("Id"); SqlResult result = compiler.Compile(new Query[] { profilesQuery, totalQuery }); using (IDbConnection connection = dbConnectionFactory.CreateConnection()) { using (SqlMapper.GridReader reader = await connection.QueryMultipleAsync(result.Sql, result.NamedBindings)) { return(new PagingModel <ProfileDto> { Items = await reader.ReadAsync <ProfileDto>(), Total = await reader.ReadSingleAsync <int>() }); } } }
private async Task PopulateTakeProfitAndStopLossAsync(SqlMapper.GridReader gridReader, List <OrderHistoryForOrderBlotterEntity> orderHistoryEntities) { var tpSlList = (await gridReader.ReadAsync <OrderHistory>()).ToList(); var tpList = tpSlList .Where(x => x.Type == OrderType.TakeProfit) .OrderByDescending(x => x.ModifiedTimestamp) .ToList(); var slList = tpSlList .Where(x => x.Type == OrderType.StopLoss || x.Type == OrderType.TrailingStop) .OrderByDescending(x => x.ModifiedTimestamp) .ToList(); orderHistoryEntities.ForEach(x => { var tp = tpList.FirstOrDefault(l => l.ParentOrderId == x.Id && l.ModifiedTimestamp >= x.ModifiedTimestamp); if (tp != null) { x.TakeProfitPrice = tp.ExpectedOpenPrice; } var sl = slList.FirstOrDefault(l => l.ParentOrderId == x.Id && l.ModifiedTimestamp >= x.ModifiedTimestamp); if (sl != null) { x.StopLossPrice = sl.ExpectedOpenPrice; } }); }
public override async Task <IPageResult <TKey, TEntity> > SelectPageAsync(IDbConnection conn, int pageNumber, int pageSize, IEnumerable <Expression <Func <TEntity, object> > > selectFields, WhereClauseResult whereClause = null, string sqlOrderbyClause = null, IDbTransaction tr = null, CancellationToken cctoken = new CancellationToken()) { if (conn == null) { throw new ArgumentNullException(nameof(conn)); } var result = this.ConstructMsSqlSelectPage(pageNumber, pageSize, selectFields, whereClause, sqlOrderbyClause); try { var cmd = base.BuildDapperCmd(result.sql, result.dynParms, tr, cancellationToken: cctoken); SqlMapper.GridReader reader = await conn.QueryMultipleAsync(cmd); int totalNumberOfRecords = await reader.ReadSingleOrDefaultAsync <int>(); IEnumerable <TEntity> entities = await reader.ReadAsync <TEntity>(); return(new PageResult <TKey, TEntity>(pageNumber, pageSize, totalNumberOfRecords).AddItems(entities)); } catch (Exception ex) { IDictionary <string, object> parameters = new Dictionary <string, object>(); foreach (var item in result.dynParms.ParameterNames) { parameters.Insert(item, result.dynParms.Get <object>(item)); } throw new ExecuteSqlErrorException(result.sql, parameters, ex.Message, ex); } }
protected async Task <dynamic> ReadMessageFromUspAsync(SqlMapper.GridReader queryData) { try { var dapperCommandFromProc = ( await queryData .ReadAsync <dynamic>() ) .AsEnumerable() .Select((leDynamicCommand) => new { Command = leDynamicCommand.Command }) .ToList() .FirstOrDefault() .Command; return(dapperCommandFromProc); } catch { throw; } }
private static async Task <ICollection <TransactionModel> > GetTransactionModelsAsync(SqlMapper.GridReader reader) { var transactions = (await reader.ReadAsync <Transaction>()).ToList(); var postings = await reader.ReadAsync <PostingDto>(); var postingsLookupByTransactionId = postings .GroupBy(x => x.TransactionId) .ToDictionary(x => x.Key, x => x.ToList()); var transactionModels = new List <TransactionModel>(transactions.Count); foreach (var transaction in transactions) { var transactionModel = new TransactionModel { Id = transaction.Id, Description = transaction.Description, PostedDate = transaction.PostedDate, }; if (postingsLookupByTransactionId.TryGetValue(transaction.Id, out var transactionPostings)) { foreach (var posting in transactionPostings) { var postingModel = new TransactionModel.Posting { Account = new TransactionModel.Account { Id = posting.AccountId, Name = posting.AccountName }, Amount = posting.Amount, }; transactionModel.Postings.Add(postingModel); } } transactionModels.Add(transactionModel); } return(transactionModels); }
protected override async Task <CalculatedPriceResponse> ProcessSqlResponse(GetCalculatedPriceQuery query, SqlMapper.GridReader gridReader) { using (gridReader) { var prices = await gridReader.ReadAsync <decimal>(); return(new CalculatedPriceResponse { Price = prices.FirstOrDefault() }); } }
private async Task PopulateSpreadAsync(SqlMapper.GridReader gridReader, List <OrderHistoryForOrderBlotterEntity> orderHistoryEntities) { var spreadList = (await gridReader.ReadAsync()).ToList(); orderHistoryEntities.ForEach(x => { var sp = spreadList.FirstOrDefault(l => l.ExternalOrderId == x.ExternalOrderId); if (sp != null) { x.Spread = (decimal?)sp.Spread; } }); }
private async Task PopulateAssetNameAsync(SqlMapper.GridReader gridReader, List <OrderHistoryForOrderBlotterEntity> orderHistoryEntities) { var productList = (await gridReader.ReadAsync()).ToList(); orderHistoryEntities.ForEach(x => { var product = productList.FirstOrDefault(l => l.ProductId == x.AssetPairId); if (product != null) { x.AssetName = product.Name; } }); }
private async Task PopulateAccountNameAsync(SqlMapper.GridReader gridReader, List <OrderHistoryForOrderBlotterEntity> orderHistoryEntities) { var accountList = (await gridReader.ReadAsync()).ToList(); orderHistoryEntities.ForEach(x => { var account = accountList.FirstOrDefault(l => l.Id == x.AccountId); if (account != null) { x.AccountName = account.AccountName; } }); }
public async Task <IEnumerable <AccountingDocument> > GetAccountingDocumentsByAccountingIdsAsync(IEnumerable <long> accountingIds, string company) { DynamicParameters queryParameters = new DynamicParameters(); queryParameters.Add("@accountingIds", ToArrayTVP(accountingIds)); queryParameters.Add(DataVersionIdParameter, null); queryParameters.Add("@CompanyId", company); IEnumerable <AccountingDocument> lstAccountingDocument; using (SqlMapper.GridReader grid = await ExecuteQueryMultipleAsync(StoredProcedureNames.GetAccountingDocumentByAccountingId, queryParameters, true)) { lstAccountingDocument = (await grid.ReadAsync <AccountingDocument>()).ToList(); IEnumerable <AccountingDocumentLine> accountingDocumentLines = await grid.ReadAsync <AccountingDocumentLine>(); foreach (AccountingDocument accountingDocument in lstAccountingDocument) { accountingDocument.AccountingDocumentLines = accountingDocumentLines.Where(document => document.AccountingDocumentId == accountingDocument.AccountingId); } } return(lstAccountingDocument); }
protected override async Task <CalculatedPriceRangeResponse> ProcessSqlResponse( GetCalculatedPriceRangeQuery query, SqlMapper.GridReader gridReader) { using (gridReader) { var prices = (await gridReader.ReadAsync <decimal>()).ToList(); var dict = CreateResponse(query, prices); return(new CalculatedPriceRangeResponse { Prices = dict }); } }
public async Task <UserDto> Handle(QueryUserCommand request, CancellationToken cancellationToken) { UserDto user = null; using (IDbConnection db = new SqlConnection(options.Value.ConnectionString)) { SqlMapper.GridReader results = null; if (!string.IsNullOrEmpty(request.Name)) { results = await db.QueryMultipleAsync( @"select u.Name ,u.DateCreated ,u.Id from users u where u.Name = @name select g.Id ,g.Name from groups g join user_in_group ug on g.Id = ug.GroupId join users u on u.Id = ug.UserId where u.Name = @name", new { name = request.Name }); } else { results = await db.QueryMultipleAsync( @"select u.Name ,u.DateCreated ,u.Id from users u where u.Id = @userId select g.Id ,g.Name from groups g join user_in_group ug on g.Id = ug.GroupId where ug.UserId = @userId and ug.InActive = 0", new { userId = request.Id }); } user = await results.ReadFirstOrDefaultAsync <UserDto>(); if (user != null) { user.Groups = await results.ReadAsync <GroupDto>(); } } return(user); }
internal static async Task <EventDtoManager> ReadAsync(SqlMapper.GridReader reader, CancellationToken cancellationToken) { cancellationToken.ThrowIfCancellationRequested(); var holder = new EventDtoManager(); holder.Events.AddRange(await reader.ReadAsync <EventDto>()); holder.Epcs.AddRange(await reader.ReadAsync <EpcDto>()); holder.CustomFields.AddRange(await reader.ReadAsync <CustomFieldDto>()); holder.Transactions.AddRange(await reader.ReadAsync <TransactionDto>()); holder.SourceDests.AddRange(await reader.ReadAsync <SourceDestDto>()); holder.CorrectiveIds.AddRange(await reader.ReadAsync <CorrectiveIdDto>()); return(holder); }
public async Task <GetPersonCollectionResult> Handle( GetPersonCollectionRequest request, CancellationToken cancellationToken) { await _validator.ValidateAndThrowAsync <GetPersonCollectionResult> (request, null, cancellationToken); List <string> sqlClauses = new List <string> { SqlClauseForQueryingProducts(request), SqlClauseForCountProducts(request) }; string combinedSqlClauses = string.Join("; ", sqlClauses); var parameters = new { Offset = (request.PageIndex - 1) * request.PageSize, request.PageSize, SearchTerm = $"%{request.SearchTerm}%" }; using IDbConnection connection = await _connectionFactory.GetConnection(cancellationToken); SqlMapper.GridReader multiQueries = await connection.QueryMultipleAsync(combinedSqlClauses, parameters); IEnumerable <GetPersonCollectionResult.PersonCollectionItem> people = await multiQueries.ReadAsync <GetPersonCollectionResult.PersonCollectionItem>(); int totalPerson = await multiQueries.ReadFirstOrDefaultAsync <int>(); GetPersonCollectionResult result = new GetPersonCollectionResult { People = people ?? Enumerable.Empty <GetPersonCollectionResult.PersonCollectionItem>(), TotalPerson = totalPerson }; return(result); }
private async Task PopulateCommissionAndOnBehalfAsync(SqlMapper.GridReader gridReader, List <OrderHistoryForOrderBlotterEntity> orderHistoryEntities) { var list = (await gridReader.ReadAsync()).ToList(); var commissionList = list.Where(x => x.ReasonType == "Commission").ToList(); var onBehalfList = list.Where(x => x.ReasonType == "OnBehalf").ToList(); orderHistoryEntities.ForEach(x => { var commission = commissionList.FirstOrDefault(l => l.EventSourceId == x.Id); if (commission != null) { x.Commission = commission.Result; } var onBehalf = onBehalfList.FirstOrDefault(l => l.EventSourceId == x.Id); if (onBehalf != null) { x.OnBehalfFee = onBehalf.Result; } }); }
protected override async Task <PagedResult <ProcessNode> > OnGetByIdsAsync(IList <int> processIds, int offset, int limit, CancellationToken cancellationToken = default(CancellationToken), IDictionary <object, object> context = null) { int count = 0; string query = @"SELECT PROCESS_ID AS ProcessId , NODE_NAME AS NodeName , NODE_LABEL AS NodeLabel , NODE_DATATYPE AS NodeDataType , NODE_VALUE AS NodeValue FROM XML_NODE WHERE PROCESS_ID IN @Ids"; try { DynamicParameters parameters = new DynamicParameters(); parameters.AddDynamicParams(new { Ids = processIds }); var policy = _connectionFactory.PolicySelector(_logger); var result = await policy.ExecuteAsync(async() => { using (var connection = _connectionFactory.Create("raa")) { IEnumerable <ProcessNode> processNodes; IDictionary <int, ProcessNodeResult> processNodeResults = new Dictionary <int, ProcessNodeResult>(); using (SqlMapper.GridReader multi = await cn.QueryMultipleAsync(query, parameters, commandType: CommandType.Text)) { processNodes = await multi.ReadAsync <ProcessNode>(); } if (processNodes != null) { count = processNodes.Count(); // Group the nodes together by processId. foreach (var processNode in processNodes) { int processId = processNode.ProcessId; if (!processNodeResults.ContainsKey(processId)) { processNodeResults.Add(processId, new ProcessNodeResult() { ProcessId = processId, Nodes = new List <Node>() }); } processNodeResults[processId].Nodes.Add(new Node() { NodeName = processNode.NodeName, NodeLabel = processNode.NodeLabel, NodeValue = processNode.NodeValue, NodeDataType = processNode.NodeDataType }); } } var result = processNodeResults .Values .ToList() .Skip(offset *limit) .Take(limit); return(new PagedResult <ProcessNodeResult>(offset, limit, count, result)); } }); return(result); } catch (Exception ex) { _logger.LogError($"Unable to retrieve process nodes list. Reason: {ex}"); throw ex; } }
public async Task <List <dynamic> > ReadAsync() { var list = await _reader.ReadAsync(false); return(list.AsList()); }
public Task <IEnumerable <T> > Read <T>() { SqlMapper.GridReader reader = _items.Dequeue(); return(reader.ReadAsync <T>()); }
public Task <IEnumerable <T> > Read <T>() { return(_reader.ReadAsync <T>()); }
public virtual Task <IEnumerable <object> > ReadAsync(bool buffered = true) => _gridReader.ReadAsync(buffered);
public static async Task <IReadOnlyCollection <Course> > MapCourses(SqlMapper.GridReader reader) { var courses = await reader.ReadAsync <CourseResult>(); var courseRuns = (await reader.ReadAsync <CourseRunResult>()) .GroupBy(r => r.CourseId) .ToDictionary(g => g.Key, g => g.AsEnumerable()); var courseRunSubRegions = (await reader.ReadAsync <CourseRunSubRegionResult>()) .GroupBy(r => r.CourseRunId) .ToDictionary(g => g.Key, g => g.Select(r => r.RegionId).AsEnumerable()); // N.B. We need to normalize HTML-encoded data here. The legacy projects HTML-encoded everything before // persisting it in Cosmos. We want to move the HTML encoding to the edge, where it should be done. // The existing data synced from Cosmos has a DataIsHtmlEncoded column set to true; read that here and // decode the relevant fields if it's set. return(courses.Select(MapCourse).ToArray()); Course MapCourse(CourseResult row) { return(new Course() { CourseId = row.CourseId, CreatedOn = row.CreatedOn, UpdatedOn = row.UpdatedOn, ProviderId = row.ProviderId, ProviderUkprn = row.ProviderUkprn, LearnAimRef = row.LearnAimRef, CourseDescription = DecodeIfNecessary(row.CourseDescription), EntryRequirements = DecodeIfNecessary(row.EntryRequirements), WhatYoullLearn = DecodeIfNecessary(row.WhatYoullLearn), HowYoullLearn = DecodeIfNecessary(row.HowYoullLearn), WhatYoullNeed = DecodeIfNecessary(row.WhatYoullNeed), HowYoullBeAssessed = DecodeIfNecessary(row.HowYoullBeAssessed), WhereNext = DecodeIfNecessary(row.WhereNext), CourseRuns = courseRuns .GetValueOrDefault(row.CourseId, Enumerable.Empty <CourseRunResult>()) .Select(MapCourseRun) .ToArray(), LearnAimRefTypeDesc = row.LearnAimRefTypeDesc, AwardOrgCode = row.AwardOrgCode, NotionalNVQLevelv2 = row.NotionalNVQLevelv2, LearnAimRefTitle = row.LearnAimRefTitle }); string DecodeIfNecessary(string field) => row.DataIsHtmlEncoded != false?HtmlDecode(field) : field; } CourseRun MapCourseRun(CourseRunResult row) { var courseRun = new CourseRun() { CourseRunId = row.CourseRunId, CourseRunStatus = row.CourseRunStatus, CreatedOn = row.CreatedOn, UpdatedOn = row.UpdatedOn, CourseName = DecodeIfNecessary(row.CourseName), VenueId = row.VenueId, ProviderCourseId = DecodeIfNecessary(row.ProviderCourseId), DeliveryMode = row.DeliveryMode, FlexibleStartDate = row.FlexibleStartDate, StartDate = row.StartDate, CourseWebsite = row.CourseWebsite, Cost = row.Cost, CostDescription = DecodeIfNecessary(row.CostDescription), DurationUnit = row.DurationUnit, DurationValue = row.DurationValue, StudyMode = row.StudyMode != 0 ? row.StudyMode : null, // Normalize 0 to null AttendancePattern = row.AttendancePattern != 0 ? row.AttendancePattern : null, // Normalize 0 to null National = row.National, SubRegionIds = courseRunSubRegions.GetValueOrDefault(row.CourseRunId, Enumerable.Empty <string>()).ToArray(), VenueName = row.VenueName, ProviderVenueRef = row.ProviderVenueRef }; // We have some bad data where fields are populated when they shouldn't be for the delivery mode. Fix it up here if (courseRun.DeliveryMode != CourseDeliveryMode.ClassroomBased) { courseRun.VenueId = null; courseRun.VenueName = null; courseRun.ProviderVenueRef = null; courseRun.StudyMode = null; courseRun.AttendancePattern = null; } if (courseRun.DeliveryMode != CourseDeliveryMode.WorkBased) { courseRun.National = null; courseRun.SubRegionIds = Array.Empty <string>(); } return(courseRun); string DecodeIfNecessary(string field) => row.DataIsHtmlEncoded != false?HtmlDecode(field) : field; } }
public async Task <IEnumerable> LoadData(SqlMapper.GridReader reader) { return(await reader.ReadAsync <TPropertyType>()); }