Example #1
0
 public LinkInfo GetById(object Id)
 {
     string cmdText = string.Format("select * from [{0}links] where [linkid]=@linkid ", ConfigHelper.Tableprefix);
     using(var conn = new DapperHelper().OpenConnection())
     {
         return conn.Query<LinkInfo>(cmdText, new {linkid=(int)Id }).First();
     }
 }
Example #2
0
 /// <summary>
 /// 获取评论
 /// </summary>
 /// <param name="categoryId"></param>
 /// <returns></returns>
 public CommentInfo GetById(object id)
 {
     string cmdText = string.Format("select * from [{0}comments] where [commentId] = @commentId", ConfigHelper.Tableprefix);
     using (var conn = new DapperHelper().OpenConnection())
     {
         var list = conn.Query<CommentInfo>(cmdText, new { commentId = (int)id });
         return list.ToList().Count > 0 ? list.ToList()[0] : null;
     }
 }
Example #3
0
 /// <summary>
 /// 获取用户 
 /// </summary>
 /// <param name="Id"></param>
 /// <returns></returns>
 public UserInfo GetById(object Id)
 {
     string cmdText = string.Format("select * from [{0}users] where [UserId] = @userId ", ConfigHelper.Tableprefix);
     using (var conn = new DapperHelper().OpenConnection())
     {
         var list = conn.Query<UserInfo>(cmdText, new { userId = (int)Id });
         return list.ToList().Count > 0 ? list.First() : null;
     }
 }
Example #4
0
 public TagInfo GetTagByPageName(string pagename)
 {
     string cmdText = string.Format("select * from [{0}category] where [PageName] = @PageName", ConfigHelper.Tableprefix);
     using (var conn = new DapperHelper().OpenConnection())
     {
         var list = conn.Query<TagInfo>(cmdText, new { PageName = pagename });
         return list.ToList().Count > 0 ? list.ToList()[0] : null;
     }
 }
Example #5
0
        public void TestMethod2()
        {
            string cmdText = "select  p.*  from [jq_posts] p  where  postid=340";

            using (var conn = new DapperHelper().OpenConnection(ConnectionString))
            {
                var post = conn.Query<PostInfo>(cmdText).First();

                conn.Execute("update jq_posts set updatetime =@updatetime where postid=340",new{updatetime = DateTime.Now.ToString()});
            }
        }
Example #6
0
 /// <summary>
 /// 获取分类
 /// </summary>
 /// <param name="categoryId"></param>
 /// <returns></returns>
 public CategoryInfo GetById(object id)
 {
     if ((int)id <= 0)
     {
         return null;
     }
     string cmdText = string.Format("select * from [{0}category] where [categoryid] = @categoryid", ConfigHelper.Tableprefix);
     using (var conn = new DapperHelper().OpenConnection())
     {
         var list = conn.Query<CategoryInfo>(cmdText, new { categoryid = (int)id });
         return list.ToList().Count > 0 ? list.First() : null;
     }
 }
Example #7
0
 /// <summary>
 /// 获取统计数据
 /// </summary>
 /// <returns></returns>
 public StatisticsInfo GetStatistics()
 {
     string cmdText = string.Format("select  * from [{0}sites] limit 1", ConfigHelper.Tableprefix);
     string insertText = string.Format("insert into [{0}sites] ([PostCount],[CommentCount],[VisitCount],[TagCount]) values ( '0','0','0','0')", ConfigHelper.Tableprefix);
     using(var conn = new DapperHelper().OpenConnection())
     {
        var list = conn.Query<StatisticsInfo>(cmdText,null).ToList();
        if (list.Count == 0)
        {
            conn.Execute(insertText,null);
        }
        return list.Count > 0 ? list[0] : null;
     }
 }
Example #8
0
        /// <summary>
        /// 检查别名是否重复
        /// </summary>
        /// <returns></returns>
        public virtual void CheckPageName(PostInfo post)
        {
            if (string.IsNullOrEmpty(post.PageName))
            {
                return;
            }
            while (true)
            {
                string cmdText = post.PostId == 0 ? string.Format("select count(1) from [{1}posts] where [pagename]='{0}'  ", post.PageName, ConfigHelper.Tableprefix) : string.Format("select count(1) from [{2}posts] where [pagename]='{0}'   and [postid]<>{1}", post.PageName, post.PostId, ConfigHelper.Tableprefix);

                using (var conn = new DapperHelper().OpenConnection())
                {
                    int r = conn.Query<int>(cmdText,null).First();

                    if (r == 0)
                    {
                        return;
                    }
                }
                post.PageName += "-2";
            }
        }
