public Answer_CommentModel GetCommentModelToAnswer(int cid) { Answer_CommentModel answercommentmodel = new Answer_CommentModel(); using (var conn = new NpgsqlConnection(connectingString)) { conn.Open(); using (var command = new NpgsqlCommand($"SELECT * FROM answer_comment WHERE comment_id = {cid}", conn)) { var reader = command.ExecuteReader(); while (reader.Read()) { var comment_id = Convert.ToInt32(reader["comment_id"]); var answer_id = Convert.ToInt32(reader["answer_id"]); var submission_time = Convert.ToDateTime(reader["submission_time"]); var comment_text = Convert.ToString(reader["comment_text"]); var edited_number = Convert.ToInt32(reader["edited_number"]); var user_id = Convert.ToInt32(reader["userid"]); answercommentmodel = new Answer_CommentModel(comment_id, comment_text, answer_id, submission_time, edited_number, user_id); } } } return(answercommentmodel); }
// --------------------------------------------------------------------------------- 1 public List <QuestionModel> GetQuestions() { List <UserModel> users = new List <UserModel>(); List <QuestionModel> questions = new List <QuestionModel>(); List <AnswerModel> answers = new List <AnswerModel>(); List <Question_CommentModel> questioncomments = new List <Question_CommentModel>(); List <Answer_CommentModel> answercomments = new List <Answer_CommentModel>(); using (var conn = new NpgsqlConnection(connectingString)) { conn.Open(); using (var command = new NpgsqlCommand($"SELECT * FROM question", conn)) { var reader = command.ExecuteReader(); while (reader.Read()) { QuestionModel questionModel = new QuestionModel(); var question_id = Convert.ToInt32(reader["question_id"]); var submission_time = Convert.ToDateTime(reader["submission_time"]); var view_number = Convert.ToInt32(reader["view_number"]); var vote_number = Convert.ToInt32(reader["vote_number"]); var downvote_number = Convert.ToInt32(reader["downvote_number"]); var question_title = Convert.ToString(reader["question_title"]); var question_text = Convert.ToString(reader["question_text"]); var question_image = Convert.ToString(reader["question_image"]); var question_messagenumber = Convert.ToInt32(reader["message_number"]); var user_id = Convert.ToInt32(reader["userid"]); questionModel = new QuestionModel(question_id, question_title, question_text, question_image, vote_number, downvote_number, view_number, submission_time, question_messagenumber, user_id); questions.Add(questionModel); } } conn.Close(); conn.Open(); using (var commandanswer = new NpgsqlCommand($"SELECT * FROM answer", conn)) { var readerans = commandanswer.ExecuteReader(); while (readerans.Read()) { AnswerModel answer = new AnswerModel(); var answer_id = Convert.ToInt32(readerans["answer_id"]); var adownvote_number = Convert.ToInt32(readerans["downvote_number"]); var aquestion_id = Convert.ToInt32(readerans["question_id"]); var asubmission_time = Convert.ToDateTime(readerans["submission_time"]); var avote_number = Convert.ToInt32(readerans["vote_number"]); var answer_text = Convert.ToString(readerans["answer_text"]); var answer_image = Convert.ToString(readerans["answer_image"]); var user_id = Convert.ToInt32(readerans["userid"]); answer = new AnswerModel(answer_id, aquestion_id, answer_text, answer_image, avote_number, adownvote_number, asubmission_time, user_id); answers.Add(answer); } } conn.Close(); conn.Open(); using (var commandcomment = new NpgsqlCommand($"SELECT * FROM question_comment", conn)) { var readercomm = commandcomment.ExecuteReader(); while (readercomm.Read()) { Question_CommentModel comment = new Question_CommentModel(); var comment_id = Convert.ToInt32(readercomm["comment_id"]); var cquestion_id = Convert.ToInt32(readercomm["question_id"]); var csubmission_time = Convert.ToDateTime(readercomm["submission_time"]); var comment_text = Convert.ToString(readercomm["comment_text"]); var comment_edited = Convert.ToInt32(readercomm["edited_number"]); var user_id = Convert.ToInt32(readercomm["userid"]); comment = new Question_CommentModel(comment_id, comment_text, cquestion_id, csubmission_time, comment_edited, user_id); questioncomments.Add(comment); } } conn.Close(); conn.Open(); using (var commandcomment = new NpgsqlCommand($"SELECT * FROM answer_comment", conn)) { var readercomm = commandcomment.ExecuteReader(); while (readercomm.Read()) { Answer_CommentModel comment = new Answer_CommentModel(); var comment_id = Convert.ToInt32(readercomm["comment_id"]); var canswer_id = Convert.ToInt32(readercomm["answer_id"]); var csubmission_time = Convert.ToDateTime(readercomm["submission_time"]); var comment_text = Convert.ToString(readercomm["comment_text"]); var comment_edited = Convert.ToInt32(readercomm["edited_number"]); var user_id = Convert.ToInt32(readercomm["userid"]); comment = new Answer_CommentModel(comment_id, comment_text, canswer_id, csubmission_time, comment_edited, user_id); answercomments.Add(comment); } } conn.Close(); conn.Open(); using (var command = new NpgsqlCommand($"SELECT * FROM users", conn)) { var reader = command.ExecuteReader(); while (reader.Read()) { UserModel user = new UserModel(); var user_id = Convert.ToInt32(reader["userid"]); var user_email = Convert.ToString(reader["user_email"]); var user_pass = Convert.ToString(reader["user_password"]); user = new UserModel(user_id, user_email, user_pass); users.Add(user); } } conn.Close(); foreach (var question in questions) { foreach (var answer in answers) { if (question.ID == answer.QuestionID) { question.AnswerModels.Add(answer); } } } foreach (var question in questions) { foreach (var answer in question.AnswerModels) { foreach (var comment in answercomments) { if (comment.AnswerID == answer.ID) { answer.CommentModels.Add(comment); } } } } foreach (var question in questions) { foreach (var comment in questioncomments) { if (question.ID == comment.QuestionID) { question.CommentModels.Add(comment); } } } foreach (var user in users) { foreach (var question in questions) { if (question.UserID == user.Id) { user.UserQuestions.Add(question); } } } foreach (var user in users) { foreach (var answer in answers) { if (answer.UserID == user.Id) { user.UserAnswers.Add(answer); } } } foreach (var user in users) { foreach (var answercomment in answercomments) { if (answercomment.UserID == user.Id) { user.UserAnswerComments.Add(answercomment); } } } foreach (var user in users) { foreach (var questioncomment in questioncomments) { if (questioncomment.UserID == user.Id) { user.UserQuestionComments.Add(questioncomment); } } } } return(questions); }