Exemplo n.º 1
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>());
        }
Exemplo n.º 2
0
        /// <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)));
        }
Exemplo n.º 3
0
        /// <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);
        }
Exemplo n.º 4
0
        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);
        }
Exemplo n.º 5
0
        //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)));
        }
Exemplo n.º 6
0
        /// <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);
        }
Exemplo n.º 7
0
        /// <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);
        }
Exemplo n.º 8
0
        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);
        }
Exemplo n.º 9
0
        /// <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)));
        }
Exemplo n.º 10
0
        ///// <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);
        }
Exemplo n.º 11
0
        ///// <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)));
        }
Exemplo n.º 12
0
        //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)));
        }
Exemplo n.º 13
0
        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)));
        }
Exemplo n.º 14
0
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns>IList</returns>
        public List <PostInfo> GetPostList()
        {
            string cmdText = "select * from [loachs_posts] order by [postid] desc";

            return(DataReaderToCommentList(SqliteDbHelper.ExecuteReader(cmdText)));
        }