public int insertNewsData(TbNewsData model)
{
string sql = getInsertSql("TbNewsDataSqlProvider", "insertTableNews");
//string sql = @"INSERT INTO TB_NEWS_INFO (BEGIN_DATE, CONTENT ,END_DATE ,FILE_PATH,TITLE , CR_DATE) VALUES( @BEGIN_DATE , @CONTENT ,@END_DATE ,@FILE_PATH ,@TITLE , getdate());";
int result = ExecuteTableBySql(sql, modelDbParametersMapping(model));
return(result);
}
public int updNewsData(TbNewsData model)
{
string sql = getUpdateSql("TbNewsDataSqlProvider", "updateTableNews");
//string sql = "update TB_NEWS_INFO set BEGIN_DATE = @BEGIN_DATE , CONTENT = @CONTENT , END_DATE = @END_DATE , FILE_PATH = @FILE_PATH , TITLE = @TITLE where SNO = @SNO";
int result = ExecuteTableBySql(sql, modelDbParametersMapping(model));
return(result);
}
private object modelDbParametersMapping(TbNewsData model)
{
return(new
{
SNO = model.SNO, //update會用
TITLE = model.TITLE,
CONTENT = model.CONTENT,
BEGIN_DATE = model.BEGIN_DATE,
END_DATE = model.END_DATE,
FILE_PATH = model.FILE_PATH,
CR_DATE = model.CR_DATE
});
}
public List <TbNewsData> qryNewsDatabyList(TbNewsData condition = null)
{
string sql = "";
string conditionStr = "";
List <TbNewsData> list = new List <TbNewsData>();
int flag = checkTableColumn();
if (condition == null)
{
sql = getSelectSql("TbNewsDataSqlProvider", "selectTableNewsList");
}
else
{
conditionStr += " where 1=1 ";
if (!string.IsNullOrEmpty(condition.TITLE))
{
conditionStr += " and TITLE like @TITLE ";
}
sql = getSelectSql("TbNewsDataSqlProvider", "selectTableNewsList", conditionStr);
}
IEnumerable <dynamic> resultList = null;
if (condition != null)
{
resultList = QueryTableListBySql(sql, new
{
TITLE = "%" + condition.TITLE + "%",
});
}
else
{
resultList = QueryTableListBySql(sql);
}
foreach (var items in resultList)
{
list.Add(new TbNewsData()
{
SNO = items.SNO,
TITLE = items.TITLE,
CONTENT = items.CONTENT,
BEGIN_DATE = items.BEGIN_DATE,
END_DATE = items.END_DATE,
FILE_PATH = items.FILE_PATH,
CR_DATE = items.CR_DATE
}
);
}
return(list);
}
public TbNewsData qryNewsData(string sno)
{
TbNewsData model = new TbNewsData();
using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){
try
{
dbConn.Open();
dao.dbConn = dbConn;
model = dao.qryNewsData(sno);
}
catch (Exception e)
{
logger.Error(e, e.Message);
}
}
return(model);
}
public List <TbNewsData> qryNewsDatabyList(TbNewsData model = null)
{
List <TbNewsData> list = new List <TbNewsData>();
using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){
try
{
dbConn.Open();
dao.dbConn = dbConn;
list = dao.qryNewsDatabyList(model);
}
catch (Exception e)
{
logger.Error(e, e.Message);
}
}
return(list);
}
public List <TbNewsData> qryNewsListByIndex()
{
List <TbNewsData> newsList = new List <TbNewsData>();
string sql = getSelectSql("TbNewsDataSqlProvider", "qryNewsListByIndex", " where BEGIN_DATE < getDate() ");
//string sql = "select top 5 BEGIN_DATE , CONTENT ,END_DATE, SNO ,TITLE , CR_DATE , FILE_PATH from TB_NEWS_INFO where BEGIN_DATE < getDate() ";
//sql += "order by CR_DATE desc, BEGIN_DATE desc";
var resultList = QueryTableListBySql(sql);
foreach (var items in resultList)
{
TbNewsData model = new TbNewsData();
model.SNO = items.SNO;
model.TITLE = items.TITLE;
model.CONTENT = items.CONTENT;
model.BEGIN_DATE = items.BEGIN_DATE;
model.END_DATE = items.END_DATE;
model.FILE_PATH = items.FILE_PATH;
model.CR_DATE = items.CR_DATE;
newsList.Add(model);
}
return(newsList);
}
public TbNewsData qryNewsData(string sno)
{
string sql = "";
string conditionStr = "";
TbNewsData model = new TbNewsData();
conditionStr += " where SNO = @SNO ";
sql = getSelectSql("TbNewsDataSqlProvider", "selectTableNewsList", conditionStr);
//sql = "select BEGIN_DATE , CONTENT ,END_DATE, SNO ,TITLE , CR_DATE , FILE_PATH from TB_NEWS_INFO " + conditionStr + " order by CR_DATE desc";
var resultList = QueryTableListBySql(sql, new { SNO = sno });
foreach (var items in resultList)
{
model.SNO = items.SNO;
model.TITLE = items.TITLE;
model.CONTENT = items.CONTENT;
model.BEGIN_DATE = items.BEGIN_DATE;
model.END_DATE = items.END_DATE;
model.FILE_PATH = items.FILE_PATH;
model.CR_DATE = items.CR_DATE;
}
return(model);
}
public Dictionary <string, dynamic> updNewsData(TbNewsData model)
{
Dictionary <string, dynamic> result = new Dictionary <string, dynamic>();
string status = "success";
string msg = "最新消息修改成功";
using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){
dbConn.Open();
SqlTransaction dbConnTxn = dbConn.BeginTransaction();
try{
dao.dbConn = dbConn;
dao.dbConnTxn = dbConnTxn;
if (dao.updNewsData(model) == 0)
{
status = "error";
msg = "最新消息修改失敗!!";
}
}catch (Exception e) {
logger.Error(e, e.Message);
status = "exception";
msg = "處理您的要求時發生錯誤!!";
}finally{
if (status == "success")
{
dbConnTxn.Commit();
}
else
{
dbConnTxn.Rollback();
}
}
}
result.Add("status", status);
result.Add("msg", msg);
return(result);
}
