/// <summary> /// 移除某一个类型(关键字,一级类型,二级类型) /// </summary> /// <param name="rmId">移除项目的ID</param> /// <param name="queryString">移除项目的类型(keyword,firstLevel,secondLevel)</param> /// <returns>是否移除成功</returns> public static bool removeItem(int rmId, string queryString) { try { using (var db = new PaperDbEntities()) { switch (queryString) { case "keyword": KeyWords keyword = db.KeyWords.Single(a => a.id == rmId); db.KeyWords.Remove(keyword); break; case "firstLevel": Category category = db.Category.Single(a => a.id == rmId); db.Category.Remove(category); break; case "secondLevel": Option option = db.Option.Single(a => a.id == rmId); db.Option.Remove(option); break; } db.SaveChanges(); return(true); } } catch (Exception ex) { return(false); } }
/// <summary> /// 添加各种选项 /// </summary> /// <param name="articleId">文章ID</param> /// <param name="ops">一级选项名字,二级选项名字</param> /// <returns></returns> public static bool addOptions(int articleId, Dictionary <string, string> ops) { List <int> opsls = new List <int>(); foreach (KeyValuePair <string, string> s in ops) { opsls.Add(LabelHelper.getOptionId(s.Key, s.Value)); } try { using (var db = new PaperDbEntities()) { //增加所有选项 foreach (int id in opsls) { TypeConnection tc = new TypeConnection(); tc.ArticleId = articleId; tc.OptionId = id; db.TypeConnection.Add(tc); } db.SaveChanges(); return(true); } } catch (Exception ex) { return(false); } }
/// <summary> /// 查询需要绑定的数据,并且绑定数据源到给出的下拉列表框中(Enum("keyword","firstLevel","secondLevel")) /// </summary> /// <param name="rpt">数据容器</param> /// <param name="queryString">查询那个表</param> /// <param name="ca">为了查二级选项,需要提供一级选项名字</param> public static void bindLables(ref GridView rpt, string queryString, string ca = null) { try { using (var db = new PaperDbEntities()) { switch (queryString) { case "keyword": List <KeyWords> keywordList = (from it in db.KeyWords select it).ToList(); rpt.DataSource = keywordList; break; case "firstLevel": List <Category> categoryList = (from it in db.Category select it).ToList(); rpt.DataSource = categoryList; break; case "secondLevel": Category cid = db.Category.Single(a => a.Name == ca); List <Option> optionList = (from it in db.Option where it.CategoryId == cid.id select it).ToList(); rpt.DataSource = optionList; break; } } } catch (Exception ex) { rpt.DataSource = null; } }
/// <summary> /// 获取文章的各种选项 /// </summary> /// <param name="articleId">文章ID</param> /// <returns>一级选项名,二级选项名的键值对</returns> public static Dictionary <string, string> getOptions(int articleId) { Dictionary <string, string> opsDict = new Dictionary <string, string>(); try { using (var db = new PaperDbEntities()) { var query = from it in db.TypeConnection where it.ArticleId == articleId join op in db.Option on it.OptionId equals op.id join ca in db.Category on op.CategoryId equals ca.id select new { firstLevel = ca.Name, secondLevel = op.Name }; //转换到字典中 opsDict.Clear(); foreach (var k in query) { opsDict.Add(k.firstLevel, k.secondLevel); } } return(opsDict); } catch (Exception) { throw; } }
/// <summary> /// 获取文章的所有关键字 /// </summary> /// <param name="articleId">文章ID</param> /// <returns>关键字对象字典</returns> public static Dictionary <int, string> getKeyowrd(int articleId) { Dictionary <int, string> keywordDict = new Dictionary <int, string>(); try { List <KeyWords> keywordLs = new List <KeyWords>(); using (var db = new PaperDbEntities()) { keywordLs = (from it in db.KeyWordConnection where it.ArticleId == articleId join key in db.KeyWords on it.KeyWordId equals key.id select key).ToList(); } //转换成字典 keywordDict.Clear(); foreach (KeyWords k in keywordLs) { keywordDict.Add(k.id, k.Name); } return(keywordDict); } catch (Exception ex) { return(null); } }
/// <summary> /// 根据一级分类的名字获取一级分类ID /// </summary> /// <param name="name">一级分类名字</param> /// <returns>一级分类ID,失败返回 -1</returns> public static int getCategoryIdByName(string name) { try { using (var db = new PaperDbEntities()) { Category caID = db.Category.Single(a => a.Name == name); return(caID.id); } } catch (Exception ex) { return(-1); } }
/// <summary> /// 根据一级分类返回本类下面的二级分类 /// </summary> /// <param name="firstLevel">一级分了</param> /// <returns>本类下面的二级分类</returns> public static List <Option> bindSecondLevel(string firstLevel) { try { using (var db = new PaperDbEntities()) { Category caID = db.Category.Single(a => a.Name == firstLevel); List <Option> opLi = (from it in db.Option where it.CategoryId == caID.id select it).ToList(); return(opLi); } } catch (Exception ex) { return(null); } }
/// <summary> /// 获取文章的所有选项记录 /// </summary> /// <param name="articleId"></param> /// <returns></returns> public static List <TypeConnection> getArticleTypeConn(int articleId) { try { List <TypeConnection> kcLs = new List <TypeConnection>(); using (var db = new PaperDbEntities()) { kcLs = (from it in db.TypeConnection where it.ArticleId == articleId select it).ToList(); } return(kcLs); } catch (Exception ex) { return(null); } }
/// <summary> /// 得到二级选项的id,建立文章和二级选项的关系 /// </summary> /// <param name="firstLevel">一级选项名字</param> /// <param name="secondLevel">二级选项名字</param> /// <returns>二级选项ID</returns> public static int getOptionId(string firstLevel, string secondLevel) { try { using (PaperDbEntities db = new PaperDbEntities()) { Category c = db.Category.Single(a => a.Name == firstLevel); Option op = db.Option.Single(a => a.CategoryId == c.id && a.Name == secondLevel); return(op.id); } } catch (Exception ex) { return(-1); } }
/// <summary> /// 根据查询多关键字字符串返回关键字ID /// </summary> /// <param name="queryKeyword">关键字字符串(分隔符;)</param> /// <returns>关键词ID列表</returns> public static List <int> getKeywordIdByStringKeyword(string queryKeyword) { List <int> ids = new List <int>(); try { using (var db = new PaperDbEntities()) { ids = db.KeyWords.Where(a => queryKeyword.Contains(a.Name)).Select(a => a.id).ToList(); } return(ids); } catch (Exception ex) { return(null); } }
/// <summary> /// 根据ID返回一片文章 /// </summary> /// <param name="articleId">文章ID</param> /// <returns>文章对象</returns> public static Article getArticleById(int articleId) { Article ar = new Article(); try { using (var db = new PaperDbEntities()) { ar = db.Article.Single(a => a.id == articleId); return(ar); } } catch (Exception ex) { return(ar); } }
/// <summary> /// 通过作者返回文章 /// </summary> /// <param name="author">作者,模糊搜索</param> /// <returns></returns> public static List <Article> getArticleByAuthor(string author) { List <Article> arLs = new List <Article>(); try { using (var db = new PaperDbEntities()) { arLs = (from it in db.Article where it.Author.Contains(author) orderby it.Title ascending select it).Distinct().ToList(); return(arLs); } } catch (Exception ex) { return(arLs); } }
/// <summary> /// 更新文章 /// </summary> /// <param name="newAr">待更新文章对象</param> /// <returns>是否添加成功</returns> public static bool updateArticle(Article newAr) { try { using (var db = new PaperDbEntities()) { Article oldAr = db.Article.Single(a => a.id == newAr.id); db.Entry(oldAr).CurrentValues.SetValues(newAr); db.SaveChanges(); return(true); } } catch (Exception ex) { return(false); } }
/// <summary> /// 通过高级选项返回文章 /// </summary> /// <param name="opIds">高级选项的ID</param> /// <returns></returns> public static List <Article> getArticleByOptions(List <int> opIds) { try { List <Article> arls = new List <Article>(); using (var db = new PaperDbEntities()) { List <int> arIds = db.TypeConnection.Where(a => opIds.Contains(a.OptionId)).Select(a => a.ArticleId).Distinct().ToList(); arls = db.Article.Where(a => arIds.Contains(a.id)).ToList(); } return(arls); } catch (Exception ex) { return(null); } }
/// <summary> /// 返回所有文章 /// </summary> /// <returns></returns> public static List <Article> getAllArticle() { List <Article> arLs = new List <Article>(); try { using (var db = new PaperDbEntities()) { arLs = (from it in db.Article orderby it.Title ascending select it).ToList(); return(arLs); } } catch (Exception ex) { return(arLs); } }
/// <summary> /// 添加新文章 /// </summary> /// <param name="ar">待添加文章对象</param> /// <returns>添加成功返回文章ID,否则返回-1</returns> public static int addArticle(Article ar) { try { using (var db = new PaperDbEntities()) { db.Article.Add(ar); db.SaveChanges(); Article tmp = db.Article.Single(a => a.Title == ar.Title && a.Author == ar.Author); return(tmp.id); } } catch (Exception ex) { return(-1); } }
/// <summary> /// 删除文章 /// </summary> /// <param name="articleID">文章ID</param> /// <returns></returns> public static bool delArticle(int articleID) { try { using (var db = new PaperDbEntities()) { Article ar = db.Article.SingleOrDefault(a => a.id == articleID); db.Article.Remove(ar); db.SaveChanges(); return(true); } } catch (Exception ex) { return(false); } }
/// <summary> /// 获取所有关键字 /// </summary> /// <returns>关键字对象列表</returns> public static List <KeyWords> getAllKeywords() { List <KeyWords> keywordList = new List <KeyWords>(); try { using (var db = new PaperDbEntities()) { keywordList = (from it in db.KeyWords select it).ToList(); } return(keywordList); } catch (Exception ex) { return(null); } }
/// <summary> /// 通过关键词返回文章 /// </summary> /// <param name="keywordIds">关键词ID列表,模糊搜索</param> /// <returns></returns> public static List <Article> getArticleByKeyword(List <int> keywordIds) { List <int> keyConn = new List <int>(); List <Article> arLs = new List <Article>(); try { using (var db = new PaperDbEntities()) { keyConn = db.KeyWordConnection.Where(a => keywordIds.Contains(a.KeyWordId)).OrderBy(a => a.ArticleId).Select(a => a.ArticleId).ToList(); arLs = db.Article.Where(a => keyConn.Contains(a.id)).OrderBy(a => a.Title).Distinct().ToList(); return(arLs); } } catch (Exception ex) { return(arLs); } }
/// <summary> /// 删除文章类型选项记录 /// </summary> /// <param name="typeConnId"></param> /// <returns></returns> public static bool delTypeConnection(List <int> typeConnId) { try { using (var db = new PaperDbEntities()) { foreach (int id in typeConnId) { TypeConnection kc = db.TypeConnection.Single(a => a.id == id); db.TypeConnection.Remove(kc); } db.SaveChanges(); } return(true); } catch (Exception ex) { return(false); } }
/// <summary> /// 通过高级选项返回文章 /// </summary> /// <param name="opIds">高级选项的ID</param> /// <returns></returns> public static List <Article> getArticleByOptions(List <int> opIds) { try { List <Article> arls = new List <Article>(); using (var db = new PaperDbEntities()) { /*** * * 这个是是要包含就能查出来,不太好 * */ /* * List<int> arIds = db.TypeConnection.Where(a => opIds.Contains(a.OptionId)).Select(a => a.ArticleId).Distinct().ToList(); * arls = db.Article.Where(a => arIds.Contains(a.id)).ToList(); */ //查询每一个 optionID 可以获得一个 set //求所有 set 的广义交,结果就是多选项查询 //set 访问速度O(1),但是求交的时候也需要遍历,速度取决于 optionId 的个数 //目前没想到什么好的办法改进,可能程序可以优化,也可能数据库结构不好,导致现在这种查询尴尬的境地 int opid0 = opIds[0]; int opidNow = 0; HashSet <int> setId = new HashSet <int>(db.TypeConnection.Where(a => a.OptionId == opid0).Select(a => a.ArticleId));// new HashSet<int>(); HashSet <int> setTmp = null; for (int i = 1; i < opIds.Count; i++) { //linq 不可以直接使用数组下标访问,需要用变量存一下 opidNow = opIds[i]; setTmp = new HashSet <int>(db.TypeConnection.Where(a => a.OptionId == opidNow).Select(a => a.ArticleId)); setId.IntersectWith(setTmp); } arls = db.Article.Where(a => setId.Contains(a.id)).ToList(); } return(arls); } catch (Exception ex) { return(null); } }
/// <summary> /// 通过时间返回文章 /// </summary> /// <param name="low">时间下限</param> /// <param name="high">时间上限</param> /// <returns></returns> public static List <Article> getArticleByTime(int low, int high) { DateTime lowYear = new DateTime(low, 1, 1); DateTime highYear = new DateTime(high, 1, 1); List <Article> arls = new List <Article>(); try { using (var db = new PaperDbEntities()) { arls = (from it in db.Article where it.UpateTime <= highYear && it.UpateTime >= lowYear select it).Distinct().ToList(); } return(arls); } catch (Exception ex) { return(null); } }
/// <summary> /// 添加各种选项,通过给出optionId /// </summary> /// <param name="articleId"></param> /// <param name="opsId"></param> /// <returns></returns> public static bool addOptions(int articleId, List <int> opsId) { try { using (var db = new PaperDbEntities()) { foreach (int i in opsId) { TypeConnection tc = new TypeConnection(); tc.ArticleId = articleId; tc.OptionId = i; db.TypeConnection.Add(tc); } db.SaveChanges(); } return(true); } catch (Exception ex) { return(false); } }
/// <summary> /// 混合条件查询中,从但条件查询中过滤出符合各种选项的文章 /// </summary> /// <param name="ll"></param> /// <param name="opIds"></param> /// <returns></returns> public static List <Article> filterArticleByOptions(ref List <Article> ll, ref List <int> opIds) { try { using (var db = new PaperDbEntities()) { List <int> arids = new List <int>(); arids.Clear(); foreach (Article ar in ll) { arids.Add(ar.id); } //获取待过滤文章的所有 TypeConnection List <TypeConnection> tyls = db.TypeConnection.Where(a => arids.Contains(a.ArticleId)).ToList(); List <Article> arls = new List <Article>(); int opid0 = opIds[0]; int opidNow = 0; HashSet <int> setId = new HashSet <int>(tyls.Where(a => a.OptionId == opid0).Select(a => a.ArticleId));// new HashSet<int>(); HashSet <int> setTmp = null; for (int i = 1; i < opIds.Count; i++) { //linq 不可以直接使用数组下标访问,需要用变量存一下 opidNow = opIds[i]; setTmp = new HashSet <int>(tyls.Where(a => a.OptionId == opidNow).Select(a => a.ArticleId)); setId.IntersectWith(setTmp); } arls = ll.Where(a => setId.Contains(a.id)).ToList(); return(arls); } } catch (Exception ex) { return(null); } }
protected void btnSecondLevel_Click(object sender, EventArgs e) { string secondLevelVal = txtSecondLevel.Text.Trim(); if (secondLevelVal.Equals(string.Empty)) { Response.Write(JSHelper.alert("二级分类不能为空,请重新输入!")); } else { try { using (var db = new PaperDbEntities()) { Category c = db.Category.Single(a => a.Name == dplFirstLevel.SelectedValue); Option tmp = db.Option.SingleOrDefault(a => a.Name == secondLevelVal && a.CategoryId == c.id); if (tmp == null) { Option secondLevel = new Option(); secondLevel.Name = secondLevelVal; secondLevel.CategoryId = LabelHelper.getCategoryIdByName(dplFirstLevel.SelectedValue); db.Option.Add(secondLevel); db.SaveChanges(); Response.Write(JSHelper.alert("添加成功!", "manageLabel.aspx")); } else { Response.Write(JSHelper.alert("二级分类重复,请检查!")); } } } catch (Exception ex) { //ex.Message; Response.Write(JSHelper.alert("添加失败,请重新尝试!")); } } }
protected void dplManageLabel_SelectedIndexChanged(object sender, EventArgs e) { if (dplManageLabel.SelectedValue.Equals("关键字管理")) { panel_keywords.Visible = true; LabelHelper.bindLables(ref gdvKeywords, "keyword"); gdvKeywords.DataBind(); panel_firstLevel.Visible = panel_secondLevel.Visible = false; } else if (dplManageLabel.SelectedValue.Equals("一级分类管理")) { panel_firstLevel.Visible = true; LabelHelper.bindLables(ref gdvFirstLevel, "firstLevel"); gdvFirstLevel.DataBind(); panel_keywords.Visible = panel_secondLevel.Visible = false; } else { try { //绑定一级分类 using (var db = new PaperDbEntities()) { List <string> caLi = (from it in db.Category select it.Name).ToList(); dplFirstLevel.DataSource = caLi; dplFirstLevel.DataBind(); } } catch (Exception ex) { throw; } panel_secondLevel.Visible = true; LabelHelper.bindLables(ref gdvSecondLevel, "secondLevel", dplFirstLevel.SelectedValue); gdvSecondLevel.DataBind(); panel_keywords.Visible = panel_firstLevel.Visible = false; } }
/// <summary> /// 添加关键字 /// </summary> /// <param name="articleId">文章id</param> /// <param name="keywordId">关键字id 列表</param> /// <returns>是否成功</returns> public static bool addKeywords(int articleId, List <int> keywordId) { try { using (var db = new PaperDbEntities()) { //增加所有关键字 foreach (int id in keywordId) { KeyWordConnection kc = new KeyWordConnection(); kc.ArticleId = articleId; kc.KeyWordId = id; db.KeyWordConnection.Add(kc); } db.SaveChanges(); return(true); } } catch (Exception ex) { return(false); } }
protected void btnKeyword_Click(object sender, EventArgs e) { string keywordvalu = txtKeyword.Text.Trim(); if (keywordvalu.Equals(string.Empty)) { Response.Write(JSHelper.alert("关键词不能为空,请重新输入!")); } else { try { using (var db = new PaperDbEntities()) { KeyWords tmp = db.KeyWords.SingleOrDefault(a => a.Name == keywordvalu); if (tmp == null) { KeyWords keyword = new KeyWords(); keyword.Name = keywordvalu; db.KeyWords.Add(keyword); db.SaveChanges(); Response.Write(JSHelper.alert("添加成功!", "manageLabel.aspx")); } else { Response.Write(JSHelper.alert("关键词重复,请检查!")); } } } catch (Exception ex) { //ex.Message; Response.Write(JSHelper.alert("添加失败,请重新尝试!")); } } }
protected void btnFirstLevel_Click(object sender, EventArgs e) { string firstLevelVal = txtFirstLevel.Text.Trim(); if (firstLevelVal.Equals(string.Empty)) { Response.Write(JSHelper.alert("一级分类不能为空,请重新输入!")); } else { try { using (var db = new PaperDbEntities()) { Category tmp = db.Category.SingleOrDefault(a => a.Name == firstLevelVal); if (tmp == null) { Category firstLevel = new Category(); firstLevel.Name = firstLevelVal; db.Category.Add(firstLevel); db.SaveChanges(); Response.Write(JSHelper.alert("添加成功!", "manageLabel.aspx")); } else { Response.Write(JSHelper.alert("一级分类重复,请检查!")); } } } catch (Exception ex) { //ex.Message; Response.Write(JSHelper.alert("添加失败,请重新尝试!")); } } }