저장된 proc을 통해 데이터 목록을 반환합니다.

.net c# dapper sql sql-server

문제

저장 프로 시저를 통해 Dapper를 사용하여 데이터를 반환하려고합니다.

내 DTO 클래스는 다음과 유사합니다 (간결성을 위해 일부 속성을 제거함).

public class CarDTO
{
    public int CarID { get; set; }
    public string Manufacturer { get; set; }
    public List<CarOptionDTO> CarOptions { get; set; }
}

그래서 기본적으로 DB에는 CarID 열이있는 CarOption 테이블이 있습니다. 즉 Car에는 여러 옵션이 있습니다.

분당 내 DAL 레이어 통화는 다음과 같습니다.

    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 클래스에 있습니다.

    public T Get<T>(Func<IDbConnection, T> query)
    {
        using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
        {
            return query.Invoke(db);
        }
    }

Dapper를 사용하여 저장 프로 시저에서 CarOptions도 반환 할 수 있습니까?

잠시 내 저장된 proc는 아래와 같습니다 :

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에 매핑합니다.

그러나 저장된 프로 시저에서 CarOptions 목록을 반환하는 방법입니다 - 다른 쿼리를 사용하거나 가능하면 별개로 분리해야합니까? 예를 들어, CarID 1과 CarID 2가 반환 된 경우 CarID가있는 CarOption 테이블에 6 행이 있고 CarID가 2 인 CarOption 테이블에 4 개의 행이있을 수 있으며 이상적으로는 CarOptions 컬렉션으로 반환해야합니다. 가능하다면 Dapper를 통해?

수락 된 답변

예 ... 가능합니다. dapper를 사용하여 "일대 다"시나리오를 처리하는 몇 가지 방법이 있습니다.

방법 1 - 두 개의 쿼리를 반환하고 DAL로 결합

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로 분할 된 하나의 쿼리 반환

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

향상

쿼리에서 임시 테이블 사용

이것은 매개 변수에 의한 모든 필터링을 단일 쿼리에 넣습니다. 후속 쿼리는 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)), 사전 (car) (있는 경우), if (car.Equals (otherCar)) 또는 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; }
}


아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow