ASP.NET] DapperManager.cs

1. Nuget 패키지 관리자에서 Dapper 설치

.NET Framework에 맞는 버전으로 설치

 

2. DapperManager.cs

    public class DapperManager
    {
        public string DBConn = "DBNAME";
        public SqlConnection con;

        public DapperManager(DB_StrName eDB)
        {
            DBConn = eDB.ToString();
        }

        private SqlConnection SqlConnection()
        {
            return new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[DBConn].ConnectionString);
        }

        /// <summary>
        /// Open new connection and return it for use
        /// </summary>
        /// <returns></returns>
        private IDbConnection CreateConnection()
        {
            var conn = SqlConnection();
            conn.Open();
            return conn;
        }

        public IEnumerable<T> GetAll<T>(string selectQuery)
        {
            using (var connection = CreateConnection())
            {
                return connection.Query<T>(selectQuery);
            }
        }

        public int GetCount(string selectQuery)
        {
            using (var connection = CreateConnection())
            {
                return connection.Query(selectQuery).Count();
            }
        }
        public T GetByIdx<T>(int idx, string tableName, string idxColumnName = null)
        {
            using (var connection = CreateConnection())
            {
                string columnName = string.IsNullOrEmpty(idxColumnName) ? "idx" : idxColumnName;
                return connection.QuerySingleOrDefault<T>($"SELECT * FROM {tableName} WHERE {columnName} = @Idx", new { Idx = idx });
            }
        }
        public T GetById<T>(string id, string tableName, string idColumnName)
        {
            using (var connection = CreateConnection())
            {
                return connection.QuerySingleOrDefault<T>($"SELECT * FROM {tableName} WHERE {idColumnName} = @Id", new { Id = id });
            }
        }

        public int AddRow<T>(string insertQuery, T entity)
        {
            using (var connection = CreateConnection())
            {
                int affectedRows = connection.Execute(insertQuery, entity);
                return affectedRows;
            }
        }

        public int UpdateRow<T>(string updateQuery, T entity)
        {
            using (var connection = CreateConnection())
            {
                int affectedRows = connection.Execute(updateQuery, entity);
                return affectedRows;
            }
        }

        public int DeleteRow(int idx, string tableName, string idxColumnName = null)
        {
            using (var connection = CreateConnection())
            {
                string columnName = string.IsNullOrEmpty(idxColumnName) ? "idx" : idxColumnName;
                int affectedRows = connection.Execute($"DELETE FROM {tableName} WHERE {columnName} = @Idx", new { Idx = idx });
                return affectedRows;
            }
        }

        public IEnumerable<T> ExecuteProcedure<T>(string storedProcedure, object parameters = null)
        {
            int commandTimeout = 180;

            using (var connection = CreateConnection())
            {
                if (parameters != null)
                {
                    return connection.Query<T>(storedProcedure, parameters,
                        commandType: CommandType.StoredProcedure, commandTimeout: commandTimeout);
                }
                else
                {
                    return connection.Query<T>(storedProcedure,
                        commandType: CommandType.StoredProcedure, commandTimeout: commandTimeout);
                }
            }
        }

    }

3. NoticeDTO.cs

    public class Notice
    {
        public int idx_num { get; set; }
        public string title { get; set; }
        public string contents { get; set; }
        public string file1 { get; set; }
        public DateTime reg_date { get; set; }
        public DateTime mod_date { get; set; }
    }

 

4. NoticeDA.cs

        #region 공지사항 등록하기
        public static int Add_Notice(Notice notice)
        {
            DapperManager dm = new DapperManager("DBNAME");
            var insertQuery = "INSERT INTO NOTICE(title, contents, file1, reg_Date, mod_date, click_count) VALUES (@title, @contents, @file1, @reg_Date, @mod_date, 0)";
            return dm.AddRow<Notice>(insertQuery, notice);
        }
        #endregion

        #region 공지사항 수정하기
        public static int Update_Notice(Notice notice)
        {
            DapperManager dm = new DapperManager("DBNAME");
            var updateQuery = "UPDATE NOTICE SET title = @title, file1 = @file1, contents = @contents WHERE idx_num = @idx_num";
            return dm.UpdateRow<Notice>(updateQuery, notice);
        }
        #endregion
        
        #region 공지사항 삭제하기
        public static int Delete_Notice(int idx)
        {
            DapperManager dm = new DapperManager("DBNAME");
            return dm.DeleteRow(idx, "NOTICE", "idx_num");
        }
        #endregion
        
        #region 공지사항 가져오기
        public static Notice Get_Notice(int idx)
        {
            DapperManager dm = new DapperManager("DBNAME");
            return dm.GetByIdx<Notice>(idx, "NOTICE", "idx_num");
        }
        #endregion