public void Update(Model.CMS.TempPage entity)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
conn.Execute("UPDATE cms_temppage SET TempName=@TempName,TempByname=@TempByname,TempParameter=@TempParameter,TempPath=@TempPath,TempContent=@TempContent,CreateTime=@CreateTime,LastTime=@LastTime WHERE TempPage_Id =@TempPage_Id", entity);
}
}
public void Delete(string ids)
{
using (IDbConnection conn = SqlString.GetSqlConnection(SqlString.MSSQLString))
{
conn.Execute(string.Format("DELETE FROM sys_role WHERE Role_Id in ({0})", ids));
}
}
public void Update(Model.CMS.Article entity)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
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.Column entity)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
conn.Execute("UPDATE cms_column SET Sort=@Sort,ColumnName=@ColumnName,Pid=@Pid,Lv=@Lv WHERE Column_Id =@Column_Id", entity);
}
}
public void DeteteToMap(int articleId)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
conn.Execute("DELETE FROM cms_tagmap WHERE m_ArticleId=@m_ArticleId", new { m_ArticleId = articleId });
}
}
public void Update(Model.Sys.Role entity)
{
using (IDbConnection conn = SqlString.GetSqlConnection(SqlString.MSSQLString))
{
conn.Execute("UPDATE sys_role SET RoleName=@RoleName,ActionIds=@ActionIds WHERE Role_Id =@Role_Id", entity);
}
}
public void Delete(string ids)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
conn.Execute(string.Format("DELETE FROM cms_tag WHERE Tag_Id in ({0})", ids));
}
}
public void Update(Model.CMS.Comment entity)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
conn.Execute("UPDATE cms_comment SET ArticleId=@ArticleId,Content=@Content,CreateTime=@CreateTime,Nickname=@Nickname,Email=@Email,VoteFavour=@VoteFavour,VoteOppose=@VoteOppose WHERE Comment_Id =@Comment_Id", entity);
}
}
public bool InsertToMapBatch(int articleId, string tagIds)
{
int[] tags = Array.ConvertAll <string, int>(tagIds.Split(','), s => Convert.ToInt32(s));
string sql = "INSERT INTO cms_tagmap(m_TagId,m_ArticleId)VALUES";
for (int i = 0; i < tags.Length; i++)
{
if (i == 0)
{
sql += "(" + tags[i] + "," + articleId + ")";
}
else
{
sql += ",(" + tags[i] + "," + articleId + ")";
}
}
using (IDbConnection conn = SqlString.GetSqlConnection())
{
int i = conn.Execute(sql);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
}
public void Update(Model.Store.StoreInfo entity)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
conn.Execute("UPDATE store_info SET StoreName=@StoreName,StoreLogoPath=@StoreLogoPath,StoreDes=@StoreDes,StoreSwitch=@StoreSwitch,CreateTime=@CreateTime,LastTime=@LastTime WHERE Store_Id =@Store_Id", entity);
}
}
public void Delete(string ids)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
conn.Execute(string.Format("DELETE FROM store_info WHERE Store_Id in ({0})", ids));
}
}
public void UpdatePath(int id, string path)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
conn.Execute("UPDATE cms_staticpage SET PagePath=@path WHERE Page_Id =@id", new { id = id, path = path });
}
}
public void Update(Model.CMS.StaticPage entity)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
conn.Execute("UPDATE cms_staticpage SET PageName=@PageName,PageParameter=@PageParameter,CreateTime=@CreateTime,LastTime=@LastTime,TempPageId =@TempPageId WHERE Page_Id =@Page_Id", entity);
}
}
public List <Model.Sys.SysLog> ToPagedList(int pageIndex, int pageSize, string keySelector, out int count)
{
/*
* pageIndex:起始索引
* pageSize:每页显示的数量
* keySelector:排序的字段名
* sql:可以是简单的单表查询语句，也可以是复杂的多表联合查询语句
*/
using (IDbConnection conn = SqlString.GetSqlConnection(SqlString.MSSQLString))
{
string sql = "select a.*,b.* from sys_log as a left join sys_user as b on a.UserId=b.User_Id";
string sql01 = "select count(*) from sys_log";
count = conn.Query <int>(sql01).SingleOrDefault();
Model.Sys.User userTemp = new Model.Sys.User();
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.Sys.SysLog, Model.Sys.User, Model.Sys.SysLog>(query, (log, user) =>
{
if (user != null)
{
log.User = user;
}
return log;
}, null, null, "User_Id", null, null).ToList());
}
}
public List <Model.CMS.Tag> GetAll()
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
return(conn.Query <Model.CMS.Tag>("select * from cms_tag").ToList());
}
}
public void Delete(string ids)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
conn.Execute(string.Format("DELETE FROM cms_column WHERE Pid in ({0});DELETE FROM cms_column WHERE Column_Id in ({0});", ids));
}
}
public List <Model.Store.StoreInfo> GetAll()
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
return(conn.Query <Model.Store.StoreInfo>("select * from store_info").ToList());
}
}
public Model.Sys.Role Find(int id)
{
using (IDbConnection conn = SqlString.GetSqlConnection(SqlString.MSSQLString))
{
string query = "select a.* from sys_role as a WHERE a.Role_Id = @id";
return(conn.Query <Model.Sys.Role>(query).SingleOrDefault());
}
}
public void WriteLog(int uid, string info)
{
using (IDbConnection conn = SqlString.GetSqlConnection(SqlString.MSSQLString))
{
int i = conn.Execute("INSERT INTO sys_log(UserId,LogInfo,CreateTs)" +
"VALUES(@UserId,@LogInfo,@CreateTs)", new { UserId = uid, LogInfo = info, CreateTs = StringHelper.GetTimeStamp() });
}
}
public Model.CMS.Column Find(int id)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
string query = "select a.* from cms_column as a WHERE a.Column_Id = @id";
return(conn.Query <Model.CMS.Column>(query, new { id = id }).SingleOrDefault());
}
}
public Model.Store.StoreInfo Find(int id)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
string query = "select a.* from store_info as a WHERE a.Store_Id = @id";
return(conn.Query <Model.Store.StoreInfo>(query, new { id = id }).SingleOrDefault());
}
}
public Model.CMS.Tag Find(int id)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
string query = "select * from cms_tag as a WHERE a.Tag_Id = @id";
return(conn.Query <Model.CMS.Tag>(query, new { id = id }).SingleOrDefault());
}
}
public List <Model.Sys.Role> GetAll()
{
using (IDbConnection conn = SqlString.GetSqlConnection(SqlString.MSSQLString))
{
Model.Sys.Role roleTemp = new Model.Sys.Role();
return(conn.Query <Model.Sys.Role>("select a.* from sys_role as a ").ToList());
}
}
public void Update(Model.Sys.User entity)
{
using (IDbConnection conn = SqlString.GetSqlConnection(SqlString.MSSQLString))
{
conn.Execute("UPDATE sys_user SET RoleId=@RoleId,Name=@Name,Password=@Password,NickName=@NickName,Avatar=@Avatar,Email=@Email," +
"Phone=@Phone,Qq=@Qq,AddTime=@AddTime,LastLoginTime=@LastLoginTime,LastLoginIp=@LastLoginIp,IsSuperUser=@IsSuperUser WHERE User_Id =@User_Id", entity);
}
}
public void Update(Model.CMS.Tag entity)
{
entity.LastTime = DateTime.Now;
using (IDbConnection conn = SqlString.GetSqlConnection())
{
conn.Execute("UPDATE cms_tag SET TagName=@TagName,TagDes=@TagDes,LastTime=@LastTime WHERE Tag_Id =@Tag_Id", entity);
}
}
public List <Cactus.Model.CMS.Tag> FindArticleTags(int articleId)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
string query = "select b.* from cms_tagmap as a left JOIN cms_tag as b on a.m_TagId=b.Tag_Id where a.m_ArticleId=@m_ArticleId";
return(conn.Query <Model.CMS.Tag>(query, new { m_ArticleId = articleId }).ToList());
}
}
public bool InsertMap(int id, string Ip, long timestamp)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
int i = conn.Execute("INSERT INTO cms_commentmap(Ip,CreateTs,CommentId)" +
"VALUES(@Ip,@CreateTs,@CommentId);", new { Ip = Ip, CreateTs = timestamp, CommentId = id });
if (i > 0) { return true; } else { return false; }
}
}
public int InsertForInt(Model.CMS.Article entity)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
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)" +
"VALUES(@ColumnId,@Tags,@TagIds,@ArticleContent,@Title,@CreateTime,@LastTime,@Browse,@Author,@ImgUrl,@Digest,@SEO_Title,@SEO_Keywords,@SEO_DES,@IsTop,@IsShow,@Source,@SourceLink,@Praise);SELECT @@IDENTITY;", entity).SingleOrDefault();
return(i);
}
}
public bool isFind(int id, string Ip, long begin_timestamp, long end_timestamp)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
int i = conn.Query<int>("SELECT count(a.CommentId) FROM cms_commentmap as a WHERE a.Ip=@Ip and a.CommentId=@CommentId and CreateTs>=@begin_timestamp and CreateTs<=end_timestamp LIMIT 0, 1",
new { CommentId = id, Ip = Ip, begin_timestamp = begin_timestamp, end_timestamp = end_timestamp }).SingleOrDefault();
if (i > 0) { return true; } else { return false; }
}
}
public List <Model.CMS.Tag> ToPagedList(int pageIndex, int pageSize, string keySelector, out int count)
{
using (IDbConnection conn = SqlString.GetSqlConnection())
{
string sql01 = "select count(Tag_Id) from cms_tag";
count = conn.Query <int>(sql01).SingleOrDefault();
string sql = "select a.* from cms_tag as a";
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.Tag>(query).ToList());
}
}