DAPPER Archives - icodebroker

[C#/ASP.NET MVC] DAPPER를 사용해 ASP.NET MVC 애플리케이션 만들기

■ DAPPER를 사용해 ASP.NET MVC 애플리케이션을 만드는 방법을 보여준다. ▶ TestDB.sql


CREATE TABLE dbo.Task
(
    ID           INT           NOT NULL PRIMARY KEY IDENTITY(1, 1) -- ID
   ,Title        NVARCHAR(140) NOT NULL                            -- 제목
   ,IsCompleted  BIT                                               -- 완료 여부
   ,CreateDate   DATETIME      DEFAULT(GETDATE())                  -- 생성일
)
GO

CREATE TABLE dbo.Task

(

ID INT NOT NULL PRIMARY KEY IDENTITY(1, 1) -- ID

,Title NVARCHAR(140) NOT NULL -- 제목

,IsCompleted BIT -- 완료 여부

,CreateDate DATETIME DEFAULT(GETDATE()) -- 생성일

)

▶ TaskModel.cs


using System;

namespace TestProject
{
    /// <summary>
    /// 작업 모델
    /// </summary>
    public class TaskModel
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Property
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region ID - ID

        /// <summary>
        /// ID
        /// </summary>
        public int ID { get; set; }

        #endregion
        #region 제목 - Title

        /// <summary>
        /// 제목
        /// </summary>
        public string Title { get; set; }

        #endregion
        #region 완료 여부 - IsCompleted

        /// <summary>
        /// 완료 여부
        /// </summary>
        public bool IsCompleted { get; set; }

        #endregion
        #region 생성일 - CreateDate

        /// <summary>
        /// 생성일
        /// </summary>
        public DateTime CreateDate { get; set; }

        #endregion
    }
}

using System;

namespace TestProject

{

/// <summary>

/// 작업 모델

/// </summary>

public class TaskModel

{

//////////////////////////////////////////////////////////////////////////////////////////////////// Property

////////////////////////////////////////////////////////////////////////////////////////// Public

#region ID - ID

/// <summary>

/// ID

/// </summary>

public int ID { get; set; }

#endregion

#region 제목 - Title

/// <summary>

/// 제목

/// </summary>

public string Title { get; set; }

#endregion

#region 완료 여부 - IsCompleted

/// <summary>

/// 완료 여부

/// </summary>

public bool IsCompleted { get; set; }

#endregion

#region 생성일 - CreateDate

/// <summary>

/// 생성일

/// </summary>

public DateTime CreateDate { get; set; }

#endregion

}

▶ TaskRepository.cs


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

using Dapper;

namespace TestProject
{
    /// <summary>
    /// 작업 저장소
    /// </summary>
    public class TaskRepository
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Field
        ////////////////////////////////////////////////////////////////////////////////////////// Private

        #region Field

        /// <summary>
        /// DB 연결
        /// </summary>
        private IDbConnection connection = new SqlConnection
        (
            ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString
        );

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Field
        ////////////////////////////////////////////////////////////////////////////////////////// Private

        #region 추가하기 - Add(task)

        /// <summary>
        /// 추가하기
        /// </summary>
        /// <param name="task">작업</param>
        public void Add(TaskModel task)
        {
            string sql = @"
INSERT INTO dbo.Task
(
    Title
   ,IsCompleted
)
VALUES
(
    @Title
   ,CAST('false' AS BIT)
)
";

            this.connection.Execute(sql, task);
        }

        #endregion
        #region 리스트 구하기 - GetList()

        /// <summary>
        /// 리스트 구하기
        /// </summary>
        /// <returns>리스트</returns>
        public List<TaskModel> GetList()
        {
            return this.connection.Query<TaskModel>("SELECT * FROM dbo.Task ORDER BY ID DESC").ToList();
        }

        #endregion
        #region 작업 완료하기 - CompleteTask(id)

        /// <summary>
        /// 작업 완료하기
        /// </summary>
        /// <param name="id">ID</param>
        public void CompleteTask(int id)
        {
            this.connection.Execute("UPDATE dbo.Task SET IsCompleted = ~IsCompleted WHERE ID = @ID", new { id });
        }

        #endregion
    }
}

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using Dapper;

namespace TestProject

{

/// <summary>

/// 작업 저장소

/// </summary>

public class TaskRepository

{

//////////////////////////////////////////////////////////////////////////////////////////////////// Field

////////////////////////////////////////////////////////////////////////////////////////// Private

#region Field

/// <summary>

/// DB 연결

/// </summary>

private IDbConnection connection = new SqlConnection

(

ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString

);

#endregion

//////////////////////////////////////////////////////////////////////////////////////////////////// Field

////////////////////////////////////////////////////////////////////////////////////////// Private

#region 추가하기 - Add(task)

/// <summary>

/// 추가하기

/// </summary>

/// <param name="task">작업</param>

public void Add(TaskModel task)

{

string sql = @"

INSERT INTO dbo.Task

(

Title

,IsCompleted

)

VALUES

(

@Title

,CAST('false' AS BIT)

)

this.connection.Execute(sql, task);

}

#endregion

#region 리스트 구하기 - GetList()

/// <summary>

/// 리스트 구하기

/// </summary>

/// <returns>리스트</returns>

public List<TaskModel> GetList()

{

return this.connection.Query<TaskModel>("SELECT * FROM dbo.Task ORDER BY ID DESC").ToList();

}

#endregion

#region 작업 완료하기 - CompleteTask(id)

/// <summary>

/// 작업 완료하기

/// </summary>

/// <param name="id">ID</param>

public void CompleteTask(int id)

{

this.connection.Execute("UPDATE dbo.Task SET IsCompleted = ~IsCompleted WHERE ID = @ID", new { id });

}

#endregion

}

▶ TaskController.cs


using System.Web.Mvc;

namespace TestProject.Controllers
{
    /// <summary>
    /// 작업 컨트롤러
    /// </summary>
    public class TaskController : Controller
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Field
        ////////////////////////////////////////////////////////////////////////////////////////// Private

        #region Field

        /// <summary>
        /// 태스크 저장소
        /// </summary>
        private TaskRepository repository = new TaskRepository();

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 인덱스 페이지 처리하기 - Index()

        /// <summary>
        /// 인덱스 페이지 처리하기
        /// </summary>
        /// <returns>액션 결과</returns>
        public ActionResult Index()
        {
            var resultList = this.repository.GetList();

            return View(resultList);
        }

        #endregion
        #region 생성 페이지 처리하기 - Create()

        /// <summary>
        /// 생성 페이지 처리하기
        /// </summary>
        /// <returns>액션 결과</returns>
        public ActionResult Create()
        {
            return View();
        }

        #endregion
        #region 생성 처리 페이지 처리하기 - ProcessCreate(title)

        /// <summary>
        /// 생성 처리 페이지 처리하기
        /// </summary>
        /// <param name="title">제목</param>
        /// <returns>액션 결과</returns>
        [HttpPost]
        public ActionResult ProcessCreate(string title)
        {
            this.repository.Add(new TaskModel { Title = title });

            return RedirectToAction("Index");
        }

        #endregion
        #region 작업 완료 페이지 처리하기 - CompleteTask(id)

        /// <summary>
        /// 작업 완료 페이지 처리하기
        /// </summary>
        /// <param name="id">ID</param>
        /// <returns>액션 결과</returns>
        public ActionResult CompleteTask(int id)
        {
            this.repository.CompleteTask(id);

            return RedirectToAction("Index");
        }

        #endregion
    }
}

using System.Web.Mvc;

namespace TestProject.Controllers

