예제 #1
0
 public int GetNumberOfPosts()
 {
     using (var db = new SovaContext())
     {
         return(db.post.Count());
     }
 }
예제 #2
0
 public int GetNumberOfComments()
 {
     using (var db = new SovaContext())
     {
         return(db.comment.Count());
     }
 }
예제 #3
0
        public PostExtended GetPostDetail(int id)
        {
            using (var db = new SovaContext())
            {
                var getParentId = from a in db.answer
                                  where a.ParentId == id
                                  select a.PostId;

                //select posts.postBody from posts
                //where postID in (select answers.postID
                //from answers
                //where answers.parentID = id);
                var getAnswerList = (from item in db.post where getParentId.Contains(item.PostId) select item.PostBody).ToList();

                var result = (from p in db.post
                              where !db.answer.Any(f => f.PostId == p.PostId)
                              where p.PostId == id
                              select new PostExtended
                {
                    PostId = p.PostId,
                    Title = p.Question.Title,
                    Score = p.Score,
                    PostBody = p.PostBody,
                    CreatedDate = p.CreatedDate,
                    UserName = p.User.UserName,
                    Answers = getAnswerList
                }).FirstOrDefault();
                return(result);
            }
        }
예제 #4
0
 public int GetNumberOfMarkings()
 {
     using (var db = new SovaContext())
     {
         return(db.marking.Count());
     }
 }
예제 #5
0
 public int GetNumberOfUsers()
 {
     using (var db = new SovaContext())
     {
         return(db.user.Count());
     }
 }
예제 #6
0
 public int GetNumberOfHistories()
 {
     using (var db = new SovaContext())
     {
         return(db.history.Count());
     }
 }
예제 #7
0
 public int GetNumberOfAnnotations()
 {
     using (var db = new SovaContext())
     {
         return(db.annotation.Count());
     }
 }
예제 #8
0
 public User GetUser(int id)
 {
     using (var db = new SovaContext())
     {
         return((from u in db.user
                 where u.UserId == id
                 select u).FirstOrDefault());
     }
 }
예제 #9
0
 public Annotation GetAnnotation(int id)
 {
     using (var db = new SovaContext())
     {
         var result = (from p in db.annotation
                       where p.AnnotationId == id
                       select p).FirstOrDefault();
         return(result);
     }
 }
예제 #10
0
 public Marking GetMarking(int id)
 {
     using (var db = new SovaContext())
     {
         var result = (from p in db.marking
                       where p.PostId == id
                       select p).FirstOrDefault();
         return(result);
     }
 }
예제 #11
0
 public Annotation AddAnnotation(Annotation annotation)
 {
     using (var db = new SovaContext())
     {
         //annotation.AnnotationId = db.annotation.Max(c => c.AnnotationId) + 1;
         db.Add(annotation);
         db.SaveChanges();
     }
     return(GetAnnotation(annotation.AnnotationId));
 }
예제 #12
0
 //======= CRUD on annotations =============
 public IList <Annotation> GetAnnotation(int page, int pagesize)
 {
     using (var db = new SovaContext())
     {
         var tmp = (from a in db.annotation
                    select a)
                   .OrderBy(o => o.AnnotationId)
                   .Skip(page * pagesize)
                   .Take(pagesize)
                   .ToList();
         return(tmp);
     }
 }
예제 #13
0
 public bool DeleteAnnotation(int id)
 {
     using (var db = new SovaContext())
     {
         var annotation = db.annotation.FirstOrDefault(c => c.AnnotationId == id);
         if (annotation == null)
         {
             return(false);
         }
         db.Remove(annotation);
         return(db.SaveChanges() > 0);
     }
 }
예제 #14
0
 //======= Get users =============
 public IList <User> GetUser(int page, int pagesize)
 {
     using (var db = new SovaContext())
     {
         var tmp = (from u in db.user
                    select u)
                   .OrderBy(o => o.UserId)
                   .Skip(page * pagesize)
                   .Take(pagesize)
                   .ToList();
         return(tmp);
     }
 }
예제 #15
0
 //======= CRUD on markings =============
 public IList <Marking> GetMarking(int page, int pagesize)
 {
     using (var db = new SovaContext())
     {
         var tmp = (from a in db.marking
                    select a)
                   .OrderBy(o => o.PostId)
                   .Skip(page * pagesize)
                   .Take(pagesize)
                   .ToList();
         return(tmp);
     }
 }
예제 #16
0
 //======= Search posts =============
 public IList <WeightedSearch> GetSearchedPost(string keyword1)
 {
     using (var db = new SovaContext())
     {
         var result  = db.weightedSearch.FromSql("call weighted_Search({0})", keyword1);
         var result1 = new List <WeightedSearch>();
         foreach (var data in result)
         {
             result1.Add(data);
         }
         return(result1);
     }
 }
예제 #17
0
        //Get weighted word list for word cloud
        public IList <WordCloud> GetWordCloud(string word)
        {
            using (var db = new SovaContext())
            {
                var wordlist = db.wordCloud.FromSql("call weighted_Wordlist({0})", word);
                var result   = new List <WordCloud>();

                foreach (var data in wordlist.Take(50))
                {
                    result.Add(data);
                }
                return(result);
            }
        }
예제 #18
0
 //======= Get tags related to a post =============
 public IList <Tag> GetPostTag(int id)
 {
     using (var db = new SovaContext())
     {
         var result = (from t in db.tag
                       where t.PostId == id
                       select new Tag
         {
             PostId = t.PostId,
             TagName = t.TagName
         }).ToList();
         return(result);
     }
 }
예제 #19
0
        public IList <BestMatchSearch> GetAllMatchPostsWithKeyword(string keyword)
        {
            using (var db = new SovaContext())
            {
                var bestMatch = db.bestMatchSearch.FromSql("call binary_Search({0})", keyword);
                var result    = new List <BestMatchSearch>();

                foreach (var data in bestMatch)
                {
                    result.Add(data);
                }
                return(result);
            }
        }
예제 #20
0
 public History GetHistory(int id)
 {
     using (var db = new SovaContext())
     {
         return((from h in db.history
                 where h.sId == id
                 select new History
         {
             sId = h.sId,
             SearchString = h.SearchString,
             SearchTime = h.SearchTime
         }).FirstOrDefault());
     }
 }
예제 #21
0
        public bool UpdateMarking(Marking marking)
        {
            using (var db = new SovaContext())

                try
                {
                    db.Attach(marking);
                    db.Entry(marking).State = EntityState.Modified;
                    return(db.SaveChanges() > 0);
                }
                catch (DbUpdateConcurrencyException)
                {
                    return(false);
                }
        }
예제 #22
0
        public bool UpdateAnnotation(Annotation annotation)
        {
            using (var db = new SovaContext())

                try
                {
                    annotation.AnnotationCreationDate = db.GetTimestamp();
                    db.Attach(annotation);
                    db.Entry(annotation).State = EntityState.Modified;
                    return(db.SaveChanges() > 0);
                }
                catch (DbUpdateConcurrencyException)
                {
                    return(false);
                }
        }
예제 #23
0
        public IList <SearchResult> GetProcedureList(int page, int pageSize, string word1, string word2, string word3, bool questionSearch)
        {
            List <SearchResult> results = new List <SearchResult>();

            using (SovaContext db = new SovaContext())
            {
                if (questionSearch)
                {
                    var resultOfQuestionSearch = db.Set <SearchResult>()
                                                 .FromSql("call questionSearch({0},{1},{2})", new[] { word1, word2, word3 })
                                                 .Skip(page * pageSize)
                                                 .Take(pageSize);
                    foreach (var data in resultOfQuestionSearch)
                    {
                        SearchResult tmp = new SearchResult {
                        };
                        tmp.PostId    = data.PostId;
                        tmp.PostTitle = data.PostTitle;
                        tmp.PostScore = data.PostScore;
                        tmp.PostText  = data.PostText;
                        tmp.Rank      = data.Rank;
                        results.Add(tmp);
                    }
                }
                else
                {
                    var resultOfAnswerSearch = db.Set <SearchResult>()
                                               .FromSql("call answerSearch({0},{1},{2})", new[] { word1, word2, word3 })
                                               .Skip(page * pageSize)
                                               .Take(pageSize);

                    foreach (var data in resultOfAnswerSearch)
                    {
                        SearchResult tmp = new SearchResult {
                        };
                        tmp.PostId    = data.PostId;
                        tmp.PostTitle = data.PostTitle;
                        tmp.PostScore = data.PostScore;
                        tmp.PostText  = data.PostText;
                        tmp.Rank      = data.Rank;
                        results.Add(tmp);
                    }
                }
                return(results);
            }
        }
예제 #24
0
 //======= Get history =============
 public IList <History> GetHistory(int page, int pagesize)
 {
     using (var db = new SovaContext())
     {
         var tmp = (from h in db.history
                    select new History
         {
             sId = h.sId,
             SearchString = h.SearchString,
             SearchTime = h.SearchTime
         }).OrderBy(o => o.sId)
                   .Skip(page * pagesize)
                   .Take(pagesize)
                   .ToList();
         return(tmp);
     }
 }
예제 #25
0
 //======= Get question posts as a list =============
 public List <PostExtended> GetListOfPosts(int page, int pagesize)
 {
     using (var db = new SovaContext())
     {
         var list = (from p in db.post
                     where !db.answer.Any(f => f.PostId == p.PostId) //select those which are post questions, not answers
                     select new PostExtended
         {
             PostId = p.PostId,
             Title = p.Question.Title,
             UserName = p.User.UserName
         }).OrderBy(o => o.PostId)
                    .Skip(page * pagesize)
                    .Take(pagesize)
                    .ToList();
         return(list);
     }
 }
예제 #26
0
 public CommentExtended GetComment(int id)
 {
     using (var db = new SovaContext())
     {
         return((from c in db.comment
                 where c.CommentId == id
                 select new CommentExtended
         {
             CommentId = c.CommentId,
             PostId = c.PostId,
             UserId = c.UserId,
             CommentBody = c.CommentBody,
             PostTitle = c.Post.Question.Title,
             CommentCreationDate = c.CommentCreationDate,
             UserName = c.User.UserName
         }).FirstOrDefault());
     }
 }
예제 #27
0
        public IList <FrequentWord> GetProcedureList(int page, int pageSize, string word1, string word2, string word3, bool questionSearch)
        {
            List <FrequentWord> results = new List <FrequentWord>();

            using (SovaContext db = new SovaContext())
            {
                if (questionSearch)
                {
                    var something = db.Set <FrequentWord>()
                                    .FromSql("call relevantQuestionWords({0},{1},{2})", new[] { word1, word2, word3 })
                                    .Skip(page * pageSize)
                                    .Take(pageSize);

                    foreach (var data in something)
                    {
                        FrequentWord tmp = new FrequentWord {
                        };
                        tmp.Frequency = data.Frequency;
                        tmp.Word      = data.Word;
                        results.Add(tmp);
                    }
                }
                else
                {
                    var something = db.Set <FrequentWord>()
                                    .FromSql("call relevantAnswerWords({0},{1},{2})", new[] { word1, word2, word3 })
                                    .Skip(page * pageSize)
                                    .Take(pageSize);

                    foreach (var data in something)
                    {
                        FrequentWord tmp = new FrequentWord {
                        };
                        tmp.Frequency = data.Frequency;
                        tmp.Word      = data.Word;
                        results.Add(tmp);
                    }
                }


                return(results);
            }
        }
예제 #28
0
 //======= CRUD on Comment Table =============
 public IList <CommentExtended> GetComment(int page, int pagesize)
 {
     using (var db = new SovaContext())
     {
         var tmp = (from c in db.comment
                    select new CommentExtended
         {
             CommentId = c.CommentId,
             PostId = c.PostId,
             UserId = c.UserId,
             CommentBody = c.CommentBody,
             PostTitle = c.Post.Question.Title,
             CommentCreationDate = c.CommentCreationDate,
             UserName = c.User.UserName
         }).OrderBy(o => o.CommentId)
                   .Skip(page * pagesize)
                   .Take(pagesize)
                   .ToList();
         return(tmp);
     }
 }
예제 #29
0
 public IList <SearchedResult> GetAllMatchPostsWithKeyword(string keyword)
 {
     using (var db = new SovaContext())
     {
         var conn = (MySqlConnection)db.Database.GetDbConnection();
         conn.Open();
         var cmd    = new MySqlCommand("call post", conn);
         var result = new List <SearchedResult>();
         using (var rdr = cmd.ExecuteReader())
         {
             while (rdr.Read())
             {
                 result.Add(new SearchedResult()
                 {
                     PostId   = rdr.GetInt16(0),
                     Score    = rdr.GetInt16(2),
                     PostBody = rdr.GetString(1),
                     UserId   = rdr.GetInt16(3)
                 });
             }
         }
         return(result.ToList());
     }
 }
예제 #30
0
 public UserDataService(SovaContext db)
 {
     this.db = db;
 }