How to retrieve the information in an order entity and invoice entity with a QueryMultiple entity ?
Thanks
QueryMultiple
is used when you are accessing multiple result sets, i.e. multiple select
, as in:
select * from Order where Id=@id
select * from Invoice where Id = (...probably some sub-query)
At the moment, there is no inbuilt API to stitch this type of query together; instead you would do something like:
using(var multi = conn.QueryMultiple(...)) {
var order = multi.ReadSingle<Order>();
order.Invoice = multi.ReadSingleOrDefault<Invoice>(); // could be null if 0 rows
return order;
}
I would like to add an improved API for this scenario, but it is very awkward to express "join this to that using this property as the association, where {this}.{SomeMember} equals {that}.{SomeOtherMember}".
However, if you are actually doing a single query, as in:
select o.*, i.*
from Order o
left outer join Link l on ...
left outer join Invoice i on ...
where o.Id = @id
then you can use the various Query<,...,>
overloads; for example:
int id = ...
var order = conn.Query<Order, Invoice, Order>(sql,
(x,y) => {x.Invoice = y; return x;}, args: new { id }, splitOn: "NumOrder").Single();
Generic code for three tables:
public static Tuple<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>> ExecuteQueryMultiple<T1, T2, T3>(string sql, object parameters,
Func<GridReader, IEnumerable<T1>> func1,
Func<GridReader, IEnumerable<T2>> func2,
Func<GridReader, IEnumerable<T3>> func3)
{
var objs = getMultiple(sql, parameters, func1, func2, func3);
return Tuple.Create(objs[0] as IEnumerable<T1>, objs[1] as IEnumerable<T2>, objs[2] as IEnumerable<T3>);
}
private static List<object> getMultiple(string procedureName, object param, params Func<GridReader, object>[] readerFuncs)
{
var returnResults = new List<object>();
using (SqlConnection sqlCon = new SqlConnection(connectionString))
{
var gridReader = sqlCon.QueryMultiple(procedureName, param, commandType: CommandType.StoredProcedure);
foreach (var readerFunc in readerFuncs)
{
var obj = readerFunc(gridReader);
returnResults.Add(obj);
}
}
return returnResults;
}
Controller:
[HttpPost]
public ActionResult GetCommodityDetails(int ID)
{
var data = new List<Commodity>();
DynamicParameters param = new DynamicParameters();
param.Add("@ATTRIBUTETYPE", "Your parameter");
param.Add("@CID", Your parameter);
var result = DapperORM.ExecuteQueryMultiple("Store procedure name", param, gr => gr.Read<order>(), gr => gr.Read<Invoice>(), gr => gr.Read<Link>());
return Json(result, JsonRequestBehavior.AllowGet);
}
You can use this concept. It worked for me