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))); }
/// <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); }
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); }
//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))); }
/// <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="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); }
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); }
/// <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> ///// 根据别名获取文章ID ///// </summary> ///// <param name="slug"></param> ///// <returns></returns> //public int GetPostId(string slug) //{ // string cmdText = "select [postid] from [loachs_posts] where [slug]=@slug"; // SqliteParameter[] prams = { // SqliteDbHelper.MakeInParam("@slug",DbType.String,200,slug), // }; // return StringHelper.ObjectToInt(SqliteDbHelper.ExecuteScalar(CommandType.Text, cmdText, prams)); //} public List <ArchiveInfo> GetArchive() { string cmdText = "select strftime('%Y%m',createdate) as [date] , count(*) as [count] from [loachs_posts] where [status]=1 and [hidestatus]=0 group by strftime('%Y%m',createdate) order by strftime('%Y%m',createdate) desc"; List <ArchiveInfo> list = new List <ArchiveInfo>(); using (SqliteDataReader read = SqliteDbHelper.ExecuteReader(cmdText)) { while (read.Read()) { ArchiveInfo archive = new ArchiveInfo(); string date = read["date"].ToString().Substring(0, 4) + "-" + read["date"].ToString().Substring(4, 2); archive.Date = Convert.ToDateTime(date); // archive.Title = read["date"].ToString(); archive.Count = StringHelper.ObjectToInt(read["count"]); list.Add(archive); } } return(list); }
///// <summary> ///// 获取实体 ///// </summary> ///// <param name="userName"></param> ///// <param name="password"></param> ///// <returns></returns> //public UserInfo GetUser(string userName, string password) //{ // string cmdText = "select * from [loachs_users] where [userName] = @userName and [Password]=@password"; // SqliteParameter[] prams = { // SqliteDbHelper.MakeInParam("@userName",DbType.String,50,userName), // SqliteDbHelper.MakeInParam("@password",DbType.String,50,password), // }; // List<UserInfo> list = DataReaderToUserList(SqliteDbHelper.ExecuteReader(CommandType.Text, cmdText, prams)); // if (list.Count > 0) // { // return list[0]; // } // return null; //} /// <summary> /// 获取全部 /// </summary> /// <returns></returns> public List <UserInfo> GetUserList() { string cmdText = "select * from [loachs_users] order by [displayorder] asc,[userid] asc"; return(DataReaderToUserList(SqliteDbHelper.ExecuteReader(cmdText))); }
//public LinkInfo GetLink(int linkid) //{ // string cmdText = "select * from [loachs_links] where [linkid] = @linkid"; // SqliteParameter[] prams = { // SqliteDbHelper.MakeInParam("@linkid",DbType.Int32,4,linkid) // }; // List<LinkInfo> list = DataReaderToList(SqliteDbHelper.ExecuteReader(CommandType.Text, cmdText, prams)); // return list.Count > 0 ? list[0] : null; //} //public List<LinkInfo> GetLinkList(int type, int position, int status) //{ // string condition = " 1=1 "; // if (type != -1) // { // condition += " and [type]=" + type; // } // if (position != -1) // { // condition += " and [position]=" + position; // } // if (status != -1) // { // condition += " and [status]=" + status; // } // string cmdText = "select * from [loachs_links] where " + condition + " order by [displayorder] asc"; // return DataReaderToList(SqliteDbHelper.ExecuteReader(cmdText)); //} public List <LinkInfo> GetLinkList() { string cmdText = "select * from [loachs_links] order by [displayorder] asc,[linkid] asc"; return(DataReaderToList(SqliteDbHelper.ExecuteReader(cmdText))); }
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>=datetime('" + begindate + "')"; } if (!string.IsNullOrEmpty(enddate)) { condition += " and createdate<datetime('" + 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(SqliteDbHelper.ExecuteScalar(CommandType.Text, cmdTotalRecord)); string cmdText = SqliteDbHelper.GetPageSql("[Loachs_Posts]", "[PostId]", "*", pageSize, pageIndex, 1, condition); return(DataReaderToCommentList(SqliteDbHelper.ExecuteReader(cmdText))); }
/// <summary> /// 获取列表 /// </summary> /// <returns>IList</returns> public List <PostInfo> GetPostList() { string cmdText = "select * from [loachs_posts] order by [postid] desc"; return(DataReaderToCommentList(SqliteDbHelper.ExecuteReader(cmdText))); }