{

/// <summary>

/// 작업 컨트롤러

/// </summary>

public class TaskController : Controller

{

//////////////////////////////////////////////////////////////////////////////////////////////////// Field

////////////////////////////////////////////////////////////////////////////////////////// Private

#region Field

/// <summary>

/// 태스크 저장소

/// </summary>

private TaskRepository repository = new TaskRepository();

#endregion

//////////////////////////////////////////////////////////////////////////////////////////////////// Method

////////////////////////////////////////////////////////////////////////////////////////// Public

#region 인덱스 페이지 처리하기 - Index()

/// <summary>

/// 인덱스 페이지 처리하기

/// </summary>

/// <returns>액션 결과</returns>

public ActionResult Index()

{

var resultList = this.repository.GetList();

return View(resultList);

}

#endregion

#region 생성 페이지 처리하기 - Create()

/// <summary>

/// 생성 페이지 처리하기

/// </summary>

/// <returns>액션 결과</returns>

public ActionResult Create()

{

return View();

}

#endregion

#region 생성 처리 페이지 처리하기 - ProcessCreate(title)

/// <summary>

/// 생성 처리 페이지 처리하기

/// </summary>

/// <param name="title">제목</param>

/// <returns>액션 결과</returns>

[HttpPost]

public ActionResult ProcessCreate(string title)

{

this.repository.Add(new TaskModel { Title = title });

return RedirectToAction("Index");

}

#endregion

#region 작업 완료 페이지 처리하기 - CompleteTask(id)

/// <summary>

/// 작업 완료 페이지 처리하기

/// </summary>

/// <param name="id">ID</param>

/// <returns>액션 결과</returns>

public ActionResult CompleteTask(int id)

{

this.repository.CompleteTask(id);

return RedirectToAction("Index");

}

#endregion

}

▶

[C#/ASP.NET] DAPPER CRUD 작업하기

■ DAPPER CRUD 작업을 하는 방법을 보여준다. ▶ TestDB.sql


CREATE TABLE dbo.Maxims
(
    ID         INT           NOT NULL IDENTITY(1, 1)
   ,[Name]     NVARCHAR(100) NOT NULL
   ,Content    NVARCHAR(500) NOT NULL
   ,CreateDate DATETIME      NULL     DEFAULT(GETDATE())
    PRIMARY KEY CLUSTERED (ID ASC)
)
GO

CREATE TABLE dbo.Maxims

(

ID INT NOT NULL IDENTITY(1, 1)

,[Name] NVARCHAR(100) NOT NULL

,Content NVARCHAR(500) NOT NULL

,CreateDate DATETIME NULL DEFAULT(GETDATE())

PRIMARY KEY CLUSTERED (ID ASC)

)

▶ Maxims.cs


using System;

namespace TestProject.Models
{
    /// <summary>
    /// 격언
    /// </summary>
    public class Maxims
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Property
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region ID - ID

        /// <summary>
        /// ID
        /// </summary>
        public int ID { get; set; }

        #endregion
        #region 명칭 - Name

        /// <summary>
        /// 명칭
        /// </summary>
        public string Name { get; set; }

        #endregion
        #region 내용 - Content

        /// <summary>
        /// 내용
        /// </summary>
        public string Content { get; set; }

        #endregion
        #region 생성일 - CreateDate

        /// <summary>
        /// 생성일
        /// </summary>
        public DateTime CreateDate { get; set; }

        #endregion
    }
}

using System;

namespace TestProject.Models

{

/// <summary>

/// 격언

/// </summary>

public class Maxims

{

//////////////////////////////////////////////////////////////////////////////////////////////////// Property

////////////////////////////////////////////////////////////////////////////////////////// Public

#region ID - ID

/// <summary>

/// ID

/// </summary>

public int ID { get; set; }

#endregion

#region 명칭 - Name

/// <summary>

/// 명칭

/// </summary>

public string Name { get; set; }

#endregion

#region 내용 - Content

/// <summary>

/// 내용

/// </summary>

public string Content { get; set; }

#endregion

#region 생성일 - CreateDate

/// <summary>

/// 생성일

/// </summary>

public DateTime CreateDate { get; set; }

#endregion

}

▶ MaximsServiceRegistory.cs


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

using Dapper;

using TestProject.Models;

namespace TestProject
{
    /// <summary>
    /// 격언 서비스 저장소
    /// </summary>
    public class MaximServiceRepository
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Field
        ////////////////////////////////////////////////////////////////////////////////////////// Private

        #region Field

        /// <summary>
        /// DB 연결
        /// </summary>
        private IDbConnection connection = new SqlConnection
        (
            ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString
        );

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 리스트 구하기 - GetList()

        /// <summary>
        /// 리스트 구하기
        /// </summary>
        /// <returns>리스트</returns>
        public List<Maxims> GetList()
        {
            string sql = "SELECT ID, [Name], Content, CreateDate FROM dbo.Maxims ORDER BY ID ASC";

            return this.connection.Query<Maxims>(sql).ToList();
        }

        #endregion
        #region 구하기 - Get(id)

        /// <summary>
        /// 구하기
        /// </summary>
        /// <param name="id">ID</param>
        /// <returns>격언</returns>
        public Maxims Get(int id)
        {
            string sql = "SELECT ID, [Name], Content, CreateDate FROM dbo.Maxims WHERE ID = @ID";

            return this.connection.Query<Maxims>(sql, new { ID = id }).SingleOrDefault();
        }

        #endregion

        #region 추가하기 - Add(maxims)

        /// <summary>
        /// 추가하기
        /// </summary>
        /// <param name="maxims">격언</param>
        /// <returns>격언</returns>
        public Maxims Add(Maxims maxims)
        {
            string sql = @"
Insert Into dbo.Maxims
(
    [Name]
   ,Content
)
Values
(
    @Name,
    @Content
);

Select Cast(SCOPE_IDENTITY() As Int);
";

            var id = this.connection.Query<int>(sql, maxims).Single();

            maxims.ID = id;

            return maxims;
        }

        #endregion
        #region 수정하기 - Update(maxims)

        /// <summary>
        /// 수정하기
        /// </summary>
        /// <param name="maxims">격언</param>
        /// <returns>격언</returns>
        public Maxims Update(Maxims maxims)
        {
            string sql = "UPDATE dbo.Maxims SET [Name] = @Name, Content = @Content WHERE ID = @ID";

            this.connection.Execute(sql, maxims);

            return maxims;
        }

        #endregion
        #region 삭제하기 - Delete(id)

        /// <summary>
        /// 삭제하기
        /// </summary>
        /// <param name="id">ID</param>
        public void Delete(int id)
        {
            string sql = "DELETE FROM dbo.Maxims WHERE ID = @ID";

            this.connection.Execute(sql, new { ID = id });
        }

