/// <summary>
/// 检查别名是否重复
/// </summary>
/// <param name="term"></param>
/// <returns></returns>
private bool CheckSlug(PostInfo post)
{
if (string.IsNullOrEmpty(post.Slug))
{
return(true);
}
while (true)
{
string cmdText = string.Empty;
if (post.PostId == 0)
{
cmdText = string.Format("select count(1) from [loachs_posts] where [slug]='{0}' ", post.Slug);
}
else
{
cmdText = string.Format("select count(1) from [loachs_posts] where [slug]='{0}' and [postid]<>{1}", post.Slug, post.PostId);
}
int r = Convert.ToInt32(SqliteDbHelper.ExecuteScalar(cmdText));
if (r == 0)
{
return(true);
}
post.Slug += "-2";
}
}
public int UpdateLink(LinkInfo link)
{
string cmdText = @"update [loachs_links] set
[type]=@type,
[name]=@name,
[href]=@href,
[position]=@position,
[target]=@target,
[description]=@description,
[displayorder]=@displayorder,
[status]=@status,
[createdate]=@createdate
where linkid=@linkid";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@type", DbType.Int32, 4, link.Type),
SqliteDbHelper.MakeInParam("@name", DbType.String, 100, link.Name),
SqliteDbHelper.MakeInParam("@href", DbType.String, 255, link.Href),
SqliteDbHelper.MakeInParam("@position", DbType.Int32, 4, link.Position),
SqliteDbHelper.MakeInParam("@target", DbType.String, 50, link.Target),
SqliteDbHelper.MakeInParam("@description", DbType.String, 255, link.Description),
SqliteDbHelper.MakeInParam("@displayorder", DbType.Int32, 4, link.Displayorder),
SqliteDbHelper.MakeInParam("@status", DbType.Int32, 4, link.Status),
SqliteDbHelper.MakeInParam("@createdate", DbType.Date, 8, link.CreateDate),
SqliteDbHelper.MakeInParam("@linkid", DbType.Int32, 4, link.LinkId),
};
return(Convert.ToInt32(SqliteDbHelper.ExecuteScalar(CommandType.Text, cmdText, prams)));
}
public int InsertLink(LinkInfo link)
{
string cmdText = @"insert into [loachs_links]
(
[type],[name],[href],[position],[target],[description],[displayorder],[status],[createdate]
)
values
(
@type,@name,@href,@position,@target,@description,@displayorder,@status,@createdate
)";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@type", DbType.Int32, 4, link.Type),
SqliteDbHelper.MakeInParam("@name", DbType.String, 100, link.Name),
SqliteDbHelper.MakeInParam("@href", DbType.String, 255, link.Href),
SqliteDbHelper.MakeInParam("@position", DbType.Int32, 4, link.Position),
SqliteDbHelper.MakeInParam("@target", DbType.String, 50, link.Target),
SqliteDbHelper.MakeInParam("@description", DbType.String, 255, link.Description),
SqliteDbHelper.MakeInParam("@displayorder", DbType.Int32, 4, link.Displayorder),
SqliteDbHelper.MakeInParam("@status", DbType.Int32, 4, link.Status),
SqliteDbHelper.MakeInParam("@createdate", DbType.Date, 8, link.CreateDate),
};
int r = SqliteDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
if (r > 0)
{
return(Convert.ToInt32(SqliteDbHelper.ExecuteScalar("select [linkid] from [loachs_links] order by [linkid] desc limit 1")));
}
return(0);
}
public int UpdateCategory(CategoryInfo category)
{
CheckSlug(category);
string cmdText = @"update [loachs_terms] set
[Type]=@Type,
[Name]=@Name,
[Slug]=@Slug,
[Description]=@Description,
[Displayorder]=@Displayorder,
[Count]=@Count,
[CreateDate]=@CreateDate
where termid=@termid";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@Type", DbType.Int32, 1, (int)TermType.Category),
SqliteDbHelper.MakeInParam("@Name", DbType.String, 255, category.Name),
SqliteDbHelper.MakeInParam("@Slug", DbType.String, 255, category.Slug),
SqliteDbHelper.MakeInParam("@Description", DbType.String, 255, category.Description),
SqliteDbHelper.MakeInParam("@Displayorder", DbType.Int32, 4, category.Displayorder),
SqliteDbHelper.MakeInParam("@Count", DbType.Int32, 4, category.Count),
SqliteDbHelper.MakeInParam("@CreateDate", DbType.Date, 8, category.CreateDate),
SqliteDbHelper.MakeInParam("@termid", DbType.Int32, 1, category.CategoryId),
};
return(Convert.ToInt32(SqliteDbHelper.ExecuteScalar(CommandType.Text, cmdText, prams)));
}
/// <summary>
/// 更新
/// </summary>
/// <param name="comment"></param>
/// <returns></returns>
public int UpdateComment(CommentInfo comment)
{
string cmdText = @"update [loachs_comments] set
PostId=@PostId,
ParentId=@ParentId,
UserId=@UserId,
Name=@Name,
Email=@Email,
SiteUrl=@SiteUrl,
Content=@Content,
EmailNotify=@EmailNotify,
IpAddress=@IpAddress,
CreateDate=@CreateDate,
Approved=@Approved
where CommentId=@CommentId ";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@PostId", DbType.Int32, 4, comment.PostId),
SqliteDbHelper.MakeInParam("@ParentId", DbType.Int32, 4, comment.ParentId),
SqliteDbHelper.MakeInParam("@UserId", DbType.Int32, 4, comment.UserId),
SqliteDbHelper.MakeInParam("@Name", DbType.String, 255, comment.Name),
SqliteDbHelper.MakeInParam("@Email", DbType.String, 255, comment.Email),
SqliteDbHelper.MakeInParam("@SiteUrl", DbType.String, 255, comment.SiteUrl),
SqliteDbHelper.MakeInParam("@Content", DbType.String, 255, comment.Content),
SqliteDbHelper.MakeInParam("@EmailNotify", DbType.Int32, 4, comment.EmailNotify),
SqliteDbHelper.MakeInParam("@IpAddress", DbType.String, 255, comment.IpAddress),
SqliteDbHelper.MakeInParam("@CreateDate", DbType.Date, 8, comment.CreateDate),
SqliteDbHelper.MakeInParam("@Approved", DbType.Int32, 4, comment.Approved),
SqliteDbHelper.MakeInParam("@CommentId", DbType.Int32, 4, comment.CommentId),
};
return(SqliteDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams));
}
/// <summary>
/// 添加分类
/// </summary>
/// <param name="category"></param>
/// <returns></returns>
public int InsertCategory(CategoryInfo category)
{
CheckSlug(category);
string cmdText = @"insert into [loachs_terms]
(
[Type],[Name],[Slug],[Description],[Displayorder],[Count],[CreateDate]
)
values
(
@Type,@Name,@Slug,@Description,@Displayorder,@Count,@CreateDate
)";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@Type", DbType.Int32, 1, (int)TermType.Category),
SqliteDbHelper.MakeInParam("@Name", DbType.String, 255, category.Name),
SqliteDbHelper.MakeInParam("@Slug", DbType.String, 255, category.Slug),
SqliteDbHelper.MakeInParam("@Description", DbType.String, 255, category.Description),
SqliteDbHelper.MakeInParam("@Displayorder", DbType.Int32, 4, category.Displayorder),
SqliteDbHelper.MakeInParam("@Count", DbType.Int32, 4, category.Count),
SqliteDbHelper.MakeInParam("@CreateDate", DbType.Date, 8, category.CreateDate)
};
SqliteDbHelper.ExecuteScalar(CommandType.Text, cmdText, prams);
int newId = Convert.ToInt32(SqliteDbHelper.ExecuteScalar("select [termid] from [loachs_terms] order by [termid] desc limit 1"));
return(newId);
}
/// <summary>
/// 添加
/// </summary>
/// <param name="comment"></param>
/// <returns></returns>
public int InsertComment(CommentInfo comment)
{
string cmdText = @"insert into [loachs_comments](
PostId, ParentId,UserId,Name,Email,SiteUrl,Content,EmailNotify,IpAddress,CreateDate,Approved)
values (
@PostId, @ParentId,@UserId,@Name,@Email,@SiteUrl,@Content,@EmailNotify,@IpAddress,@CreateDate,@Approved)";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@PostId", DbType.Int32, 4, comment.PostId),
SqliteDbHelper.MakeInParam("@ParentId", DbType.Int32, 4, comment.ParentId),
SqliteDbHelper.MakeInParam("@UserId", DbType.Int32, 4, comment.UserId),
SqliteDbHelper.MakeInParam("@Name", DbType.String, 255, comment.Name),
SqliteDbHelper.MakeInParam("@Email", DbType.String, 255, comment.Email),
SqliteDbHelper.MakeInParam("@SiteUrl", DbType.String, 255, comment.SiteUrl),
SqliteDbHelper.MakeInParam("@Content", DbType.String, 255, comment.Content),
SqliteDbHelper.MakeInParam("@EmailNotify", DbType.Int32, 4, comment.EmailNotify),
SqliteDbHelper.MakeInParam("@IpAddress", DbType.String, 255, comment.IpAddress),
SqliteDbHelper.MakeInParam("@CreateDate", DbType.Date, 8, comment.CreateDate),
SqliteDbHelper.MakeInParam("@Approved", DbType.Int32, 4, comment.Approved),
};
SqliteDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
int newId = Convert.ToInt32(SqliteDbHelper.ExecuteScalar("select [CommentId] from [loachs_comments] order by [CommentId] desc limit 1"));
return(newId);
}
/// <summary>
/// 添加
/// </summary>
/// <param name="_userinfo"></param>
/// <returns></returns>
public int InsertUser(UserInfo _userinfo)
{
string cmdText = @" insert into [loachs_users](
[Type],[UserName],[Name],[Password],[Email],[SiteUrl],[AvatarUrl],[Description],[displayorder],[Status],[PostCount],[CommentCount],[CreateDate])
values (
@Type,@UserName,@Name,@Password,@Email,@SiteUrl,@AvatarUrl,@Description,@Displayorder,@Status, @PostCount,@CommentCount,@CreateDate )";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@Type", DbType.Int32, 4, _userinfo.Type),
SqliteDbHelper.MakeInParam("@UserName", DbType.String, 50, _userinfo.UserName),
SqliteDbHelper.MakeInParam("@Name", DbType.String, 50, _userinfo.Name),
SqliteDbHelper.MakeInParam("@Password", DbType.String, 50, _userinfo.Password),
SqliteDbHelper.MakeInParam("@Email", DbType.String, 50, _userinfo.Email),
SqliteDbHelper.MakeInParam("@SiteUrl", DbType.String, 255, _userinfo.SiteUrl),
SqliteDbHelper.MakeInParam("@AvatarUrl", DbType.String, 255, _userinfo.AvatarUrl),
SqliteDbHelper.MakeInParam("@Displayorder", DbType.String, 255, _userinfo.Description),
SqliteDbHelper.MakeInParam("@Status", DbType.Int32, 4, _userinfo.Displayorder),
SqliteDbHelper.MakeInParam("@Status", DbType.Int32, 4, _userinfo.Status),
SqliteDbHelper.MakeInParam("@PostCount", DbType.Int32, 4, _userinfo.PostCount),
SqliteDbHelper.MakeInParam("@CommentCount", DbType.Int32, 4, _userinfo.CommentCount),
SqliteDbHelper.MakeInParam("@CreateDate", DbType.Date, 8, _userinfo.CreateDate),
};
int r = SqliteDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
if (r > 0)
{
return(Convert.ToInt32(SqliteDbHelper.ExecuteScalar("select [UserId] from [loachs_users] order by [UserId] desc limit 1")));
}
return(0);
}
public List <PostInfo> GetPostListByRelated(int postId, int rowCount)
{
string tags = string.Empty;
PostInfo post = GetPost(postId);
if (post != null && post.Tag.Length > 0)
{
tags = post.Tag;
tags = tags.Replace("}", "},");
string[] idList = tags.Split(',');
string where = " (";
foreach (string tagID in idList)
{
if (!string.IsNullOrEmpty(tagID))
{
where += string.Format(" [tags] like '%{0}%' or ", tagID);
}
}
where += " 1=2 ) and [status]=1 and [postid]<>" + postId;
string cmdText = string.Format("select * from [loachs_posts] where {1} order by [postid] desc limit {0}", rowCount, where);
return(DataReaderToCommentList(SqliteDbHelper.ExecuteReader(cmdText)));
}
return(new List <PostInfo>());
}
/// <summary>
/// 获取全部分类
/// </summary>
/// <returns></returns>
public List <CategoryInfo> GetCategoryList()
{
string condition = " [type]=" + (int)TermType.Category;
string cmdText = "select * from [loachs_terms] where " + condition + " order by [displayorder] asc,[termid] asc";
return(DataReaderToList(SqliteDbHelper.ExecuteReader(cmdText)));
}
public int DeleteLink(int linkId)
{
string cmdText = "delete from [loachs_links] where [linkid] = @linkid";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@linkid", DbType.Int32, 4, linkId)
};
return(SqliteDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams));
}
public int DeleteCategory(int categoryId)
{
string cmdText = "delete from [loachs_terms] where [termid] = @termid";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@termid", DbType.Int32, 4, categoryId)
};
return(SqliteDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams));
}
/// <summary>
/// 是否存在
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public bool ExistsUserName(string userName)
{
string cmdText = "select count(1) from [loachs_users] where [userName] = @userName ";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@userName", DbType.String, 50, userName),
};
return(Convert.ToInt32(SqliteDbHelper.ExecuteScalar(CommandType.Text, cmdText, prams)) > 0);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public int DeleteUser(int userid)
{
string cmdText = "delete from [loachs_users] where [userid] = @userid";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@userid", DbType.Int32, 4, userid)
};
return(SqliteDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams));
}
public int UpdatePostViewCount(int postId, int addCount)
{
string cmdText = "update [loachs_posts] set [viewcount] = [viewcount] + @addcount where [postid]=@postid";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@addcount", DbType.Int32, 4, addCount),
SqliteDbHelper.MakeInParam("@postid", DbType.Int32, 4, postId),
};
return(SqliteDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams));
}
public bool UpdateSetting(SettingInfo setting)
{
string cmdText = @"update [loachs_sites] set [setting]=@setting";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@setting", DbType.String, 0, Serialize(setting)),
};
return(SqliteDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams) == 1);
}
/// <summary>
/// 获取实体
/// </summary>
/// <param name="commentId"></param>
/// <returns></returns>
public CommentInfo GetComment(int commentId)
{
string cmdText = "select * from [loachs_comments] where [commentId] = @commentId";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@commentId", DbType.Int32, 4, commentId)
};
List <CommentInfo> list = DataReaderToCommentList(SqliteDbHelper.ExecuteReader(cmdText, prams));
return(list.Count > 0 ? list[0] : null);
}
/// <summary>
/// 根据日志ID删除评论
/// </summary>
/// <param name="postId">日志ID</param>
/// <returns></returns>
public int DeleteCommentByPost(int postId)
{
string cmdText = "delete from [loachs_comments] where [postId] = @postId";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@postId", DbType.Int32, 4, postId)
};
int result = SqliteDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
return(result);
}
//public List<TagInfo> GetTagList(int pageSize, int pageIndex, out int recordCount)
//{
// string condition = " [type]=" + (int)TermType.Tag;
// string cmdTotalRecord = "select count(1) from [loachs_terms] where " + condition;
// recordCount = Convert.ToInt32(SqliteDbHelper.ExecuteScalar(CommandType.Text, cmdTotalRecord));
// string cmdText = SqliteDbHelper.GetPageSql("[loachs_terms]", "[termid]", "*", pageSize, pageIndex, 1, condition);
// return DataReaderToList(SqliteDbHelper.ExecuteReader(cmdText));
//}
public List <TagInfo> GetTagList(string ids)
{
if (string.IsNullOrEmpty(ids))
{
return(new List <TagInfo>());
}
string cmdText = "select * from [loachs_terms] where [termid] in (" + ids + ")";
// throw new Exception(cmdText);
return(DataReaderToList(SqliteDbHelper.ExecuteReader(cmdText)));
}
public CategoryInfo GetCategory(int categoryId)
{
string cmdText = "select * from [loachs_terms] where [termid] = @termid";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@termid", DbType.Int32, 4, categoryId)
};
List <CategoryInfo> list = DataReaderToList(SqliteDbHelper.ExecuteReader(CommandType.Text, cmdText, prams));
return(list.Count > 0 ? list[0] : null);
}
/// <summary>
/// 获取实体
/// </summary>
/// <param name="PostId">主键</param>
/// <returns></returns>
public PostInfo GetPost(int postid)
{
string cmdText = "select * from [loachs_posts] where [PostId] = @PostId limit 1";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@PostId", DbType.Int32, 4, postid)
};
List <PostInfo> list = DataReaderToCommentList(SqliteDbHelper.ExecuteReader(cmdText, prams));
return(list.Count > 0 ? list[0] : null);
}
/// <summary>
/// 获取实体
/// </summary>
/// <param name="slug"></param>
/// <returns></returns>
public PostInfo GetPost(string slug)
{
string cmdText = "select * from [loachs_posts] where [slug] = @slug limit 1";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@slug", DbType.String, 200, slug)
};
List <PostInfo> list = DataReaderToCommentList(SqliteDbHelper.ExecuteReader(cmdText, prams));
return(list.Count > 0 ? list[0] : null);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="commentId"></param>
/// <returns></returns>
public int DeleteComment(int commentId)
{
CommentInfo comment = GetComment(commentId); //删除前
string cmdText = "delete from [loachs_comments] where [commentId] = @commentId";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@commentId", DbType.Int32, 4, commentId)
};
int result = SqliteDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
return(result);
}
public SettingInfo GetSetting()
{
string cmdText = "select [setting] from [loachs_sites] limit 1";
string str = Convert.ToString(SqliteDbHelper.ExecuteScalar(cmdText));
object obj = DeSerialize(typeof(SettingInfo), str);
if (obj == null)
{
return(new SettingInfo());
}
return((SettingInfo)obj);
}
public StatisticsInfo GetStatistics()
{
string cmdText = "select * from [loachs_sites] limit 1";
string insertText = "insert into [loachs_sites] ([PostCount],[CommentCount],[VisitCount],[TagCount],[setting]) values ( '0','0','0','0','<?xml version=\"1.0\" encoding=\"utf-8\"?><SettingInfo xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"></SettingInfo>')";
List <StatisticsInfo> list = DataReaderToList(SqliteDbHelper.ExecuteReader(cmdText));
if (list.Count == 0)
{
SqliteDbHelper.ExecuteNonQuery(insertText);
}
list = DataReaderToList(SqliteDbHelper.ExecuteReader(cmdText));
return(list.Count > 0 ? list[0] : null);
}
public bool UpdateStatistics(StatisticsInfo statistics)
{
string cmdText = @"update [loachs_sites] set
PostCount=@PostCount,
CommentCount=@CommentCount,
VisitCount=@VisitCount,
TagCount=@TagCount";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@PostCount", DbType.Int32, 4, statistics.PostCount),
SqliteDbHelper.MakeInParam("@CommentCount", DbType.Int32, 4, statistics.CommentCount),
SqliteDbHelper.MakeInParam("@VisitCount", DbType.Int32, 4, statistics.VisitCount),
SqliteDbHelper.MakeInParam("@TagCount", DbType.Int32, 4, statistics.TagCount),
};
return(SqliteDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams) == 1);
}
/// <summary>
/// 添加
/// </summary>
/// <param name="postinfo">实体</param>
/// <returns>成功返回新记录的ID,失败返回 0</returns>
public int InsertPost(PostInfo postinfo)
{
CheckSlug(postinfo);
string cmdText = @"insert into [loachs_posts]
(
[CategoryId],[Title],[Summary],[Content],[Slug],[UserId],[CommentStatus],[CommentCount],[ViewCount],[Tag],[UrlFormat],[Template],[Recommend],[Status],[TopStatus],[HideStatus],[CreateDate],[UpdateDate]
)
values
(
@CategoryId,@Title,@Summary,@Content,@Slug,@UserId,@CommentStatus,@CommentCount,@ViewCount,@Tag,@UrlFormat,@Template,@Recommend,@Status,@TopStatus,@HideStatus,@CreateDate,@UpdateDate
)";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@CategoryId", DbType.Int32, 4, postinfo.CategoryId),
SqliteDbHelper.MakeInParam("@Title", DbType.String, 255, postinfo.Title),
SqliteDbHelper.MakeInParam("@Summary", DbType.String, 0, postinfo.Summary),
SqliteDbHelper.MakeInParam("@Content", DbType.String, 0, postinfo.Content),
SqliteDbHelper.MakeInParam("@Slug", DbType.String, 255, postinfo.Slug),
SqliteDbHelper.MakeInParam("@UserId", DbType.Int32, 4, postinfo.UserId),
SqliteDbHelper.MakeInParam("@CommentStatus", DbType.Int32, 1, postinfo.CommentStatus),
SqliteDbHelper.MakeInParam("@CommentCount", DbType.Int32, 4, postinfo.CommentCount),
SqliteDbHelper.MakeInParam("@ViewCount", DbType.Int32, 4, postinfo.ViewCount),
SqliteDbHelper.MakeInParam("@Tag", DbType.String, 255, postinfo.Tag),
SqliteDbHelper.MakeInParam("@UrlFormat", DbType.Int32, 1, postinfo.UrlFormat),
SqliteDbHelper.MakeInParam("@Template", DbType.String, 50, postinfo.Template),
SqliteDbHelper.MakeInParam("@Recommend", DbType.Int32, 1, postinfo.Recommend),
SqliteDbHelper.MakeInParam("@Status", DbType.Int32, 1, postinfo.Status),
SqliteDbHelper.MakeInParam("@TopStatus", DbType.Int32, 1, postinfo.TopStatus),
SqliteDbHelper.MakeInParam("@HideStatus", DbType.Int32, 1, postinfo.HideStatus),
SqliteDbHelper.MakeInParam("@CreateDate", DbType.Date, 8, postinfo.CreateDate),
SqliteDbHelper.MakeInParam("@UpdateDate", DbType.Date, 8, postinfo.UpdateDate)
};
SqliteDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
int newId = StringHelper.ObjectToInt(SqliteDbHelper.ExecuteScalar("select [PostId] from [Loachs_Posts] order by [PostId] desc limit 1"));
//if (newId > 0)
//{
// SqliteDbHelper.ExecuteNonQuery(string.Format("update [loachs_users] set [postcount]=[postcount]+1 where [userid]={0}", postinfo.UserId));
// SqliteDbHelper.ExecuteNonQuery("update [loachs_sites] set [postcount]=[postcount]+1");
// SqliteDbHelper.ExecuteNonQuery(string.Format("update [loachs_terms] set [count]=[count]+1 where [termid]={0}", postinfo.CategoryId));
//}
return(newId);
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="postId"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="totalRecord"></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);
}
string cmdTotalRecord = "select count(1) from [loachs_comments] where " + condition;
totalRecord = Convert.ToInt32(SqliteDbHelper.ExecuteScalar(CommandType.Text, cmdTotalRecord));
// throw new Exception(cmdTotalRecord);
string cmdText = SqliteDbHelper.GetPageSql("[loachs_comments]", "[CommentId]", "*", pageSize, pageIndex, order, condition);
return(DataReaderToCommentList(SqliteDbHelper.ExecuteReader(cmdText)));
}
/// <summary>
/// 修改
/// </summary>
/// <param name="postinfo">实体</param>
/// <returns>修改的行数</returns>
public int UpdatePost(PostInfo postinfo)
{
CheckSlug(postinfo);
//PostInfo oldPost = GetPost(postinfo.PostId); //修改前
//if (oldPost.CategoryId != postinfo.CategoryId)
//{
// SqliteDbHelper.ExecuteNonQuery(string.Format("update [loachs_terms] set [count]=[count]-1 where [termid]={0}", oldPost.CategoryId));
// SqliteDbHelper.ExecuteNonQuery(string.Format("update [loachs_terms] set [count]=[count]+1 where [termid]={0}", postinfo.CategoryId));
//}
string cmdText = "update [loachs_posts] set [CategoryId]=@CategoryId,[Title]=@Title,[Summary]=@Summary,[Content]=@Content,[Slug]=@Slug,[UserId]=@UserId,[CommentStatus]=@CommentStatus,[CommentCount]=@CommentCount,[ViewCount]=@ViewCount,[Tag]=@Tag,[UrlFormat]=@UrlFormat,[Template]=@Template,[Recommend]=@Recommend,[Status]=@Status,[TopStatus]=@TopStatus,[HideStatus]=@HideStatus,[CreateDate]=@CreateDate,[UpdateDate]=@UpdateDate where [PostId]=@PostId";
SqliteParameter[] prams =
{
SqliteDbHelper.MakeInParam("@CategoryId", DbType.Int32, 4, postinfo.CategoryId),
SqliteDbHelper.MakeInParam("@Title", DbType.String, 255, postinfo.Title),
SqliteDbHelper.MakeInParam("@Summary", DbType.String, 0, postinfo.Summary),
SqliteDbHelper.MakeInParam("@Content", DbType.String, 0, postinfo.Content),
SqliteDbHelper.MakeInParam("@Slug", DbType.String, 255, postinfo.Slug),
SqliteDbHelper.MakeInParam("@UserId", DbType.Int32, 4, postinfo.UserId),
SqliteDbHelper.MakeInParam("@CommentStatus", DbType.Int32, 1, postinfo.CommentStatus),
SqliteDbHelper.MakeInParam("@CommentCount", DbType.Int32, 4, postinfo.CommentCount),
SqliteDbHelper.MakeInParam("@ViewCount", DbType.Int32, 4, postinfo.ViewCount),
SqliteDbHelper.MakeInParam("@Tag", DbType.String, 255, postinfo.Tag),
SqliteDbHelper.MakeInParam("@UrlFormat", DbType.Int32, 1, postinfo.UrlFormat),
SqliteDbHelper.MakeInParam("@Template", DbType.String, 50, postinfo.Template),
SqliteDbHelper.MakeInParam("@Recommend", DbType.Int32, 1, postinfo.Recommend),
SqliteDbHelper.MakeInParam("@Status", DbType.Int32, 1, postinfo.Status),
SqliteDbHelper.MakeInParam("@TopStatus", DbType.Int32, 1, postinfo.TopStatus),
SqliteDbHelper.MakeInParam("@HideStatus", DbType.Int32, 1, postinfo.HideStatus),
SqliteDbHelper.MakeInParam("@CreateDate", DbType.Date, 8, postinfo.CreateDate),
SqliteDbHelper.MakeInParam("@UpdateDate", DbType.Date, 8, postinfo.UpdateDate),
SqliteDbHelper.MakeInParam("@PostId", DbType.Int32, 4, postinfo.PostId),
};
return(SqliteDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams));
}
/// <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";
}
string cmdText = "select count(1) from [loachs_comments] where " + condition;
return(Convert.ToInt32(SqliteDbHelper.ExecuteScalar(CommandType.Text, cmdText)));
}