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>());
}