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

첨부 실행 코드는 나눔고딕코딩 폰트를 사용합니다.

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

C#/ASP.NET 2020. 9. 29. 16:34

728x90

728x170

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

728x90

▶ 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
    }
}

300x250

▶ 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
    }
}

728x90

그리드형(광고전용)

'C# > ASP.NET' 카테고리의 다른 글

[C#/ASP.NET] ASP.NET 게시판 사용하기 (0)	2020.10.01
[C#/ASP.NET] FormsAuthentication 클래스 : 사용자 로그인 관리하기 (0)	2020.09.30
[C#/ASP.NET] Chart 클래스 사용하기 (0)	2020.09.30
[C#/ASP.NET] ObjectDataSource 클래스 사용하기 (0)	2020.09.29
[C#/ASP.NET] XmlDataSource 클래스 사용하기 (0)	2020.09.29
[C#/ASP.NET] 데이터베이스 CRUD 작업하기 (0)	2020.09.29
[C#/ASP.NET] Page 클래스 : Session 속성을 사용해 일정 시간 내 글쓰기 방지하기 (0)	2020.09.29
[C#/ASP.NET] ASP.NET 상태 관리하기 (0)	2020.09.29
[C#/ASP.NET] MasterPage 클래스 : 마스터 페이지 사용하기 (0)	2020.09.28
[C#/ASP.NET] UserControl 클래스 : 웹 사이트 뼈대 만들기 (0)	2020.09.28

Posted by icodebroker

TAG .netframework, ASP.NET, c#, dapper, Database, Microsoft, ORM

, 댓글 0개가 달렸습니다.

ICODEBROKER

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

▶ TestDB.sql

▶ Maxims.cs

▶ MaximsServiceRegistory.cs

▶ Web.config

▶ ListPage.aspx

▶ ListPage.aspx.cs

▶ ViewPage.aspx

▶ ViewPage.aspx.cs

▶ WritePage.aspx

▶ WritePage.aspx.cs

▶ UpdatePage.aspx

▶ UpdatePage.aspx.cs

▶ DeletePage.aspx

▶ DeletePage.aspx.cs

'C# > ASP.NET' 카테고리의 다른 글

댓글을 달아 주세요

카테고리

태그목록

최근에 올라온 글

티스토리툴바