■ DAPPER CRUD 작업을 하는 방법을 보여준다.
▶ TestDB.sql
|
1 2 3 4 5 6 7 8 9 10 11 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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
|
1 2 3 4 5 6 7 8 9 10 11 |
<?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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
<%@ 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
<%@ 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
<%@ 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
<%@ 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
<%@ 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
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 } } |
