Ejemplo n.º 1
0
        public List <dynamic> GetAllPersonalPic(PicManage picManage, PageModel pm)
        {
            StringBuilder sbSql      = new StringBuilder(@"SELECT * FROM dbo.Image WHERE UserCode=@UserCode");
            var           dyParamter = new DynamicParameters();

            dyParamter.Add("UserCode", picManage.UserCode);
            dyParamter.Add("PageIndex", pm.PageIndex);
            dyParamter.Add("PageSize", pm.PageSize);
            string querySql = string.Format("WITH query AS ({0}) ", sbSql);
            string countSql = querySql + " SELECT COUNT(*) FROM query";

            using (var conn = AdoConfig.GetDBConnection())
            {
                try
                {
                    pm.TotalCount = conn.Query <int>(countSql, dyParamter).FirstOrDefault();
                    if (pm.TotalCount > 0)
                    {
                        string pageSql = querySql + " SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY IsDel ASC) AS RowNum, * FROM query ) t WHERE t.RowNum > (@PageIndex -1) * @PageSize AND t.RowNum <= @PageIndex * @PageSize";
                        return(conn.Query <dynamic>(pageSql, dyParamter).ToList());
                    }
                }
                catch (Exception ex)
                {
                    new SendWarnningEMail().SendEMail(ex.ToString());
                }
                return(new List <dynamic>());
            }
        }