        #endregion
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using Dapper;

using TestProject.Models;

namespace TestProject

{

/// <summary>

/// 격언 서비스 저장소

/// </summary>

public class MaximServiceRepository

{

//////////////////////////////////////////////////////////////////////////////////////////////////// Field

////////////////////////////////////////////////////////////////////////////////////////// Private

#region Field

/// <summary>

/// DB 연결

/// </summary>

private IDbConnection connection = new SqlConnection

(

ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString

);

#endregion

//////////////////////////////////////////////////////////////////////////////////////////////////// Method

////////////////////////////////////////////////////////////////////////////////////////// Public

#region 리스트 구하기 - GetList()

/// <summary>

/// 리스트 구하기

/// </summary>

/// <returns>리스트</returns>

public List<Maxims> GetList()

{

string sql = "SELECT ID, [Name], Content, CreateDate FROM dbo.Maxims ORDER BY ID ASC";

return this.connection.Query<Maxims>(sql).ToList();

}

#endregion

#region 구하기 - Get(id)

/// <summary>

/// 구하기

/// </summary>

/// <param name="id">ID</param>

/// <returns>격언</returns>

public Maxims Get(int id)

{

string sql = "SELECT ID, [Name], Content, CreateDate FROM dbo.Maxims WHERE ID = @ID";

return this.connection.Query<Maxims>(sql, new { ID = id }).SingleOrDefault();

}

#endregion

#region 추가하기 - Add(maxims)

/// <summary>

/// 추가하기

/// </summary>

/// <param name="maxims">격언</param>

/// <returns>격언</returns>

public Maxims Add(Maxims maxims)

{

string sql = @"

Insert Into dbo.Maxims

(

[Name]

,Content

)

Values

(

@Name,

@Content

);

Select Cast(SCOPE_IDENTITY() As Int);

var id = this.connection.Query<int>(sql, maxims).Single();

maxims.ID = id;

return maxims;

}

#endregion

#region 수정하기 - Update(maxims)

/// <summary>

/// 수정하기

/// </summary>

/// <param name="maxims">격언</param>

/// <returns>격언</returns>

public Maxims Update(Maxims maxims)

{

string sql = "UPDATE dbo.Maxims SET [Name] = @Name, Content = @Content WHERE ID = @ID";

this.connection.Execute(sql, maxims);

return maxims;

}

#endregion

#region 삭제하기 - Delete(id)

/// <summary>

/// 삭제하기

/// </summary>

/// <param name="id">ID</param>

public void Delete(int id)

{

string sql = "DELETE FROM dbo.Maxims WHERE ID = @ID";

this.connection.Execute(sql, new { ID = id });

}

#endregion

}

▶ Web.config


<?xml version="1.0"?>
<configuration>
    <system.web>
        <compilation targetFramework="4.6.1" debug="true" />
    </system.web>
    <connectionStrings>
        <add name="ConnectionString" connectionString="Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=TestDB;Integrated Security=True" />
    </connectionStrings>
</configuration>

<?xml version="1.0"?>

<system.web>

</system.web>

</connectionStrings>

</configuration>

▶ ListPage.aspx


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ListPage.aspx.cs" Inherits="TestProject.ListPage" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <title>DAPPER CRUD 작업하기</title>
    </head>
    <body>
        <form id="form" runat="server">
            <div>
                <asp:GridView ID="gridView" runat="server">
                    <Columns>
                        <asp:HyperLinkField Text="상세보기"
                            DataNavigateUrlFormatString="~/ViewPage.aspx?ID={0}"
                            DataNavigateUrlFields="ID" />
                    </Columns>
                </asp:GridView>
                <hr />
                <asp:HyperLink ID="writeHyperLink" runat="server"
                    NavigateUrl="~/WritePage.aspx">
                    쓰기
                </asp:HyperLink>
            </div>
        </form>
    </body>
</html>

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ListPage.aspx.cs" Inherits="TestProject.ListPage" %>

<!DOCTYPE html>

<title>DAPPER CRUD 작업하기</title>

</head>

<body>

<div>

<asp:GridView ID="gridView" runat="server">

<asp:HyperLinkField Text="상세보기"

DataNavigateUrlFormatString="~/ViewPage.aspx?ID={0}"

DataNavigateUrlFields="ID" />

</Columns>

</asp:GridView>

<hr />

<asp:HyperLink ID="writeHyperLink" runat="server"

NavigateUrl="~/WritePage.aspx">

쓰기

</asp:HyperLink>

</div>

</form>

</body>

</html>

[C#/COMMON/DAPPER] Dapper 누겟 설치하기

■ SignalR 누겟을 설치하는 방법을 보여준다. 1. Visual Studio를 실행한다. 2. [도구] / [NuGet 패키지 관리자] / [패키지 관리자 콘솔] 메뉴를 실행한다.

[C#/COMMON/DAPPER] SqlMapper 클래스 : GetRowParser 확장 메소드를 사용해 제네릭 타입 데이터의 .CSV 파일 생성하기

■ SqlMapper 클래스의 GetRowParser 확장 메소드를 사용해 제네릭 타입 데이터의 .CSV 파일을 생성하는 방법을 보여준다. ▶ MainForm.cs


using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;

using DevExpress.Utils;
using DevExpress.XtraEditors;
using DevExpress.XtraGrid.Views.Grid;

using Dapper;

namespace TestProject
{
    /// <summary>
    /// 메인 폼
    /// </summary>
    public partial class MainForm : XtraForm
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Field
        ////////////////////////////////////////////////////////////////////////////////////////// Private

        #region Field

        /// <summary>
        /// 연결
        /// </summary>
        private OleDbConnection connection;

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Constructor
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 생성자 - MainForm()

        /// <summary>
        /// 생성자
        /// </summary>
        public MainForm()
        {
            InitializeComponent();

            this.connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind.mdb");

            this.gridView.InitializeView
            (
                true,                           // Multi Select
                GridMultiSelectMode.CellSelect, // Grid Multi Select Mode
                DrawFocusRectStyle.CellFocus,   // Draw Focus Rect Style
                true,                           // Show Indicator
                true,                           // Show Column Headers
                true,                           // Allow Column Moving
                true,                           // Allow Column Resizing
                true,                           // Allow Filter
                true,                           // Allow Sort
                false,                          // Allow Cell Merge
                EditorShowMode.Default,         // Editor Show Mode
                false                           // Editable
            );

            this.gridView.OptionsBehavior.AutoPopulateColumns = true;

            this.gridControl.DataSource = Order.GetOrderList(this.connection);

            this.gridView.BestFitColumns();

            this.createButton.Click += createButton_Click;
        }

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Private
        //////////////////////////////////////////////////////////////////////////////// Event

        #region 생성하기 버튼 클릭시 처리하기 - createButton_Click(sender, e)

        /// <summary>
        /// 생성하기 버튼 클릭시 처리하기
        /// </summary>
        /// <param name="sender">이벤트 발생자</param>
        /// <param name="e">이벤트 인자</param>
        private void createButton_Click(object sender, EventArgs e)
        {
            string sql = "SELECT * FROM Orders";

            WriteFile(this.connection, sql, null, 500, "D:\\", "Sample");
        }

        #endregion

        //////////////////////////////////////////////////////////////////////////////// Function

        #region 헤더 라인 쓰기 - WriteHeaderLine(writer, rowDictionary)

        /// <summary>
        /// 헤더 라인 쓰기
        /// </summary>
        /// <param name="writer">스트림 라이터</param>
        /// <param name="rowDictionary">행 딕셔너리</param>
        private void WriteHeaderLine(StreamWriter writer, IDictionary<string, object> rowDictionary)
        {
            int x = 0;

            foreach(KeyValuePair<string, object> keyValuePair in rowDictionary)
            {
                if(x > 0)
                {
                    writer.Write(",");
                }

                if(x < rowDictionary.Count - 1)
                {
                    writer.Write(keyValuePair.Key);
                }
                else
                {
                    writer.WriteLine(keyValuePair.Key);
                }

                x++;
            }
        }

        #endregion
        #region 데이터 라인 쓰기 - WriteDataLine(writer, rowDictionary)

        /// <summary>
        /// 데이터 라인 쓰기
        /// </summary>
        /// <param name="writer">스트림 라이터</param>
        /// <param name="rowDictionary">행 딕셔너리</param>
        private void WriteDataLine(StreamWriter writer, IDictionary<string, object> rowDictionary)
        {
            int x = 0;

            foreach(KeyValuePair<string, object> keyValuePair in rowDictionary)
            {
                if(x > 0)
                {
                    writer.Write(",");
                }

                object valueObject = keyValuePair.Value;

                if(valueObject == null)
                {
                    #region 값 객체가 NULL인 경우 처리한다.

                    if(x < rowDictionary.Count - 1)
                    {
                        writer.Write(string.Empty);
                    }
                    else
                    {
                        writer.WriteLine(string.Empty);
                    }

                    #endregion
                }
                else
                {
                    Type valueObjectType = valueObject?.GetType();

                    if(valueObjectType == typeof(DateTime))
                    {
                        #region 값 객체 타입이 DateTime인 경우 처리한다.

                        DateTime valueDateTime = (DateTime)valueObject;

                        if(x < rowDictionary.Count - 1)
                        {
                            writer.Write(valueDateTime.ToString("yyyy-MM-dd HH:mm:ss"));
                        }
                        else
                        {
                            writer.WriteLine(valueDateTime.ToString("yyyy-MM-dd HH:mm:ss"));
                        }

                        #endregion
                    }
                    else if(valueObjectType == typeof(DateTime?))
                    {
                        #region 값 객체 타입이 DateTime?인 경우 처리한다.

                        DateTime? valueDateTime = (DateTime?)valueObject;

                        if(valueDateTime.HasValue)
                        {
                            if(x < rowDictionary.Count - 1)
                            {
                                writer.Write(valueDateTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
                            }
                            else
                            {
                                writer.WriteLine(valueDateTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
                            }
                        }
                        else
                        {
                            if(x < rowDictionary.Count - 1)
                            {
                                writer.Write(string.Empty);
                            }
                            else
                            {
                                writer.WriteLine(string.Empty);
                            }
                        }

                        #endregion
                    }
                    else if(valueObjectType == typeof(string))
                    {
                        #region 값 객체 타입이 string인 경우 처리한다.

                        string valueString = (string)valueObject;

                        if(valueString == null)
                        {
                            if(x < rowDictionary.Count - 1)
                            {
                                writer.Write(string.Empty);
                            }
                            else
                            {
                                writer.WriteLine(string.Empty);
                            }
                        }
                        else
                        {
                            if(x < rowDictionary.Count - 1)
                            {
                                writer.Write($"\"{valueString.Replace("\r", string.Empty).Replace("\n", string.Empty)}\"");
                            }
                            else
                            {
                                writer.WriteLine($"\"{valueString.Replace("\r", string.Empty).Replace("\n", string.Empty)}\"");
                            }
                        }

                        #endregion
                    }
                    else
                    {
                        #region 값 객체 타입이 기타인 경우 처리한다.

                        if(x < rowDictionary.Count - 1)
                        {
                            writer.Write(valueObject);
                        }
                        else
                        {
                            writer.WriteLine(valueObject);
                        }

                        #endregion
                    }
                }

                x++;
            }
        }

        #endregion
        #region 파일 쓰기 - WriteFile(connection, sql, condition, lineCountPerFile, saveDirectoryPath, fileName)

        /// <summary>
        /// 파일 쓰기
        /// </summary>
        /// <param name="connection">연결</param>
        /// <param name="sql">SQL</param>
        /// <param name="condition">조건</param>
        /// <param name="lineCountPerFile">파일당 라인 수</param>
        /// <param name="saveDirectoryPath">저장 디렉토리 경로</param>
        /// <param name="fileName">파일 확장자가 없는 파일명</param>
        private void WriteFile(IDbConnection connection, string sql, object condition, int lineCountPerFile, string saveDirectoryPath, string fileName)
        {
            using(IDataReader reader = connection.ExecuteReader(sql, condition))
            {
                int lineCount = 0;

                StreamWriter writer = null;

                try
                {
                    while(reader.Read())
                    {
                        int rest = lineCount % lineCountPerFile;

                        if(rest == 0)
                        {
                            writer?.Dispose();

                            int sequence = lineCount / lineCountPerFile + 1;

                            string actualFileName = $"{fileName}_{sequence}.csv";

                            string actualFilePath = Path.Combine(saveDirectoryPath, actualFileName);

                            writer = new StreamWriter(actualFilePath, false, Encoding.UTF8);
                        }

                        var rowParser = reader.GetRowParser<dynamic>();

                        var row = rowParser(reader);

                        IDictionary<string, object> rowDictionary = row as IDictionary<string, object>;

                        if(rest == 0)
                        {
                            WriteHeaderLine(writer, rowDictionary);
                        }

                        WriteDataLine(writer, rowDictionary);

                        lineCount++;
                    }

                    if(lineCount == 0)
                    {
                        string actualFileName = $"{fileName}_1.csv";

                        string actualFilePath = Path.Combine(saveDirectoryPath, actualFileName);

                        writer = new StreamWriter(actualFilePath, false, Encoding.UTF8);
                    }
                }
                finally
                {
                    writer?.Dispose();
                }
            }
        }

        #endregion
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.OleDb;

using System.IO;

using System.Text;

using DevExpress.Utils;

using DevExpress.XtraEditors;

using DevExpress.XtraGrid.Views.Grid;

using Dapper;

namespace TestProject

{

/// <summary>

/// 메인 폼

/// </summary>

public partial class MainForm : XtraForm

{

//////////////////////////////////////////////////////////////////////////////////////////////////// Field

////////////////////////////////////////////////////////////////////////////////////////// Private

#region Field

/// <summary>

/// 연결

/// </summary>

private OleDbConnection connection;

#endregion

//////////////////////////////////////////////////////////////////////////////////////////////////// Constructor

////////////////////////////////////////////////////////////////////////////////////////// Public

#region 생성자 - MainForm()

/// <summary>

/// 생성자

/// </summary>

public MainForm()

{

InitializeComponent();

this.connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind.mdb");

this.gridView.InitializeView

(

true, // Multi Select

GridMultiSelectMode.CellSelect, // Grid Multi Select Mode

DrawFocusRectStyle.CellFocus, // Draw Focus Rect Style

true, // Show Indicator

true, // Show Column Headers

true, // Allow Column Moving

true, // Allow Column Resizing

true, // Allow Filter

true, // Allow Sort

false, // Allow Cell Merge

EditorShowMode.Default, // Editor Show Mode

false // Editable

);

this.gridView.OptionsBehavior.AutoPopulateColumns = true;

this.gridControl.DataSource = Order.GetOrderList(this.connection);

this.gridView.BestFitColumns();

this.createButton.Click += createButton_Click;

}

#endregion

//////////////////////////////////////////////////////////////////////////////////////////////////// Method

////////////////////////////////////////////////////////////////////////////////////////// Private

//////////////////////////////////////////////////////////////////////////////// Event

#region 생성하기 버튼 클릭시 처리하기 - createButton_Click(sender, e)

/// <summary>

/// 생성하기 버튼 클릭시 처리하기

/// </summary>

/// <param name="sender">이벤트 발생자</param>

/// <param name="e">이벤트 인자</param>

private void createButton_Click(object sender, EventArgs e)

{

string sql = "SELECT * FROM Orders";

WriteFile(this.connection, sql, null, 500, "D:\\", "Sample");

}

#endregion

//////////////////////////////////////////////////////////////////////////////// Function

#region 헤더 라인 쓰기 - WriteHeaderLine(writer, rowDictionary)

/// <summary>

/// 헤더 라인 쓰기

/// </summary>

/// <param name="writer">스트림 라이터</param>

/// <param name="rowDictionary">행 딕셔너리</param>

private void WriteHeaderLine(StreamWriter writer, IDictionary<string, object> rowDictionary)

{

int x = 0;

foreach(KeyValuePair<string, object> keyValuePair in rowDictionary)

{

if(x > 0)

{

writer.Write(",");

}

if(x < rowDictionary.Count - 1)

{

writer.Write(keyValuePair.Key);

}

else

{

writer.WriteLine(keyValuePair.Key);

}

x++;

}

#endregion

#region 데이터 라인 쓰기 - WriteDataLine(writer, rowDictionary)

/// <summary>

/// 데이터 라인 쓰기

/// </summary>

/// <param name="writer">스트림 라이터</param>

/// <param name="rowDictionary">행 딕셔너리</param>

private void WriteDataLine(StreamWriter writer, IDictionary<string, object> rowDictionary)

{

int x = 0;

foreach(KeyValuePair<string, object> keyValuePair in rowDictionary)

{

if(x > 0)

{

writer.Write(",");

}

object valueObject = keyValuePair.Value;

if(valueObject == null)

{

#region 값 객체가 NULL인 경우 처리한다.

if(x < rowDictionary.Count - 1)

{

writer.Write(string.Empty);

}

else

{

writer.WriteLine(string.Empty);

}

#endregion

}

else

{

Type valueObjectType = valueObject?.GetType();

if(valueObjectType == typeof(DateTime))

{

#region 값 객체 타입이 DateTime인 경우 처리한다.

DateTime valueDateTime = (DateTime)valueObject;

if(x < rowDictionary.Count - 1)

{

writer.Write(valueDateTime.ToString("yyyy-MM-dd HH:mm:ss"));

}

else

{

writer.WriteLine(valueDateTime.ToString("yyyy-MM-dd HH:mm:ss"));

}

#endregion

}

else if(valueObjectType == typeof(DateTime?))

{

#region 값 객체 타입이 DateTime?인 경우 처리한다.

DateTime? valueDateTime = (DateTime?)valueObject;

if(valueDateTime.HasValue)

{

if(x < rowDictionary.Count - 1)

{

writer.Write(valueDateTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));

}

else

{

writer.WriteLine(valueDateTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));

}

else

{

if(x < rowDictionary.Count - 1)

{

writer.Write(string.Empty);

}

else

{

writer.WriteLine(string.Empty);

}

#endregion

}

else if(valueObjectType == typeof(string))

{

#region 값 객체 타입이 string인 경우 처리한다.

string valueString = (string)valueObject;

if(valueString == null)

{

if(x < rowDictionary.Count - 1)

{

writer.Write(string.Empty);

}

else

{

writer.WriteLine(string.Empty);

}

else

{

if(x < rowDictionary.Count - 1)

{

writer.Write($"\"{valueString.Replace("\r", string.Empty).Replace("\n", string.Empty)}\"");

}

else

{

writer.WriteLine($"\"{valueString.Replace("\r", string.Empty).Replace("\n", string.Empty)}\"");

}

#endregion

}

else

{

#region 값 객체 타입이 기타인 경우 처리한다.

if(x < rowDictionary.Count - 1)

{

writer.Write(valueObject);

}

else

{

writer.WriteLine(valueObject);

}

#endregion

}

x++;

}

#endregion

#region 파일 쓰기 - WriteFile(connection, sql, condition, lineCountPerFile, saveDirectoryPath, fileName)

/// <summary>

/// 파일 쓰기

/// </summary>

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

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

/// <param name="condition">조건</param>

/// <param name="lineCountPerFile">파일당 라인 수</param>

/// <param name="saveDirectoryPath">저장 디렉토리 경로</param>

/// <param name="fileName">파일 확장자가 없는 파일명</param>

private void WriteFile(IDbConnection connection, string sql, object condition, int lineCountPerFile, string saveDirectoryPath, string fileName)

{

using(IDataReader reader = connection.ExecuteReader(sql, condition))

{

int lineCount = 0;

StreamWriter writer = null;

try

{

while(reader.Read())

{

int rest = lineCount % lineCountPerFile;

if(rest == 0)

{

writer?.Dispose();

int sequence = lineCount / lineCountPerFile + 1;

string actualFileName = $"{fileName}_{sequence}.csv";

string actualFilePath = Path.Combine(saveDirectoryPath, actualFileName);

writer = new StreamWriter(actualFilePath, false, Encoding.UTF8);

}

var rowParser = reader.GetRowParser<dynamic>();

var row = rowParser(reader);

IDictionary<string, object> rowDictionary = row as IDictionary<string, object>;

if(rest == 0)

{

WriteHeaderLine(writer, rowDictionary);

}

WriteDataLine(writer, rowDictionary);

lineCount++;

}

if(lineCount == 0)

{

string actualFileName = $"{fileName}_1.csv";

string actualFilePath = Path.Combine(saveDirectoryPath, actualFileName);

writer = new StreamWriter(actualFilePath, false, Encoding.UTF8);

}

finally

{

writer?.Dispose();

}

#endregion

}

TestProject.zip

[C#/COMMON/DAPPER] CustomPropertyTypeMap 클래스 : 커스텀 컬럼 매핑 사용하기

■ CustomPropertyTypeMap 클래스에서 커스텀 컬럼 매핑을 사용하는 방법을 보여준다. ▶ EmployeeModel.cs


namespace TestProject
{
    /// <summary>
    /// 직원 모델
    /// </summary>
    public class EmployeeModel
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Property
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region ID - ID

        /// <summary>
        /// ID
        /// </summary>
        public string ID { get; set; }

        #endregion
        #region 성명 - Name

        /// <summary>
        /// 성명
        /// </summary>
        public string Name { get; set; }

        #endregion
    }
}

namespace TestProject

{

/// <summary>

/// 직원 모델

/// </summary>

public class EmployeeModel

{

//////////////////////////////////////////////////////////////////////////////////////////////////// Property

////////////////////////////////////////////////////////////////////////////////////////// Public

#region ID - ID

/// <summary>

/// ID

/// </summary>

public string ID { get; set; }

#endregion

#region 성명 - Name

/// <summary>

/// 성명

/// </summary>

public string Name { get; set; }

#endregion

}

▶ Program.cs


using System;
using System.Collections.Generic;
using System.Reflection;
using System.Linq;

using Oracle.ManagedDataAccess.Client;

using Dapper;

namespace TestProject
{
    /// <summary>
    /// 프로그램
    /// </summary>
    class Program
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Static
        //////////////////////////////////////////////////////////////////////////////// Private

        #region Field

        /// <summary>
        /// 직원 매핑 딕셔너리
        /// </summary>
        private static Dictionary<string, string> _employeeMappingDictionary = new Dictionary<string, string>
        {
            { "EMPNO", "ID"   },
            { "ENAME", "Name" }
        };

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Static
        //////////////////////////////////////////////////////////////////////////////// Private

        #region 프로그램 시작하기 - Main()

        /// <summary>
        /// 프로그램 시작하기
        /// </summary>
        private static void Main()
        {
            CustomPropertyTypeMap employeeMap = new CustomPropertyTypeMap
            (
                typeof(EmployeeModel),
                (type, columnName) => SelectEmployeeProperty(type, columnName)
            );

            SqlMapper.SetTypeMap(typeof(EmployeeModel), employeeMap);

            string connectionString = @"Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = orcl)));User ID=SYSTEM;Password=P@ssw0rd";

            string sql = @"
SELECT
    EMPNO
   ,ENAME
FROM SCOTT.EMP
";

            using(OracleConnection connection = new OracleConnection(connectionString))
            {
                var resultList = connection.Query<EmployeeModel>(sql).ToList();

                foreach(var item in resultList)
                {
                    Console.WriteLine($"{item.ID} {item.Name}");
                }
            }
        }

        #endregion

        #region 직원 속성 선택하기 - SelectEmployeeProperty(type, columnName)

        /// <summary>
        /// 직원 속성 선택하기
        /// </summary>
        /// <param name="type">타입</param>
        /// <param name="columnName">컬럼명</param>
        /// <returns>속성 정보</returns>
        private static PropertyInfo SelectEmployeeProperty(Type type, string columnName)
        {
            if(_employeeMappingDictionary.ContainsKey(columnName))
            {
                return type.GetProperty(_employeeMappingDictionary[columnName]);
            }
            else
            {
                return type.GetProperty(columnName);
            }
        }

        #endregion
    }
}

100

using System;

using System.Collections.Generic;

using System.Reflection;

using System.Linq;

using Oracle.ManagedDataAccess.Client;

using Dapper;

namespace TestProject

{

/// <summary>

/// 프로그램

/// </summary>

class Program

{

//////////////////////////////////////////////////////////////////////////////////////////////////// Method

////////////////////////////////////////////////////////////////////////////////////////// Static

//////////////////////////////////////////////////////////////////////////////// Private

#region Field

/// <summary>

/// 직원 매핑 딕셔너리

/// </summary>

private static Dictionary<string, string> _employeeMappingDictionary = new Dictionary<string, string>

{

{ "EMPNO", "ID" },

{ "ENAME", "Name" }

};

#endregion

//////////////////////////////////////////////////////////////////////////////////////////////////// Method

////////////////////////////////////////////////////////////////////////////////////////// Static

//////////////////////////////////////////////////////////////////////////////// Private

#region 프로그램 시작하기 - Main()

/// <summary>

/// 프로그램 시작하기

/// </summary>

private static void Main()

{

CustomPropertyTypeMap employeeMap = new CustomPropertyTypeMap

(

typeof(EmployeeModel),

(type, columnName) => SelectEmployeeProperty(type, columnName)

);

SqlMapper.SetTypeMap(typeof(EmployeeModel), employeeMap);

string connectionString = @"Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS =

(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = orcl)));User ID=SYSTEM;Password=P@ssw0rd";

string sql = @"

SELECT

EMPNO

,ENAME

FROM SCOTT.EMP

using(OracleConnection connection = new OracleConnection(connectionString))

{

var resultList = connection.Query<EmployeeModel>(sql).ToList();

foreach(var item in resultList)

{

Console.WriteLine($"{item.ID} {item.Name}");

}

#endregion

#region 직원 속성 선택하기 - SelectEmployeeProperty(type, columnName)

/// <summary>

/// 직원 속성 선택하기

/// </summary>

/// <param name="type">타입</param>

/// <param name="columnName">컬럼명</param>

/// <returns>속성 정보</returns>

private static PropertyInfo SelectEmployeeProperty(Type type, string columnName)

{

if(_employeeMappingDictionary.ContainsKey(columnName))

{

return type.GetProperty(_employeeMappingDictionary[columnName]);

}

else

{

return type.GetProperty(columnName);

}

#endregion

}

TestProject.zip

[C#/COMMON/DAPPER] SqlMapper 클래스 : TypeHandler 정적 메소드를 사용해 커스텀 타입 처리하기

■ SqlMapper 클래스의 TypeHandler<T> 정적 메소드를 사용해 커스텀 타입을 처리하는 방법을 보여준다. ▶ 테이블 생성 스크립트 (SQL)


DROP TABLE SCOTT."USER";

CREATE TABLE SCOTT."USER"
(
    "ID"   VARCHAR(20) NOT NULL,
    "NAME" VARCHAR(20),
    "ROLE" VARCHAR(50),
    PRIMARY KEY (ID)
);

INSERT INTO SCOTT."USER" VALUES ('001', '홍길동', 'User'               );
INSERT INTO SCOTT."USER" VALUES ('002', '김철수', 'User, Administrator');

COMMIT;

DROP TABLE SCOTT."USER";

CREATE TABLE SCOTT."USER"

(

"ID" VARCHAR(20) NOT NULL,

"NAME" VARCHAR(20),

"ROLE" VARCHAR(50),

PRIMARY KEY (ID)

);

INSERT INTO SCOTT."USER" VALUES ('001', '홍길동', 'User' );

INSERT INTO SCOTT."USER" VALUES ('002', '김철수', 'User, Administrator');

COMMIT;

▶ Role.cs


using System;

namespace TestProject
{
    /// <summary>
    /// 역할
    /// </summary>
    [Flags]
    public enum Role
    {
        /// <summary>
        /// 해당 무
        /// </summary>
        None = 0,

        /// <summary>
        /// 관리자
        /// </summary>
        Administrator = 0x1,

        /// <summary>
        /// 사용자
        /// </summary>
        User = 0x2
    }
}

using System;

namespace TestProject

{

/// <summary>

/// 역할

/// </summary>

[Flags]

public enum Role

{

/// <summary>

/// 해당 무

/// </summary>

None = 0,

/// <summary>

/// 관리자

/// </summary>

Administrator = 0x1,

/// <summary>

/// 사용자

/// </summary>

User = 0x2

}

▶

[C#/COMMON/DAPPER] IDbConnection 클래스 : QueryMultiple 확장 메소드를 사용해 2개 이상의 SELECT문 조회하기

■ IDbConnection 클래스의 QueryMultiple 확장 메소드를 사용해 2개 이상의 SELECT문을 조회하는 방법을 보여준다. ▶ CodeGroupModel.cs


using System;

namespace TestProject
{
    /// <summary>
    /// 코드 그룹 모델
    /// </summary>
    public class CodeGroupModel
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Property
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region ID - ID

        /// <summary>
        /// ID
        /// </summary>
        public string ID { get; set; }

        #endregion
        #region 명칭 - NAME

        /// <summary>
        /// 명칭
        /// </summary>
        public string NAME { get; set; }

        #endregion
    }
}

using System;

namespace TestProject

{

/// <summary>

/// 코드 그룹 모델

/// </summary>

public class CodeGroupModel

{

//////////////////////////////////////////////////////////////////////////////////////////////////// Property

////////////////////////////////////////////////////////////////////////////////////////// Public

#region ID - ID

/// <summary>

/// ID

/// </summary>

public string ID { get; set; }

#endregion

#region 명칭 - NAME

/// <summary>

/// 명칭

/// </summary>

public string NAME { get; set; }

#endregion

}

▶ CodeModel.cs


using System;

namespace TestProject
{
    /// <summary>
    /// 코드 모델
    /// </summary>
    public class CodeModel
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Property
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region ID - ID

        /// <summary>
        /// ID
        /// </summary>
        public string ID { get; set; }

        #endregion
        #region 그룹 ID - GROUP_ID

        /// <summary>
        /// 그룹 ID
        /// </summary>
        public string GROUP_ID { get; set; }

        #endregion
        #region 명칭 - NAME

        /// <summary>
        /// 명칭
        /// </summary>
        public string NAME { get; set; }

        #endregion
        #region 값 - VALUE

        /// <summary>
        /// 값
        /// </summary>
        public string VALUE { get; set; }

        #endregion
    }
}

using System;

namespace TestProject

{

/// <summary>

/// 코드 모델

/// </summary>

public class CodeModel

{

//////////////////////////////////////////////////////////////////////////////////////////////////// Property

////////////////////////////////////////////////////////////////////////////////////////// Public

#region ID - ID

/// <summary>

/// ID

/// </summary>

public string ID { get; set; }

#endregion

#region 그룹 ID - GROUP_ID

/// <summary>

/// 그룹 ID

/// </summary>

public string GROUP_ID { get; set; }

#endregion

#region 명칭 - NAME

/// <summary>

/// 명칭

/// </summary>

public string NAME { get; set; }

#endregion

#region 값 - VALUE

/// <summary>

/// 값

/// </summary>

public string VALUE { get; set; }

#endregion

}

▶ Program.cs


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

using Npgsql;

using Dapper;

namespace TestProject
{
    /// <summary>
    /// 프로그램
    /// </summary>
    class Program
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Static
        //////////////////////////////////////////////////////////////////////////////// Private

        #region 프로그램 시작하기 - Main()

        /// <summary>
        /// 프로그램 시작하기
        /// </summary>
        private static void Main()
        {
            string sql = @"
SELECT *
FROM   COM_CODE_GROUP
WHERE  ID = '5A9E697A-A154-4F21-85D6-258E457AF8DF';

SELECT *
FROM   COM_CODE
WHERE  GROUP_ID = '5A9E697A-A154-4F21-85D6-258E457AF8DF';
";

            string connectionString = @"Host=127.0.0.1;Port=5432;Database=test;User ID=postgres;Password=postgres";

            CodeGroupModel  codeGroup = null;
            List<CodeModel> codeList  = null;

            using(NpgsqlConnection connection = new NpgsqlConnection(connectionString))
            {
                var reader = connection.QueryMultiple(sql);

                using(reader)
                {
                    codeGroup = reader.ReadFirstOrDefault<CodeGroupModel>();

                    codeList = reader.Read<CodeModel>().ToList();
                }
            }

            if(codeGroup != null)
            {
                Console.WriteLine("CODE GROUP NAME : {0}", codeGroup.NAME);
            }

            if(codeList != null && codeList.Count > 0)
            {
                foreach(CodeModel code in codeList)
                {
                    Console.WriteLine("    {0}", code.NAME);
                }
            }
        }

        #endregion
    }
}

using System;

using System.Collections.Generic;

using System.Linq;

using Npgsql;

using Dapper;

namespace TestProject

{

/// <summary>

/// 프로그램

/// </summary>

class Program

{

//////////////////////////////////////////////////////////////////////////////////////////////////// Method

////////////////////////////////////////////////////////////////////////////////////////// Static

//////////////////////////////////////////////////////////////////////////////// Private

#region 프로그램 시작하기 - Main()

/// <summary>

/// 프로그램 시작하기

/// </summary>

private static void Main()

{

string sql = @"

SELECT *

FROM COM_CODE_GROUP

WHERE ID = '5A9E697A-A154-4F21-85D6-258E457AF8DF';

SELECT *

FROM COM_CODE

WHERE GROUP_ID = '5A9E697A-A154-4F21-85D6-258E457AF8DF';

string connectionString = @"Host=127.0.0.1;Port=5432;Database=test;User ID=postgres;Password=postgres";

CodeGroupModel codeGroup = null;

List<CodeModel> codeList = null;

using(NpgsqlConnection connection = new NpgsqlConnection(connectionString))

{

var reader = connection.QueryMultiple(sql);

using(reader)

{

codeGroup = reader.ReadFirstOrDefault<CodeGroupModel>();

codeList = reader.Read<CodeModel>().ToList();

}

if(codeGroup != null)

{

Console.WriteLine("CODE GROUP NAME : {0}", codeGroup.NAME);

}

if(codeList != null && codeList.Count > 0)

{

foreach(CodeModel code in codeList)

{

Console.WriteLine(" {0}", code.NAME);

}

#endregion

}

[C#/COMMON/DAPPER] SYS_REFCURSOR 출력 타입의 입력 매개 변수를 갖는 오라클 저장 프로시저 호출하기

■ SYS_REFCURSOR 출력 타입의 입력 매개 변수를 갖는 오라클 저장 프로시저를 호출하는 방법을 보여준다. ▶ 저장 프로시저 (SQL)


CREATE OR REPLACE PROCEDURE SCOTT.GETMEMBERLIST
(
    I_NAME   IN  VARCHAR2,
    O_CURSOR OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN O_CURSOR FOR
        SELECT *
        FROM   SCOTT.EMP
        WHERE  ENAME LIKE I_NAME || '%';
END;

CREATE OR REPLACE PROCEDURE SCOTT.GETMEMBERLIST

(

I_NAME IN VARCHAR2,

O_CURSOR OUT SYS_REFCURSOR

)

BEGIN

OPEN O_CURSOR FOR

SELECT *

FROM SCOTT.EMP

WHERE ENAME LIKE I_NAME || '%';

END;

※ 오라클 11G의

[C#/COMMON/DAPPER] NULLABLE DATETIME 사용시 처리하기

■ NULLABLE DATETIME 사용시 처리하는 방법을 보여준다. Dapper에서 DateTime? 컬럼 값 조회시 타입 변환 에러가 발생하기 때문에 NullableDateTimeHandler.cs 소스코드를 추가하고, 프로그램 시작시

[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는

[C#/COMMON/DAPPER] 자식 리스트를 포함해 조회하기

■ 자식 리스트를 포함해 조회하는 방법을 보여준다. ▶ 자식 리스트를 포함해 조회하기 예제 (C#)


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

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

    ...

    public List<CodeModel> CodeList { get; set; }
}

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

    public string GROUP_ID { get; set; }

    ...
}

...

private IDbConnection connetcion;

...

string sql = @"
SELECT
    PARENT.*
   ,CHILD.*
FROM      COM_CODE       CHILD
LEFT JOIN COM_CODE_GROUP PARENT ON PARENT.ID = CHILD.GROUP_ID
WHERE PARENT.ID = @ID
ORDER BY
    PARENT.ID ASC
   ,CHILD.ID  ASC;
";

var result = this.connection.QueryIncludingChildList<CodeGroupModel, string, CodeModel>
(
    sql,
    codeGroup => codeGroup.ID,
    codeGroup => {

        if(codeGroup.CodeList == null)
        {
            codeGroup.CodeList = new List<CodeModel>();
        }

        return codeGroup.CodeList;
    },
    parameter : new { ID = id },
    splitOn : "ID"
);

using System.Collections.Generic;

using System.Data;

public class CodeGroupModel

{

public string ID { get; set; }

...

public List<CodeModel> CodeList { get; set; }

}

public class CodeModel

{

public string ID { get; set; }

public string GROUP_ID { get; set; }

...

}

...

private IDbConnection connetcion;

...

string sql = @"

SELECT

PARENT.*

,CHILD.*

FROM COM_CODE CHILD

LEFT JOIN COM_CODE_GROUP PARENT ON PARENT.ID = CHILD.GROUP_ID

WHERE PARENT.ID = @ID

ORDER BY

PARENT.ID ASC

,CHILD.ID ASC;

var result = this.connection.QueryIncludingChildList<CodeGroupModel, string, CodeModel>

(

sql,

codeGroup => codeGroup.ID,

codeGroup => {

if(codeGroup.CodeList == null)

{

codeGroup.CodeList = new List<CodeModel>();

}

return codeGroup.CodeList;

parameter : new { ID = id },

splitOn : "ID"

);

※ COM_CODE_GROUP 테이블은 CodeGroupModel 타입에 대응하고 PK는

[C#/COMMON/DAPPER] IN 조건문 조회하기

■ IN 조건문을 사용해 조회하는 방법을 보여준다. ▶ SQL Server 테이블


CREATE TABLE dbo.Student
(
    ID         INT          NOT NULL
   ,Name       NVARCHAR(50) NOT NULL
   ,CreateTime DATETIME     NOT NULL
    CONSTRAINT PKStudent PRIMARY KEY CLUSTERED
    (
	    ID ASC
    )
    WITH
    (
        PAD_INDEX              = OFF
       ,STATISTICS_NORECOMPUTE = OFF
       ,IGNORE_DUP_KEY         = OFF
       ,ALLOW_ROW_LOCKS        = ON
       ,ALLOW_PAGE_LOCKS       = ON
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE dbo.Student

(

ID INT NOT NULL

,Name NVARCHAR(50) NOT NULL

,CreateTime DATETIME NOT NULL

CONSTRAINT PKStudent PRIMARY KEY CLUSTERED

(

ID ASC

)

WITH

(

PAD_INDEX = OFF

,STATISTICS_NORECOMPUTE = OFF

,IGNORE_DUP_KEY = OFF

,ALLOW_ROW_LOCKS = ON

,ALLOW_PAGE_LOCKS = ON

) ON [PRIMARY]

▶ 예제 코드 (C#)


using System;
using System.Data.SqlClient;
using System.Linq;

using Dapper;

...

/// <summary>
/// 학생
/// </summary>
public class Student
{
    /// <summary>
    /// ID
    /// </summary>
    public int ID { get; set; }

    /// <summary>
    /// 성명
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// 생성 일시
    /// </summary>
    public DateTime CreateTime { get; set; }
}

...

string connectionString = "Data Source=127.0.0.1;Initial Catalog=TestDB;UID=sa;PWD=1234;Timeout=30";

using(SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    string sql = "SELECT * FROM dbo.Student WHERE ID in @IDList";

    var result = connection.Query(sql, new { IDList = new[] { 1, 2, 3, 4, 5 } });

    Console.WriteLine(result.Count());
}

using System;

using System.Data.SqlClient;

using System.Linq;

using Dapper;

...

/// <summary>

/// 학생

/// </summary>

public class Student

{

/// <summary>

/// ID

/// </summary>

public int ID { get; set; }

/// <summary>

/// 성명

/// </summary>

public string Name { get; set; }

/// <summary>

/// 생성 일시

/// </summary>

public DateTime CreateTime { get; set; }

}

...

string connectionString = "Data Source=127.0.0.1;Initial Catalog=TestDB;UID=sa;PWD=1234;Timeout=30";

using(SqlConnection connection = new SqlConnection(connectionString))

{

connection.Open();

string sql = "SELECT * FROM dbo.Student WHERE ID in @IDList";

var result = connection.Query(sql, new { IDList = new[] { 1, 2, 3, 4, 5 } });

Console.WriteLine(result.Count());

}

[C#/COMMON/DAPPER] COUNT 집계 함수 사용하기

■ COUNT 집계 함수를 사용하는 방법을 보여준다. ▶ SQL Server 테이블 (SQL)


CREATE TABLE dbo.Student
(
    ID         INT          NOT NULL
   ,Name       NVARCHAR(50) NOT NULL
   ,CreateTime DATETIME     NOT NULL
    CONSTRAINT PKStudent PRIMARY KEY CLUSTERED
    (
	    ID ASC
    )
    WITH
    (
        PAD_INDEX              = OFF
       ,STATISTICS_NORECOMPUTE = OFF
       ,IGNORE_DUP_KEY         = OFF
       ,ALLOW_ROW_LOCKS        = ON
       ,ALLOW_PAGE_LOCKS       = ON
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE dbo.Student

(

ID INT NOT NULL

,Name NVARCHAR(50) NOT NULL

,CreateTime DATETIME NOT NULL

CONSTRAINT PKStudent PRIMARY KEY CLUSTERED

(

ID ASC

)

WITH

(

PAD_INDEX = OFF

,STATISTICS_NORECOMPUTE = OFF

,IGNORE_DUP_KEY = OFF

,ALLOW_ROW_LOCKS = ON

,ALLOW_PAGE_LOCKS = ON

) ON [PRIMARY]

▶ 예제 코드 (C#)


using System;
using System.Data.SqlClient;
using System.Linq;

using Dapper;

...

/// <summary>
/// 학생
/// </summary>
public class Student
{
    /// <summary>
    /// ID
    /// </summary>
    public int ID { get; set; }

    /// <summary>
    /// 성명
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// 생성 일시
    /// </summary>
    public DateTime CreateTime { get; set; }
}

...

string connectionString = "Data Source=127.0.0.1;Initial Catalog=TestDB;UID=sa;PWD=1234;Timeout=30";

using(SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    int? count = connection.Query<int?>("SELECT COUNT(*) FROM dbo.Student").First();

    Console.WriteLine(count);
}

using System;

using System.Data.SqlClient;

using System.Linq;

using Dapper;

...

/// <summary>

/// 학생

/// </summary>

public class Student

{

/// <summary>

/// ID

/// </summary>

public int ID { get; set; }

/// <summary>

/// 성명

/// </summary>

public string Name { get; set; }

/// <summary>

/// 생성 일시

/// </summary>

public DateTime CreateTime { get; set; }

}

...

string connectionString = "Data Source=127.0.0.1;Initial Catalog=TestDB;UID=sa;PWD=1234;Timeout=30";

using(SqlConnection connection = new SqlConnection(connectionString))

{

connection.Open();

int? count = connection.Query<int?>("SELECT COUNT(*) FROM dbo.Student").First();

Console.WriteLine(count);

}

※ 사용하는 조건문에 따라

[C#/COMMON/DAPPER] DELETE문 실행하기

■ DELETE문을 실행하는 방법을 보여준다. ▶ SQL Server 테이블 (SQL)


CREATE TABLE dbo.Student
(
    ID         INT          NOT NULL
   ,Name       NVARCHAR(50) NOT NULL
   ,CreateTime DATETIME     NOT NULL
    CONSTRAINT PKStudent PRIMARY KEY CLUSTERED
    (
	    ID ASC
    )
    WITH
    (
        PAD_INDEX              = OFF
       ,STATISTICS_NORECOMPUTE = OFF
       ,IGNORE_DUP_KEY         = OFF
       ,ALLOW_ROW_LOCKS        = ON
       ,ALLOW_PAGE_LOCKS       = ON
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE dbo.Student

(

ID INT NOT NULL

,Name NVARCHAR(50) NOT NULL

,CreateTime DATETIME NOT NULL

CONSTRAINT PKStudent PRIMARY KEY CLUSTERED

(

ID ASC

)

WITH

(

PAD_INDEX = OFF

,STATISTICS_NORECOMPUTE = OFF

,IGNORE_DUP_KEY = OFF

,ALLOW_ROW_LOCKS = ON

,ALLOW_PAGE_LOCKS = ON

) ON [PRIMARY]

▶ 예제 코드 (C#)


using System;
using System.Data.SqlClient;

using Dapper;

...

/// <summary>
/// 학생
/// </summary>
public class Student
{
    /// <summary>
    /// ID
    /// </summary>
    public int ID { get; set; }

    /// <summary>
    /// 성명
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// 생성 일시
    /// </summary>
    public DateTime CreateTime { get; set; }
}

...

string connectionString = "Data Source=127.0.0.1;Initial Catalog=TestDB;UID=sa;PWD=1234;Timeout=30";

using(SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    Student student = new Student();

    student.ID = 1;

    connection.Execute("DELETE FROM dbo.Student WHERE ID = @ID", student);
}

using System;

using System.Data.SqlClient;

using Dapper;

...

/// <summary>

/// 학생

/// </summary>

public class Student

{

/// <summary>

/// ID

/// </summary>

public int ID { get; set; }

/// <summary>

/// 성명

/// </summary>

public string Name { get; set; }

/// <summary>

/// 생성 일시

/// </summary>

public DateTime CreateTime { get; set; }

}

...

string connectionString = "Data Source=127.0.0.1;Initial Catalog=TestDB;UID=sa;PWD=1234;Timeout=30";

using(SqlConnection connection = new SqlConnection(connectionString))

{

connection.Open();

Student student = new Student();

student.ID = 1;

connection.Execute("DELETE FROM dbo.Student WHERE ID = @ID", student);

}

[C#/COMMON/DAPPER] 복수 INSERT문 실행하기

■ 복수 INSERT문을 실행하는 방법을 보여준다. ▶ SQL Server 테이블 (SQL)


CREATE TABLE dbo.Student
(
    ID         INT          NOT NULL
   ,Name       NVARCHAR(50) NOT NULL
   ,CreateTime DATETIME     NOT NULL
    CONSTRAINT PKStudent PRIMARY KEY CLUSTERED (ID ASC) WITH
    (
        PAD_INDEX              = OFF
       ,STATISTICS_NORECOMPUTE = OFF
       ,IGNORE_DUP_KEY         = OFF
       ,ALLOW_ROW_LOCKS        = ON
       ,ALLOW_PAGE_LOCKS       = ON
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE dbo.Student

(

ID INT NOT NULL

,Name NVARCHAR(50) NOT NULL

,CreateTime DATETIME NOT NULL

CONSTRAINT PKStudent PRIMARY KEY CLUSTERED (ID ASC) WITH

(

PAD_INDEX = OFF

,STATISTICS_NORECOMPUTE = OFF

,IGNORE_DUP_KEY = OFF

,ALLOW_ROW_LOCKS = ON

,ALLOW_PAGE_LOCKS = ON

) ON [PRIMARY]

▶ 예제 코드 (C#)


using System;
using System.Data.SqlClient;

using Dapper;

...

/// <summary>
/// 학생
/// </summary>
public class Student
{
    /// <summary>
    /// ID
    /// </summary>
    public int ID { get; set; }

    /// <summary>
    /// 성명
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// 생성 일시
    /// </summary>
    public DateTime CreateTime { get; set; }
}

...

string connectionString = "Data Source=127.0.0.1;Initial Catalog=TestDB;UID=sa;PWD=1234;Timeout=30";

using(SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    List<Student> studentList = new List<Student>();

    for(int i = 1; i < 101; i++)
    {
        studentList.Add(new Student { ID = i, Name = "학생" + i.ToString(), CreateTime = DateTime.Now });
    }

    SqlTransaction transaction = connection.BeginTransaction();

    try
    {
        connection.Execute("INSERT dbo.Student (ID, Name, CreateTime) VALUES (@ID, @Name, @CreateTime)", studentList, transaction);

        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
    }
}

using System;

using System.Data.SqlClient;

using Dapper;

...

/// <summary>

/// 학생

/// </summary>

public class Student

{

/// <summary>

/// ID

/// </summary>

public int ID { get; set; }

/// <summary>

/// 성명

/// </summary>

public string Name { get; set; }

/// <summary>

/// 생성 일시

/// </summary>

public DateTime CreateTime { get; set; }

}

...

string connectionString = "Data Source=127.0.0.1;Initial Catalog=TestDB;UID=sa;PWD=1234;Timeout=30";

using(SqlConnection connection = new SqlConnection(connectionString))

{

connection.Open();

List<Student> studentList = new List<Student>();

for(int i = 1; i < 101; i++)

{

studentList.Add(new Student { ID = i, Name = "학생" + i.ToString(), CreateTime = DateTime.Now });

}

SqlTransaction transaction = connection.BeginTransaction();

try

{

connection.Execute("INSERT dbo.Student (ID, Name, CreateTime) VALUES (@ID, @Name, @CreateTime)", studentList, transaction);

transaction.Commit();

}

catch

{

transaction.Rollback();

}

[C#/COMMON/DAPPER] UPDATE문 실행하기

■ UPDATE문을 실행하는 방법을 보여준다. ▶ SQL Server 테이블 (SQL)


CREATE TABLE dbo.Student
(
    ID         INT          NOT NULL
   ,Name       NVARCHAR(50) NOT NULL
   ,CreateTime DATETIME     NOT NULL
    CONSTRAINT PKStudent PRIMARY KEY CLUSTERED
    (
	    ID ASC
    )
    WITH
    (
        PAD_INDEX              = OFF
       ,STATISTICS_NORECOMPUTE = OFF
       ,IGNORE_DUP_KEY         = OFF
       ,ALLOW_ROW_LOCKS        = ON
       ,ALLOW_PAGE_LOCKS       = ON
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE dbo.Student

(

ID INT NOT NULL

,Name NVARCHAR(50) NOT NULL

,CreateTime DATETIME NOT NULL

CONSTRAINT PKStudent PRIMARY KEY CLUSTERED

(

ID ASC

)

WITH

(

PAD_INDEX = OFF

,STATISTICS_NORECOMPUTE = OFF

,IGNORE_DUP_KEY = OFF

,ALLOW_ROW_LOCKS = ON

,ALLOW_PAGE_LOCKS = ON

) ON [PRIMARY]

▶ 예제 코드 (C#)


using System;
using System.Data.SqlClient;

using Dapper;

...

/// <summary>
/// 학생
/// </summary>
public class Student
{
    /// <summary>
    /// ID
    /// </summary>
    public int ID { get; set; }

    /// <summary>
    /// 성명
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// 생성 일시
    /// </summary>
    public DateTime CreateTime { get; set; }
}

...

string connectionString = "Data Source=127.0.0.1;Initial Catalog=TestDB;UID=sa;PWD=1234;Timeout=30";

using(SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    Student student = new Student();

    student.ID   = 1;
    student.Name = "홍길동";

    connection.Execute("UPDATE dbo.Student SET Name = @Name WHERE ID = @ID", student);
}

using System;

using System.Data.SqlClient;

using Dapper;

...

/// <summary>

/// 학생

/// </summary>

public class Student

{

/// <summary>

/// ID

/// </summary>

public int ID { get; set; }

/// <summary>

/// 성명

/// </summary>

public string Name { get; set; }

/// <summary>

/// 생성 일시

/// </summary>

public DateTime CreateTime { get; set; }

}

...

string connectionString = "Data Source=127.0.0.1;Initial Catalog=TestDB;UID=sa;PWD=1234;Timeout=30";

using(SqlConnection connection = new SqlConnection(connectionString))

{

connection.Open();

Student student = new Student();

student.ID = 1;

student.Name = "홍길동";

connection.Execute("UPDATE dbo.Student SET Name = @Name WHERE ID = @ID", student);

}

[C#/COMMON/DAPPER] INSERT문 실행하기

■ INSERT문을 실행하는 방법을 보여준다. ▶ SQL Server 테이블 (SQL)


CREATE TABLE dbo.Student
(
    ID         INT          NOT NULL
   ,Name       NVARCHAR(50) NOT NULL
   ,CreateTime DATETIME     NOT NULL
    CONSTRAINT PKStudent PRIMARY KEY CLUSTERED
    (
	    ID ASC
    )
    WITH
    (
        PAD_INDEX              = OFF
       ,STATISTICS_NORECOMPUTE = OFF
       ,IGNORE_DUP_KEY         = OFF
       ,ALLOW_ROW_LOCKS        = ON
       ,ALLOW_PAGE_LOCKS       = ON
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE dbo.Student

(

ID INT NOT NULL

,Name NVARCHAR(50) NOT NULL

,CreateTime DATETIME NOT NULL

CONSTRAINT PKStudent PRIMARY KEY CLUSTERED

(

ID ASC

)

WITH

(

PAD_INDEX = OFF

,STATISTICS_NORECOMPUTE = OFF

,IGNORE_DUP_KEY = OFF

,ALLOW_ROW_LOCKS = ON

,ALLOW_PAGE_LOCKS = ON

) ON [PRIMARY]

▶ 예제 코드 (C#)


using System;
using System.Data.SqlClient;

using Dapper;

...

/// <summary>
/// 학생
/// </summary>
public class Student
{
    /// <summary>
    /// ID
    /// </summary>
    public int ID { get; set; }

    /// <summary>
    /// 성명
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// 생성 일시
    /// </summary>
    public DateTime CreateTime { get; set; }
}

...

string connectionString = "Data Source=127.0.0.1;Initial Catalog=TestDB;UID=sa;PWD=1234;Timeout=30";

using(SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    Student student = new Student();

    student.ID         = 1;
    student.Name       = "홍길동";
    student.CreateTime = DateTime.Now;

    connection.Execute("INSERT dbo.Student (ID, Name, CreateTime) VALUES (@ID, @Name, @CreateTime)", student);
}

using System;

using System.Data.SqlClient;

using Dapper;

...

/// <summary>

/// 학생

/// </summary>

public class Student

{

/// <summary>

/// ID

/// </summary>

public int ID { get; set; }

/// <summary>

/// 성명

/// </summary>

public string Name { get; set; }

/// <summary>

/// 생성 일시

/// </summary>

public DateTime CreateTime { get; set; }

}

...

string connectionString = "Data Source=127.0.0.1;Initial Catalog=TestDB;UID=sa;PWD=1234;Timeout=30";

using(SqlConnection connection = new SqlConnection(connectionString))

{

connection.Open();

Student student = new Student();

student.ID = 1;

student.Name = "홍길동";

student.CreateTime = DateTime.Now;

connection.Execute("INSERT dbo.Student (ID, Name, CreateTime) VALUES (@ID, @Name, @CreateTime)", student);

}

[C#/COMMON/DAPPER] DAPPER 1.42.0 버전 사용하기

■ DAPPER 1.42.0 버전을 사용하는 방법을 보여준다. 1. 프로젝트 참조에서 "NuGet 패키지 관리…" 컨텍스트 메뉴를 클릭한다. 2. NuGet 패키지 관리자의 "찾아보기" 탭에서