通过存储的proc向dapper返回数据列表 [英] return a list of data via stored proc to dapper
问题描述
我正在尝试使用Dapper通过存储的proc返回数据
I am trying to return data using Dapper via stored proc
我的DTO类类似于以下内容(为简便起见删除了一些属性)
My DTO Class is similar to below (removed some properties for brevity)
public class CarDTO
{
public int CarID { get; set; }
public string Manufacturer { get; set; }
public List<CarOptionDTO> CarOptions { get; set; }
}
所以基本上在数据库中,我有一个CarOption表,其中有一个CarID列-例如,汽车可以有很多选择。
So basically in the DB I have a CarOption table that has a CarID column - i.e a Car can have many options.
我此时的DAL层调用如下:
My DAL Layer call at the minute is as below:
private string getCarDataSp = "[dbo].[GetCarData]";
public IEnumerable<CarDTO> GetCarData(int customerId, int year)
{
return Get(db => db.Query<CarDTO>(getCarDataSp , new { CustomerID = customerId, Year = year },
commandType: CommandType.StoredProcedure));
}
Get函数的实现在我的BaseRepository类中为:
Implementation of my Get function is in my BaseRepository class as:
public T Get<T>(Func<IDbConnection, T> query)
{
using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
{
return query.Invoke(db);
}
}
是否可以使用Dapper从存储中返回
Is it possible using Dapper I can return from my stored proc the CarOptions as well?
我此时存储的proc如下:
My stored proc at the minute is as below:
ALTER PROCEDURE [dbo].[GetCarData]
@CustomerID int,
@Year int
AS
BEGIN
SET NOCOUNT ON;
SELECT * from [dbo].Car c
JOIN [dbo].Customer cust ON c.CarID = cust.CarID
WHERE cust.CustID = @CustomerID AND cust.Year = @Year
END
上面的查询可能返回很多行, CarID和Manufacturer以及我为简洁而删除的其他属性。 Dapper会将这些映射到预期的DTO。
the query above may return many rows and the CarID and Manufacturer and the other properties I removed for Brevity. Dapper will map those back to the DTO as expected.
但是,这是如何返回存储的proc中的CarOptions列表-是否可以通过另一个查询还是应该以某种方式将其分开?例如,如果我返回了CarID 1和CarID 2,则CarOption表中的CarID为1的行可能有6行,CarID表2的CarOption的表中可能有4行,理想情况下,我希望将它们全部返回给CarOptions集合
However, it is how to return the list of CarOptions in the stored proc - is it possible with another query or should it be separated out somehow? If I have CarID 1 and CarID 2 returned, for example, there may be 6 rows in the CarOption table with CarID 1 and 4 rows in the CarOption table with CarID 2 and ideally, I would like them all to be returned to the CarOptions collection via Dapper if possible?
推荐答案
是的。有两种方法可以用dapper解决一对多情况:
Yes...it is possible. There are a couple of ways of addressing the "one-to-many" scenario with dapper:
ALTER PROCEDURE [dbo].[GetCarData]
@CustomerID int,
@Year int
AS
BEGIN
SET NOCOUNT ON;
--return cars
SELECT c.*
from [dbo].Car c
INNER JOIN [dbo].Customer cust ON c.CarID = cust.CarID
WHERE cust.CustID = @CustomerID AND cust.Year = @Year
--return options
SELECT opt.*
from [dbo].Car c
INNER JOIN [dbo].Customer cust ON c.CarID = cust.CarID
INNER JOIN dbo.CarOptions opt ON op.CarID = c.CarID
WHERE cust.CustID = @CustomerID AND cust.Year = @Year
END
DAL
var multi = db.QueryMultiple(getCarDataSp , new { CustomerID = customerId, Year = year },
commandType: CommandType.StoredProcedure));
var cars = multi.Read<CarDTO>();
var options = multi.Read<CarOptionDTO>();
//wire the options to the cars
foreach(var car in cars){
var carOptions = options.Where(w=>w.Car.CarID == car.CarID); //I would override Equals in general so you can write w.Car.Equals(car)...do this on a common DataModel class
car.Options = carOptions.ToList();
}
方法2-返回一个查询,分为DAL
过程
ALTER PROCEDURE [dbo].[GetCarData]
@CustomerID int,
@Year int
AS
BEGIN
SET NOCOUNT ON;
SELECT c.*, opt.*
from [dbo].Car c
INNER JOIN [dbo].Customer cust ON c.CarID = cust.CarID
LEFT OUTER JOIN dbo.CarOptions opt ON op.CarID = c.CarID
WHERE cust.CustID = @CustomerID AND cust.Year = @Year
END
DAL
var tuples = db.Query<CarDTO, CarOptionDTO,Tuple<CarDTO,CarOptionDTO>>(getCarDataSp , new { CustomerID = customerId, Year = year },
(car,opt)=> Tuple.Create(car,opt), commandType: CommandType.StoredProcedure);
//group tuples by car
var cars = tuple.GroupBy(gb=>gb.Item1.CarID) //again, overriding equals makes it so you can just to GroupBy(gb=>gb.Item1)
.Select(s=>{
var car = s.First().Item1;
var carOptions = s.Select(t=>t.Item2).ToList()
return car;
});
增强功能
使用a查询中的临时表
这会将所有按参数进行的过滤放入单个查询中。后续查询是按ID进行简单选择。
This puts all filtering by parameters into a single query. Subsequent queries are drop-dead simple selects by ID.
ALTER PROCEDURE [dbo].[GetCarData]
@CustomerID int,
@Year int
AS
BEGIN
SET NOCOUNT ON;
declare @t table(CarID int);
--filter cars (only deal with parameters here)
INSERT INTO @t(CarID)
SELECT c.CarID
FROM dbo.Car c
INNER JOIN [dbo].Customer cust ON c.CarID = cust.CarID
WHERE cust.CustID = @CustomerID AND cust.Year = @Year
--return cars
SELECT c.*
FROM [dbo].Car c
INNER JOIN @t t ON t.CarID = c.CarID
--return options
SELECT opt.*
FROM dbo.CarOptions opt
INNER JOIN @t t ON t.CarID = opt.CarID
END
应用BaseDTO来帮助实现平等
一旦有了BaseDTO,并列出您的ID,您可以简单地说出以下内容:cars.Where(w => w.Equals(car)),dictionary [car](如果在其中),if(car.Equals(otherCar))或results.GroupBy(gb => gb.Car)...
Once you have the BaseDTO, and wire up your ID you can simply say things such as:cars.Where(w=>w.Equals(car)), dictionary[car] (if it's in there), if(car.Equals(otherCar)), or results.GroupBy(gb=>gb.Car)...
public class BaseDTO
{
internal int ID { get; set; }
/// <summary>
/// If the obj is the same type with the same id we'll consider it equal.
/// </summary>
public override bool Equals(object obj)
{
if(obj == null || this.GetType() != obj.GetType())
{
return false;
}
return this.GetType().GetHashCode() == obj.GetType().GetHashCode() &&
this.ID == (BaseDTO)obj.ID;
}
/// <summary>
/// If you override equals, you should override gethashcode.
/// http://stackoverflow.com/questions/263400/what-is-the-best-algorithm-for-an-overridden-system-object-gethashcode#263416
/// </summary>
public override int GetHashCode()
{
unchecked
{
int hash = 17;
hash = hash * 23 + this.GetType().GetHashCode();
hash = hash * 23 + this.ID;
return hash;
}
}
}
public class CarDTO : BaseDTO
{
public int CarID
{
get { return this.ID; }
set { this.ID = value; }
}
public string Manufacturer { get; set; }
public List<CarOptionDTO> CarOptions { get; set; }
}
这篇关于通过存储的proc向dapper返回数据列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!