Ejemplo n.º 2
0
        public int UpdateArticle(Article article, UserInfoForCookie user)
        {
            StringBuilder sbsql      = new StringBuilder(@"UPDATE  dbo.Article
        SET ");
            var           dyParamter = new DynamicParameters();

            dyParamter.Add("UserCode", user.UserCode);
            dyParamter.Add("ArticleNo", article.ArticleNo);
            if (article.Title != null)
            {
                sbsql.Append("Title=@Title");
                dyParamter.Add("Title", article.Title);
            }
            if (article.Content != null)
            {
                sbsql.Append("Content=@Content");
                dyParamter.Add("Content", article.Content);
            }
            if (article.ImageUrl != null)
            {
                sbsql.Append("ImageUrl=@ImageUrl");
                dyParamter.Add("ImageUrl", article.ImageUrl);
            }
            sbsql.Append(@"
                Modifier = @UserCode ,
                ModDate = GETDATE()
        WHERE   ArticleNo = @ArticleNo");
            using (var conn = AdoConfig.GetDBConnection())
            {
                return(conn.Execute(sbsql.ToString(), dyParamter));
            }
        }
Ejemplo n.º 3
0
        /// <summary>
        /// 判断该用户名是否被占用
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool GetUserByName(string name)
        {
            string sql = @"SELECT  COUNT(*)
FROM    dbo.[User]
WHERE   UserName = @UserName
        AND IsDel = 0;";

            using (var conn = AdoConfig.GetDBConnection())
            {
                return(conn.Query(sql, new { UserName = name }).FirstOrDefault() > 0 ? true : false);
            }
        }
Ejemplo n.º 4
0
        /// <summary>
        /// 判断该邮箱是否已经注册
        /// </summary>
        /// <param name="e_mail"></param>
        /// <returns></returns>
        public bool GetUserByEmail(string e_mail)
        {
            string sql = @"SELECT  COUNT(*)
FROM    dbo.[User]
WHERE   E_Mail = @E_Mail
        AND IsDel = 0;";

            using (var conn = AdoConfig.GetDBConnection())
            {
                return(conn.Query(sql, new { E_Mail = e_mail }).FirstOrDefault() > 0 ? true : false);
            }
        }
Ejemplo n.º 5
0
        /// <summary>
        /// 修改用户名
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int ModifyUserName(U_User user)
        {
            string sql = @"UPDATE  dbo.[User]
SET     UserName = @UserName
WHERE   UserCode = @UserCode
        AND IsDel = 0";

            using (var conn = AdoConfig.GetDBConnection())
            {
                return(conn.Execute(sql, user));
            }
        }
Ejemplo n.º 6
0
        /// <summary>
        /// 重置密码
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int ResetUserPassword(U_User user)
        {
            string sql = @"UPDATE  dbo.[User]
SET     PassWord = @PassWord ,
        ModDate = GETDATE()
WHERE   E_Mail = @E_Mail
        AND IsDel = 0";

            using (var conn = AdoConfig.GetDBConnection())
            {
                return(conn.Execute(sql, user));
            }
        }
Ejemplo n.º 7
0
        public int DeleteArticle(Article article, UserInfoForCookie user)
        {
            string sql = @"UPDATE  dbo.Article
        SET     IsDel = 1 ,
                Modifier = @UserCode ,
                ModDate = GETDATE()
        WHERE   ArticleNo = @ArticleNo;";

            using (var conn = AdoConfig.GetDBConnection())
            {
                return(conn.Execute(sql, new { article.ArticleNo, user.UserCode }));
            }
        }
Ejemplo n.º 8
0
        public int SavePicToDB(UserImage userImage)
        {
            string sql = @"UPDATE  dbo.Image
SET IsDel = 1,ModDate = GETDATE()
WHERE UserCode = @UserCode AND IsDel=0";

            sql += @"UPDATE  dbo.Image
SET IsDel = 0,ModDate = GETDATE()
WHERE UserCode = @UserCode AND ImageCode=@ImageCode
;";
            using (var conn = AdoConfig.GetDBConnection())
            {
                return(conn.Execute(sql, new { ImageCode = userImage.ImageCode, ImageSrc = userImage.ImageSrc, UserCode = userImage.UserCode, }));
            }
        }
Ejemplo n.º 9
0
        public int Register(U_User user)
        {
            string sql = @"INSERT dbo.[User]
        ( UserName ,
          PassWord ,
          UserCode ,
          E_Mail          
        )
VALUES  ( @UserName ,
          @PassWord , 
          @UserCode  , 
          @E_Mail
        )";

            using (var conn = AdoConfig.GetDBConnection())
            {
                return(conn.Execute(sql, user));
            }
        }
Ejemplo n.º 10
0
        public U_User UserLogin(U_User user)
        {
            string sql = @"
SELECT  U.* ,
        I.ImageSrc AS ImageUrl
FROM    dbo.[User] U
        LEFT JOIN dbo.Image I ON I.UserCode = U.UserCode
WHERE   ( UserName = @UserName
          OR E_Mail = @E_Mail
        )
        AND PassWord = @PassWord
        AND U.IsDel = 0
        AND I.IsDel = 0;";

            using (var conn = AdoConfig.GetDBConnection())
            {
                return(conn.Query <U_User>(sql, new { user.UserName, user.PassWord, user.E_Mail }).FirstOrDefault());
            }
        }
Ejemplo n.º 11
0
        public List <dynamic> GetFollowMessage(PageModel pm)
        {
            StringBuilder sbSql      = new StringBuilder(@"SELECT TOP 2000
        pro.PropertyID ,
        pro.PropertyCode ,
        fol.FollowID ,
        fol.FollowCode ,
        fol.FollowDate ,
        fol.FollowType ,
        fol.DeptName + '.' + fol.EmpName AS EmpName ,
        pro.PropertyNo ,
        est.EstateName ,
        pro.TradeInt ,
        fol.Content ,
        fol.AlertDate ,
        fol.AlertInfo ,
        fol.AlertType ,
        fol.FlagAlerted
FROM    Property AS pro ( NOLOCK )
        LEFT JOIN Follow AS fol ( NOLOCK ) ON fol.PropertyCode = pro.PropertyCode
        LEFT JOIN Estate AS est ( NOLOCK ) ON pro.EstateCode = est.EstateCode
WHERE   fol.FlagDeleted = 0
        AND fol.FlagTrashed = 0
        AND pro.IsDel = 0 
");
            var           dyParamter = new DynamicParameters();

            dyParamter.Add("PageIndex", pm.PageIndex);
            dyParamter.Add("PageSize", pm.PageSize);
            string querySql = string.Format("WITH query AS ({0}) ", sbSql);
            string countSql = querySql + " SELECT COUNT(*) FROM query";

            using (var conn = AdoConfig.GetDBConnection())
            {
                pm.TotalCount = conn.Query <int>(countSql, dyParamter).FirstOrDefault();
                if (pm.TotalCount > 0)
                {
                    string pageSql = querySql + " SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY FollowID ASC) AS RowNum, * FROM query ) t WHERE t.RowNum > (@PageIndex -1) * @PageSize AND t.RowNum <= @PageIndex * @PageSize";
                    return(conn.Query <dynamic>(pageSql, dyParamter).ToList());
                }
                return(new List <dynamic>());
            }
        }
Ejemplo n.º 12
0
        public void SavePicToDB(string UserCode, List <string> urlList)
        {
            StringBuilder     InsertSql    = new StringBuilder();
            DynamicParameters dyParameters = new DynamicParameters();

            dyParameters.Add("UserCode", UserCode);
            for (int i = 0; i < urlList.Count; i++)
            {
                InsertSql.AppendFormat(@"INSERT  dbo.Image
        ( ImageCode, ImageSrc, UserCode,IsDel )
VALUES  ( @ImageCode{0}, @ImageSrc{0}, @UserCode,1 );", i);
                dyParameters.Add("ImageSrc" + i, urlList[i]);
                dyParameters.Add("ImageCode" + i, new CreateEmpCode().GetRandomEmpCode(2, 4));
            }
            using (var conn = AdoConfig.GetDBConnection())
            {
                conn.Execute(InsertSql.ToString(), dyParameters);
            }
        }
Ejemplo n.º 13
0
        public int InsertArticle(Article article, UserInfoForCookie user)
        {
            string sql = @" INSERT dbo.Article
                ( Title ,
                  ArticleNo,
                  Creator ,
                  CreateDate ,
                  Content ,
                  ImageUrl
                )
        VALUES  ( @Title , 
                  @ArticleNo,
                  @UserCode , 
                  GETDATE() , 
                  @Content ,
                  @ImageUrl
                )";

            using (var conn = AdoConfig.GetDBConnection())
            {
                return(conn.Execute(sql, new { article.Title, article.ArticleNo, article.Content, article.ImageUrl, user.UserCode }));
            }
        }
Ejemplo n.º 14
0
        public List <dynamic> GetUserArticle(ArticleModel article, PageModel pm)
        {
            //            string sql = @"SELECT  * ,
            //        SrcString = STUFF(( SELECT   ',' + img.ImageSrc
            //                               FROM     dbo.Image img
            //                               WHERE    img.ImageCode = art.ImageCode
            //                             FOR
            //                               XML PATH('')
            //                             ), 1, 1, '')
            //FROM    dbo.Article art;";
            //            StringBuilder sbSql = new StringBuilder(@"SELECT  A.* ,
            //        I.ImageSrc
            //FROM    dbo.Article A
            //        LEFT JOIN dbo.Image I
            //        ON I.ImageCode = A.ImageCode
            //WHERE   A.IsDel = 0");
            StringBuilder sbSql      = new StringBuilder(@"SELECT  A.* 
FROM    dbo.Article A
");
            var           dyParamter = new DynamicParameters();

            dyParamter.Add("PageIndex", pm.PageIndex);
            dyParamter.Add("PageSize", pm.PageSize);
            string querySql = string.Format("WITH query AS ({0}) ", sbSql);
            string countSql = querySql + " SELECT COUNT(*) FROM query";

            using (var conn = AdoConfig.GetDBConnection())
            {
                pm.TotalCount = conn.Query <int>(countSql, dyParamter).FirstOrDefault();
                if (pm.TotalCount > 0)
                {
                    string pageSql = querySql + " SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS RowNum, * FROM query ) t WHERE t.RowNum > (@PageIndex -1) * @PageSize AND t.RowNum <= @PageIndex * @PageSize";
                    return(conn.Query <dynamic>(pageSql, dyParamter).ToList());
                }
                return(new List <dynamic>());
            }
        }