Dapper ORM 사용 예제

ADO.NET과 가장 동일하며 쿼리문을 직접 제어할 때 사용되는 Dapper 에 대한 사용법을 정리해 보았다.

- Dapper ORM 은 일반적으로 MSSQL, MYSQL(MariaDB), Oracle, PostgreSQL, SQLite를 지원하며 이 밖에 다른 프로바이더
들도 지원할 수 있다.
- Dapper 는 오픈 소스이다 (https://github.com/DapperLib/Dapper)
- NuGet 패키지를 통해 설치 가능하다 (https://www.nuget.org/packages/dapper)
- 연결문자열을 만들기 위해 MSSQL 기준 NetCore 에서는 System.Data.SqlClient 를 NuGet 에서 받아야 할 수 있다.
- NET의 ORM중 EFCore 대비 높은 성능을 보여준다. 

아래의 예제는 Dapper 2.1 버전에서 작성 되었습니다. (구버전일 경우 사용법이 조금 다를 수 있음을 참고해 주세요)

A. 준비작업
 - 클래스 상단에 커넥션 문자열을 작성한 예시

public class HomeController : Controller
{	
	private readonly string _connectionString = @"Server=127.0.0.1;Database=DogDB2;User Id=dog;Password=123456;TrustServerCertificate=True;";    
}

 

B. Sql 쿼리에는 반드시 SqlParameter 를 사용하자.
 - 인잭션 공격에 대해 어느정도 방어 효과가 있다. 
 - 즉 "WHERE Name = '" + DOG + "'  "  문자열 더하기는 좋지 않다.
 - 프로시저가 아닌 SQL문에 대해서 WHERE Name = '@Name' 이 아닌
  WHERE = @Name 으로 사용해야 한다 즉 홀따옴표 '' 는 제거해야 정상 작동한다.

 

 

1. Execute() 는 SQL 명령어를 실행할 떄 사용되는 메소드이다.
- ADO.Net에서 ExecuteNonQuery() 이것과 비슷하게 사용된다.

- 반환값이 void 가 아니라 실행으로 인해 int로 영향을 받는 행의 갯수를 반환 한다
- 성능이슈로 프로시저 작성시  SET NOCOUNT ON; 옵션을 사용하기에 void 처럼 사용하는것이
일반적이다.

- 주로 조회가 아닌 등록,수정,삭제,테이블생성 등에 사용된다

        /// <summary>
        /// Execute 는 쿼리를 실행할 때 사용된다. (Create, Insert, Update, Delete...)
        /// </summary>
        /// <returns></returns>
        [Route("index")]
        public JsonResult Index()
        {            
            using (IDbConnection con = new SqlConnection(_connectionString))
            {
                // Execute
                string queryCreate = @"
                    CREATE TABLE [dbo].[Dog](
	                    [DogId] [int] IDENTITY(1,1) NOT NULL,
	                    [Name] [varchar](30) NULL,
	                    [Address] [varchar](100) NULL,
	                    [Zipcode] [varchar](10) NULL,
	                    [Tel] [varchar](20) NULL,
	                    [CreateDate] [smalldatetime] NULL
                    )                    
                ";

                con.Execute(queryCreate);

                // Execute (데이터 Insert)
                var p = new DynamicParameters();
                p.Add("@Name", "김도그");
                p.Add("@Address", "서울시 강남구 다리밑");
                p.Add("@Zipcode", "10000");
                p.Add("@Tel", "01012341234");
                p.Add("@CreateDate", DateTime.Now);

                string queryInsert = @"
                    INSERT INTO [dbo].[Dog]
                               ([Name]
                               ,[Address]
                               ,[Zipcode]
                               ,[Tel]
                               ,[CreateDate])
                         VALUES
                               (@Name
                                ,@Address
                                ,@ZipCode
                                ,@Tel
                                ,@CreateDate)
                ";

                con.Execute(queryInsert, p);

            }

            return Json("ok");            
        }

        /// <summary>
        /// Execute 를 비동기로 실행한다
        /// </summary>
        /// <returns></returns>
        [Route("index2")]
        public async Task<JsonResult> Index2()
        {            
            using (IDbConnection con = new SqlConnection(_connectionString))
            {
                string queryInsertAsync = @"
                    INSERT INTO [dbo].[Dog]
                               ([Name]
                               ,[Address]
                               ,[Zipcode]
                               ,[Tel]
                               ,[CreateDate])
                         VALUES
                               (@Name
                                ,@Address
                                ,@ZipCode
                                ,@Tel
                                ,@CreateDate)
                ";

                var p2 = new DynamicParameters();
                p2.Add("@Name", "이도그");
                p2.Add("@Address", "서울시 서초구 다리밑");
                p2.Add("@Zipcode", "10002");
                p2.Add("@Tel", "01012341235");
                p2.Add("@CreateDate", DateTime.Now);

                await con.ExecuteAsync(queryInsertAsync, p2);
            }

            return Json("ok");
        }

 

2. Query() 는 SQL 의 SELECT 결과를 반환한다.
 - 레코드셋, 레코드 단일, 스칼라, 멀티레코드 형식을 지원한다

using (IDbConnection con = new SqlConnection(_connectionString))
{   
    // 1. List 에 담기 (여러마리의 도그 정보를 가져옴)
    string query = @"
        SELECT *
        FROM DOG WITH(NOLOCK)                    
    ";

    var list = con.Query<Dog>(query, null).ToList();


    // 2. 하나의 Dog에 담기
    var p = new DynamicParameters();
    p.Add("@Name", "김도그");

    string querySingle = @"
        SELECT *
        FROM DOG WITH(NOLOCK)
        WHERE Name = @Name
    ";

    // 두가지 방법중 하나를 선택해서 사용
    Dog? dog = con.QuerySingleOrDefault<Dog>(querySingle, p);
    //Dog dog2 = con.Query<Dog>(querySingle, p).Single();


    // 3. 쿼리의 첫번째 1행1열 1개의 반환 값 (Scalar)
    var p2 = new DynamicParameters();
    p2.Add("@Name", "이도그");

    string queryScalar = @"
        SELECT *
        FROM DOG WITH(NOLOCK)
        WHERE Name = @Name
    ";

    int dogID = con.QueryFirst<int>(querySingle, p2);

    // 4. 쿼리 결과에 대해 여러개 받기
    string queryMulti = @"
        SELECT COUNT(*) AS Count
        FROM DOG WITH(NOLOCK)

        SELECT *
        FROM DOG WITH(NOLOCK)
    ";

    using (var multiResult = con.QueryMultiple(queryMulti))
    {
        // 첫 번째 결과 집합
        int count = multiResult.ReadSingle<int>();

        // 두 번째 결과 집합
        List<Dog> dogList = multiResult.Read<Dog>().AsList();
    }
}

 

3. 프로시저 사용 및 Output 파라미터 받기
- GetDogName 이란 프로시저를 통해 Output 파라미터 가져오기
a) SQL

CREATE PROCEDURE GetDogName
    @DogID int,
    @OutputName NVARCHAR(50) OUTPUT
AS
BEGIN
    SELECT TOP 1 @OutputName = Name
    FROM DOG WITH(NOLOCK)
    WHERE DogId = @DogID
END

 b) C#

  using (IDbConnection con = new SqlConnection(_connectionString))
  {
      var p3 = new DynamicParameters();
      p3.Add("@DogID", 1);
      p3.Add("@OutputName", dbType: DbType.String, size: 50, direction: ParameterDirection.Output);

      con.Execute("GetDogName", p3, commandType: CommandType.StoredProcedure);

      string dogName = p3.Get<string>("@OutputName");
  }

 

