public int Update()
{
using (var db = new MySqlDapperHelper())
{
db.BeginTransaction();
try
{
int r = 0;
string sql = @"
UPDATE t_ticket
SET
title = @title
WHERE
ticket_id = @ticket_id
";
r += db.Execute(sql, this);
db.Commit();
return(r);
}
catch (Exception ex)
{
db.Rollback();
throw ex;
}
}
}
public IActionResult Save(BoardModel input)
{
using (var db = new MySqlDapperHelper())
{
db.BeginTransaction();
try
{
//input.TITLE 및 input.CONTENTS 검증 필요
//특히 contents 는 xss와 같은 script태그나 img 태그 공격도 체크해야함....
input.REG_IP = HttpContext.Connection.RemoteIpAddress.ToString();
input.REG_USER = User.Identity.Name; //USER의 고유 ID를 Claim에 저장하고 가져오는 방법????
input.REG_USERNAME = User.Identity.Name;
input.Insert(db);
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
return(Json(new { msg = ex.Message }));
}
return(Json(new { msg = "OK" }));
}
}
public int Insert(MySqlDapperHelper db)
{
return(db.Execute($@"
INSERT INTO BOARD (
BOARD_TYPE
,SEQ
,TITLE
,CONTENTS
,DUP_KEY
,VIEW_CNT
,STATUS_FLAG
,REG_IP
,REG_USER
,REG_USERNAME
,REG_DATE
)
SELECT
@BOARD_TYPE
,IFNULL((SELECT MAX(SEQ) + 1 FROM BOARD), 1)
,@TITLE
,@CONTENTS
,@DUP_KEY
,@VIEW_CNT
,'Y'
,@REG_IP
,@REG_USER
,@REG_USERNAME
,now()
", this));
}
public static BoardModel Get(string board_type, int seq)
{
MySqlDapperHelper.RunExecute("UPDATE BOARD SET VIEW_CNT = VIEW_CNT + 1 WHERE BOARD_TYPE = @board_type AND SEQ = @seq", new
{
board_type = board_type,
seq = seq
});
return(MySqlDapperHelper.RunGetQuery <BoardModel>(@"
SELECT
A.BOARD_TYPE
,A.SEQ
,A.TITLE
,A.CONTENTS
,A.DUP_KEY
,A.VIEW_CNT
,A.STATUS_FLAG
,A.REG_IP
,A.REG_USER
,A.REG_USERNAME
,A.REG_DATE
FROM
BOARD A
WHERE
A.BOARD_TYPE = @board_type
AND A.SEQ = @seq
ORDER BY
A.SEQ DESC
", new
{
board_type = board_type,
seq = seq
}).FirstOrDefault());
}
public static int GetCount(string board_type)
{
return(MySqlDapperHelper.RunGetQueryFromXml <int>("Sql/Board.xml", "GetBoardCount", new
{
board_type = board_type,
}).FirstOrDefault());
}
public static IList <BoardModel> GetList(string board_type, int page = 1, int page_size = 20)
{
return(MySqlDapperHelper.RunGetQuery <BoardModel>($@"
SELECT
A.BOARD_TYPE
,A.SEQ
,A.TITLE
,A.VIEW_CNT
,A.STATUS_FLAG
,A.REG_IP
,A.REG_USER
,A.REG_USERNAME
,A.REG_DATE
FROM
BOARD A
WHERE
A.BOARD_TYPE = @board_type
ORDER BY
A.SEQ DESC
LIMIT
{(page - 1) * page_size}, {page_size}
", new
{
board_type = board_type
}));
}
public int Insert()
{
CheckContents();
string sql = @"
INSERT INTO t_board (
title
,contents
,reg_user
,reg_username
,reg_date
,view_Cnt
,status_flag
)
VALUES (
@title
,@contents
,@reg_user
,@reg_username
,now()
,0
,0
)
";
using (var db = new MySqlDapperHelper())
{
return(db.Execute(sql, this));
}
}
public async static Task <IEnumerable <DeliveryInfoModel> > GetList(string company_type, string invoice_no)
{
return(await MySqlDapperHelper.RunGetQueryFromXmlAsync <DeliveryInfoModel>("/Sql/Lab.xml", "GetDeliveryList", new {
company_type = company_type,
invoice_no = invoice_no,
}));
}
public async Task <IActionResult> WritePost(MBoard input)
{
using (var db = new MySqlDapperHelper())
{
db.BeginTransaction();
try
{
input.CONTENTS = new Ganss.XSS.HtmlSanitizer().Sanitize(input.CONTENTS);
input.REG_UID = _login.U_ID; //USER의 고유 ID를 Claim에 저장하고 가져오는 방법????
input.REG_USERNAME = _login.USER_NAME;
input.REG_IP = HttpContext.Connection.GetRemoteIpAddress();
await input.Insert(db);
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
return(Json(new { msg = ex.Message }));
}
return(Json(new { msg = "OK" }));
}
}
/* 필요한가?
* public string UPDATE_IP { get; set; }
* public string UPDATE_USER { get; set; }
* public string UPDATE_USERNAME { get; set; }
* public DateTime? UPDATE_DATE { get; set; }
*/
public static BoardModel Get(string board_type, int seq)
{
return(MySqlDapperHelper.RunGetQueryFromXml <BoardModel>("Sql/Board.xml", "GetBoard", new
{
board_type = board_type,
seq = seq
}).FirstOrDefault());
}
public static async Task <BoardModel> GetAsync(string board_type, int seq)
{
var model = await MySqlDapperHelper.RunGetQueryFromXmlAsync <BoardModel>("Sql/Board.xml", "GetBoard", new
{
board_type = board_type,
seq = seq
});
return(model.FirstOrDefault());
}
public static async Task <IEnumerable <BoardModel> > GetListAsync(string board_type, int page = 1, int page_size = 20)
{
//데이터가 많아지면 LIMIT가 느려질수 있다고함, WHERE 로 모집합을 줄이고 LIMIT를 걸어야한다고....
var sql = MySqlDapperHelper.GetSqlFromXml("Sql/Board.xml", "GetBoardList");
var limit = $" LIMIT {(page - 1) * page_size}, {page_size}";
return(await MySqlDapperHelper.RunGetQueryAsync <BoardModel>(sql + limit, new
{
board_type = board_type,
}));
}
public int Delete()
{
string sql = @"
DELETE FROM t_board
WHERE
idx = @idx
";
using (var db = new MySqlDapperHelper())
{
return(db.Execute(sql, this));
}
}
public static IEnumerable <MySqlDbTableModel> GetTableList(string schemaName)
{
return(MySqlDapperHelper.RunGetQuery <MySqlDbTableModel>(@"
SELECT
TABLE_NAME
,ENGINE
FROM
INFORMATION_SCHEMA.tables
WHERE
TABLE_SCHEMA = @schemaName
ORDER BY
TABLE_NAME
", new { schemaName = schemaName.ToLower() }));
}
public static List <TicketModel> GetList(string status)
{
using (var db = new MySqlDapperHelper())
{
string sql = @"
SELECT
A.ticket_id
,A.title
,A.status
FROM
t_ticket A
WHERE
A.status = @status
";
return(db.Query <TicketModel>(sql, new { status = status }));
}
}
public int Update()
{
CheckContents();
string sql = @"
UPDATE t_board
SET
title = @title
,contents = @contents
WHERE
idx = @idx
";
using (var db = new MySqlDapperHelper())
{
return(db.Execute(sql, this));
}
}
public static UserinfoModel GetLogin(string user_id, string pw)
{
var model = MySqlDapperHelper.RunGetQueryFromXml <UserinfoModel>("Sql/User.xml", "GetUserInfoAll", new { user_id = user_id.ToLower() }).FirstOrDefault();
if (model == null)
{
throw new Exception("id 없음");
}
else
{
var pwhash = CoreLib.Crypter.HMacSha256.GetHMac(pw.Trim(), model.UNIQUE_ID.ToString());
if (model.PASSWORD != pwhash)
{
throw new Exception("패스워드가 틀렸습니다");
}
return(model);
}
}
internal int Register()
{
//중복 user_name이 있는지?
//중복 email 있는지?
string sql = @"
INSERT INTO t_user (
user_name
,email
,password
)
SELECT
@user_name
,@email
,@password
";
using (var db = new MySqlDapperHelper())
{
return(db.Execute(sql, this));
}
}
public static BoardModel Get(uint idx)
{
using (var db = new MySqlDapperHelper())
{
string sql = @"
SELECT
A.idx
,A.title
,A.contents
,A.reg_user
,A.reg_username
,A.reg_date
,A.view_Cnt
,A.status_flag
FROM
t_board A
WHERE
A.idx = @idx
";
return(db.QuerySingle <BoardModel>(sql, new { idx = idx }));
}
}
public static List <BoardModel> GetList(string search)
{
using (var db = new MySqlDapperHelper())
{
string sql = @"
SELECT
A.idx
,A.title
,A.reg_user
,A.reg_username
,A.reg_date
,A.view_Cnt
,A.status_flag
FROM
t_board A
WHERE
A.title LIKE CONCAT('%', IFNULL(@search, ''), '%')
ORDER BY
A.idx DESC
";
return(db.Query <BoardModel>(sql, new { search = search }));
}
}
public static IEnumerable <MySqlDbTableModel> GetTableColumnsList(string schemaName, string tableName)
{
return(MySqlDapperHelper.RunGetQuery <MySqlDbTableModel>(@"
SELECT
COLUMN_NAME
,DATA_TYPE
,CHARACTER_MAXIMUM_LENGTH
,IS_NULLABLE
,COLUMN_COMMENT
,COLUMN_KEY
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = @schemaName
AND TABLE_NAME = @tableName
ORDER BY
ORDINAL_POSITION
", new
{
schemaName = schemaName.ToLower(),
tableName = tableName.ToLower()
}));
}
internal UserModel GetLoginUser()
{
//this.User_Name
//this.Password
string sql = @"
SELECT
user_seq
,user_name
,email
,password
FROM
t_user
WHERE
user_name = @user_name
";
UserModel user;
using (var db = new MySqlDapperHelper())
{
user = db.QuerySingle <UserModel>(sql, this);
}
if (user == null)
{
throw new Exception("사용자가 존재하지 않습니다");
}
if (user.Password != this.Password)
{
throw new Exception("비밀번호가 틀립니다");
//비밀번호 틀린 횟수 -- update
}
return(user);
}
public static IList <TestModel> GetList(int col1)
{
return(MySqlDapperHelper.RunGetQueryXml <TestModel>("Sql/Home.xml", "GetTestData", new { COL1 = col1 }));
}
public int AddViewCount()
{
return(MySqlDapperHelper.RunExecuteFromXml("Sql/Board.xml", "UpdateViewCount", this));
}
public int Insert(MySqlDapperHelper db)
{
return(db.ExecuteFromXml("Sql/Board.xml", "InsertBoard", this));
}
public async Task <int> Insert(MySqlDapperHelper db)
{
return(await db.ExecuteFromXmlAsync("Board.xml", "InsertBoard", this));
}
