public IEnumerable <Entity.blog_tb_tag> GetTags(string blogID) { string sql = "select blog_tb_tag.*,categoryDisplay from blog_tb_tag LEFT JOIN blog_tb_category on blog_tb_category.categoryID=blog_tb_tag.categoryID where blog_tb_tag.blogID=@blogID"; DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@blogID", blogID)); return(ObjectHelper.DataTableToModel <blog_tb_tag>(dt)); }
public List <Entity.blog_tb_slider> Query(string blogID) { string sql = "select * from blog_tb_slider where BlogID=@BlogID order by OrderWeight DESC limit 0,5"; DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@BlogID", blogID)); return(ObjectHelper.DataTableToModel <blog_tb_slider>(dt)); }
public IEnumerable <Entity.blog_tb_topic> GetTopic(string blogID) { string sql = "select blog_tb_topic.*,ifnull(ArticleCount,0) ArticleCount from blog_tb_topic LEFT JOIN (select count(*) ArticleCount ,topicID from blog_tb_article GROUP BY topicID) table1 on table1.topicID=blog_tb_topic.topicID where blogID=@blogID"; DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@blogID", blogID)); return(ObjectHelper.DataTableToModel <blog_tb_topic>(dt)); }
public blog_tb_article_content GetArticleContent(string articleID) { string sql = "select * from blog_tb_article_content where articleID=@articleID"; DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@articleID", articleID)); return(ObjectHelper.DataTableToSingleModel <blog_tb_article_content>(dt)); }
public IList <blog_tb_Photo> Query(string albumID) { string sql = "select * from blog_tb_Photo where AlbumID=@AlbumID and IsDelete=0"; DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@AlbumID", albumID)); return(FYJ.ObjectHelper.DataTableToModel <blog_tb_Photo>(dt)); }
public DataTable GetMenuTable(string blogID) { string sql = "select *,(select count(1) from blog_tb_menu where parentID=table1.menuID ) as cc from blog_tb_menu as table1 where blogID=@blogID order by menuOrder desc"; DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@blogID", blogID)); return(dt); }
public IEnumerable <blog_tb_tag> GetArticleTags(string articleID) { string sql = "select * from blog_tb_tag where tagID in ( select tagID from blog_tb_tagArticle where articleID=@articleID)"; DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@articleID", articleID)); return(ObjectHelper.DataTableToModel <blog_tb_tag>(dt)); }
public int SaveTemp(string userID, string content) { if (!String.IsNullOrWhiteSpace(content)) { string sql = "select ID,TalkContent from blog_tb_Talk where UserID=@UserID and IsTemp=1 limit 0,1"; DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@UserID", userID)); if (dt.Rows.Count > 0) { blog_tb_Talk entity = GetEntity(dt.Rows[0]["ID"].ToString()); entity.TalkContent = content; entity.TalkDatetime = DateTime.Now; entity.ADD_DATE = DateTime.Now; entity.UPDATE_DATE = DateTime.Now; Update(entity); } else { blog_tb_Talk entity = new blog_tb_Talk(); entity.UserID = userID; entity.ID = Guid.NewGuid().ToString("N"); entity.TalkContent = content; entity.TalkDatetime = DateTime.Now; entity.ADD_DATE = DateTime.Now; entity.UPDATE_DATE = DateTime.Now; entity.IsTemp = true; Insert(entity); } } return(1); }
public int UpdateComment(string articleID, int add) { string addstr = (add >= 0 ? "+" + add : add.ToString()); string sql = "update blog_tb_article_extend set articleCommentTimes=articleCommentTimes" + addstr + " where articleID=@articleID"; return(DbInstance.ExecuteSql(sql, DbInstance.CreateParameter("@articleID", articleID))); }
public bool IsReplyed(string articleID, string userID) { string sql = "select 1 from blog_tb_comment where articleID=@articleID and userID=@userID"; return(DbInstance.Exists(sql, DbInstance.CreateParameter("@articleID", articleID) , DbInstance.CreateParameter("@userID", userID))); }
public new int Delete(string commentID) { try { DbInstance.BeginTran(); string sql = "select articleID,articleCommentTimes from blog_view_article where articleID=(select articleID from blog_tb_comment where commentID=@commentID)"; DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@commentID", commentID)); if (dt.Rows.Count > 0) { int articleCommentTimes = Convert.ToInt32(dt.Rows[0]["articleCommentTimes"]); articleCommentTimes = Math.Max(articleCommentTimes - 1, 0); sql = "update blog_tb_article_extend set articleCommentTimes=" + articleCommentTimes + " where articleID=@articleID"; DbInstance.ExecuteSql(sql, DbInstance.CreateParameter("@articleID", dt.Rows[0]["articleID"])); } base.Delete(commentID); DbInstance.Commit(); return(1); } catch { DbInstance.Rollback(); throw; } }
public IList <SelectModel> QueryAlbumSelect(string userID) { string sql = @"SELECT ID Value,Display Text FROM blog_tb_Album where UserID=@UserID"; DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@UserID", userID)); return(FYJ.ObjectHelper.DataTableToModel <SelectModel>(dt)); }
public List <blog_tb_Talk> Query(string userID) { string sql = "select * from blog_tb_Talk where UserID=@UserID and IsTemp=0 and IsDisabled=0 order by TalkDatetime DESC limit 0,100"; DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@UserID", userID)); return(ObjectHelper.DataTableToModel <blog_tb_Talk>(dt)); }
public DataTable Query(string albumID) { string sql = "select * from blog_tb_Photo where AlbumID=@AlbumID and IsDelete=0"; DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@AlbumID", albumID)); return(dt); }
public System.Collections.Generic.IEnumerable <Entity.blog_tb_topic> GetTopic(string blogID) { string sql = "select * from blog_tb_topic where blogID=@blogID"; DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@blogID", blogID)); return(FYJ.Common.ObjectHelper.DataTableToModel <blog_tb_topic>(dt)); }
//public override object NewID() //{ // int max = DbInstance.GetInt("select max(articleID) from blog_tb_article"); // return base.NewID(); //} public int UpdateExtend(string articleID, string lastOpenIP, string lastOpenUserID) { string sql = "select * from blog_tb_article_extend where articleID=@articleID"; if (DbInstance.Exists(sql, DbInstance.CreateParameter("@articleID", articleID))) { sql = " update blog_tb_article_extend set lastOpenDatetime=GETDATE(),lastOpenIP=@ip,lastOpenUserID=@userID,UPDATE_DATE=GETDATE(),articleClickTimes=articleClickTimes+1 where articleID=@articleID"; DbInstance.ExecuteSql(sql , DbInstance.CreateParameter("@articleID", articleID) , DbInstance.CreateParameter("@ip", lastOpenIP) , DbInstance.CreateParameter("@userID", lastOpenUserID)); } else { blog_tb_article_extend entity = new blog_tb_article_extend(); entity.articleClickTimes = 1; entity.articleCommentTimes = 0; entity.articleID = Convert.ToInt32(articleID); entity.extendID = Guid.NewGuid().ToString("N"); entity.lastOpenDatetime = DateTime.Now; entity.UPDATE_DATE = DateTime.Now; entity.lastOpenIP = lastOpenIP; entity.lastOpenUserID = lastOpenUserID; FYJ.Data.Entity.EntityHelper <blog_tb_article_extend> .Insert(entity, "blog_tb_article_extend", "extendID", true, DbInstance); } return(1); }
public IEnumerable <Entity.blog_tb_tag> GetTags(string blogID) { string sql = "select * from blog_tb_tag where blogID=@blogID"; DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@blogID", blogID)); return(FYJ.Common.ObjectHelper.DataTableToModel <blog_tb_tag>(dt)); }
public System.Data.DataTable GetPhotoBySha1(string sha1, string userID) { DataTable dt = DbInstance.GetDataTable("select * from blog_tb_Photo where Sha1=@Sha1 and UserID=@UserID " , DbInstance.CreateParameter("@Sha1", sha1) , DbInstance.CreateParameter("@UserID", userID)); return(dt); }
public IList <blog_tb_Photo> GetPhotoBySha1(string sha1, string userID) { DataTable dt = DbInstance.GetDataTable("select * from blog_tb_Photo where Sha1=@Sha1 and UserID=@UserID " , DbInstance.CreateParameter("@Sha1", sha1) , DbInstance.CreateParameter("@UserID", userID)); return(FYJ.ObjectHelper.DataTableToModel <blog_tb_Photo>(dt)); }
public blog_tb_article GetSingle(int articleID, string select = "*") { DataTable dt = DbInstance.GetDataTable("select " + select + " from blog_tb_article where articleID=@articleID" , DbInstance.CreateParameter("@articleID", articleID)); blog_tb_article model = FYJ.Data.Util.DataTableHelper.DataTableToModel <blog_tb_article>(dt).FirstOrDefault(); return(model); }
/// <summary> /// 获取相关主题文章 /// </summary> /// <param name="articleID"></param> /// <returns></returns> public DataTable GetArticleTopicList(string articleID) { string sql = @"select row_number() over(order by articleDatetime desc) as rownum,articleID,articleTitle,articleTitle2,articleUrl,topicDisplay from blog_view_article where topicID =(select topicID from blog_view_article where articleID=@articleID) and articleID <>@articleID and isDisabled=0"; DataTable dt = this.DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@articleID", articleID)); return(dt); }
public long GetArticleUpdateLong(string articleID) { //不用EF 因为有缓存 //long date = GetEntities().Where(c => c.articleID == articleID).Select(c => c.UPDATE_DATE).FirstOrDefault().ToFileTimeUtc(); object obj = DbInstance.GetObject("select UPDATE_DATE from blog_tb_article where articleID=@articleID" , DbInstance.CreateParameter("@articleID", articleID)); long date = Convert.ToInt64(Convert.ToDateTime(obj).ToString("yyyyMMddHHmmss")); return(date); }
public int GetNoReadCommentCount(string blogID) { string sql = @"select COUNT(*) from blog_tb_comment inner join blog_view_article on blog_view_article.articleID=blog_tb_comment.articleID left join blog_tb_article_extend on blog_tb_article_extend.articleID=blog_view_article.articleID where blogID=@blogID and blog_tb_comment.ADD_DATE>=blog_tb_article_extend.lastOpenDatetime"; int count = DbInstance.GetInt(sql, DbInstance.CreateParameter("@blogID", blogID)); return(count); }
public int ChangeArticle(string ids, string categoryID) { string tmp = ""; foreach (string s in ids.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) { string t = s.Trim('\''); tmp += "'" + t + "',"; } tmp = tmp.TrimEnd(','); string sql = "update blog_tb_article set categoryID=@categoryID where articleID in (" + tmp + ")"; return DbInstance.ExecuteSql(sql, DbInstance.CreateParameter("@categoryID", categoryID)); }
public IList <blog_tb_Album> QueryAlbum(ref GridPager pager) { int count = 0; List <IDataParameter> plist = null; if (pager.Parameters != null && pager.Parameters.Count > 0) { plist = new List <IDataParameter>(); foreach (string key in pager.Parameters.Keys) { plist.Add(DbInstance.CreateParameter("@" + key.TrimStart('@'), pager.Parameters[key])); } } string table = @" (SELECT a.ID, a.UserID, a. NAME, a.Display, a.ADD_DATE, a.Permission, a.UPDATE_DATE, IFNULL(c.cc, 0) AS PhotoCount, CASE WHEN IFNULL(CoverUrl, '') = '' THEN p.ThumbUrl ELSE CoverUrl END AS CoverUrl FROM blog_tb_Album AS a LEFT JOIN ( SELECT COUNT(*) AS cc,AlbumID FROM blog_tb_Photo GROUP BY AlbumID ) AS c ON c.AlbumID = a.ID LEFT OUTER JOIN ( SELECT AlbumID, MIN(ThumbUrl) AS ThumbUrl FROM blog_tb_Photo AS blog_tb_Photo_1 GROUP BY AlbumID ) AS p ON p.AlbumID = a.ID ) t "; DataTable dt = DbInstance.GetDataTable(out count, table, pager.OrderColumn + " " + pager.Order, plist, "*", pager.Where, pager.CurrentPage, pager.PageSize); pager.TotalRows = count; return(FYJ.ObjectHelper.DataTableToModel <blog_tb_Album>(dt)); }
public int CreateCatelog(string ids) { ids = ids.Trim(','); string sql = "select articleTitle,articleID from blog_tb_article where articleID in (" + ids + ") order by ADD_DATE"; DataTable dt = DbInstance.GetDataTable(sql); string str = ""; foreach (DataRow dr in dt.Rows) { str += "<p><a href=\"/artic-" + dr["articleID"] + ".html\">" + dr["articleTitle"] + "</a></p>"; } DbInstance.BeginTran(); int result = DbInstance.ExecuteSql("update blog_tb_article_content set articleContent=@articleContent+articleContent,UPDATE_DATE=@UPDATE_DATE where articleID in (" + ids + ")" , DbInstance.CreateParameter("@articleContent", str) , DbInstance.CreateParameter("@UPDATE_DATE", DateTime.Now)); DbInstance.Commit(); return result; }
public List <blog_tb_menu> GetList(string blogID, string parentID) { string sql = @"select *,(select count(1) from blog_tb_menu where parentID=table1.menuID ) as rowcount from blog_tb_menu as table1 where blogID=@blogID "; List <IDbDataParameter> paras = new List <IDbDataParameter>(); if (!String.IsNullOrEmpty(parentID)) { sql += " and parentID=@parentID"; paras.Add(DbInstance.CreateParameter("@parentID", parentID)); } sql += " order by menuOrder desc"; paras.Add(DbInstance.CreateParameter("@blogID", blogID)); DataTable dt = DbInstance.GetDataTable(sql, paras); List <blog_tb_menu> list = ObjectHelper.DataTableToModel <blog_tb_menu>(dt); return(list); }
public System.Data.DataTable QueryAlbum(ref GridPager pager) { int count = 0; List <IDataParameter> plist = null; if (pager.Parameters != null && pager.Parameters.Count > 0) { plist = new List <IDataParameter>(); foreach (string key in pager.Parameters.Keys) { plist.Add(DbInstance.CreateParameter("@" + key.TrimStart('@'), pager.Parameters[key])); } } DataTable dt = DbInstance.GetDataTable(out count, "blog_view_Album", pager.OrderColumn + " " + pager.Order, plist, "*", pager.Where, pager.CurrentPage, pager.PageSize); pager.TotalRows = count; return(dt); }
public int SaveTempContent(string articleID, string content) { int result = 0; if (this.DbInstance.Exists("select 1 from blog_tb_article_temp where articleID=@articleID", DbInstance.CreateParameter("@articleID", articleID))) { result = DbInstance.ExecuteSql("update blog_tb_article_temp set articleContent=@articleContent,UPDATE_DATE=GETDATE() where articleID=@articleID" , DbInstance.CreateParameter("@articleID", articleID) , DbInstance.CreateParameter("@articleContent", content)); } else { result = DbInstance.ExecuteSql("insert into blog_tb_article_temp values(@ID,@articleID,@articleContent,GETDATE(),GETDATE())", DbInstance.CreateParameter("@ID", Guid.NewGuid().ToString("N")), DbInstance.CreateParameter("@articleID", articleID), DbInstance.CreateParameter("@articleContent", content)); } return(result); }
public int UpdateLastOpenDatetime(string articleID) { int count = DbInstance.GetInt("select count(*) from blog_tb_article_extend where articleID=@articleID", DbInstance.CreateParameter("@articleID", articleID)); if (count == 0) { return(DbInstance.ExecuteSql("insert into blog_tb_article_extend(extendID,articleID,lastOpenDatetime,UPDATE_DATE) values (@extendID,@articleID,@lastOpenDatetime,@UPDATE_DATE)" , DbInstance.CreateParameter("@extendID", Guid.NewGuid().ToString("N")) , DbInstance.CreateParameter("@articleID", articleID) , DbInstance.CreateParameter("@lastOpenDatetime", DateTime.Now) , DbInstance.CreateParameter("@UPDATE_DATE", DateTime.Now))); } else { return(DbInstance.ExecuteSql("update blog_tb_article_extend set lastOpenDatetime=@lastOpenDatetime,UPDATE_DATE=@UPDATE_DATE where articleID=@articleID" , DbInstance.CreateParameter("@articleID", articleID) , DbInstance.CreateParameter("@lastOpenDatetime", DateTime.Now) , DbInstance.CreateParameter("@UPDATE_DATE", DateTime.Now))); } }