Example #9
0
        public void TestMethod1()
        {
            //var connection = new DapperHelper().OpenConnection();
            //var sql = @"select q.*,t.typeid,t.typename from ems_Question q,ems_Questiontype t where t.typeid=q.typeid";
            //var data = connection.Query<Question, QuestionType, Question>(sql, (quest, type) =>
            //{
            //    quest.QuestionType = type;
            //    return quest;
            //}, splitOn: "typeid,qstid");

            //foreach (Question q in data)
            //{
            //    Console.WriteLine(q.QstDescription + "   " + q.QuestionType.TypeName);
            //}
            //connection.Close();

               // string cmdText = string.Format("select p.*,u.UserId,u.UserName,u.NickName from [{0}posts] p,[{0}Users] u where p.UserId=u.UserId ", ConfigHelper.Tableprefix);
            // using (var conn = new DapperHelper().OpenConnection(ConnectionString))
            string cmdText = string.Format("select  p.*,u.UserId,u.UserName,c.CategoryId,c.CateName from [{0}posts] p,[{0}Users] u,[{0}category] c where p.PostId=@PostId and p.UserId=u.UserId and p.CategoryId=c.CategoryId", ConfigHelper.Tableprefix);

            using (var conn = new DapperHelper().OpenConnection(ConnectionString))
            {
                // var list = conn.Query<PostInfo>(cmdText, new { PostId = (int)id });
                // return list.Any() ? list.ToList()[0] : null;
                var list = conn.Query<PostInfo, UserInfo, CategoryInfo, PostInfo>(cmdText, (post, user, cate) =>
                {
                    post.Author = user;
                    post.Category = cate;
                    return post;
                }, new { PostId = 384 }, splitOn: "UserId,CategoryId");

                foreach (var postInfo in list)
                {
                    Console.WriteLine(postInfo.PostId + "  " + postInfo.Title);
                }
            }
        }
Example #10
0
        /// <summary>
        /// 新增文章
        /// </summary>
        /// <param name="post"></param>
        /// <returns></returns>
        public virtual int Insert(PostInfo post)
        {
            CheckPageName(post);
            string cmdText = string.Format(@"insert into [{0}posts]
                                (
                               [CategoryId],[TitlePic],[Title],[Summary],[PostContent],[PageName],[UserId],[CommentStatus],[CommentCount],[ViewCount],[Tag],[UrlFormat],[Template],[Recommend],[Status],[TopStatus],[HomeStatus],[PostTime],[UpdateTime]
                                )
                                values
                                (
                                @CategoryId,@TitlePic,@Title,@Summary,@PostContent,@PageName,@UserId,@CommentStatus,@CommentCount,@ViewCount,@Tag,@UrlFormat,@Template,@Recommend,@Status,@TopStatus,@HomeStatus,@PostTime,@UpdateTime
                                )", ConfigHelper.Tableprefix);

            using(var conn = new DapperHelper().OpenConnection())
            {
                conn.Execute(cmdText, post);
                return conn.Query<int>(string.Format("select  [PostId] from [{0}Posts] order by [PostId] desc limit 1", ConfigHelper.Tableprefix), null).First();
            }
        }
Example #11
0
        /// <summary>
        /// 获取相关文章
        /// </summary>
        /// <param name="postId"></param>
        /// <param name="rowCount"></param>
        /// <returns></returns>
        public virtual List<PostInfo> GetPostListByRelated(int postId, int rowCount)
        {
            string tags;

            PostInfo post = GetById(postId);

            if (post != null && post.Tag.Length > 0)
            {
                tags = post.Tag;

                tags = tags.Replace("}", "},");
                string[] idList = tags.Split(',');

                string where = idList.Where(tagId => !string.IsNullOrEmpty(tagId)).Aggregate(" (", (current, tagId) => current + string.Format("  [tags] like '%{0}%' or ", tagId));
                where += " 1=2 ) and [status]=1 and [postid]<>" + postId;

                string cmdText = string.Format("select  * from [{2}posts] where {1} order by [postid] desc limit {0}", rowCount, where, ConfigHelper.Tableprefix);

                using (var conn = new DapperHelper().OpenConnection())
                {
                    var list = conn.Query<PostInfo>(cmdText, null);
                    return list.ToList();
                }
            }
            return new List<PostInfo>();
        }
Example #12
0
        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="recordCount"></param>
        /// <param name="categoryId"></param>
        /// <param name="tagId"></param>
        /// <param name="userId"></param>
        /// <param name="recommend"></param>
        /// <param name="status"></param>
        /// <param name="topstatus"></param>
        /// <param name="PostStatus"></param>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <param name="keyword"></param>
        /// <returns></returns>
        public virtual List<PostInfo> GetPostList(int pageSize, int pageIndex, out int recordCount, string categoryId, int tagId, int userId, int recommend, int status, int topstatus, int PostStatus, string begindate, string enddate, string keyword)
        {
            string condition = " 1=1 ";

            if (categoryId != ""&&categoryId!="-1")
            {
                condition += " and categoryId in(" + 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 (PostStatus != -1)
            {
                condition += " and PostStatus=" + PostStatus;
            }

            if (!string.IsNullOrEmpty(begindate))
            {
                condition += " and PostTime>=#" + begindate + "#";
            }
            if (!string.IsNullOrEmpty(enddate))
            {
                condition += " and PostTime<#" + enddate + "#";
            }

            if (!string.IsNullOrEmpty(keyword))
            {
                condition += string.Format(" and (summary like '%{0}%' or title like '%{0}%'  )", keyword);
            }

            using (var conn = new DapperHelper().OpenConnection())
            {
                string cmdTotalRecord = "select count(1) from [" + ConfigHelper.Tableprefix + "posts] where " + condition;
                recordCount = conn.Query<int>(cmdTotalRecord,null).First();
                string cmdText = new DapperHelper().GetPageSql("[" + ConfigHelper.Tableprefix + "Posts]", "[PostId]", "*", pageSize, pageIndex, 1, condition);

                var list = conn.Query<PostInfo>(cmdText, null);
                return list.ToList();
            }
        }
Example #13
0
        /// <summary>
        /// 根据pagename获取实体
        /// </summary>
        /// <param name="pagename"></param>
        /// <returns></returns>
        public virtual PostInfo GetPostbyPageName(string pagename)
        {
            string cmdText = string.Format("select  * from [{0}posts] where [pagename] = @_pagename limit 1", ConfigHelper.Tableprefix);

            using (var conn = new DapperHelper().OpenConnection())
            {
                var list = conn.Query<PostInfo>(cmdText, new { _pagename = pagename });
                return list.ToList().Count > 0 ? list.ToList()[0] : null;
            }
        }
Example #14
0
 /// <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";
     }
     using (var conn = new DapperHelper().OpenConnection())
     {
         string cmdTotalRecord = "select count(1) from [" + ConfigHelper.Tableprefix + "comments] where " + condition;
         return conn.Query<int>(cmdTotalRecord, null).First();
     }
 }
Example #15
0
 /// <summary>
 /// 根据用户名和密码获取用户
 /// </summary>
 /// <param name="userName"></param>
 /// <param name="password"></param>
 /// <returns></returns>
 public UserInfo GetUser(string userName, string password)
 {
     string cmdText = string.Format("select * from [{0}users] where [userName] = @userName and [password]=@password ", ConfigHelper.Tableprefix);
     using (var conn = new DapperHelper().OpenConnection())
     {
         var list =conn.Query<UserInfo>(cmdText, new { userName = userName,password = password });
         return list.Count()==0?null:list.First();
     }
 }
Example #16
0
 /// <summary>
 /// 添加
 /// </summary>
 /// <param name="comment"></param>
 /// <returns></returns>
 public int Insert(CommentInfo comment)
 {
     string cmdText = string.Format(@"insert into [{0}comments](
                     PostId, ParentId,UserId,Author,Email,AuthorUrl,Contents,EmailNotify,IpAddress,CreateTime,Approved)
                      values (
                     @PostId, @ParentId,@UserId,@Author,@Email,@AuthorUrl,@Contents,@EmailNotify,@IpAddress,@CreateTime,@Approved)", ConfigHelper.Tableprefix);
     using (var conn = new DapperHelper().OpenConnection())
     {
         conn.Execute(cmdText, comment);
         return conn.Query<int>(string.Format("select  [CommentId] from [{0}comments]  order by [CommentId] desc limit 1", ConfigHelper.Tableprefix), null).First();
     }
 }
