public async Task <IEnumerable <Search> > GetAll(PagingInfo pagingInfo)
{
using (var db = new StackoverflowDbContext())
{
return(await db.Searches.ToListAsync());
}
}
public async Task <IList <AssociationsListDto> > GetAssociations(string word)
{
using (var db = new StackoverflowDbContext())
{
var conn = (MySqlConnection)db.Database.GetDbConnection();
conn.Open();
var cmd = new MySqlCommand
{
Connection = conn
};
cmd.Parameters.Add("@param", DbType.String);
cmd.Parameters["@param"].Value = word;
cmd.CommandText = "call GetAssociations(@param)";
var result = new List <AssociationsListDto>();
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
result.Add(new AssociationsListDto
{
Word = (string)reader["word2"],
Grade = (decimal)reader["grade"]
});
}
}
return(result);
}
}
public async Task <IList <WeightedWordListDto> > WeightedWordList(string term)
{
using (var db = new StackoverflowDbContext())
{
var conn = (MySqlConnection)db.Database.GetDbConnection();
conn.Open();
var cmd = new MySqlCommand
{
Connection = conn
};
cmd.Parameters.Add("@param", DbType.String);
cmd.Parameters["@param"].Value = term;
cmd.CommandText = "call WeightedWordList(@param)";
var result = new List <WeightedWordListDto>();
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
result.Add(new WeightedWordListDto
{
Term = (string)reader["term"],
Rank = (decimal)reader["rank"]
});
}
}
return(result);
}
}
public async Task <Post> Get(int id)
{
using (var db = new StackoverflowDbContext())
{
return(await db.Posts
.FirstOrDefaultAsync(x => x.ID == id));
}
}
public async Task <Comment> Get(int postId)
{
using (var db = new StackoverflowDbContext())
{
return(await db.Comments
.FirstOrDefaultAsync(x => x.PostID == postId && x.UserID == _user.ID));
}
}
public async Task <Bookmark> Get(int postId)
{
using (var db = new StackoverflowDbContext())
{
return(await db.Bookmarks
.FirstOrDefaultAsync(x => x.UserID == _user.ID && x.PostID == postId));
}
}
public async Task <IEnumerable <Bookmark> > GetAll(PagingInfo pagingInfo)
{
using (var db = new StackoverflowDbContext())
{
return(await db.Bookmarks
.Include(x => x.Post)
.ToListAsync());
}
}
public async Task <Note> Get(int postId)
{
using (var db = new StackoverflowDbContext())
{
return(await db.Notes
.Include(x => x.Post)
.Include(x => x.User)
.FirstOrDefaultAsync(x => x.UserID == _user.ID && x.PostID == postId));
}
}
public async Task <Search> Add(Search newSearch)
{
using (var db = new StackoverflowDbContext())
{
await db.Searches.AddAsync(newSearch);
db.SaveChanges();
return(newSearch);
}
}
public async Task <IEnumerable <Search> > GetSearchHistory(int userid, PagingInfo pagingInfo)
{
using (var db = new StackoverflowDbContext())
{
return(await db.Searches
.Skip((pagingInfo.Page - 1) *pagingInfo.PageSize)
.Take(pagingInfo.PageSize)
.Where(x => x.UserID == userid).ToListAsync());
}
}
public async Task <Bookmark> Add(Bookmark bookmark)
{
using (var db = new StackoverflowDbContext())
{
await db.Bookmarks.AddAsync(bookmark);
db.SaveChanges();
return(bookmark);
}
}
public async Task <Question> Add(Question q)
{
using (var db = new StackoverflowDbContext())
{
await db.Questions.AddAsync(q);
db.SaveChanges();
return(q);
}
}
public async Task <bool> Update(Comment com)
{
using (var db = new StackoverflowDbContext())
{
db.Comments.Update(com);
await db.SaveChangesAsync();
return(true);
}
}
public async Task <bool> Update(Note updateNote)
{
using (var db = new StackoverflowDbContext())
{
db.Notes.Update(updateNote);
await db.SaveChangesAsync();
return(true);
}
}
public async Task <Note> Add(Note note)
{
using (var db = new StackoverflowDbContext())
{
await db.Notes.AddAsync(note);
db.SaveChanges();
return(note);
}
}
public async Task <bool> Update(Question q)
{
using (var db = new StackoverflowDbContext())
{
db.Questions.Update(q);
await db.SaveChangesAsync();
return(true);
}
}
public async Task <IEnumerable <Post> > GetAll(PagingInfo pagingInfo)
{
using (var db = new StackoverflowDbContext())
{
return(await db.Questions
.Include(x => x.Answers)
.Skip(pagingInfo.Page * pagingInfo.PageSize)
.Take(pagingInfo.PageSize)
.ToListAsync());
}
}
public async Task <Comment> Add(int id, Comment comment)
{
using (var db = new StackoverflowDbContext())
{
comment.UserID = _user.ID;
comment.PostID = id;
await db.Comments.AddAsync(comment);
db.SaveChanges();
return(comment);
}
}
public async Task <IEnumerable <Question> > GetAll(PagingInfo pagingInfo)
{
using (var db = new StackoverflowDbContext())
{
return(await db.Questions
.Include(x => x.PostTags)
.Include(x => x.User)
//.Include(x => x.Notes)
.Skip((pagingInfo.Page - 1) * pagingInfo.PageSize)
.Take(pagingInfo.PageSize)
.ToListAsync());
}
}
public async Task <bool> Delete(int id)
{
using (var db = new StackoverflowDbContext())
{
var q = await Get(id);
if (q == null)
{
return(false);
}
db.Questions.Remove(q);
await db.SaveChangesAsync();
return(true);
}
}
public async Task <bool> Delete(int id)
{
using (var db = new StackoverflowDbContext())
{
var comment = await Get(id);
if (comment == null)
{
return(false);
}
db.Comments.Remove(comment);
await db.SaveChangesAsync();
return(true);
}
}
public async Task <Question> GetQuestion(int id)
{
using (var db = new StackoverflowDbContext())
{
return(await db.Questions
.Include(x => x.Comments)
.ThenInclude(x => x.User)
.Include(x => x.Answers)
.ThenInclude(x => x.Comments)
.ThenInclude(x => x.User)
.Include(x => x.PostTags)
.Include(x => x.Bookmarks)
.Include(x => x.Notes)
.FirstOrDefaultAsync(x => x.ID == id));
}
}
public async Task <IEnumerable <QuestionCommentsDto> > GetQuestionComments(int id)
{
using (var db = new StackoverflowDbContext())
{
return(await db.Comments
.Where(q => q.PostID == id)
.Select(q => new QuestionCommentsDto
{
ID = q.ID,
Score = q.Score,
Text = q.Text,
Creationdate = q.CreationDate,
})
.ToListAsync());
}
}
public async Task <bool> Delete(int postId)
{
using (var db = new StackoverflowDbContext())
{
var bookmark = await Get(postId);
if (bookmark == null)
{
return(false);
}
db.Bookmarks.Remove(bookmark);
await db.SaveChangesAsync();
return(true);
}
}
public async Task <IEnumerable <QuestionAnswersDto> > GetQuestionAnswers(int id)
{
using (var db = new StackoverflowDbContext())
{
return(await db.Answers
.Where(q => q.ParentID == id)
.Select(q => new QuestionAnswersDto
{
ID = q.ID,
Score = q.Score,
Body = q.Body,
Creationdate = q.CreationDate,
})
.ToListAsync());
}
}
public async Task <bool> Delete(int userId)
{
using (var db = new StackoverflowDbContext())
{
var search = await Get(userId);
if (search == null)
{
return(false);
}
db.Searches.Remove(search);
await db.SaveChangesAsync();
return(true);
}
}
public async Task <Tuple <IList <SearchResultDto>, int> > BestMatchWeighted(string query, PagingInfo pagingInfo, string startDate, string endDate)
{
using (var db = new StackoverflowDbContext())
{
var conn = (MySqlConnection)db.Database.GetDbConnection();
conn.Open();
var cmd = InitCommand(conn, query, pagingInfo, startDate, endDate);
cmd.CommandText = "call BestMatchWeighted(@query, @pageSize, @pageNumber, @startDate, @endDate)";
var result = await ReadFromDatabase(cmd);
var numberOfRows = await GetNumberOfRows(cmd, "call BestMatchWeighted_Count(@query, @startDate, @endDate)");
return(new Tuple <IList <SearchResultDto>, int>(result, numberOfRows));
}
}
public async Task <IList <SearchResultDto> > MatchAll(string query, PagingInfo pagingInfo, string method, string sortby, string orderby)
{
using (var db = new StackoverflowDbContext())
{
var result = new List <SearchResultDto>();
var conn = (MySqlConnection)db.Database.GetDbConnection();
conn.Open();
var cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.Parameters.Add("@query", DbType.String);
cmd.Parameters["@query"].Value = query;
cmd.CommandText = "call MatchAll(@query)";
var reader = cmd.ExecuteReader();
while (await reader.ReadAsync())
{
result.Add(new SearchResultDto
{
Id = (int)reader["id"],
Rank = (decimal)reader["rank"],
Body = (string)reader["body"]
});
}
// TODO: perhaps move to stored procedure AND/OR create helper for methods ?
if (!string.IsNullOrEmpty(orderby) && orderby == "\"asc\"" || orderby == "\"desc\"")
{
result = orderby == "\"desc\""
? result.OrderBy(x => x.Rank).ToList()
: result.OrderBy(x => x.Rank).Reverse().ToList();
}
// TODO: fetch correct data and implement it
//if (!string.IsNullOrEmpty(sortby))
//{
// result = result.Where(date is within sortby date);
//}
return(result);
}
}
public async Task <IList <SearchResultDto> > Bestmatch(string query, PagingInfo pagingInfo, string method, string sortby, string orderby)
{
using (var db = new StackoverflowDbContext())
{
var result = new List <SearchResultDto>();
var conn = (MySqlConnection)db.Database.GetDbConnection();
conn.Open();
var cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.Parameters.Add("@pageSize", DbType.Int32);
cmd.Parameters.Add("@pageNumber", DbType.Int32);
cmd.Parameters.Add("@query", DbType.String);
cmd.Parameters["@pageSize"].Value = pagingInfo.PageSize;
cmd.Parameters["@pageNumber"].Value = pagingInfo.Page;
cmd.Parameters["@query"].Value = query;
cmd.CommandText = "call bestmatch(@query, @pageSize, @pageNumber)";
var reader = cmd.ExecuteReader();
while (await reader.ReadAsync())
{
Console.WriteLine("{0}, {1}", reader.GetInt32(0), reader.GetInt32(1));
result.Add(new SearchResultDto
{
Id = (int)reader["id"],
Rank = (decimal)reader["rank"]
});
}
return(result);
}
}
public async Task <TermNetworkDto> TermNetwork(string word, double grade)
{
using (var db = new StackoverflowDbContext())
{
var conn = (MySqlConnection)db.Database.GetDbConnection();
conn.Open();
var cmd = new MySqlCommand
{
Connection = conn
};
cmd.Parameters.Add("@w", DbType.String);
cmd.Parameters.Add("@n", DbType.Double);
cmd.Parameters["@w"].Value = word;
cmd.Parameters["@n"].Value = grade;
cmd.CommandText = "call term_network(@w, @n)";
var result = new TermNetworkDto();
var sb = new StringBuilder();
using (var reader = await cmd.ExecuteReaderAsync())
{
await reader.ReadAsync();
while (await reader.ReadAsync())
{
sb.Append((string)reader["var graph = "]);
}
}
result.Graph = sb.ToString();
return(result);
}
}
