public CommonResult Execute(AdminUserInitCommand command)
{
try
{
using (var conn = _dapperConnection.GetDataBaseConnection())
{
var count = conn.QuerySingle <int>("select count(1) from user_info");
if (count == 0)
{
conn.Execute("INSERT into user_info(user_name,user_email,password) VALUES(@userName, @userEmail, @password) ",
new { userName = command.UserName, userEmail = command.EMail, password = command.Password });
}
}
return(new CommonResult()
{
State = 1, Message = string.Empty
});
}
catch (Exception ex)
{
return(new CommonResult()
{
State = 0, Message = string.Empty
});
}
}
public UserInfoViewModel Execute(GetUserCommand command)
{
const string userInfoSql = "SELECT u.user_id,u.user_name,u.password from user_info u ";
try
{
using (var conn = _dapperConnection.GetDataBaseConnection())
{
var userInfo = conn.Query <UserInfo>(userInfoSql).FirstOrDefault();
if (userInfo == null)
{
return(null);
}
else
{
UserInfoViewModel model = new UserInfoViewModel();
model.UserId = userInfo.UserId;
model.UserName = userInfo.UserName;
model.Password = userInfo.Password;
return(model);
}
}
}
catch (Exception ex)
{
_logger.LogError(string.Format("获取用户信息出现异常，异常信息为{0}", ex));
return(null);
}
}
public PostInfoVIewModel Execute(GetPostInfoCommand command)
{
try
{
const string selectSql = "select * from post where post_id = @postId";
const string selectTagSql = "select * from tag where post_id = @postId";
using (var conn = _dapperConnection.GetDataBaseConnection())
{
var tagResult = conn.Query <BlogTag>(selectTagSql, new { postId = command.PostId });
var result = conn.QuerySingle <Post>(selectSql, new { postId = command.PostId });
PostInfoVIewModel model = new PostInfoVIewModel();
var tags = tagResult.Select(p => p.Tag).ToArray();
var tagStr = String.Join(",", tags);
model.IsPublish = result.IsPublish;
model.LayoutType = result.LayoutType;
model.PostContentPreview = result.PostContentPreview;
model.PostDate = result.PostDate;
model.PostHeaderMask = result.HeaderMask;//.HasValue?result.PostHeaderMask.Value:0;
model.PostHeadImageUrl = result.PostHeadImgUrl;
model.PostId = result.PostId;
model.PostMarkdown = result.PostMarkdown;
model.PostSubTitle = result.PostSubTitle;
model.PostTitle = result.PostTitle;
model.PostTitleSlug = result.PostTitleSlug;
model.Tags = tagStr;
return(model);
}
}
catch (Exception ex)
{
_logger.LogError(string.Format("获取博客信息出现异常{0}", ex));
return(null);
}
}
public List <TagPostsViewModel> Execute(TagPostsDataBind command)
{
try
{
const string sql = "SELECT t.tag as TagName,count(t.post_id) as TagPostCount from tag t GROUP BY t.tag";
const string allPostSql = @"select t.tag as PostTag,p.post_title as PostTitle,
p.post_sub_title as PostSubTitle,p.post_id as PostId,p.post_date as PostCreateDate from post p left join tag t on t.post_id = p.post_id";
using (var conn = _dapperConnection.GetDataBaseConnection())
{
var tagResult = conn.Query <TagPostsViewModel>(sql).ToList();
var tagPostInfos = conn.Query <TagPostInfo>(allPostSql);
foreach (var item in tagResult)
{
item.TagPostInfos = tagPostInfos.Where(p => p.PostTag == item.TagName).OrderByDescending(p => p.PostCreateDate).ToList();
}
return(tagResult);
}
}
catch (Exception ex)
{
_logger.LogError(string.Format("获取标签页信息失败出现异常，异常信息为{0}", ex));
return(null);
}
// throw new NotImplementedException();
}
public List <PostManagerListViewModel> Execute(PostIndexCommand command)
{
List <PostManagerListViewModel> listResult = new List <PostManagerListViewModel>();
const string sql = "select a.post_id,a.post_title,a.post_sub_title,a.post_date from post a ";
try
{
using (var conn = _dapperConnection.GetDataBaseConnection())
{
var result = conn.Query <Post>(sql);
foreach (var item in result)
{
PostManagerListViewModel viewModel = new PostManagerListViewModel();
viewModel.PostDate = item.PostDate.ToString("yyyy-MM-dd HH:mm:ss");
viewModel.PostId = item.PostId;
viewModel.PostSubTitle = item.PostSubTitle;
viewModel.PostTitle = item.PostTitle;
listResult.Add(viewModel);
}
}
return(listResult);
}
catch (Exception ex)
{
_logger.LogError(string.Format("获取博客列表失败，异常信息为{0}", ex));
return(null);
}
}
public CommonResult Execute(LoginCommand command)
{
string sql = "select * from user_info where user_name=@user_name and password =@password";
using (var conn = _dapperConnection.GetDataBaseConnection())
{
var result = conn.Query <UserInfo>(sql, new { user_name = command.UserName, password = command.Password });
if (result.Count() > 0)
{
return(new CommonResult()
{
State = 1,
Message = "Success"
});
}
else
{
return(new CommonResult()
{
State = 0,
Message = "用户名或密码不正确"
});
}
}
}
public CommonResult <IndexInfoViewModel> Execute(IndexInfoCommand command)
{
try
{
const string countSql = "select count(*) from post";
const string countTagSql = "select count(distinct(tag)) from tag";
using (var conn = _dapperConnection.GetDataBaseConnection())
{
var result = conn.QuerySingle <int>(countSql);
var result2 = conn.QuerySingle <int>(countTagSql);
IndexInfoViewModel resultInfo = new IndexInfoViewModel();
resultInfo.BlogCount = result;
resultInfo.TagCount = result2;
return(new CommonResult <IndexInfoViewModel>()
{
State = 1,
Message = string.Empty,
Value = resultInfo
});
}
}
catch (Exception ex)
{
return(new CommonResult <IndexInfoViewModel>()
{
State = 0,
Message = "进行数量获取出现异常"
});
}
}
public CommonResult Execute(ModifyUserCommand command)
{
const string sql = "update user_info set user_name=@user_name,password=@password where user_id=@user_id";
try
{
using (var conn = _dapperConnection.GetDataBaseConnection())
{
var result = conn.Execute(sql, new { user_name = command.UserName, password = command.Password, user_id = command.UserId });
if (result > 0)
{
return(new CommonResult()
{
Message = string.Empty, State = 1
});
}
else
{
return(new CommonResult()
{
Message = string.Empty, State = 0
});
}
}
}
catch (Exception ex)
{
_logger.LogError(string.Format("更新用户信息出现异常，异常信息为{0}", ex));
return(new CommonResult()
{
Message = string.Empty, State = 0
});
}
}
public List <TagCloudViewModel> Execute(TagCloudDataBind command)
{
const string sql = "SELECT t.tag as TagName,count(t.post_id) as TagPostCount from tag t GROUP BY t.tag";
using (var conn = _dapperConnection.GetDataBaseConnection())
{
var result = conn.Query <TagCloudViewModel>(sql);
if (result != null && result.Count() > 0)
{
return(result.ToList());
}
}
return(null);
}
public List <TagManagerListViewModel> Execute(TagManagerCommand command)
{
const string sql = "SELECT t.tag as TagName,count(t.post_id) as TagCount,slug as TagSlug from tag t GROUP BY t.tag";
try
{
using (var conn = _dapperConnection.GetDataBaseConnection())
{
var result = conn.Query <TagManagerListViewModel>(sql).ToList();
return(result);
}
}
catch (Exception ex)
{
_logger.LogError(String.Format("查询标签出现异常，异常信息为{0}", ex));
return(null);
}
}
public PageInfo <PostListViewModel> Execute(PostListDataBind command)
{
try
{
const string countSql = "select count(1) from post p ";
const string sql = "select p.post_id,p.post_content_preview,p.post_date,p.post_title,p.layout_type from post p ORDER BY p.post_date DESC LIMIT @startIndex,@pagesize ";
using (var conn = _dapperConnection.GetDataBaseConnection())
{
var count = conn.QueryFirstOrDefault <int>(countSql);
var result = conn.Query <Post>(sql, new { startIndex = (command.PageIndex - 1) * command.PageSize, pagesize = command.PageSize });
PageInfo <PostListViewModel> resultViewModel = new PageInfo <PostListViewModel>();
resultViewModel.CurrentPage = command.PageIndex;
resultViewModel.CurrentPageSize = command.PageSize;
resultViewModel.TotalPage = (int)Math.Ceiling((double)count / command.PageSize);
resultViewModel.TotalRecord = count;
if (result != null)
{
List <PostListViewModel> modelList = new List <PostListViewModel>();
foreach (var item in result)
{
PostListViewModel model = new PostListViewModel();
model.PostContentPreview = item.PostContentPreview;
model.PostCreateTime = item.PostDate.ToString("yyyy-MM-dd HH:mm:ss");
model.PostId = item.PostId;
model.PostTitle = item.PostTitle;
model.PostLayout = item.LayoutType;
modelList.Add(model);
}
resultViewModel.Value = modelList;
}
return(resultViewModel);
}
}
catch (Exception ex)
{
_logger.LogError(string.Format("请求博客列表出现异常，异常信息为{0}", ex));
return(null);
}
}
public CommonResult Execute(UpdatePostCommand command)
{
const string sql = @"update post set post_date =@post_date , post_title =@post_title ,post_sub_title =@post_sub_title ,
post_content_preview =@post_content_preview ,
post_markdown =@post_markdown ,post_title_slug =@post_title_slug ,
layout_type =@layout_type , post_head_img_url =@post_head_img_url ,
header_mask =@header_mask , is_publish =@is_publish where post_id =@post_id ";
const string deleteSql = "delete from tag where post_id = @post_id ";
const string tagSql = @"INSERT into tag(
tag,
post_id,
slug
)
VALUES(
@tag,
@postId,
@slug
)";
Markdown markdown = new Markdown();
try
{
using (var conn = _dapperConnection.GetDataBaseConnection())
{
Post post = new Post();
post.AuthorDisplayName = "White Lee";
post.AuthorEmail = "*****@*****.**";
post.LayoutType = command.LayoutType;
post.PostContent = null;
// post.PostContentPreview = command.PostMarkDown;
if (!string.IsNullOrWhiteSpace(command.PostMarkDown))
{
var content = markdown.Transform(command.PostMarkDown);
var des = Regex.Replace(content, "<[^>]+>", string.Empty).Trim();
var length = des.Length < 240 ? des.Length : 240;
des = des.Substring(0, length) + " ...";
post.PostContentPreview = des;
}
post.PostDate = DateTime.Now;
double headMask = 0f;
if (!string.IsNullOrWhiteSpace(command.HeadMask))
{
if (double.TryParse(command.HeadMask, out headMask))
{
post.HeaderMask = headMask;
}
}
post.PostHeadImgUrl = command.PostHeadImageUrl;
post.PostMarkdown = command.PostMarkDown;
post.PostSubTitle = command.postSubTitle;
post.PostTitle = command.PostTitle;
post.PostTitleSlug = command.PostSlug;
post.IsPublish = command.Published;
post.PostId = command.PostId;
var result = conn.Execute(sql, new
{
post_date = post.PostDate,
post_title = post.PostTitle,
post_sub_title = post.PostSubTitle,
post_content_preview = post.PostContentPreview,
post_markdown = post.PostMarkdown,
post_title_slug = post.PostTitleSlug,
layout_type = post.LayoutType,
post_head_img_url = post.PostHeadImgUrl,
header_mask = post.HeaderMask,
is_publish = post.IsPublish,
post_id = post.PostId
});
if (result > 0)
{
conn.Execute(deleteSql, new { post_id = command.PostId });
var tags = command.Tags.Split(',');
foreach (var item in tags)
{
var tagResult = conn.Execute(tagSql, new
{
tag = item,
postId = command.PostId,
slug = item.ToSlug()
});
if (tagResult != 1)
{
_logger.LogError(string.Format("添加标签{0}出现错误", item));
}
}
return(new CommonResult()
{
State = 1,
Message = string.Empty
});
}
else
{
return(new CommonResult()
{
State = 0,
Message = "更新失败"
});
}
}
}
catch (Exception ex)
{
_logger.LogError(string.Format("更新blog失败，异常信息为{0}", ex));
return(new CommonResult()
{
State = 0,
Message = "更新出现异常"
});
}
}
public PostDetialViewModel Execute(PostDetailDataBind command)
{
try
{
const string postGetSql = "select * from post p where p.post_id = @postId";
const string tagGetsql = "select t.tag from tag t where t.post_id = @postId";
const string prePostSql = "select p2.post_id,p2.layout_type,p2.post_title from post p2 where p2.post_id=(SELECT max(p.post_id) from post p where p.post_id<@postId)";
const string nextPostSql = "select p2.post_id,p2.layout_type,p2.post_title from post p2 where p2.post_id=(SELECT min(p.post_id) from post p where p.post_id>@postId)";
using (var conn = _dapperConnection.GetDataBaseConnection())
{
var postinfo = conn.QueryFirstOrDefault <MyBlog.Model.DataModel.Post>(postGetSql, new { postId = command.PostId });
if (postinfo != null)
{
var tags = conn.Query <string>(tagGetsql, new { postId = command.PostId });
var prePost = conn.QueryFirstOrDefault <PostNev>(prePostSql, new { postId = command.PostId });
var nevPost = conn.QueryFirstOrDefault <PostNev>(nextPostSql, new { postId = command.PostId });
PostDetialViewModel model = new PostDetialViewModel();
PostNavigation pre = new PostNavigation();
PostNavigation next = new PostNavigation();
if (prePost != null)
{
pre.PostId = prePost.PostId;
pre.PostLayoutType = prePost.PostLayoutType;
pre.PostTitle = prePost.PostTitle;
}
else
{
pre = null;
}
if (nevPost != null)
{
next.PostId = nevPost.PostId;
next.PostLayoutType = nevPost.PostLayoutType;
next.PostTitle = nevPost.PostTitle;
}
else
{
next = null;
}
model.PostTags = tags.ToList();
model.Next = next;
model.Previous = pre;
Markdown markdown = new Markdown();
model.PostContent = markdown.Transform(postinfo.PostMarkdown); //postinfo.PostContent;
model.PostDate = postinfo.PostDate.ToString("yyyy-MM-dd HH:mm:ss");
model.PostHeaderImageUrl = postinfo.PostHeadImgUrl;
model.PostHeaderMask = postinfo.HeaderMask.ToString();
model.PostId = postinfo.PostId;
model.PostSubTitle = postinfo.PostSubTitle;
model.PostTitle = postinfo.PostTitle;
model.PostLayoutType = postinfo.LayoutType;
return(model);
}
}
}
catch (Exception ex)
{
_logger.LogError(string.Format("查询博客详细信息出现异常，异常信息为{0}", ex));
}
return(null);
}