첨부 실행 코드는 나눔고딕코딩 폰트를 사용합니다.
유용한 소스 코드가 있으면 icodebroker@naver.com으로 보내주시면 감사합니다.
블로그 자료는 자유롭게 사용하세요.

■ DAPPER CRUD 작업하기

------------------------------------------------------------------------------------------------------------------------


TestProject.zip


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

 

 

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

    }

}

 

 

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

    }

}

 

 

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>

 

 

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>

 

 

ListPage.aspx.cs

 

 

using System;

using System.Web.UI;

 

namespace TestProject

{

    /// <summary>

    /// 목록 페이지

    /// </summary>

    public partial class ListPage : Page

    {

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

        ////////////////////////////////////////////////////////////////////////////////////////// Protected

 

        #region 페이지 로드시 처리하기 - Page_Load(sender, e)

 

        /// <summary>

        /// 페이지 로드시 처리하기

        /// </summary>

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

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

        protected void Page_Load(object sender, EventArgs e)

        {

            if(!Page.IsPostBack)

            {

                DisplayData();

            }

        }

 

        #endregion

 

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

 

        #region 데이터 표시하기 - DisplayData()

 

        /// <summary>

        /// 데이터 표시하기

        /// </summary>

        private void DisplayData()

        {

            MaximServiceRepository repository = new MaximServiceRepository();

 

            this.gridView.DataSource = repository.GetList();

 

            this.gridView.DataBind();

        }

 

        #endregion

    }

}

 

 

ViewPage.aspx

 

 

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

<!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>

                ID : <asp:Label ID="idLabel" runat="server" /><br />

                명칭 : <asp:Label ID="nameLabel" runat="server" /><br />

                내용 : <asp:Label ID="contentLabel" runat="server" /><br />

                <hr />

                <asp:HyperLink ID="updateButton" runat="server">수정</asp:HyperLink>

                <asp:HyperLink ID="deleteButton" runat="server">삭제</asp:HyperLink>

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

                    NavigateUrl="~/ListPage.aspx">

                    목록

                </asp:HyperLink>

            </div>

        </form>

    </body>

</html>

 

 

ViewPage.aspx.cs

 

 

using System;

using System.Web.UI;

 

using TestProject.Models;

 

namespace TestProject

{

    /// <summary>

    /// 뷰 페이지

    /// </summary>

    public partial class ViewPage : Page

    {

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

        ////////////////////////////////////////////////////////////////////////////////////////// Protected

 

        #region 페이지 로드시 처리하기 - Page_Load(sender, e)

 

        /// <summary>

        /// 페이지 로드시 처리하기

        /// </summary>

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

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

        protected void Page_Load(object sender, EventArgs e)

        {

            if(!Page.IsPostBack)

            {

                DisplayData();

            }

        }

 

        #endregion

 

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

 

        #region 데이터 표시하기 - DisplayData()

 

        /// <summary>

        /// 데이터 표시하기

        /// </summary>

        private void DisplayData()

        {

            int id = Convert.ToInt32(Request.QueryString["ID"]);

 

            MaximServiceRepository repository = new MaximServiceRepository();

 

            Maxims maxims = repository.Get(id);

 

            this.idLabel.Text      = id.ToString();

            this.nameLabel.Text    = maxims.Name;

            this.contentLabel.Text = maxims.Content;

 

            this.updateButton.NavigateUrl = "UpdatePage.aspx?ID=" + id;

            this.deleteButton.NavigateUrl = "DeletePage.aspx?ID=" + id;

        }

 

        #endregion

    }

}

 

 

WritePage.aspx

 

 

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

<!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:TextBox ID="nameTextBox" runat="server" />

                <br />

                내용 : <asp:TextBox ID="contentTextBox" runat="server" />

                <br />

                <asp:Button ID="writeButton" runat="server"

                    Text="쓰기"

                    OnClick="writeButton_Click" />

                <br />

                <asp:Label ID="displayLabel" runat="server" />

                <hr />

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

                    NavigateUrl="~/ListPage.aspx">

                    목록

                </asp:HyperLink>    

            </div>

        </form>

    </body>

</html>

 

 

WritePage.aspx.cs

 

 

using System;

using System.Web.UI;

 

using TestProject.Models;

 

namespace TestProject

{

    /// <summary>

    /// 쓰기 페이지

    /// </summary>

    public partial class WritePage : Page

    {

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

        ////////////////////////////////////////////////////////////////////////////////////////// Protected

 

        #region 페이지 로드시 처리하기 - Page_Load(sender, e)

 

        /// <summary>

        /// 페이지 로드시 처리하기

        /// </summary>

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

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

        protected void Page_Load(object sender, EventArgs e)

        {

        }

 

        #endregion

        #region 쓰기 버튼 클릭시 처리하기 - writeButton_Click(sender, e)

 

        /// <summary>

        /// 쓰기 버튼 클릭시 처리하기

        /// </summary>

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

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

        protected void writeButton_Click(object sender, EventArgs e)

