[C#/COMMON/DAPPER] 부모 객체를 포함해 조회하기

■ 부모 객체를 포함해 조회하는 방법을 보여준다.

▶ 부모 객체를 포함해 조회하기 예제 (C#)


using System.Collections.Generic;
using System.Data;

public class MenuModel
{
    public string ID { get; set; }

    ...
}

public class MenuStructureModel
{
    public string MENU_TEMPLATE_ID { get; set; }

    public string TARGET { get; set; }

    public string ID { get; set; }

    ...

    public string MENU_ID { get; set; }

    ...

    public MenuModel Menu { get; set; }

    ...
}

...

private IDbConnection connetcion;

...

string sql = @"
SELECT
    PARENT.*
   ,CHILD.*
FROM      COM_MENU_STRUCTURE CHILD
LEFT JOIN COM_MENU           PARENT ON PARENT.ID = CHILD.MENU_ID
WHERE CHILD.MENU_TEMPLATE_ID = @MENU_TEMPLATE_ID
AND   CHILD.TARGET           = @TARGET
ORDER BY CHILD.ROWID ASC;
";

var result = this.connection.QueryIncludingParent<MenuModel, string, MenuStructureModel>
(
    sql,
    parent => parent.ID,
    (child, parent) => { child.Menu = parent; },
    parameter : new { MENU_TEMPLATE_ID = menuTemplateID, TARGET = target },
    splitOn : "MENU_TEMPLATE_ID"
);

using System.Collections.Generic;

using System.Data;

public class MenuModel

{

public string ID { get; set; }

...

}

public class MenuStructureModel

{

public string MENU_TEMPLATE_ID { get; set; }

public string TARGET { get; set; }

public string ID { get; set; }

...

public string MENU_ID { get; set; }

...

public MenuModel Menu { get; set; }

...

}

...

private IDbConnection connetcion;

...

string sql = @"

SELECT

PARENT.*

,CHILD.*

FROM COM_MENU_STRUCTURE CHILD

LEFT JOIN COM_MENU PARENT ON PARENT.ID = CHILD.MENU_ID

WHERE CHILD.MENU_TEMPLATE_ID = @MENU_TEMPLATE_ID

AND CHILD.TARGET = @TARGET

ORDER BY CHILD.ROWID ASC;

var result = this.connection.QueryIncludingParent<MenuModel, string, MenuStructureModel>

(

sql,

parent => parent.ID,

(child, parent) => { child.Menu = parent; },

parameter : new { MENU_TEMPLATE_ID = menuTemplateID, TARGET = target },

splitOn : "MENU_TEMPLATE_ID"

);

※ COM_MENU 테이블은 MenuModel 타입에 대응하고 PK는 ID이다.
※ COM_MENU_STRUCTURE 테이블은 MenuStructureModel 타입에 대응하고 PK는 MENU_TEMPLATE_ID, TARGET, ID이며 FK는 MENU_ID이다.
※ SQL문에서 부모 테이블 필드를 먼저 나열하고 자식 테이블 필드는 다음에 나열해야 한다.

▶ 부모 객체를 포함해 조회하기 (C#)


using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

using Dapper;

#region 부모 객체를 포함해 조회하기 - QueryIncludingParent<TParent, TChild, TParentKey>(connection, sql, parentKeySelector, childSelector, parameter, transaction, buffered, splitOn, commandTimeout, commandType)

/// <summary>
/// 부모 객체를 포함해 조회하기
/// </summary>
/// <typeparam name="TParent">부모 타입</typeparam>
/// <typeparam name="TParentKey">부모 키 타입</typeparam>
/// <typeparam name="TChild">자식 타입</typeparam>
/// <param name="connection">연결</param>
/// <param name="sql">SQL</param>
/// <param name="parentKeySelector">부모 키 셀렉터</param>
/// <param name="childSetter">자식 설정자</param>
/// <param name="parameter">매개 변수</param>
/// <param name="transaction">트랜잭션</param>
/// <param name="buffered">버퍼링 여부</param>
/// <param name="splitOn">분리자</param>
/// <param name="commandTimeout">명령 타임아웃</param>
/// <param name="commandType">명령 타입</param>
/// <returns>자식 리스트</returns>
public static List<TChild> QueryIncludingParent<TParent, TParentKey, TChild>
(
    this IDbConnection        connection,
    string                    sql,
    Func<TParent, TParentKey> parentKeySelector,
    Action<TChild, TParent>   childSetter,
    dynamic                   parameter = null,
    IDbTransaction            transaction = null,
    bool                      buffered = true,
    string                    splitOn = "ID",
    int?                      commandTimeout = null,
    CommandType?              commandType = null
)
{
    Dictionary<TParentKey, TParent> parentDictionary = new Dictionary<TParentKey, TParent>();

    List<TChild> childList = new List<TChild>();

    connection.Query<TParent, TChild, TChild>
    (
        sql,
        (parent, child) => {

            TParentKey parentKey = parentKeySelector(parent);

            if(parentKey != null)
            {
                if(parentDictionary.ContainsKey(parentKey))
                {
                    childSetter(child, parentDictionary[parentKey]);
                }
                else
                {
                    childSetter(child, parent);

                    parentDictionary.Add(parentKey, parent);
                }
            }

            childList.Add(child);

            return child;
        },
        parameter as object,
        transaction,
        buffered,
        splitOn,
        commandTimeout,
        commandType
    );

    return childList;
}

#endregion

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using Dapper;

#region 부모 객체를 포함해 조회하기 - QueryIncludingParent<TParent, TChild, TParentKey>(connection, sql, parentKeySelector, childSelector, parameter, transaction, buffered, splitOn, commandTimeout, commandType)

/// <summary>

/// 부모 객체를 포함해 조회하기

/// </summary>

/// <typeparam name="TParent">부모 타입</typeparam>

/// <typeparam name="TParentKey">부모 키 타입</typeparam>

/// <typeparam name="TChild">자식 타입</typeparam>

/// <param name="connection">연결</param>

/// <param name="sql">SQL</param>

/// <param name="parentKeySelector">부모 키 셀렉터</param>

/// <param name="childSetter">자식 설정자</param>

/// <param name="parameter">매개 변수</param>

/// <param name="transaction">트랜잭션</param>

/// <param name="buffered">버퍼링 여부</param>

/// <param name="splitOn">분리자</param>

/// <param name="commandTimeout">명령 타임아웃</param>

/// <param name="commandType">명령 타입</param>

/// <returns>자식 리스트</returns>

public static List<TChild> QueryIncludingParent<TParent, TParentKey, TChild>

(

this IDbConnection connection,

string sql,

Func<TParent, TParentKey> parentKeySelector,

Action<TChild, TParent> childSetter,

dynamic parameter = null,

IDbTransaction transaction = null,

bool buffered = true,

string splitOn = "ID",

int? commandTimeout = null,

CommandType? commandType = null

)

{

Dictionary<TParentKey, TParent> parentDictionary = new Dictionary<TParentKey, TParent>();

List<TChild> childList = new List<TChild>();

connection.Query<TParent, TChild, TChild>

(

sql,

(parent, child) => {

TParentKey parentKey = parentKeySelector(parent);

if(parentKey != null)

{

if(parentDictionary.ContainsKey(parentKey))

{

childSetter(child, parentDictionary[parentKey]);

}

else

{

childSetter(child, parent);

parentDictionary.Add(parentKey, parent);

}

childList.Add(child);

return child;

parameter as object,

transaction,

buffered,

splitOn,

commandTimeout,

commandType

);

return childList;

}

#endregion