public void Update(Model.CMS.Article entity)
{
using (IDbConnection conn = SqlString.GetMySqlConnection())
{
conn.Execute("UPDATE cms_article SET ColumnId=@ColumnId,Tags=@Tags,Tagids=@TagIds,ArticleContent=@ArticleContent,Title=@Title,CreateTime=@CreateTime,LastTime=@LastTime,Browse=@Browse,Author=@Author,ImgUrl=@ImgUrl,Digest=@Digest,SEO_Title=@SEO_Title,SEO_Keywords=@SEO_Keywords,SEO_DES=@SEO_DES,IsTop=@IsTop,IsShow=@IsShow,Source=@Source,SourceLink=@SourceLink,Praise=@Praise WHERE Article_Id =@Article_Id", entity);
}
}
public void Update(Model.CMS.Article entity)
{
using (IDbConnection conn = SqlString.GetMySqlConnection())
{
conn.Execute("UPDATE cms_article SET ColumnId=@ColumnId,Tags=@Tags,ArticleContent=@ArticleContent,Title=@Title,CreateTime=@CreateTime,LastTime=@LastTime,Browse=@Browse,Author=@Author,IsTop=@IsTop,IsShow=@IsShow WHERE Article_Id =@Article_Id", entity);
}
}
public System.Collections.Generic.List <Model.CMS.Article> ToSearchList(int pageIndex, int pageSize, string searchTitle, int sort, out int count)
{
using (IDbConnection conn = SqlString.GetSQLiteConnection())
{
//sqlite使用||链接字符串
string sql01 = "select count(Article_Id) from cms_article where Title like '%'||@Title||'%'";
count = conn.Query <int>(sql01, new { Title = searchTitle }).SingleOrDefault();
Model.CMS.Article articleTemp = new Model.CMS.Article();
string sql = "select a.*,b.* from cms_article as a left join cms_column as b on a.ColumnId=b.Column_Id";
string sort_str = "DESC";
if (sort == 1)
{
sort_str = "DESC";
}
else if (sort == 2)
{
sort_str = "ASC";
}
string query = "SELECT * from (" + sql + ")as c where Title like '%'||@Title||'%' ORDER BY CreateTime " + sort_str +
" LIMIT " + (pageIndex - 1) * pageSize + "," + pageSize;
return(conn.Query <Model.CMS.Article, Model.CMS.Column, Model.CMS.Article>(query, (article, column) =>
{
if (column != null)
{
article.Column = column;
}
return article;
}, new { Title = searchTitle }, null, "Column_Id", null, null).ToList());
}
}
public System.Collections.Generic.List <Model.CMS.Article> ToSearchList(int pageIndex, int pageSize, string searchTitle, int sort, out int count)
{
using (IDbConnection conn = SqlString.GetMySqlConnection())
{
//sqlite使用||链接字符串
string sql01 = "select count(Article_Id) from cms_article where Title like concat('%',@Title,'%')";
count = conn.Query <int>(sql01, new { Title = searchTitle }).SingleOrDefault();
Model.CMS.Article articleTemp = new Model.CMS.Article();
string sql = "select a.*,b.* from cms_article as a left join cms_column as b on a.ColumnId=b.Column_Id";
string sort_str = "DESC";
if (sort == 1)
{
sort_str = "DESC";
}
else if (sort == 2)
{
sort_str = "ASC";
}
string query = string.Format(@"SELECT * from ({0})as c where Title like concat('%',@Title,'%') ORDER BY CreateTime {1}
LIMIT {2},{3}", sql, sort_str, (pageIndex - 1) * pageSize, pageIndex * pageSize);
return(conn.Query <Model.CMS.Article, Model.CMS.Column, Model.CMS.Article>(query, (article, column) =>
{
if (column != null)
{
article.Column = column;
}
return article;
}, new { Title = searchTitle }, null, "Column_Id", null, null).ToList());
}
}
public int InsertForInt(Model.CMS.Article entity)
{
using (IDbConnection conn = SqlString.GetSQLiteConnection())
{
int i = conn.Query <int>("INSERT INTO cms_article(ColumnId,Tags,Tagids,ArticleContent,Title,CreateTime,LastTime,Browse,Author,IsTop,IsShow)" +
"VALUES(@ColumnId,@Tags,@TagIds,@ArticleContent,@Title,@CreateTime,@LastTime,@Browse,@Author,@IsTop,@IsShow);select last_insert_rowid() newid;", entity).SingleOrDefault();
return(i);
}
}
public int InsertForInt(Model.CMS.Article entity)
{
using (IDbConnection conn = SqlString.GetMySqlConnection())
{
int i = conn.Query <int>("INSERT INTO cms_article(ColumnId,Tags,Tagids,ArticleContent,Title,CreateTime,LastTime,Browse,Author,ImgUrl,Digest,SEO_Title,SEO_Keywords,SEO_DES,IsTop,IsShow,Source,SourceLink,Praise)" +
"VALUES(@ColumnId,@Tags,@TagIds,@ArticleContent,@Title,@CreateTime,@LastTime,@Browse,@Author,@ImgUrl,@Digest,@SEO_Title,@SEO_Keywords,@SEO_DES,@IsTop,@IsShow,@Source,@SourceLink,@Praise);select last_insert_rowid() newid;", entity).SingleOrDefault();
return(i);
}
}
public ActionResult Detail(int?id)
{
Model.CMS.Article article = articleServer.Find(id.Value);
if (article.IsShow)
{
ViewData["DetailInfo"] = article;
return(View());
}
else
{
return(View("/Error/Error"));
}
}
public bool Insert(Model.CMS.Article entity)
{
using (IDbConnection conn = SqlString.GetMySqlConnection())
{
int i = conn.Execute("INSERT INTO cms_article(ColumnId,Tags,ArticleContent,Title,CreateTime,LastTime,Browse,Author,IsTop,IsShow)" +
"VALUES(@ColumnId,@Tags,@ArticleContent,@Title,@CreateTime,@LastTime,@Browse,@Author,@IsTop,@IsShow)", entity);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
}
public bool Insert(Model.CMS.Article entity)
{
using (IDbConnection conn = SqlString.GetMySqlConnection())
{
int i = conn.Execute("INSERT INTO cms_article(ColumnId,Tags,ArticleContent,Title,CreateTime,LastTime,Browse,Author,ImgUrl,Digest,SEO_Title,SEO_Keywords,SEO_DES,IsTop,IsShow,Source,SourceLink,Praise)" +
"VALUES(@ColumnId,@Tags,@ArticleContent,@Title,@CreateTime,@LastTime,@Browse,@Author,@ImgUrl,@Digest,@SEO_Title,@SEO_Keywords,@SEO_DES,@IsTop,@IsShow,@Source,@SourceLink,@Praise)", entity);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
}
public System.Collections.Generic.List <Model.CMS.Article> ToPagedList(int pageIndex, int pageSize, string where, string keySelector, out int count)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
string sql = "select a.*,b.* from cms_article as a left join cms_column as b on a.ColumnId=b.Column_Id";
string sql01 = "select count(Article_Id) from cms_article " + where;
count = conn.Query <int>(sql01).SingleOrDefault();
Model.CMS.Article articleTemp = new Model.CMS.Article();
//string query = "select * from(select ROW_NUMBER() OVER(ORDER BY "+ keySelector + " ) AS RowNumber, *from (" + sql + ")as c " + where + ") T where RowNumber BETWEEN "+ ((pageIndex - 1) * pageSize+1) + " and " + pageIndex*pageSize;
string query = "select top " + pageSize + " o.* from (select row_number() over(order by " + keySelector + ") as rownumber,* from(" + sql + ") as oo " + where + ") as o where rownumber>" + (pageIndex - 1) * pageSize;
return(conn.Query <Model.CMS.Article, Model.CMS.Column, Model.CMS.Article>(query, (article, column) =>
{
if (column != null)
{
article.Column = column;
}
return article;
}, null, null, "Column_Id", null, null).ToList());
}
}
public System.Collections.Generic.List <Model.CMS.Article> ToPagedList(int pageIndex, int pageSize, string where, string keySelector, out int count)
{
using (IDbConnection conn = SqlString.GetSQLiteConnection())
{
string sql = "select a.*,b.* from cms_article as a left join cms_column as b on a.ColumnId=b.Column_Id";
string sql01 = "select count(*) from cms_article " + where;
count = conn.Query <int>(sql01).SingleOrDefault();
Model.CMS.Article articleTemp = new Model.CMS.Article();
string query = "SELECT * from (" + sql + ")as c " + where + " ORDER BY " + keySelector + " LIMIT " + (pageIndex - 1) * pageSize + "," + pageSize;
return(conn.Query <Model.CMS.Article, Model.CMS.Column, Model.CMS.Article>(query, (article, column) =>
{
if (column != null)
{
article.Column = column;
}
return article;
}, null, null, "Column_Id", null, null).ToList());
}
}
public System.Collections.Generic.List <Model.CMS.Article> ToPagedList(int pageIndex, int pageSize, string where, string keySelector, out int count)
{
using (IDbConnection conn = SqlString.GetMySqlConnection())
{
string sql = "select a.*,b.* from cms_article as a left join cms_column as b on a.ColumnId=b.Column_Id";
string sql01 = "select count(*) from cms_article " + where;
count = conn.Query <int>(sql01).SingleOrDefault();
Model.CMS.Article articleTemp = new Model.CMS.Article();
string query = string.Format(@"SELECT * from ({0})as c {1} ORDER BY {2} LIMIT {3},{4} ", sql, where, keySelector, (pageIndex - 1) * pageSize, pageSize * pageSize);
return(conn.Query <Model.CMS.Article, Model.CMS.Column, Model.CMS.Article>(query, (article, column) =>
{
if (column != null)
{
article.Column = column;
}
return article;
}, null, null, "Column_Id", null, null).ToList());
}
}
public List <Model.CMS.Comment> ToPagedList(int pageIndex, int pageSize, string keySelector, out int count)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
string sql01 = "select count(Comment_Id) from cms_comment";
count = conn.Query <int>(sql01).SingleOrDefault();
Model.CMS.Article articleTemp = new Model.CMS.Article();
string sql = "select [Comment_Id],[ArticleId],[Content],[Nickname],[Email],[Article_Id],[ColumnId],[Tagids],[Tags],[ArticleContent],[Title],b.[CreateTime],[LastTime],[Browse],[Author],[IsTop],[IsShow] from cms_comment as a left join cms_article as b on a.ArticleId=b.Article_Id";
string query = "select top " + pageSize + " o.* from (select row_number() over(order by " + keySelector + ") as rownumber,* from(" + sql + ") as oo ) as o where rownumber>" + (pageIndex - 1) * pageSize;
return(conn.Query <Model.CMS.Comment, Model.CMS.Article, Model.CMS.Comment>(query,
(comment, article) =>
{
if (comment != null)
{
comment.Article = article;
}
return comment;
}, null, null, "Article_Id", null, null).ToList());
}
}
public System.Collections.Generic.List <Model.CMS.Article> ToSearchList(int pageIndex, int pageSize, string searchTitle, int sort, out int count)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
//sqlite使用||链接字符串
string sql01 = "select count(Article_Id) from cms_article where Title like '%'+@Title+'%'";
count = conn.Query <int>(sql01, new { Title = searchTitle }).SingleOrDefault();
Model.CMS.Article articleTemp = new Model.CMS.Article();
string sql = "select a.*,b.* from cms_article as a left join cms_column as b on a.ColumnId=b.Column_Id";
//string sort_str = "DESC";
//if (sort == 1) { sort_str = "DESC"; } else if (sort == 2) { sort_str = "ASC"; }
string query = "select top " + pageSize + " o.* from (select row_number() over(order by CreateTime) as rownumber,* from(" + sql + ") as oo) as o where rownumber>" + (pageIndex - 1) * pageSize;
return(conn.Query <Model.CMS.Article, Model.CMS.Column, Model.CMS.Article>(query, (article, column) =>
{
if (column != null)
{
article.Column = column;
}
return article;
}, new { Title = searchTitle }, null, "Column_Id", null, null).ToList());
}
}
public List <Model.CMS.Comment> ToPagedList(int pageIndex, int pageSize, string keySelector, out int count)
{
using (IDbConnection conn = SqlString.GetSQLiteConnection())
{
string sql01 = "select count(Comment_Id) from cms_comment";
count = conn.Query <int>(sql01).SingleOrDefault();
Model.CMS.Article articleTemp = new Model.CMS.Article();
string sql = "select a.*,b.* from cms_comment as a left join cms_article as b on a.ArticleId=b.Article_Id";
string query = "SELECT * from (" + sql + ")as c ORDER BY CreateTime " +
" LIMIT " + (pageIndex - 1) * pageSize + "," + pageSize;
return(conn.Query <Model.CMS.Comment, Model.CMS.Article, Model.CMS.Comment>(query,
(comment, article) =>
{
if (comment != null)
{
comment.Article = article;
}
return comment;
}, null, null, "Article_Id", null, null).ToList());
}
}
public List <Model.CMS.Comment> ToPagedList(int pageIndex, int pageSize, string keySelector, out int count)
{
using (IDbConnection conn = SqlString.GetMySqlConnection())
{
//mysql 出现Duplicate column name 的时候可以取别名，然后model映射的是取别名,Comment的CreateTime别名有_CreateTime的别名
string sql01 = "select count(Comment_Id) from cms_comment";
count = conn.Query <int>(sql01).SingleOrDefault();
Model.CMS.Article articleTemp = new Model.CMS.Article();
string sql = "select a.Comment_Id,a.ArticleId,a.Content ,a.CreateTime as _CreateTime,a.Email,a.Nickname,a.VoteFavour,a.VoteOppose,b.* from cms_comment as a left join cms_article as b on a.ArticleId=b.Article_Id";
string query = "SELECT * from (" + sql + ")as c ORDER BY _CreateTime " +
" LIMIT " + (pageIndex - 1) * pageSize + "," + pageSize;
return(conn.Query <Model.CMS.Comment, Model.CMS.Article, Model.CMS.Comment>(query,
(comment, article) =>
{
if (comment != null)
{
comment.Article = article;
}
return comment;
}, null, null, "Article_Id", null, null).ToList());
}
}
public int InsertForInt(Model.CMS.Article entity)
{
return(0);
}
