public LinkInfo GetById(object Id)
{
string cmdText = string.Format("select * from [{0}links] where [linkid]=@linkid ", ConfigHelper.Tableprefix);
using(var conn = new DapperHelper().OpenConnection())
{
return conn.Query<LinkInfo>(cmdText, new {linkid=(int)Id }).First();
}
}
/// <summary>
/// 获取评论
/// </summary>
/// <param name="categoryId"></param>
/// <returns></returns>
public CommentInfo GetById(object id)
{
string cmdText = string.Format("select * from [{0}comments] where [commentId] = @commentId", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
var list = conn.Query<CommentInfo>(cmdText, new { commentId = (int)id });
return list.ToList().Count > 0 ? list.ToList()[0] : null;
}
}
/// <summary>
/// 获取用户
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public UserInfo GetById(object Id)
{
string cmdText = string.Format("select * from [{0}users] where [UserId] = @userId ", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
var list = conn.Query<UserInfo>(cmdText, new { userId = (int)Id });
return list.ToList().Count > 0 ? list.First() : null;
}
}
public TagInfo GetTagByPageName(string pagename)
{
string cmdText = string.Format("select * from [{0}category] where [PageName] = @PageName", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
var list = conn.Query<TagInfo>(cmdText, new { PageName = pagename });
return list.ToList().Count > 0 ? list.ToList()[0] : null;
}
}
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="categoryId"></param>
/// <returns></returns>
public CategoryInfo GetById(object id)
{
if ((int)id <= 0)
{
return null;
}
string cmdText = string.Format("select * from [{0}category] where [categoryid] = @categoryid", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
var list = conn.Query<CategoryInfo>(cmdText, new { categoryid = (int)id });
return list.ToList().Count > 0 ? list.First() : null;
}
}
/// <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>
/// <returns></returns>
public virtual void CheckPageName(PostInfo post)
{
if (string.IsNullOrEmpty(post.PageName))
{
return;
}
while (true)
{
string cmdText = post.PostId == 0 ? string.Format("select count(1) from [{1}posts] where [pagename]='{0}' ", post.PageName, ConfigHelper.Tableprefix) : string.Format("select count(1) from [{2}posts] where [pagename]='{0}' and [postid]<>{1}", post.PageName, post.PostId, ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
int r = conn.Query<int>(cmdText,null).First();
if (r == 0)
{
return;
}
}
post.PageName += "-2";
}
}
public void TestMethod1()
{
//var connection = new DapperHelper().OpenConnection();
//var sql = @"select q.*,t.typeid,t.typename from ems_Question q,ems_Questiontype t where t.typeid=q.typeid";
//var data = connection.Query<Question, QuestionType, Question>(sql, (quest, type) =>
//{
// quest.QuestionType = type;
// return quest;
//}, splitOn: "typeid,qstid");
//foreach (Question q in data)
//{
// Console.WriteLine(q.QstDescription + " " + q.QuestionType.TypeName);
//}
//connection.Close();
// string cmdText = string.Format("select p.*,u.UserId,u.UserName,u.NickName from [{0}posts] p,[{0}Users] u where p.UserId=u.UserId ", ConfigHelper.Tableprefix);
// using (var conn = new DapperHelper().OpenConnection(ConnectionString))
string cmdText = string.Format("select p.*,u.UserId,u.UserName,c.CategoryId,c.CateName from [{0}posts] p,[{0}Users] u,[{0}category] c where p.PostId=@PostId and p.UserId=u.UserId and p.CategoryId=c.CategoryId", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection(ConnectionString))
{
// var list = conn.Query<PostInfo>(cmdText, new { PostId = (int)id });
// return list.Any() ? list.ToList()[0] : null;
var list = conn.Query<PostInfo, UserInfo, CategoryInfo, PostInfo>(cmdText, (post, user, cate) =>
{
post.Author = user;
post.Category = cate;
return post;
}, new { PostId = 384 }, splitOn: "UserId,CategoryId");
foreach (var postInfo in list)
{
Console.WriteLine(postInfo.PostId + " " + postInfo.Title);
}
}
}
/// <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="postId"></param>
/// <param name="rowCount"></param>
/// <returns></returns>
public virtual List<PostInfo> GetPostListByRelated(int postId, int rowCount)
{
string tags;
PostInfo post = GetById(postId);
if (post != null && post.Tag.Length > 0)
{
tags = post.Tag;
tags = tags.Replace("}", "},");
string[] idList = tags.Split(',');
string where = idList.Where(tagId => !string.IsNullOrEmpty(tagId)).Aggregate(" (", (current, tagId) => current + string.Format(" [tags] like '%{0}%' or ", tagId));
where += " 1=2 ) and [status]=1 and [postid]<>" + postId;
string cmdText = string.Format("select * from [{2}posts] where {1} order by [postid] desc limit {0}", rowCount, where, ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
var list = conn.Query<PostInfo>(cmdText, null);
return list.ToList();
}
}
return new List<PostInfo>();
}
/// <summary>
/// 获取分页列表
/// </summary>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="recordCount"></param>
/// <param name="categoryId"></param>
/// <param name="tagId"></param>
/// <param name="userId"></param>
/// <param name="recommend"></param>
/// <param name="status"></param>
/// <param name="topstatus"></param>
/// <param name="PostStatus"></param>
/// <param name="begindate"></param>
/// <param name="enddate"></param>
/// <param name="keyword"></param>
/// <returns></returns>
public virtual List<PostInfo> GetPostList(int pageSize, int pageIndex, out int recordCount, string categoryId, int tagId, int userId, int recommend, int status, int topstatus, int PostStatus, string begindate, string enddate, string keyword)
{
string condition = " 1=1 ";
if (categoryId != ""&&categoryId!="-1")
{
condition += " and categoryId in(" + categoryId+")";
}
if (tagId != -1)
{
condition += " and tag like '%{" + tagId + "}%'";
}
if (userId != -1)
{
condition += " and userid=" + userId;
}
if (recommend != -1)
{
condition += " and recommend=" + recommend;
}
if (status != -1)
{
condition += " and status=" + status;
}
if (topstatus != -1)
{
condition += " and topstatus=" + topstatus;
}
if (PostStatus != -1)
{
condition += " and PostStatus=" + PostStatus;
}
if (!string.IsNullOrEmpty(begindate))
{
condition += " and PostTime>=#" + begindate + "#";
}
if (!string.IsNullOrEmpty(enddate))
{
condition += " and PostTime<#" + enddate + "#";
}
if (!string.IsNullOrEmpty(keyword))
{
condition += string.Format(" and (summary like '%{0}%' or title like '%{0}%' )", keyword);
}
using (var conn = new DapperHelper().OpenConnection())
{
string cmdTotalRecord = "select count(1) from [" + ConfigHelper.Tableprefix + "posts] where " + condition;
recordCount = conn.Query<int>(cmdTotalRecord,null).First();
string cmdText = new DapperHelper().GetPageSql("[" + ConfigHelper.Tableprefix + "Posts]", "[PostId]", "*", pageSize, pageIndex, 1, condition);
var list = conn.Query<PostInfo>(cmdText, null);
return list.ToList();
}
}
/// <summary>
/// 根据pagename获取实体
/// </summary>
/// <param name="pagename"></param>
/// <returns></returns>
public virtual PostInfo GetPostbyPageName(string pagename)
{
string cmdText = string.Format("select * from [{0}posts] where [pagename] = @_pagename limit 1", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
var list = conn.Query<PostInfo>(cmdText, new { _pagename = pagename });
return list.ToList().Count > 0 ? list.ToList()[0] : null;
}
}
/// <summary>
/// 统计评论
/// </summary>
/// <param name="userId"></param>
/// <param name="postId"></param>
/// <param name="incChild"></param>
/// <returns></returns>
public int GetCommentCount(int userId, int postId, bool incChild)
{
string condition = " 1=1 ";
if (userId != -1)
{
condition += " and [userId] = " + userId;
}
if (postId != -1)
{
condition += " and [postId] = " + postId;
}
if (incChild == false)
{
condition += " and [parentid]=0";
}
using (var conn = new DapperHelper().OpenConnection())
{
string cmdTotalRecord = "select count(1) from [" + ConfigHelper.Tableprefix + "comments] where " + condition;
return conn.Query<int>(cmdTotalRecord, null).First();
}
}
/// <summary>
/// 根据用户名和密码获取用户
/// </summary>
/// <param name="userName"></param>
/// <param name="password"></param>
/// <returns></returns>
public UserInfo GetUser(string userName, string password)
{
string cmdText = string.Format("select * from [{0}users] where [userName] = @userName and [password]=@password ", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
var list =conn.Query<UserInfo>(cmdText, new { userName = userName,password = password });
return list.Count()==0?null:list.First();
}
}
/// <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="postid"></param>
/// <returns></returns>
public virtual List<CommentInfo> GetCommentsByPost(int postid)
{
string cmdText = string.Format("select * from [{0}comments] where postid=@PostId order by createtime desc", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
var list = conn.Query<CommentInfo>(cmdText, new {PostId = postid});
return list!=null ? list.ToList() : null;
}
}
/// <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();
}
}
/// <summary>
/// 获取实体
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public virtual PostInfo GetById(object id)
{
//string cmdText = string.Format("select p.PostId as Id,p.Title,p.CategoryId,p.TitlePic,p.PageName,u.UserId,u.UserName,u.Role,u.NickName from [{0}posts] p,[{0}Users] u where p.UserId=u.UserId limit 1", ConfigHelper.Tableprefix);
//using (var conn = new DapperHelper().OpenConnection())
//{
// // var list = conn.Query<PostInfo>(cmdText, new { PostId = (int)id });
// // return list.Any() ? list.ToList()[0] : null;
// var list = conn.Query<PostInfo, UserInfo, PostInfo>(cmdText, (post, user) =>
// {
// post.Author = user;
// return post;
// }, splitOn: "UserId");
// return list.Any() ? list.ToList()[0] : null;
//}
string cmdText = string.Format("select p.*,u.*,c.* from [{0}posts] p,[{0}Users] u,[{0}category] c where p.PostId=@PostId and p.UserId=u.UserId and p.CategoryId=c.CategoryId", ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
var list = conn.Query<PostInfo, UserInfo, CategoryInfo, PostInfo>(cmdText, (post, user, cate) =>
{
post.Author = user;
post.Category = cate;
return post;
}, new { PostId = (int)id }, splitOn: "UserId,CategoryId");
return list.Any() ? list.ToList()[0] : null;
}
}
/// <summary>
/// 检查别名是否重复
/// </summary>
/// <param name="cate"></param>
/// <returns></returns>
private void CheckPageName(TagInfo cate)
{
while (true)
{
string cmdText = cate.TagId == 0 ? string.Format("select count(1) from [{2}category] where [PageName]='{0}' and [type]={1}", cate.PageName, (int)CategoryType.Tag, ConfigHelper.Tableprefix) : string.Format("select count(1) from [{3}category] where [PageName]='{0}' and [type]={1} and [categoryid]<>{2}", cate.PageName, (int)CategoryType.Tag, cate.TagId, ConfigHelper.Tableprefix);
using (var conn = new DapperHelper().OpenConnection())
{
int r = conn.Query<int>(cmdText, null).First();
if (r == 0)
{
return;
}
}
cate.PageName += "-2";
}
}
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>
/// 获取文章归档统计
/// </summary>
/// <returns></returns>
public virtual List<ArchiveInfo> GetArchive()
{
string cmdText = string.Format("select format(PostTime, 'yyyymm') as [date] , count(*) as [count] from [{0}posts] where [status]=1 and [PostStatus]=0 group by format(PostTime, 'yyyymm') order by format(PostTime, 'yyyymm') desc", ConfigHelper.Tableprefix);
using(var conn = new DapperHelper().OpenConnection())
{
return conn.Query<ArchiveInfo>(cmdText,null).ToList();
}
}
/// <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="userId"></param>
/// <param name="postId"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="totalRecord"></param>
/// <param name="order"></param>
/// <param name="parentId"></param>
/// <param name="approved"></param>
/// <param name="emailNotify"></param>
/// <param name="keyword"></param>
/// <returns></returns>
public List<CommentInfo> GetCommentList(int pageSize, int pageIndex, out int totalRecord, int order, int userId, int postId, int parentId, int approved, int emailNotify, string keyword)
{
string condition = " 1=1 ";// "[ParentId]=0 and [PostId]=" + postId;
if (userId != -1)
{
condition += " and userid=" + userId;
}
if (postId != -1)
{
condition += " and postId=" + postId;
}
if (parentId != -1)
{
condition += " and parentId=" + parentId;
}
if (approved != -1)
{
condition += " and approved=" + approved;
}
if (emailNotify != -1)
{
condition += " and emailNotify=" + emailNotify;
}
if (!string.IsNullOrEmpty(keyword))
{
condition += string.Format(" and (content like '%{0}%' or author like '%{0}%' or ipaddress like '%{0}%' or email like '%{0}%' or siteurl like '%{0}%' )", keyword);
}
using (var conn = new DapperHelper().OpenConnection())
{
string cmdTotalRecord = "select count(1) from [" + ConfigHelper.Tableprefix + "comments] where " + condition;
totalRecord = conn.Query<int>(cmdTotalRecord, null).First();
string cmdText = new DapperHelper().GetPageSql("[" + ConfigHelper.Tableprefix + "comments]", "[CommentId]", "*", pageSize, pageIndex, 1, condition);
var list = conn.Query<CommentInfo>(cmdText, null);
return list.ToList();
}
}
/// <summary>
/// 根据用户名获取用户
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public UserInfo GetUserByName(string userName)
{
string cmdText = string.Format("select * from [{0}users] where [userName] = @userName ", ConfigHelper.Tableprefix);
using(var conn = new DapperHelper().OpenConnection())
{
var list = conn.Query<UserInfo>(cmdText, new {userName = userName});
if (list.Any())
{
return list.First();
}
return null;
}
}