/// <summary>
/// 删除
/// </summary>
/// <param name="commentId"></param>
/// <returns></returns>
public int Delete(CommentInfo comment)
{
string cmdText = string.Format("delete from [{0}comments] where [commentId] = @commentId", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
return conn.Execute(cmdText, new { commentId = comment.CommentId });
}
}
/// <summary>
/// 根据日志ID删除评论
/// </summary>
/// <param name="postId">日志ID</param>
/// <returns></returns>
public int DeleteCommentByPost(int postId)
{
string cmdText = string.Format("delete from [{0}comments] where [postId] = @postId", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
return conn.Execute(cmdText, new { postId = postId });
}
}
/// <summary>
/// 是否存在此用户名
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public bool ExistsUserName(string userName)
{
string cmdText = string.Format("select count(1) from [{0}users] where [userName] = @userName ", ConfigHelper.Tableprefix);
using(var conn = new DapperHelper().OpenConnection())
{
return conn.Execute(cmdText, new {userName = userName })>0;
}
}
/// <summary>
/// 删除
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public int Delete(UserInfo userinfo)
{
string cmdText = string.Format("delete from [{0}users] where [userid] = @userid", ConfigHelper.Tableprefix);
using(var conn = new DapperHelper().OpenConnection())
{
return conn.Execute(cmdText, new {userid = userinfo.UserId });
}
}
public int Delete(TagInfo tag)
{
string cmdText = string.Format("delete from [{0}category] where [categoryid] = @categoryid", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
return conn.Execute(cmdText, new { categoryid = tag.TagId });
}
}
/// <summary>
/// delete link
/// </summary>
/// <param name="link"></param>
/// <returns></returns>
public int Delete(LinkInfo link)
{
string cmdText = string.Format("delete from [{0}links] where [linkid] = @linkid", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
return conn.Execute(cmdText, new { categoryid = link.LinkId });
}
}
/// <summary>
/// 删除文章
/// </summary>
/// <param name="post"></param>
public virtual int Delete(PostInfo post)
{
PostInfo oldPost = GetById(post.PostId);
if (oldPost == null) throw new Exception("文章不存在");
string cmdText = string.Format("delete from [{0}posts] where [PostId] = @PostId", ConfigHelper.Tableprefix);
using(var conn = new DapperHelper().OpenConnection())
{
return conn.Execute(cmdText, new {PostId = post.PostId });
}
}
public void TestMethod2()
{
string cmdText = "select p.* from [jq_posts] p where postid=340";
using (var conn = new DapperHelper().OpenConnection(ConnectionString))
{
var post = conn.Query<PostInfo>(cmdText).First();
conn.Execute("update jq_posts set updatetime =@updatetime where postid=340",new{updatetime = DateTime.Now.ToString()});
}
}
/// <summary>
/// 更新统计数据
/// </summary>
/// <param name="statistics"></param>
/// <returns></returns>
public bool UpdateStatistics(StatisticsInfo statistics)
{
string cmdText = string.Format("update [{0}sites] set PostCount=@PostCount,CommentCount=@CommentCount,VisitCount=@VisitCount,TagCount=@TagCount", ConfigHelper.Tableprefix);
using(var conn = new DapperHelper().OpenConnection())
{
return conn.Execute(cmdText, new
{
PostCount = statistics.PostCount,
CommentCount = statistics.CommentCount,
VisitCount = statistics.VisitCount,
TagCount = statistics.TagCount
})>0;
}
}
/// <summary>
/// 获取统计数据
/// </summary>
/// <returns></returns>
public StatisticsInfo GetStatistics()
{
string cmdText = string.Format("select * from [{0}sites] limit 1", ConfigHelper.Tableprefix);
string insertText = string.Format("insert into [{0}sites] ([PostCount],[CommentCount],[VisitCount],[TagCount]) values ( '0','0','0','0')", ConfigHelper.Tableprefix);
using(var conn = new DapperHelper().OpenConnection())
{
var list = conn.Query<StatisticsInfo>(cmdText,null).ToList();
if (list.Count == 0)
{
conn.Execute(insertText,null);
}
return list.Count > 0 ? list[0] : null;
}
}
/// <summary>
/// 更新访问量
/// </summary>
/// <param name="postId"></param>
/// <param name="addCount"></param>
/// <returns></returns>
public virtual int UpdatePostViewCount(int postId, int addCount)
{
string cmdText = string.Format("update [{0}posts] set [viewcount] = [viewcount] + @addcount where [postid]=@postid", ConfigHelper.Tableprefix);
using(var conn = new DapperHelper().OpenConnection())
{
return conn.Execute(cmdText, new { addcount =addCount,postid = postId });
}
}
/// <summary>
/// 更新文章
/// </summary>
/// <param name="post"></param>
public virtual int Update(PostInfo post)
{
string cmdText = string.Format(@"update [{0}posts] set
[CategoryId]=@CategoryId,
[TitlePic]=@TitlePic,
[Title]=@Title,
[Summary]=@Summary,
[PostContent]=@PostContent,
[PageName]=@PageName,
[UserId]=@UserId,
[CommentStatus]=@CommentStatus,
[CommentCount]=@CommentCount,
[ViewCount]=@ViewCount,
[Tag]=@Tag,
[UrlFormat]=@UrlFormat,
[Template]=@Template,
[Recommend]=@Recommend,
[Status]=@Status,
[TopStatus]=@TopStatus,
[HomeStatus]=@HomeStatus,
[PostTime]=@PostTime,
[UpdateTime]=@UpdateTime
where [PostId]=@PostId", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
return conn.Execute(cmdText, post);
}
}
/// <summary>
/// 新增文章
/// </summary>
/// <param name="post"></param>
/// <returns></returns>
public virtual int Insert(PostInfo post)
{
CheckPageName(post);
string cmdText = string.Format(@"insert into [{0}posts]
(
[CategoryId],[TitlePic],[Title],[Summary],[PostContent],[PageName],[UserId],[CommentStatus],[CommentCount],[ViewCount],[Tag],[UrlFormat],[Template],[Recommend],[Status],[TopStatus],[HomeStatus],[PostTime],[UpdateTime]
)
values
(
@CategoryId,@TitlePic,@Title,@Summary,@PostContent,@PageName,@UserId,@CommentStatus,@CommentCount,@ViewCount,@Tag,@UrlFormat,@Template,@Recommend,@Status,@TopStatus,@HomeStatus,@PostTime,@UpdateTime
)", ConfigHelper.Tableprefix);
using(var conn = new DapperHelper().OpenConnection())
{
conn.Execute(cmdText, post);
return conn.Query<int>(string.Format("select [PostId] from [{0}Posts] order by [PostId] desc limit 1", ConfigHelper.Tableprefix), null).First();
}
}
/// <summary>
/// 更新分类
/// </summary>
/// <param name="category"></param>
/// <returns></returns>
public virtual int Update(CategoryInfo category)
{
CheckPageName(category);
string cmdText = string.Format(@"update [{0}category] set
[ParentId]=@ParentId,
[CateName]=@CateName,
[PageName]=@PageName,
[Description]=@Description,
[SortNum]=@SortNum,
[PostCount]=@PostCount,
[CreateTime]=@CreateTime,
[Type]=@Type
where categoryid=@categoryid", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
return conn.Execute(cmdText, new
{
ParentId = category.ParentId,
CateName = category.CateName,
PageName = category.PageName,
Description = category.Description,
SortNum = category.SortNum,
PostCount = category.PostCount,
CreateTime = category.CreateTime,
Type = (int)CategoryType.Category,
categoryid = category.CategoryId
});
}
}
public int Update(TagInfo tag)
{
CheckPageName(tag);
string cmdText = string.Format(@"update [{0}category] set
[Type]=@Type,
[CateName]=@CateName,
[PageName]=@PageName,
[Description]=@Description,
[SortNum]=@SortNum,
[PostCount]=@PostCount,
[CreateTime]=@CreateTime
where categoryid=@categoryid", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
return conn.Execute(cmdText, new
{
Type = (int)CategoryType.Tag,
CateName = tag.CateName,
PageName = tag.PageName,
Description = tag.Description,
SortNum = tag.SortNum,
PostCount = tag.PostCount,
CreateTime = tag.CreateTime,
categoryid = tag.TagId
});
}
}
/// <summary>
/// 更新
/// </summary>
/// <param name="comment"></param>
/// <returns></returns>
public int Update(CommentInfo comment)
{
string cmdText = string.Format(@"update [{0}comments] set
PostId=@PostId,
ParentId=@ParentId,
UserId=@UserId,
Author=@Author,
Email=@Email,
AuthorUrl=@AuthorUrl,
Contents=@Contents,
EmailNotify=@EmailNotify,
IpAddress=@IpAddress,
CreateTime=@CreateTime,
Approved=@Approved
where CommentId=@CommentId ", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
return conn.Execute(cmdText, comment);
}
}
/// <summary>
/// 添加
/// </summary>
/// <param name="comment"></param>
/// <returns></returns>
public int Insert(CommentInfo comment)
{
string cmdText = string.Format(@"insert into [{0}comments](
PostId, ParentId,UserId,Author,Email,AuthorUrl,Contents,EmailNotify,IpAddress,CreateTime,Approved)
values (
@PostId, @ParentId,@UserId,@Author,@Email,@AuthorUrl,@Contents,@EmailNotify,@IpAddress,@CreateTime,@Approved)", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
conn.Execute(cmdText, comment);
return conn.Query<int>(string.Format("select [CommentId] from [{0}comments] order by [CommentId] desc limit 1", ConfigHelper.Tableprefix), null).First();
}
}
/// <summary>
/// 更新
/// </summary>
/// <param name="userinfo"></param>
/// <returns></returns>
public int Update(UserInfo userinfo)
{
string cmdText = string.Format(@"update [{0}users] set
[Role]=@Role,
[NickName]=@NickName,
[Password]=@Password,
[Email]=@Email,
[SiteUrl]=@SiteUrl,
[AvatarUrl]=@AvatarUrl,
[Description]=@Description,
[SortNum]=@SortNum,
[Status]=@Status,
[PostCount]=@PostCount,
[CommentCount]=@CommentCount,
[CreateTime]=@CreateTime
where UserId=@UserId", ConfigHelper.Tableprefix);
using(var conn = new DapperHelper().OpenConnection())
{
return conn.Execute(cmdText, new
{
Role = userinfo.Role,
NickName = userinfo.NickName,
Password = userinfo.Password,
Email = userinfo.Email,
SiteUrl = userinfo.SiteUrl,
AvatarUrl = userinfo.AvatarUrl,
Description = userinfo.Description,
SortNum = userinfo.SortNum,
Status = userinfo.Status,
PostCount = userinfo.PostCount,
CommentCount = userinfo.CommentCount,
CreateTime = userinfo.CreateTime,
UserId = userinfo.UserId
});
}
}
/// <summary>
/// update link
/// </summary>
/// <param name="link"></param>
/// <returns></returns>
public int Update(LinkInfo link)
{
string cmdText = string.Format(@"update [{0}links] set
[type]=@type,
[linkname]=@linkname,
[linkurl]=@linkurl,
[position]=@position,
[target]=@target,
[description]=@description,
[sortnum]=@sortnum,
[status]=@status,
[createtime]=@createtime
where linkid=@linkid", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
return conn.Execute(cmdText, new
{
Type = link.Type,
LinkName = link.LinkName,
LinkUrl = link.LinkUrl,
Postion = link.Position,
Target = link.Target,
Description = link.Description,
SortNum = link.SortNum,
Status = link.Status,
CreateTime = link.CreateTime,
Linkid = link.LinkId
});
}
}
public int Insert(TagInfo tag)
{
CheckPageName(tag);
string cmdText = string.Format(@"insert into [{0}category]
(
[Type],[ParentId],[CateName],[PageName],[Description],[SortNum],[PostCount],[CreateTime]
)
values
(
@Type,@ParentId,@CateName,@PageName,@Description,@SortNum,@PostCount,@CreateTime
)", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
conn.Execute(cmdText, new
{
Type = (int)CategoryType.Tag,
ParentId = 0,
CateName = tag.CateName,
PageName = tag.PageName,
Description = tag.Description,
SortNum = tag.SortNum,
PostCount = tag.PostCount,
CreateTime = tag.CreateTime
});
return conn.Query<int>(string.Format("select [categoryid] from [{0}category] order by [categoryid] desc limit 1", ConfigHelper.Tableprefix), null).First();
}
}
/// <summary>
/// insert link
/// </summary>
/// <param name="link"></param>
/// <returns></returns>
public int Insert(LinkInfo link)
{
string cmdText = string.Format(@"insert into [{0}links]
(
[type],[linkname],[linkurl],[position],[target],[description],[sortnum],[status],[createtime]
)
values
(
@type,@linkname,@linkurl,@position,@target,@description,@sortnum,@status,@createtime
)", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
conn.Execute(cmdText, new
{
Type = link.Type,
LinkName = link.LinkName,
LinkUrl = link.LinkUrl,
Postion = link.Position,
Target = link.Target,
Description = link.Description,
SortNum = link.SortNum,
Status = link.Status,
CreateTime = link.CreateTime
});
return conn.Query<int>(string.Format("select [linkid] from [{0}links] order by [linkid] desc limit 1", ConfigHelper.Tableprefix), null).First();
}
}
/// <summary>
/// 添加
/// </summary>
/// <param name="userinfo"></param>
/// <returns></returns>
public int Insert(UserInfo userinfo)
{
string cmdText = string.Format(@" insert into [{0}users](
[Role],[UserName],[NickName],[Password],[Email],[SiteUrl],[AvatarUrl],[Description],[sortnum],[Status],[PostCount],[CommentCount],[CreateTime])
values (
@Role,@UserName,@NickName,@Password,@Email,@SiteUrl,@AvatarUrl,@Description,@SortNum,@Status, @PostCount,@CommentCount,@CreateTime )", ConfigHelper.Tableprefix);
using(var conn = new DapperHelper().OpenConnection())
{
conn.Execute(cmdText, new {
Role = userinfo.Role,
UserName = userinfo.UserName,
NickName = userinfo.NickName,
Password = userinfo.Password,
Email = userinfo.Email,
SiteUrl = userinfo.SiteUrl,
AvatarUrl = userinfo.AvatarUrl,
Description = userinfo.Description,
SortNum = userinfo.SortNum,
Status = userinfo.Status,
PostCount = userinfo.PostCount,
CommentCount = userinfo.CommentCount,
CreateTime = userinfo.CreateTime
});
return conn.Query<int>(string.Format("select [UserId] from [{0}users] order by [UserId] desc limit 1", ConfigHelper.Tableprefix), null).First();
}
}
