/// <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))); }