Example #17
0
 /// <summary>
 /// 获取文章评论
 /// </summary>
 /// <param name="postid"></param>
 /// <returns></returns>
 public virtual List<CommentInfo> GetCommentsByPost(int postid)
 {
     string cmdText = string.Format("select * from [{0}comments] where postid=@PostId order by createtime desc", ConfigHelper.Tableprefix);
     using (var conn = new DapperHelper().OpenConnection())
     {
         var list = conn.Query<CommentInfo>(cmdText, new {PostId = postid});
         return list!=null ? list.ToList() : null;
     }
 }
Example #18
0
 /// <summary>
 /// 添加
 /// </summary>
 /// <param name="userinfo"></param>
 /// <returns></returns>
 public int Insert(UserInfo userinfo)
 {
     string cmdText = string.Format(@" insert into [{0}users](
                         [Role],[UserName],[NickName],[Password],[Email],[SiteUrl],[AvatarUrl],[Description],[sortnum],[Status],[PostCount],[CommentCount],[CreateTime])
                         values (
                         @Role,@UserName,@NickName,@Password,@Email,@SiteUrl,@AvatarUrl,@Description,@SortNum,@Status, @PostCount,@CommentCount,@CreateTime )", ConfigHelper.Tableprefix);
     using(var conn = new DapperHelper().OpenConnection())
     {
         conn.Execute(cmdText, new {
             Role = userinfo.Role,
             UserName = userinfo.UserName,
             NickName = userinfo.NickName,
             Password = userinfo.Password,
             Email = userinfo.Email,
             SiteUrl = userinfo.SiteUrl,
             AvatarUrl = userinfo.AvatarUrl,
             Description = userinfo.Description,
             SortNum = userinfo.SortNum,
             Status = userinfo.Status,
             PostCount = userinfo.PostCount,
             CommentCount = userinfo.CommentCount,
             CreateTime = userinfo.CreateTime
         });
         return conn.Query<int>(string.Format("select  [UserId] from [{0}users]  order by [UserId] desc limit 1", ConfigHelper.Tableprefix), null).First();
     }
 }
Example #19
0
        /// <summary>
        /// 获取实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public virtual PostInfo GetById(object id)
        {
            //string cmdText = string.Format("select  p.PostId as Id,p.Title,p.CategoryId,p.TitlePic,p.PageName,u.UserId,u.UserName,u.Role,u.NickName from [{0}posts] p,[{0}Users] u where p.UserId=u.UserId limit 1", ConfigHelper.Tableprefix);
            //using (var conn = new DapperHelper().OpenConnection())
            //{
            //   // var list = conn.Query<PostInfo>(cmdText, new { PostId = (int)id });
            //   // return list.Any() ? list.ToList()[0] : null;
            //    var list = conn.Query<PostInfo, UserInfo, PostInfo>(cmdText, (post, user) =>
            //    {
            //        post.Author = user;
            //        return post;
            //    }, splitOn: "UserId");

            //    return list.Any() ? list.ToList()[0] : null;
            //}

             string cmdText = string.Format("select  p.*,u.*,c.* from [{0}posts] p,[{0}Users] u,[{0}category] c where p.PostId=@PostId and p.UserId=u.UserId and p.CategoryId=c.CategoryId", ConfigHelper.Tableprefix);
            using (var conn = new DapperHelper().OpenConnection())
            {
                var list = conn.Query<PostInfo, UserInfo, CategoryInfo, PostInfo>(cmdText, (post, user, cate) =>
                {
                    post.Author = user;
                    post.Category = cate;
                    return post;
                }, new { PostId = (int)id }, splitOn: "UserId,CategoryId");
                return list.Any() ? list.ToList()[0] : null;
            }
        }
Example #20
0
        /// <summary>
        /// 检查别名是否重复
        /// </summary>
        /// <param name="cate"></param>
        /// <returns></returns>
        private void CheckPageName(TagInfo cate)
        {
            while (true)
            {
                string cmdText = cate.TagId == 0 ? string.Format("select count(1) from [{2}category] where [PageName]='{0}' and [type]={1}", cate.PageName, (int)CategoryType.Tag, ConfigHelper.Tableprefix) : string.Format("select count(1) from [{3}category] where [PageName]='{0}'  and [type]={1} and [categoryid]<>{2}", cate.PageName, (int)CategoryType.Tag, cate.TagId, ConfigHelper.Tableprefix);
                using (var conn = new DapperHelper().OpenConnection())
                {
                    int r = conn.Query<int>(cmdText, null).First();

                    if (r == 0)
                    {
                        return;
                    }
                }
                cate.PageName += "-2";
            }
        }
