■ 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 } } |
▶ UserModel.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 |
using System; namespace TestProject { /// <summary> /// 사용자 모델 /// </summary> public class UserModel { //////////////////////////////////////////////////////////////////////////////////////////////////// Property ////////////////////////////////////////////////////////////////////////////////////////// Public #region ID - ID /// <summary> /// ID /// </summary> public string ID { get; set; } #endregion #region 성명 - NAME /// <summary> /// 성명 /// </summary> public string NAME { get; set; } #endregion #region 역할 - ROLE /// <summary> /// 역할 /// </summary> public Role ROLE { get; set; } #endregion //////////////////////////////////////////////////////////////////////////////////////////////////// Method ////////////////////////////////////////////////////////////////////////////////////////// Static //////////////////////////////////////////////////////////////////////////////// Public #region 역할 구하기 - GetRole(value) /// <summary> /// 역할 구하기 /// </summary> /// <param name="value">값</param> /// <returns>역할</returns> public static Role GetRole(string value) { if(string.IsNullOrWhiteSpace(value)) { return Role.None; } string[] valueItemArray = value.Split(','); Role role = Role.None; int valueItemArrayLength = valueItemArray.Length; for(int i = 0; i < valueItemArrayLength; i++) { string valueItem = valueItemArray[i]; if(i == 0) { role = (Role)Enum.Parse(typeof(Role), valueItem); } else { role |= (Role)Enum.Parse(typeof(Role), valueItem); } } return role; } #endregion } } |
▶ RoleTypeHandler.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 |
using System.Data; using Dapper; namespace TestProject { /// <summary> /// 역할 타입 핸들러 /// </summary> public class RoleTypeHandler : SqlMapper.TypeHandler<Role> { //////////////////////////////////////////////////////////////////////////////////////////////////// Method ////////////////////////////////////////////////////////////////////////////////////////// Public #region 파싱하기 - Parse(value) /// <summary> /// 파싱하기 /// </summary> /// <param name="value">값</param> /// <returns>역할</returns> public override Role Parse(object value) { return UserModel.GetRole(value.ToString()); } #endregion #region 값 설정하기 - SetValue(parameter, value) /// <summary> /// 값 설정하기 /// </summary> /// <param name="parameter">매개 변수</param> /// <param name="value">값</param> public override void SetValue(IDbDataParameter parameter, Role value) { parameter.Value = value.ToString(); } #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 |
using System; using System.Linq; using Oracle.ManagedDataAccess.Client; using Dapper; namespace TestProject { /// <summary> /// 프로그램 /// </summary> class Program { //////////////////////////////////////////////////////////////////////////////////////////////////// Method ////////////////////////////////////////////////////////////////////////////////////////// Static //////////////////////////////////////////////////////////////////////////////// Private #region 프로그램 시작하기 - Main() /// <summary> /// 프로그램 시작하기 /// </summary> private static void Main() { 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"; SqlMapper.AddTypeHandler(new RoleTypeHandler()); using(OracleConnection connection = new OracleConnection(connectionString)) { var resultList = connection.Query<UserModel>("SELECT * FROM SCOTT.\"USER\"").ToList(); foreach(var item in resultList) { Console.WriteLine(item.ROLE); } } } #endregion } } |
