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);
}
