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 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 IEnumerable <BoardModel> GetList(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(MySqlDapperHelper.RunGetQuery <BoardModel>(sql + limit, new
{
board_type = board_type,
}));
}
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 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()
}));
}