Example #21
0
        public int Insert(TagInfo tag)
        {
            CheckPageName(tag);

            string cmdText = string.Format(@"insert into [{0}category]
                            (
                            [Type],[ParentId],[CateName],[PageName],[Description],[SortNum],[PostCount],[CreateTime]
                            )
                            values
                            (
                            @Type,@ParentId,@CateName,@PageName,@Description,@SortNum,@PostCount,@CreateTime
                            )", ConfigHelper.Tableprefix);
            using (var conn = new DapperHelper().OpenConnection())
            {
                conn.Execute(cmdText, new
                {
                    Type = (int)CategoryType.Tag,
                    ParentId = 0,
                    CateName = tag.CateName,
                    PageName = tag.PageName,
                    Description = tag.Description,
                    SortNum = tag.SortNum,
                    PostCount = tag.PostCount,
                    CreateTime = tag.CreateTime
                });
                return conn.Query<int>(string.Format("select  [categoryid] from [{0}category] order by [categoryid] desc limit 1", ConfigHelper.Tableprefix), null).First();
            }
        }
Example #22
0
 /// <summary>
 /// 获取文章归档统计
 /// </summary>
 /// <returns></returns>
 public virtual List<ArchiveInfo> GetArchive()
 {
     string cmdText = string.Format("select format(PostTime, 'yyyymm') as [date] ,  count(*) as [count] from [{0}posts] where [status]=1 and [PostStatus]=0  group by  format(PostTime, 'yyyymm')  order by format(PostTime, 'yyyymm') desc", ConfigHelper.Tableprefix);
     using(var conn = new DapperHelper().OpenConnection())
     {
       return  conn.Query<ArchiveInfo>(cmdText,null).ToList();
     }
 }
Example #23
0
        /// <summary>
        /// insert link
        /// </summary>
        /// <param name="link"></param>
        /// <returns></returns>
        public int Insert(LinkInfo link)
        {
            string cmdText = string.Format(@"insert into [{0}links]
                            (
                            [type],[linkname],[linkurl],[position],[target],[description],[sortnum],[status],[createtime]
                            )
                            values
                            (
                            @type,@linkname,@linkurl,@position,@target,@description,@sortnum,@status,@createtime
                            )", ConfigHelper.Tableprefix);

            using (var conn = new DapperHelper().OpenConnection())
            {
                conn.Execute(cmdText, new
                {
                    Type = link.Type,
                    LinkName = link.LinkName,
                    LinkUrl = link.LinkUrl,
                    Postion = link.Position,
                    Target = link.Target,
                    Description = link.Description,
                    SortNum = link.SortNum,
                    Status = link.Status,
                    CreateTime = link.CreateTime
                });
                return conn.Query<int>(string.Format("select [linkid] from [{0}links]  order by [linkid] desc  limit 1", ConfigHelper.Tableprefix), null).First();
            }
        }
Example #24
0
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="postId"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="totalRecord"></param>
        /// <param name="order"></param>
        /// <param name="parentId"></param>
        /// <param name="approved"></param>
        /// <param name="emailNotify"></param>
        /// <param name="keyword"></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);
            }

            using (var conn = new DapperHelper().OpenConnection())
            {
                string cmdTotalRecord = "select count(1) from [" + ConfigHelper.Tableprefix + "comments] where " + condition;
                totalRecord = conn.Query<int>(cmdTotalRecord, null).First();
                string cmdText = new DapperHelper().GetPageSql("[" + ConfigHelper.Tableprefix + "comments]", "[CommentId]", "*", pageSize, pageIndex, 1, condition);

                var list = conn.Query<CommentInfo>(cmdText, null);
                return list.ToList();
            }
        }
Example #25
0
 /// <summary>
 /// 根据用户名获取用户 
 /// </summary>
 /// <param name="userName"></param>
 /// <returns></returns>
 public UserInfo GetUserByName(string userName)
 {
     string cmdText = string.Format("select * from [{0}users] where [userName] = @userName ", ConfigHelper.Tableprefix);
     using(var conn = new DapperHelper().OpenConnection())
     {
         var list = conn.Query<UserInfo>(cmdText, new {userName = userName});
         if (list.Any())
         {
             return list.First();
         }
         return null;
     }
 }