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
출처 : https://bigexecution.tistory.com/79
