public SettingInfo GetSetting() { //string cmdText = "select top 1 [setting] from [Loachs_Sites]"; string cmdText = "select setting from Loachs_Sites limit 0,1"; string str = Convert.ToString(SqliteHelper.ExecuteScalar(cmdText)); object obj = DeSerialize(typeof(SettingInfo), str); if (obj == null) { return(new SettingInfo()); } return((SettingInfo)obj); }
/// <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 = { SqliteHelper.MakeInParam("@CategoryId", DbType.Int32, 4, postinfo.CategoryId), SqliteHelper.MakeInParam("@Title", DbType.String, 255, postinfo.Title), SqliteHelper.MakeInParam("@Summary", DbType.String, 0, postinfo.Summary), SqliteHelper.MakeInParam("@Content", DbType.String, 0, postinfo.Content), SqliteHelper.MakeInParam("@Slug", DbType.String, 255, postinfo.Slug), SqliteHelper.MakeInParam("@UserId", DbType.Int32, 4, postinfo.UserId), SqliteHelper.MakeInParam("@CommentStatus", DbType.Int32, 1, postinfo.CommentStatus), SqliteHelper.MakeInParam("@CommentCount", DbType.Int32, 4, postinfo.CommentCount), SqliteHelper.MakeInParam("@ViewCount", DbType.Int32, 4, postinfo.ViewCount), SqliteHelper.MakeInParam("@Tag", DbType.String, 255, postinfo.Tag), SqliteHelper.MakeInParam("@UrlFormat", DbType.Int32, 1, postinfo.UrlFormat), SqliteHelper.MakeInParam("@Template", DbType.String, 50, postinfo.Template), SqliteHelper.MakeInParam("@Recommend", DbType.Int32, 1, postinfo.Recommend), SqliteHelper.MakeInParam("@Status", DbType.Int32, 1, postinfo.Status), SqliteHelper.MakeInParam("@TopStatus", DbType.Int32, 1, postinfo.TopStatus), SqliteHelper.MakeInParam("@HideStatus", DbType.Int32, 1, postinfo.HideStatus), SqliteHelper.MakeInParam("@CreateDate", DbType.Date, 8, postinfo.CreateDate), SqliteHelper.MakeInParam("@UpdateDate", DbType.Date, 8, postinfo.UpdateDate) }; SqliteHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams); //int newId = StringHelper.ObjectToInt(SqliteHelper.ExecuteScalar("select top 1 PostId from Loachs_Posts order by PostId desc")); int newId = StringHelper.ObjectToInt(SqliteHelper.ExecuteScalar("select PostId from Loachs_Posts order by PostId desc limit 0,1")); //if (newId > 0) //{ // SqliteHelper.ExecuteNonQuery(string.Format("update [Loachs_Users] set [postcount]=[postcount]+1 where [userid]={0}", postinfo.UserId)); // SqliteHelper.ExecuteNonQuery("update [Loachs_Sites] set [postcount]=[postcount]+1"); // SqliteHelper.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(SqliteHelper.ExecuteScalar(CommandType.Text, cmdTotalRecord)); // throw new Exception(cmdTotalRecord); string cmdText = SqliteHelper.GetPageSql("Loachs_Comments", "CommentId", "*", pageSize, pageIndex, order, condition); return(DataReaderToCommentList(SqliteHelper.ExecuteReader(cmdText))); }
/// <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(SqliteHelper.ExecuteScalar(CommandType.Text, cmdText))); }
/// <summary> /// 检查别名是否重复 /// </summary> /// <param name="term"></param> /// <returns></returns> private bool CheckSlug(CategoryInfo term) { while (true) { string cmdText = string.Empty; if (term.CategoryId == 0) { cmdText = string.Format("select count(1) from Loachs_Terms where Slug='{0}' and type={1}", term.Slug, (int)TermType.Category); } else { cmdText = string.Format("select count(1) from Loachs_Terms where Slug='{0}' and type={1} and termid<>{2}", term.Slug, (int)TermType.Category, term.CategoryId); } int r = Convert.ToInt32(SqliteHelper.ExecuteScalar(cmdText)); if (r == 0) { return(true); } term.Slug += "-2"; } }
public List <PostInfo> GetPostList(int pageSize, int pageIndex, out int recordCount, int categoryId, int tagId, int userId, int recommend, int status, int topstatus, int hidestatus, string begindate, string enddate, string keyword) { string condition = " 1=1 "; if (categoryId != -1) { condition += " and categoryId=" + 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 (hidestatus != -1) { condition += " and hidestatus=" + hidestatus; } if (!string.IsNullOrEmpty(begindate)) { condition += " and createdate>=#" + begindate + "#"; } if (!string.IsNullOrEmpty(enddate)) { condition += " and createdate<#" + enddate + "#"; } if (!string.IsNullOrEmpty(keyword)) { condition += string.Format(" and (summary like '%{0}%' or title like '%{0}%' )", keyword); } string cmdTotalRecord = "select count(1) from Loachs_Posts where " + condition; // throw new Exception(cmdTotalRecord); recordCount = StringHelper.ObjectToInt(SqliteHelper.ExecuteScalar(CommandType.Text, cmdTotalRecord)); string cmdText = SqliteHelper.GetPageSql("Loachs_Posts", "PostId", "*", pageSize, pageIndex, 1, condition); return(DataReaderToCommentList(SqliteHelper.ExecuteReader(cmdText))); }