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 top {0} * from Loachs_Posts where {1} order by postid desc", rowCount, where); string cmdText = string.Format("select * from Loachs_Posts where {1} order by postid desc limit 0,{0}", rowCount, where); return(DataReaderToCommentList(MYSQLHelper.ExecuteReader(cmdText))); } return(new List <PostInfo>()); }
public List <TagInfo> GetTagList() { string condition = " type=" + (int)TermType.Tag; string cmdText = "select * from Loachs_Terms where " + condition + " order by displayorder asc ,termid asc"; return(DataReaderToList(MYSQLHelper.ExecuteReader(cmdText))); }
/// <summary> /// 获取实体 /// </summary> /// <param name="commentId"></param> /// <returns></returns> public CommentInfo GetComment(int commentId) { string cmdText = "select * from Loachs_Comments where commentId = @commentId"; MySqlParameter[] prams = { MYSQLHelper.MakeInParam("@commentId", MySqlDbType.Int32, 4, commentId) }; List <CommentInfo> list = DataReaderToCommentList(MYSQLHelper.ExecuteReader(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(MYSQLHelper.ExecuteScalar(CommandType.Text, cmdTotalRecord)); // string cmdText = MYSQLHelper.GetPageSql("[Loachs_Terms]", "[termid]", "*", pageSize, pageIndex, 1, condition); // return DataReaderToList(MYSQLHelper.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(MYSQLHelper.ExecuteReader(cmdText))); }
public TagInfo GetTag(int tagId) { string cmdText = "select * from Loachs_Terms where termid = @termid"; MySqlParameter[] prams = { MYSQLHelper.MakeInParam("@termid", MySqlDbType.Int32, 4, tagId) }; List <TagInfo> list = DataReaderToList(MYSQLHelper.ExecuteReader(CommandType.Text, 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 top 1 * from [Loachs_Posts] where [slug] = @slug"; string cmdText = "select * from Loachs_Posts where slug = @slug limit 0,1"; MySqlParameter[] prams = { MYSQLHelper.MakeInParam("@slug", MySqlDbType.VarChar, 200, slug) }; List <PostInfo> list = DataReaderToCommentList(MYSQLHelper.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 top 1 * from [Loachs_Posts] where [PostId] = @PostId"; string cmdText = "select * from Loachs_Posts where PostId = @PostId limit 0,1"; MySqlParameter[] prams = { MYSQLHelper.MakeInParam("@PostId", MySqlDbType.Int32, 4, postid) }; List <PostInfo> list = DataReaderToCommentList(MYSQLHelper.ExecuteReader(cmdText, prams)); return(list.Count > 0 ? list[0] : null); }
public StatisticsInfo GetStatistics() { //string cmdText = "select top 1 * from [Loachs_Sites]"; string cmdText = "select * from Loachs_Sites limit 0,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(MYSQLHelper.ExecuteReader(cmdText)); if (list.Count == 0) { MYSQLHelper.ExecuteNonQuery(insertText); } list = DataReaderToList(MYSQLHelper.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(MYSQLHelper.ExecuteScalar(CommandType.Text, cmdTotalRecord)); // throw new Exception(cmdTotalRecord); string cmdText = MYSQLHelper.GetPageSql("Loachs_Comments", "CommentId", "*", pageSize, pageIndex, order, condition); return(DataReaderToCommentList(MYSQLHelper.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"; // MySqlParameter[] prams = { // MYSQLHelper.MakeInParam("@slug",MySqlDbType.VarChar,200,slug), // }; // return StringHelper.ObjectToInt(MYSQLHelper.ExecuteScalar(CommandType.Text, cmdText, prams)); //} public List <ArchiveInfo> GetArchive() { //string cmdText = "select format(createdate, 'yyyymm') as [date] , count(*) as [count] from [Loachs_Posts] where [status]=1 and [hidestatus]=0 group by format(createdate, 'yyyymm') order by format(createdate, 'yyyymm') desc"; string cmdText = "select DATE_FORMAT(createdate, '%Y%m') as date , count(*) as count from Loachs_Posts where status=1 and hidestatus=0 group by DATE_FORMAT(createdate, '%Y%m') order by DATE_FORMAT(createdate, '%Y%m') desc"; List <ArchiveInfo> list = new List <ArchiveInfo>(); using (MySqlDataReader read = MYSQLHelper.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"; // MySqlParameter[] prams = { // MYSQLHelper.MakeInParam("@userName",MySqlDbType.VarChar,50,userName), // MYSQLHelper.MakeInParam("@password",MySqlDbType.VarChar,50,password), // }; // List<UserInfo> list = DataReaderToUserList(MYSQLHelper.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(MYSQLHelper.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>=#" + 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(MYSQLHelper.ExecuteScalar(CommandType.Text, cmdTotalRecord)); string cmdText = MYSQLHelper.GetPageSql("Loachs_Posts", "PostId", "*", pageSize, pageIndex, 1, condition); return(DataReaderToCommentList(MYSQLHelper.ExecuteReader(cmdText))); }
/// <summary> /// 获取列表 /// </summary> /// <returns>IList</returns> public List <PostInfo> GetPostList() { string cmdText = "select * from Loachs_Posts order by postid desc"; return(DataReaderToCommentList(MYSQLHelper.ExecuteReader(cmdText))); }
//public LinkInfo GetLink(int linkid) //{ // string cmdText = "select * from [Loachs_Links] where [linkid] = @linkid"; // MySqlParameter[] prams = { // MYSQLHelper.MakeInParam("@linkid",MySqlDbType.Int32,4,linkid) // }; // List<LinkInfo> list = DataReaderToList(MYSQLHelper.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(MYSQLHelper.ExecuteReader(cmdText)); //} public List <LinkInfo> GetLinkList() { string cmdText = "select * from Loachs_Links order by displayorder asc,linkid asc"; return(DataReaderToList(MYSQLHelper.ExecuteReader(cmdText))); }