J'essaie de retourner des données en utilisant Dapper via proc stocké
Ma classe DTO est similaire à celle ci-dessous (suppression de certaines propriétés pour des raisons de concision)
public class CarDTO
{
public int CarID { get; set; }
public string Manufacturer { get; set; }
public List<CarOptionDTO> CarOptions { get; set; }
}
Donc, dans la base de données, j'ai une table CarOption qui a une colonne CarID - c’est-à-dire qu’une voiture peut avoir de nombreuses options.
Mon appel DAL Layer à la minute est comme ci-dessous:
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));
}
L'implémentation de ma fonction Get est dans ma classe BaseRepository comme:
public T Get<T>(Func<IDbConnection, T> query)
{
using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
{
return query.Invoke(db);
}
}
Est-il possible d'utiliser Dapper, je peux aussi retourner à partir de ma proc stockée?
Mon proc stocké à la minute est comme ci-dessous:
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
La requête ci-dessus peut renvoyer plusieurs lignes ainsi que le CarID et le fabricant, ainsi que les autres propriétés que j'ai supprimées pour la qualité de service. Dapper les associera à la DTO comme prévu.
Cependant, c'est la façon de retourner la liste des CarOptions dans la procédure stockée - est-ce possible avec une autre requête ou devrait-il être séparé d'une manière ou d'une autre? Si j'ai par exemple CarID 1 et CarID 2 renvoyés, il peut y avoir 6 lignes dans la table CarOption avec les lignes CarID 1 et 4 dans la table CarOption avec CarID 2 et, idéalement, j'aimerais qu'elles soient toutes renvoyées à la collection CarOptions. via Dapper si possible?
Oui c'est possible. Il y a plusieurs manières d'aborder le scénario "un-à-plusieurs" avec 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();
}
Proc
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;
});
Utiliser une table temporaire dans la requête
Cela place tous les filtres par paramètres en une seule requête. Les requêtes ultérieures sont des sélections simples par 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
Appliquer un BaseDTO pour aider à l'égalité
Une fois que vous avez le BaseDTO, et que vous connectez votre identifiant, vous pouvez simplement dire des choses telles que: cars.Where (w => w.Equals (voiture)), (otherCar)), ou 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; }
}