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)); }