        {

            Maxims maxims = new Maxims();

 

            maxims.Name    = this.nameTextBox.Text;

            maxims.Content = this.contentTextBox.Text;

 

            MaximServiceRepository repository = new MaximServiceRepository();

 

            maxims.ID = repository.Add(maxims).ID;

 

            this.displayLabel.Text = maxims.ID.ToString() + "번 데이터가 추가되었습니다.";

        }

 

        #endregion

    }

}

 

 

UpdatePage.aspx

 

 

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

<!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>

                ID : <asp:Label ID="idLabel" runat="server" /><br />

                명칭 : <asp:TextBox ID="nameTextBox" runat="server" /><br />

                내용 : <asp:TextBox ID="contentTextBox" runat="server" /><br />

                <asp:Button ID="updateButton" runat="server"

                    Text="수정"

                    OnClick="updateButton_Click" />

                <br />

                <asp:Label ID="displayLabel" runat="server" />

                <hr />

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

                    NavigateUrl="~/ListPage.aspx">

                    목록

                </asp:HyperLink>

            </div>

        </form>

    </body>

</html>

 

 

UpdatePage.aspx.cs

 

 

using System;

using System.Web.UI;

 

using TestProject.Models;

 

namespace TestProject

{

    /// <summary>

    /// 수정 페이지

    /// </summary>

    public partial class UpdatePage : Page

    {

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

        ////////////////////////////////////////////////////////////////////////////////////////// Protected

 

        #region 페이지 로드시 처리하기 - Page_Load(sender, e)

 

        /// <summary>

        /// 페이지 로드시 처리하기

        /// </summary>

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

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

        protected void Page_Load(object sender, EventArgs e)

        {

            if(!string.IsNullOrEmpty(Request.QueryString["ID"]))

            {

                if(!Page.IsPostBack)

                {

                    DisplayData();

                }

            }

            else

            {

                Response.Write("잘못된 요청입니다.");

 

                Response.End();

            }

        }

 

        #endregion

        #region 수정 버튼 클릭시 처리하기 - updateButton_Click(sender, e)

 

        /// <summary>

        /// 수정 버튼 클릭시 처리하기

        /// </summary>

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

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

        protected void updateButton_Click(object sender, EventArgs e)

        {

            Maxims maxims = new Maxims();

 

            maxims.ID      = Convert.ToInt32(Request.QueryString["ID"]);

            maxims.Name    = this.nameTextBox.Text;

            maxims.Content = this.contentTextBox.Text;

 

            MaximServiceRepository repository = new MaximServiceRepository();

 

            maxims = repository.Update(maxims);

 

            this.displayLabel.Text = maxims.ID.ToString() + "번 데이터가 수정되었습니다.";

 

            DisplayData();

        }

 

        #endregion

 

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

 

        #region 데이터 표시하기 - DisplayData()

 

        /// <summary>

        /// 데이터 표시하기

        /// </summary>

        private void DisplayData()

        {

            int id = Convert.ToInt32(Request.QueryString["ID"]);

 

            MaximServiceRepository repository = new MaximServiceRepository();

 

            Maxims maxim = repository.Get(id);

 

            this.idLabel.Text        = id.ToString();

            this.nameTextBox.Text    = maxim.Name;

            this.contentTextBox.Text = maxim.Content;

        }

 

        #endregion

    }

}

 

 

DeletePage.aspx

 

 

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

<!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:Label ID="idLabel" runat="server" />

                번 글을 삭제하시겠습니까?

                <asp:Button ID="deleteButton" runat="server"

                    Text="삭제"

                    OnClick="deleteButton_Click" />

                <hr />

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

                    NavigateUrl="~/ListPage.aspx">

                    목록

                </asp:HyperLink>

            </div>

        </form>

    </body>

</html>

 

 

DeletePage.aspx.cs

 

 

using System;

using System.Web.UI;

 

namespace TestProject

{

    /// <summary>

    /// 삭제 페이지

    /// </summary>

    public partial class DeletePage : Page

    {

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

        ////////////////////////////////////////////////////////////////////////////////////////// Protected

 

        #region 페이지 로드시 처리하기 - Page_Load(sender, e)

 

        /// <summary>

        /// 페이지 로드시 처리하기

        /// </summary>

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

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

        protected void Page_Load(object sender, EventArgs e)

        {

            if(!string.IsNullOrEmpty(Request.QueryString["ID"]))

            {

                if(!Page.IsPostBack)

                {

                    this.idLabel.Text = Request["ID"];

                }

            }

            else

            {

                Response.Write("잘못된 요청입니다.");

 

                Response.End();

            }

        }

 

        #endregion

        #region 삭제 버튼 클릭시 처리하기 - deleteButton_Click(sender, e)

 

        /// <summary>

        /// 삭제 버튼 클릭시 처리하기

        /// </summary>

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

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

        protected void deleteButton_Click(object sender, EventArgs e)

        {

            int id = Convert.ToInt32(Request.QueryString["ID"]);

 

            MaximServiceRepository repository = new MaximServiceRepository();

 

            repository.Delete(id);

 

            Response.RedirectPermanent("ListPage.aspx");

        }

 

        #endregion

    }

}

 

------------------------------------------------------------------------------------------------------------------------

Posted by 사용자 icodebroker

댓글을 달아 주세요