private (DeliveryData deliveryData, List <ProductLineData> linesData) GetRawData(int id)
{
using (var connection = new SqlConnection(Settings.ConnectionString))
{
var query = @"
SELECT *
FROM [dbo].[Delivery]
WHERE DeliveryID = @ID
SELECT l.*, p.WeightInPounds ProductWeightInPounds, p.Name ProductName
FROM [dbo].[ProductLine] l
INNER JOIN [dbo].[Product] p ON p.ProductID = l.ProductID
WHERE l.DeliveryID = @ID";
SqlMapper.GridReader reader = connection.QueryMultiple(query, new { ID = id });
var deliveryData = reader.Read <DeliveryData>().SingleOrDefault();
var lineData = reader.Read <ProductLineData>().ToList();
return(deliveryData, lineData);
}
}
/// <summary>
/// Executes query sql text or stored procedure for 5 lists.
/// </summary>
/// <typeparam name="TFirst">Type of first list</typeparam>
/// <typeparam name="TSecond">Type of second list</typeparam>
/// <typeparam name="TThird">Type of third list</typeparam>
/// <typeparam name="TForth">Type of forth list</typeparam>
/// <typeparam name="TFifth">Type of fifth list</typeparam>
/// <param name="sqlQuery">SQL Query</param>
/// <param name="commandType">SQL Query command type</param>
/// <param name="parametersCollection">Input/Output parameter list</param>
/// <param name="isReturnValueExists">Indicates whether return value, needed to be included</param>
/// <returns>QueryMultipleListsReturnItem</returns>
protected virtual QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth, TFifth> ExecuteQueryMultipleByCommandType <TFirst, TSecond, TThird, TForth, TFifth>(string sqlQuery, CommandType commandType, IDbParameterList parametersCollection, bool isReturnValueExists)
{
QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth, TFifth> returnItem = new QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth, TFifth>();
GetDynamicParametersAndReturnDbParametersByDbParameters(parametersCollection, isReturnValueExists,
out DynamicParameters parameters, out IDbParameterList returnParameterList);
OpenConnectionForQueryExecution();
using (SqlMapper.GridReader gridReader = ExecuteQueryMultiple(sqlQuery, commandType, parameters))
{
returnItem.FirstCollection = gridReader.Read <TFirst>();
returnItem.SecondCollection = gridReader.Read <TSecond>();
returnItem.ThirdCollection = gridReader.Read <TThird>();
returnItem.FourthCollection = gridReader.Read <TForth>();
returnItem.FifthCollection = gridReader.Read <TFifth>();
}
CloseConnectionForQueryExecution();
SetReturnItemByReturnDbParameters(returnParameterList, parameters, isReturnValueExists, returnItem);
return(returnItem);
}
public IEnumerable <T> GetPage <T>(IDbConnection connection, int page, int resultsPerPage, out long allRowsCount, string sql, dynamic param = null, string allRowsCountSql = null, IDbTransaction transaction = null, int?commandTimeout = null, bool buffered = false) where T : class
{
while (sql.Contains("\r\n"))
{
sql = sql.Replace("\r\n", " ");
}
while (sql.Contains(" "))
{
sql = sql.Replace(" ", " ");
}
Dictionary <string, object> parameters = new Dictionary <string, object>();
StringBuilder pageSql = new StringBuilder(SqlGenerator.SelectPaged(sql, page, resultsPerPage, parameters));
DynamicParameters dynamicParameters = new DynamicParameters();
if (param != null)
{
dynamicParameters = param as DynamicParameters;
}
foreach (var parameter in parameters)
{
dynamicParameters.Add(parameter.Key, parameter.Value);
}
if (allRowsCountSql != null)
{
pageSql.Append(" ");
pageSql.Append(allRowsCountSql);
}
else
{
pageSql.Append(" ");
pageSql.Append(SqlGenerator.PageCount(sql));
}
SqlMapper.GridReader grid = connection.QueryMultiple(pageSql.ToString(), dynamicParameters, transaction, commandTimeout, CommandType.Text);
IEnumerable <T> list = grid.Read <T>();
allRowsCount = grid.Read <int>().Single();
return(list);
}
public static SpecializationGroup ReadSingle(SqlMapper.GridReader grid)
{
var group = grid.ReadFirstOrDefault <SpecializationGroup>();
if (group == null)
{
return(null);
}
var specs = grid.Read <SpecializationRecord>();
group.Specializations = specs.Select(s => s.Specialization());
return(group);
}
public static PreflightConcepts ReadConcepts(SqlMapper.GridReader grid)
{
var preflight = grid.Read <ConceptPreflightCheckResultRecord>();
var concepts = HydratedConceptReader.Read(grid);
return(new PreflightConcepts
{
PreflightCheck = new ConceptPreflightCheck {
Results = preflight.Select(p => p.ConceptPreflightCheckResult())
},
Concepts = concepts?.ToArray()
});
}
/// <summary>
/// Get PagedResultSet from a execute query with not nested object with/without TotalCount using the IncludeMetada
/// </summary>
/// <param name="fullSqlQuery">Sql Query to execute</param>
/// <param name="filter">The SearchFilter </param>
/// <param name="param">Parameters to blind with the sql query</param>
/// <param name="buffered"></param>
/// <returns></returns>
public IPaginatedList <TReturn> ExecutePagedQuery <T1, T2, T3, TReturn>(Func <T1, T2, T3, TReturn> func, string fullSqlQuery, ISearchFilter filter, object param = null, string splitOn = "id", bool buffered = true)
{
// Paging construct helper
string sqlGetQuery = PagedQueryBuilder.PagedQuery(fullSqlQuery, filter, ref param);
SqlMapper.GridReader reader = DbConnection.QueryMultiple(sqlGetQuery, param, CurrentTransaction);
IList <TReturn> entity = (IList <TReturn>)reader.Read(func, splitOn, buffered);
int?totalCount = filter.IncludeMetadata ? reader.ReadSingleOrDefault <int?>() : null;
return(new PaginatedList <TReturn>(entity, filter, totalCount));
}
private IEnumerable <GameEntity> MapGames(SqlMapper.GridReader query)
{
var games = query.Read <dynamic>();
ICollection <GameEntity> gameEntities = new List <GameEntity>();
foreach (var game in games)
{
gameEntities.Add(GetGame(game));
}
return(gameEntities);
}
/// <summary>
/// Set the transfer objects. We create the transfer object from the entity.
/// </summary>
/// <param name="reader">GridReader reader of dapper results</param>
/// <param name="office">Poco to check if there is a null parameter.</param>
private void SetDataTransferObject(SqlMapper.GridReader reader, SUBLICEN company, ref CompanyDto dto)
{
// think about the GridReader.
if (reader == null)
{
return;
}
if (!string.IsNullOrEmpty(company.CP))
{
if (reader.IsConsumed)
{
return;
}
Helper.CityDto = MapperUtils.GetMappedValue <POBLACIONES, CityDto>(reader.Read <POBLACIONES>().FirstOrDefault(), _mapper);
}
if (!string.IsNullOrEmpty(company.NACIO))
{
if (reader.IsConsumed)
{
return;
}
Helper.CountryDto = MapperUtils.GetMappedValue <Country, CountryDto>(reader.Read <Country>().FirstOrDefault(), _mapper);
}
if (!string.IsNullOrEmpty(company.PROVINCIA))
{
if (reader.IsConsumed)
{
return;
}
Helper.ProvinciaDto = MapperUtils.GetMappedValue <PROVINCIA, ProvinciaDto>(reader.Read <PROVINCIA>().FirstOrDefault(), _mapper);
}
var listOfOffices = reader.Read <OFICINAS>().ToList();
dto.Offices = _mapper.Map <IEnumerable <OFICINAS>, IEnumerable <OfficeDtos> >(listOfOffices);
}
public ActionResult BookPage(int pageId)
{
var conStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
IDbConnection connection = new SqlConnection(conStr);
connection.Open();
DynamicParameters dp = new DynamicParameters();
dp.Add("@PageId", pageId, DbType.Int32);
SqlMapper.GridReader result = connection.QueryMultiple("prBookPage", dp, commandType: CommandType.StoredProcedure);
List <Book> bks = new List <Book>();
Book models1 = result.Read <Book>().FirstOrDefault();
bks.Add(models1);
Book models2 = result.Read <Book>().FirstOrDefault();
bks.Add(models2);
Book models3 = result.Read <Book>().FirstOrDefault();
bks.Add(models3);
Book models4 = result.Read <Book>().FirstOrDefault();
bks.Add(models4);
ViewBag.Page = result.Read <FanHobbyInfo>().FirstOrDefault();
SqlMapper.GridReader result2 = connection.QueryMultiple("GetWXBG", commandType: CommandType.StoredProcedure);
var list = result2.Read <WXBGModel>().ToList();
ViewBag.EWX = list[0];
ViewBag.LJ = list[1];
connection.Dispose();
return(View(bks));
}
internal Tuple <T, List <K>, List <L>, List <M> > GetItem <T, K, L, M>(CommandDefinition command)
where T : new()
where K : new()
where L : new()
where M : new()
{
T resultT = new T();
List <K> resultK = new List <K>();
List <L> resultL = new List <L>();
List <M> resultM = new List <M>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlMapper.GridReader grid = connection.QueryMultiple(command))
{
resultT = grid.Read <T>().SingleOrDefault();
resultK = grid.Read <K>().ToList();
resultL = grid.Read <L>().ToList();
resultM = grid.Read <M>().ToList();
}
}
return(Tuple.Create <T, List <K>, List <L>, List <M> >(resultT, resultK, resultL, resultM));
}
public IEnumerable <Supplier> Select(int pageSize, int pageIndex, out int totalRows)
{
if (pageIndex <= 0)
{
pageIndex = 1;
}
if (pageSize <= 0 || pageSize > 100)
{
pageSize = 100;
}
using (var conn = DataBase.Open())
{
var tuple = Sql.Select(pageIndex, pageSize);
SqlMapper.GridReader reader = conn.QueryMultiple(tuple.Item1 + tuple.Item2, (object)tuple.Item3);
var result = reader.Read <object>().First() as IDictionary <string, object>;
totalRows = Convert.ToInt32(result["Count"]);
return(reader.Read <Supplier, object, object, object, Supplier>(Sql.SelectConvert, splitOn: "split"));
}
}
public static IEnumerable <TFirst> Map <TFirst, TSecond, TKey>(
this SqlMapper.GridReader reader,
Func <TFirst, TKey> firstKey,
Func <TSecond, TKey> secondKey,
Action <TFirst, IEnumerable <TSecond> > addChildren)
{
var first = reader.Read <TFirst>().ToList();
var childMap = reader
.Read <TSecond>()
.GroupBy(secondKey)
.ToDictionary(g => g.Key, g => g.AsEnumerable());
foreach (var item in first)
{
IEnumerable <TSecond> children;
if (childMap.TryGetValue(firstKey(item), out children))
{
addChildren(item, children);
}
}
return(first);
}
/// <summary>
/// 返回多个结果集
/// </summary>
/// <returns></returns>
public IEnumerable <dynamic> ProcedureWithOutAndReturnParameter2()
{
using (IDbConnection connection = Common.OpenConnection())
{
DynamicParameters parameter = new DynamicParameters();
string name = "test2";
parameter.Add("@Name", name);
parameter.Add("@Result", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
IEnumerable <dynamic> resultA = null;
IEnumerable <dynamic> resultB = null;
using (SqlMapper.GridReader grid = connection.QueryMultiple(sql: "DapperNETDemoSP2", param: parameter, commandType: CommandType.StoredProcedure))
{
resultA = grid.Read <dynamic>();
resultB = grid.Read <dynamic>();
}
List <dynamic> result = new List <dynamic>();
result.Add(resultA.Single());
result.Add(resultB.Single());
return(result);
}
}
/// <summary>
/// From a multi result set, builds an entities collection with its related data.
/// </summary>
private IEnumerable <Event> BuildEntitiesList(SqlMapper.GridReader reader)
{
var entities = reader.Read(_eventMap).ToList();
var imagesCollection = reader
.Read <EventImage>()
.GroupBy(eImg => eImg.EventId)
.ToDictionary(group => group.Key, group => group.ToList());
foreach (var entity in entities)
{
List <EventImage> images;
if (!imagesCollection.TryGetValue(entity.Id, out images))
{
images = new List <EventImage>();
}
entity.Images = images;
}
return(entities);
}
static IEnumerable <TMain> MapKeys <TMain, TFirstChild, TSecondChild, TKey>
(
SqlMapper.GridReader reader,
Func <TMain, TKey> mainKey,
Func <TFirstChild, TKey> firstChild,
Func <TSecondChild, TKey> secondChild,
Action <TMain, IEnumerable <TFirstChild> > addFirstChild,
Action <TMain, IEnumerable <TSecondChild> > addSecondChild
)
{
var first = reader.Read <TMain>().ToList();
var childFirstMap = reader
.Read <TFirstChild>()
.GroupBy(s => firstChild(s))
.ToDictionary(g => g.Key, g => g.AsEnumerable());
var childSecondMap = reader
.Read <TSecondChild>()
.GroupBy(s => secondChild(s))
.ToDictionary(g => g.Key, g => g.AsEnumerable());
foreach (var item in first)
{
IEnumerable <TFirstChild> childrenOfFirst;
if (childFirstMap.TryGetValue(mainKey(item), out childrenOfFirst))
{
addFirstChild(item, childrenOfFirst);
}
IEnumerable <TSecondChild> childrenOfSecond;
if (childSecondMap.TryGetValue(mainKey(item), out childrenOfSecond))
{
addSecondChild(item, childrenOfSecond);
}
}
return(first);
}
public static IEnumerable <TFirst> Map <TFirst, TSecond, TThird, TKey>(
this SqlMapper.GridReader reader,
Func <TFirst, TKey> firstKey,
Func <TSecond, TKey> secondKey,
Func <TThird, TKey> thirdKey,
Action <TFirst, IEnumerable <TSecond> > addChildren,
Action <TFirst, IEnumerable <TThird> > addChildrenThird
)
{
var first = reader.Read <TFirst>().ToList();
var firstMap = reader
.Read <TSecond>()
.GroupBy(secondKey)
.ToDictionary(g => g.Key, g => g.AsEnumerable());
var secondMap = reader
.Read <TThird>()
.GroupBy(thirdKey)
.ToDictionary(g => g.Key, g => g.AsEnumerable());
foreach (var item in first)
{
IEnumerable <TSecond> second;
IEnumerable <TThird> third;
if (firstMap.TryGetValue(firstKey(item), out second))
{
addChildren(item, second);
}
if (secondMap.TryGetValue(firstKey(item), out third))
{
addChildrenThird(item, third);
}
}
return(first);
}
public async Task <Operacion> ListOperacionID(int idDetalle)
{
var parameters = new DynamicParameters();
parameters.Add("@IDDETALLE", idDetalle);
Operacion operacion = new Operacion();
using (var connection = new SqlConnection(_connectionString))
{
SqlMapper.GridReader reader = await connection.QueryMultipleAsync("[dbo].[SPE_LIST_OPERACION_ID]", parameters, commandType : CommandType.StoredProcedure);
List <OperacionDatos> detalle1 = reader.Read <OperacionDatos>().ToList();
List <OperacionDatos> detalle2 = reader.Read <OperacionDatos>().ToList();
List <Cliente> cliente = reader.Read <Cliente>().ToList();
if (detalle1.Count > 0)
{
operacion.Detalle1 = detalle1;
operacion.Detalle2 = detalle2;
operacion.Cliente = cliente;
}
return(operacion);
}
}
internal static MainResponseModel GetSimpleResp(SqlMapper.GridReader objDetails)
{
var obj = new SampleResponse();
try
{
obj.Sample = objDetails.Read <Sample>().ToList();
}
catch (Exception)
{
obj.Sample = new List <Sample>();
}
return(obj);
}
public async Task <bool> CheckAvailabilityAddingAuthorToBook(Guid authorId, Guid bookId)
{
var sql = "select count(*) from dbo.authors where Id = @author; " +
"select count(*) from dbo.books where Id = @book;" +
"select count(*) from dbo.books_by_author where AuthorId = @author and BookId = @book;";
int authorsAmount;
int booksAmount;
int booksWithAuthors;
using (SqlMapper.GridReader multi = await Connection.QueryMultipleAsync(sql, new { author = authorId, book = bookId }))
{
authorsAmount = multi.Read <int>().Single();
booksAmount = multi.Read <int>().Single();
booksWithAuthors = multi.Read <int>().Single();
}
if (booksAmount == 1 && authorsAmount == 1 && booksWithAuthors == 0)
{
return(true);
}
return(false);
}
private IEnumerable <DocumentoClienteDados> QuerySPCustom(String storedProcedure, DynamicParameters parameters)
{
Usuario usuarioLogado = new Usuario();
IEnumerable <DocumentoClienteDados> documentoClienteRetorno = null;
using (var connection = new DapperSqlHelper().NewSqlConnection)
{
using (SqlMapper.GridReader reader = connection.QueryMultiple(storedProcedure, parameters, commandType: CommandType.StoredProcedure))
{
documentoClienteRetorno = reader.Read <DocumentoClienteDados>();
}
connection.Close();
}
return(documentoClienteRetorno.ToList());
}
public async Task <bool> CheckAvailabilityAddingBookToSeries(Guid bookId, Guid seriesId)
{
var sql = "select count(*) from dbo.serieses where Id = @series; " +
"select count(*) from dbo.books where Id = @book;" +
"select count(*) from dbo.books_in_series where SeriesId = @series and BookId = @book;";
int seriesAmount;
int booksAmount;
int booksInSeriesAmount;
using (SqlMapper.GridReader multi = await Connection.QueryMultipleAsync(sql, new { series = seriesId, book = bookId }))
{
seriesAmount = multi.Read <int>().Single();
booksAmount = multi.Read <int>().Single();
booksInSeriesAmount = multi.Read <int>().Single();
}
if (booksAmount == 1 && seriesAmount == 1 && booksInSeriesAmount == 0)
{
return(true);
}
return(false);
}
private GetResponse <IDocument> GetResponse(SqlMapper.GridReader reader)
{
var response = new GetResponse <IDocument>();
var mergeDocumentDto = reader.Read <DocumentDto>()
.FirstOrDefault();
if (mergeDocumentDto != null)
{
var document = mergeDocumentDto.ToModel();
var fieldDtos = reader.Read <MergeDocumentFieldDto>()
.AsList();
if (fieldDtos.Any())
{
document.Fields = fieldDtos
.Select(dto => dto.ToModel())
.AsList();
}
response.Content = document;
}
return(response);
}
public Usuario QuerySPCustom(String storedProcedure, DynamicParameters pIn)
{
Usuario usuarioLogado = new Usuario();
IEnumerable <Usuario> usuarioRetorno = null;
using (var connection = new DapperSqlHelper().NewSqlConnection)
{
using (SqlMapper.GridReader reader = connection.QueryMultiple(storedProcedure, pIn, commandType: CommandType.StoredProcedure))
{
//recupera StatusProcessamento (1 = usuario com acesso, 1 = usuario inativo e 2 = usuario ou senha invalido / e MensagemProcessamento
var infosLoginExecucao = reader.Read().ToList()[0];
var codigoExecucao = ((object[])((System.Collections.Generic.IDictionary <string, object>)infosLoginExecucao).Values)[0]; //codigo de execucao
var mensagemExecucao = ((object[])((System.Collections.Generic.IDictionary <string, object>)infosLoginExecucao).Values)[1]; //mensagem apos execucao
//caso usuario ter acesso ao sistema, recupera-se as demais informações do usuario logado
if ((int)codigoExecucao == 0)
{
//recupera dados do cliente e informações referenciadas
usuarioRetorno = reader.Read <Usuario, UsuarioPerfil, Cliente, Usuario>((usuario, usuarioPerfil, cliente) =>
{
usuario.UsuarioPerfil = usuarioPerfil;
usuario.Cliente = cliente;
return(usuario);
}, splitOn: "UsuarioId, UsuPerfilId, ClienteId");
usuarioLogado = (Usuario)usuarioRetorno.ToList()[0];
}
usuarioLogado.StatusProcessamento = (int)codigoExecucao;
usuarioLogado.MensagemProcessamento = (string)mensagemExecucao;
}
}
return(usuarioLogado);
}
/// <summary>
/// Get PagedResultSet from a execute query with not nested object with/without TotalCount using the IncludeMetada
/// </summary>
/// <param name="fullSqlQuery">Sql Query to execute</param>
/// <param name="filter">The SearchFilter </param>
/// <param name="param">Parameters to blind with the sql query</param>
/// <param name="buffered"></param>
/// <returns></returns>
public IPaginatedList <TReturn> ExecutePagedQuery <T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12, T13, T14, T15, T16, TReturn>(Func <T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12, T13, T14, T15, T16, TReturn> func, string fullSqlQuery, ISearchFilter filter, object param = null, string splitOn = "id", bool buffered = true)
{
// Paging construct helper
string sqlGetQuery = PagedQueryBuilder.PagedQuery(fullSqlQuery, filter, ref param);
SqlMapper.GridReader reader = DbConnection.QueryMultiple(sqlGetQuery, param, CurrentTransaction);
Type[] types =
{
typeof(T1),
typeof(T2),
typeof(T3),
typeof(T4),
typeof(T5),
typeof(T6),
typeof(T7),
typeof(T8),
typeof(T9),
typeof(T10),
typeof(T11),
typeof(T12),
typeof(T13),
typeof(T14),
typeof(T15),
typeof(T16)
};
IList <TReturn> entity = (IList <TReturn>)reader.Read(types, objects => func((T1)objects[0],
(T2)objects[1],
(T3)objects[2],
(T4)objects[3],
(T5)objects[4],
(T6)objects[5],
(T7)objects[6],
(T8)objects[7],
(T9)objects[8],
(T10)objects[9],
(T11)objects[10],
(T12)objects[11],
(T13)objects[12],
(T14)objects[13],
(T15)objects[14],
(T16)objects[15]), splitOn, buffered);
int?totalCount = filter.IncludeMetadata ? reader.ReadSingleOrDefault <int?>() : null;
return(new PaginatedList <TReturn>(entity, filter, totalCount));
}
public JsonResult GetBookTypeListAjax(int pBookTypeId)
{
var conStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
IDbConnection connection = new SqlConnection(conStr);
connection.Open();
DynamicParameters dp = new DynamicParameters();
dp.Add("@pBookTypeId", pBookTypeId, DbType.Int32);
SqlMapper.GridReader result = connection.QueryMultiple("prBookTypeList", dp, commandType: CommandType.StoredProcedure);
var model = result.Read <BookType>().ToList();
return(Json(new { model }, JsonRequestBehavior.AllowGet));
}
public static IList <TReturn> Read <TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, T8, TReturn>(this SqlMapper.GridReader reader, Func <TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, T8, TReturn> func, string splitOn = "id", bool buffered = true)
{
Type[] types =
{
typeof(TFirst),
typeof(TSecond),
typeof(TThird),
typeof(TFourth),
typeof(TFifth),
typeof(TSixth),
typeof(TSeventh)
, typeof(T8)
};
return((IList <TReturn>)reader.Read(types, objects => func((TFirst)objects[0], (TSecond)objects[1], (TThird)objects[2], (TFourth)objects[3], (TFifth)objects[4], (TSixth)objects[5], (TSeventh)objects[6], (T8)objects[7]), splitOn, buffered));
}
/// <summary>
/// Many to many (N -> *) multi-map.
/// Load a list of children to a parent's property.
/// Example:
/// SELECT Parent.ID AS ParentId, TC.* FROM TableChild TC
/// INNER JOIN
/// (
/// SELECT ID From TableParent
/// ) Parent ON Parent.ID = TC.ParentID
///
/// </summary>
public static Dictionary <TParentKey, IList <TReturn> > ReadChild <TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, T8, T9, T10, T11, T12, T13, T14, T15, T16, TReturn, TParentKey>(this SqlMapper.GridReader reader, Func <TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, T8, T9, T10, T11, T12, T13, T14, T15, T16, TReturn> func, string splitOn = "Id", bool buffered = true)
{
Type[] types =
{
typeof(TFirst),
typeof(TSecond),
typeof(TThird),
typeof(TFourth),
typeof(TFifth),
typeof(TSixth),
typeof(TSeventh)
, typeof(T8)
, typeof(T9)
, typeof(T10)
, typeof(T11)
, typeof(T12)
, typeof(T13)
, typeof(T14)
, typeof(T15)
, typeof(T16)
, typeof(DapperManyToMany <TParentKey, TReturn>)
};
Dictionary <TParentKey, IList <TReturn> > childMap = new Dictionary <TParentKey, IList <TReturn> >();
reader.Read(types, objects =>
{
DapperManyToMany <TParentKey, TReturn> wrapper = (DapperManyToMany <TParentKey, TReturn>)objects[16];
wrapper.Child = func((TFirst)objects[0], (TSecond)objects[1], (TThird)objects[2], (TFourth)objects[3], (TFifth)objects[4], (TSixth)objects[5], (TSeventh)objects[6], (T8)objects[7], (T9)objects[8], (T10)objects[9], (T11)objects[10], (T12)objects[11], (T13)objects[12], (T14)objects[13], (T15)objects[14], (T16)objects[15]);
if (childMap.ContainsKey(wrapper.ParentId))
{
childMap[wrapper.ParentId].Add(wrapper.Child);
}
else
{
childMap.Add(wrapper.ParentId, new List <TReturn>
{
wrapper.Child
});
}
return(wrapper.Child);
}, splitOn, buffered);
return(childMap);
}
private List <Tuple <string, int> > GetCountryPopulations()
{
var result = new List <Tuple <string, int> >();
try
{
using (this.SqliteDbContextInstance)
{
var countries = this.SqliteDbContextInstance.Country.ToList();
foreach (Country country in countries)
{
int population = 0;
foreach (State state in country.States)
{
using (SQLiteConnection cnn = this.SimpleDbConnection())
{
cnn.Open();
string sql = "select * from City where Population is not null and StateId = " + state.StateId;
using (SqlMapper.GridReader multi = cnn.QueryMultiple(sql))
{
var cities = multi.Read <City>().ToList();
foreach (City city in cities)
{
population += city.Population.Value;
}
}
cnn.Close();
}
}
result.Add(
Tuple.Create(country.CountryName, population)
);
}
}
return(result);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(new List <Tuple <string, int> >());
}
}
/// <summary>
/// Executes query sql text or stored procedure for item with list - Async.
/// </summary>
/// <typeparam name="TFirst">Type of first item</typeparam>
/// <typeparam name="TSecond">Type of second list</typeparam>
/// <param name="sqlQuery">SQL Query</param>
/// <param name="commandType">SQL Query command type</param>
/// <param name="parametersCollection">Input/Output parameter list</param>
/// <param name="isReturnValueExists">Indicates whether return value, needed to be included</param>
/// <returns>QueryMultipleListsReturnItem</returns>
protected virtual async Task <QueryMultipleSingleAndListReturnItem <TFirst, TSecond> > ExecuteQueryMultipleSingleWithListByCommandTypeAsync <TFirst, TSecond>(string sqlQuery, CommandType commandType, IDbParameterList parametersCollection, bool isReturnValueExists)
{
QueryMultipleSingleAndListReturnItem <TFirst, TSecond> returnItem = new QueryMultipleSingleAndListReturnItem <TFirst, TSecond>();
GetDynamicParametersAndReturnDbParametersByDbParameters(parametersCollection, isReturnValueExists,
out DynamicParameters parameters, out IDbParameterList returnParameterList);
OpenConnectionForQueryExecution();
using (SqlMapper.GridReader gridReader = await ExecuteQueryMultipleAsync(sqlQuery, commandType, parameters))
{
returnItem.FirstItem = gridReader.ReadSingleOrDefault <TFirst>();
returnItem.SecondCollection = gridReader.Read <TSecond>();
}
CloseConnectionForQueryExecution();
SetReturnItemByReturnDbParameters(returnParameterList, parameters, isReturnValueExists, returnItem);
return(returnItem);
}
public async Task <Series> GetSeries(Guid id)
{
var sql = "select * from dbo.serieses where Id = @seriesId; " +
"select b.Id, b.Name, b.Description from dbo.books b inner join books_in_series bs on b.Id = bs.BookId and SeriesId = @seriesId";
Series series;
using (SqlMapper.GridReader multi = await Connection.QueryMultipleAsync(sql, new { seriesId = id }))
{
series = multi.Read <Series>().SingleOrDefault();
if (series != null)
{
//TODO: fix
//series.BooksInSeries = multi.Read<Book>().ToList();
}
}
return(series);
}
