public DataTable GetPostList(string postSearh) { var dataTable = new DataTable(); using (var context = new FeedBackDBContext()) { using (var command = context.Database.GetDbConnection().CreateCommand()) { command.CommandText = @"SELECT p.ID as PostID, p.Post, u.UserID , p.CreateDate, sum(c.ID) as TotalComment from PostInfo as p inner join UserInfo u on u.ID = p.UserID inner join CommentInfo c on c.PostID = p.ID where p.Post like '%" + postSearh + "%' Group by p.ID, p.Post, u.UserID , p.CreateDate"; context.Database.OpenConnection(); var dataReader = command.ExecuteReader(); dataTable.Load(dataReader); } } return(dataTable); }
public DataTable GetCustomVotList(int postID) { var dataTable = new DataTable(); using (var context = new FeedBackDBContext()) { using (var command = context.Database.GetDbConnection().CreateCommand()) { command.CommandText = @"select p.Post as Post, c.Comment as Comment, u.UserID as UserID, c.CreateDate as CommentDate, (select sum(ID) from VoteInfo v where v.CommentID = c.ID and v.AggreByVote = 1) as AggreCount, (select sum(ID) from VoteInfo v where v.CommentID = c.ID and v.AggreByVote = 0) as DisAggreCount from CommentInfo as c inner join PostInfo p on c.PostID = p.ID inner join UserInfo u on u.ID = c.UserID where p.ID = " + postID.ToString(); context.Database.OpenConnection(); var dataReader = command.ExecuteReader(); dataTable.Load(dataReader); } } return(dataTable); }