[C#/COMMON/DAPPER] Dapper 누겟 설치하기
■ SignalR 누겟을 설치하는 방법을 보여준다. 1. Visual Studio를 실행한다. 2. [도구] / [NuGet 패키지 관리자] / [패키지 관리자 콘솔] 메뉴를 실행한다.
[카테고리:] DAPPER
[C#/COMMON/DAPPER] SqlMapper 클래스 : GetRowParser 확장 메소드를 사용해 제네릭 타입 데이터의 .CSV 파일 생성하기
■ SqlMapper 클래스의 GetRowParser 확장 메소드를 사용해 제네릭 타입 데이터의 .CSV 파일을 생성하는 방법을 보여준다. ▶ MainForm.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 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
|
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 |
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
|
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 |
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)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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
|
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 |
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
|
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 |
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
|
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 { /// <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
|
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 |
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)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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; |
※ 오라클 11G의
[C#/COMMON/DAPPER] NULLABLE DATETIME 사용시 처리하기
■ NULLABLE DATETIME 사용시 처리하는 방법을 보여준다. Dapper에서 DateTime? 컬럼 값 조회시 타입 변환 에러가 발생하기 때문에 NullableDateTimeHandler.cs 소스코드를 추가하고, 프로그램 시작시
[C#/COMMON/DAPPER] 부모 객체를 포함해 조회하기
■ 부모 객체를 포함해 조회하는 방법을 보여준다. ▶ 부모 객체를 포함해 조회하기 예제 (C#)
|
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 |
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#)
|
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 |
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 테이블
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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 |
▶ 예제 코드 (C#)
|
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 |
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)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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 |
▶ 예제 코드 (C#)
|
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 |
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)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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 |
▶ 예제 코드 (C#)
|
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 |
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)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 |
▶ 예제 코드 (C#)
|
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 |
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)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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 |
▶ 예제 코드 (C#)
|
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 |
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)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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 |
▶ 예제 코드 (C#)
|
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 |
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 패키지 관리자의 "찾아보기" 탭에서