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>()); } }
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)); } }
/// <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); } }
/// <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); } }
/// <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)); } }
/// <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)); } }
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 })); } }
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, })); } }
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)); } }
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()); } }
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>()); } }
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); } }
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 })); } }
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>()); } }