4. 프로시저 사용 및 Return 파라미터 받기

 - INSERT 나 UPDATE 또는 DELETE의 처리 결과를 INT로 받을 때  주로 Return 파라미터를 사용한다

 a) SQL

CREATE PROCEDURE DogInsert
    @Name NVARCHAR(50),
    @Address NVARCHAR(100),
    @ZipCode NVARCHAR(10),
    @Tel NVARCHAR(15),
    @CreateDate DATETIME
AS
BEGIN

	BEGIN TRY

    	INSERT INTO [dbo].[Dog] ([Name], [Address], [Zipcode], [Tel], [CreateDate])
    	VALUES (@Name, @Address, @ZipCode, @Tel, @CreateDate);
    
    	RETURN 1;
        
    END TRY
    BEGIN CATCH
    
    	RETURN 0;
        
    END CATCH
    
END

 

b) c#

using (IDbConnection con = new SqlConnection("YourConnectionStringHere"))
{
    var p = new DynamicParameters();
    p.Add("@Name", "누렁이");
    p.Add("@Address", "경기도 안산시 공단");
    p.Add("@ZipCode", "12345");
    p.Add("@Tel", "01012345678");
    p.Add("@CreateDate", DateTime.Now);
    p.Add("ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

    con.Execute("DogInsert", p, commandType: CommandType.StoredProcedure);
    int returnValue = p.Get<int>("ReturnValue");
}

 

 

5. 위 2번 Query() 를 비동기 형태 호출로 변경한 예제
 - 컨트롤러의 반환부분이 async Task<> 이며 
 - dapper 메소드 실행부가 await 키워드와 메소드 뒤에 Async 로 변경되어 있는 차이니 응용하여 사용하자

public async Task<JsonResult> Index2()
{
    string name = string.Empty;

    using (IDbConnection con = new SqlConnection(_connectionString))
    {
        // 1. List 에 담기 (여러마리의 도그 정보를 가져옴)
        string query = @"
            SELECT *
            FROM DOG WITH(NOLOCK)                    
        ";

        var list = (await con.QueryAsync<Dog>(query)).ToList();

        // 2. 하나의 Dog에 담기
        var p = new DynamicParameters();
        p.Add("@Name", "김도그");

        string querySingle = @"
            SELECT *
            FROM DOG WITH(NOLOCK)
            WHERE Name = @Name
        ";

        // 두가지 방법중 하나를 선택해서 사용
        Dog? dog = await con.QuerySingleOrDefaultAsync<Dog>(querySingle, p);
        //Dog dog2 = (await con.QueryAsync<Dog>(querySingle, p)).Single();

        // 3. 쿼리의 첫번째 1행1열 1개의 반환 값 (Scalar)
        var p2 = new DynamicParameters();
        p2.Add("@Name", "이도그");

        string queryScalar = @"
            SELECT *
            FROM DOG WITH(NOLOCK)
            WHERE Name = @Name
        ";

        int dogID = await con.QueryFirstAsync<int>(queryScalar, p2);

        // 4. 쿼리 결과에 대해 여러개 받기
        string queryMulti = @"
            SELECT COUNT(*) AS Count
            FROM DOG WITH(NOLOCK)

            SELECT *
            FROM DOG WITH(NOLOCK)
        ";

        using (var multiResult = await con.QueryMultipleAsync(queryMulti))
        {
            // 첫 번째 결과 집합
            int count = multiResult.ReadSingle<int>();

            // 두 번째 결과 집합
            List<Dog> dogList = (await multiResult.ReadAsync<Dog>()).AsList();
        }
    }

    return Json("ok");
}