Kingkong Kingkong - 17 days ago 3
SQL Question

Return object through 3 lists and one object

I have a query that gives me a Quiz with a Category, with a question and an answer, as you can see in the query below. However, I want to only use one reader, and I want it to be as object oriented as possible meaning I want Quiz to be the root object. Everything has to go through Quiz.

How can i return a quiz with a list of categories, with another list of questions and another list of answers?

Code below:

public Quiz GetQuizWithCategoriesWithQuestionsWithAnswers(int id)
{
Quiz quiz = null;
Category category = null;
Question question = null;
Answer answer = null;
dbConnection.connection.Open();
using (SqlCommand command = dbConnection.connection.CreateCommand())
{
command.CommandText = "SELECT Quiz.name, Category.name AS catName, Category.id as CatId, Category.quizId, Question.[description] as questDesc, Answer.[description] as ansDesc FROM Quiz JOIN Category ON Category.quizId = Quiz.id JOIN Question ON Question.categoryId = Category.id JOIN Answer ON Answer.questionId = Question.id WHERE Quiz.id = @id";
command.Parameters.Add("@id", SqlDbType.Int).Value = id;
var reader = command.ExecuteReader();
while (reader.Read())
{
if (quiz == null)
{
quiz = new Quiz();
quiz.name = reader.GetString(reader.GetOrdinal("name"));
quiz.id = reader.GetInt32(reader.GetOrdinal("quizId"));
}
if (category != null)
{
if (category.id != reader.GetInt32(reader.GetOrdinal("CatId")))
{
category = new Category();
category.id = reader.GetInt32(reader.GetOrdinal("CatId"));
category.name = reader.GetString(reader.GetOrdinal("catName"));
quiz.categories.Add(category);
}
}
else
{
category = new Category();
category.id = reader.GetInt32(reader.GetOrdinal("CatId"));
category.name = reader.GetString(reader.GetOrdinal("catName"));
quiz.categories.Add(category);
}
if (question != null)
{
//You'll need to get the question ID or just use the description here instead for comparison
if (question.description != reader.GetString(reader.GetOrdinal("questDesc")))
{
question = new Question();
question.description = reader.GetString(reader.GetOrdinal("questDesc"));
category.question.Add(question);
}
}
else
{
question = new Question();
question.description = reader.GetString(reader.GetOrdinal("questDesc"));
category.question.Add(question);
}

answer = new Answer();
answer.description = reader.GetString(reader.GetOrdinal("ansDesc"));
question.Answers.Add(answer);
}
}
dbConnection.connection.Close();
return quiz;
}

public class Quiz
{
[DataMember]
public int id { get; set; }
[DataMember]
public string name { get; set; }
[DataMember]
public List<Category> categories { get; set; }
[DataMember]
public List<Player> players { get; set; }

public Quiz()
{
categories = new List<Category>();
}
}

public class Category
{
[DataMember]
public int id { get; set; }
[DataMember]
public string name { get; set; }
[DataMember]
public int amount { get; set; }
[DataMember]
public List<Quiz> quiz { get; set; }
[DataMember]
public List<Question> question { get; set; }

public Category()
{
question = new List<Question>();
}
}

public class Question
{

[DataMember]
public int id { get; set; }
[DataMember]
public string description { get; set; }
[DataMember]
public Category category { get; set; }
[DataMember]
public bool isAnswered { get; set; }
[DataMember]
public List<Answer> Answers { get; set; }

public Question()
{
Answers = new List<Answer>();
}
}

public class Answer
{
[DataMember]
public int id { get; set; }
[DataMember]
public string description { get; set; }
[DataMember]
public Question question { get; set; }
[DataMember]
public bool isCorrect { get; set; }

}

Answer

I think you're problem is that you're loosing the values because you're re-declaring the Quiz object every time in the while loop, so it's loosing all it's previous values (the lists). You need to get the base values for the Quiz object outside the while loop (in another query and it's own loop) and separate queries for each list (to avoid duplicates in the joins)

Edit:

Or just check if the quiz object is instantiated and if new objects need to be created for categories and questions:

using (SqlCommand command = dbConnection.connection.CreateCommand())
            {
                command.CommandText = "SELECT Quiz.name, QuizCategory.quizId, QuizCategory.categoryId, Category.name AS catName, Question.[description], Answer.[description] FROM Quiz JOIN QuizCategory ON QuizCategory.quizId = Quiz.id JOIN Category ON Category.id = QuizCategory.categoryId JOIN Question ON Question.categoryId = Category.id JOIN Answer ON Answer.questionId = Question.id WHERE Quiz.id = @id";
                command.Parameters.Add("@id", SqlDbType.Int).Value = id;
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    if(quiz==null)
                    {
                        quiz = new Quiz();
                        quiz.name = reader.GetString(reader.GetOrdinal("name"));
                        quiz.id = reader.GetInt32(reader.GetOrdinal("quizId"));
                    }

                    if(question!=null)
                    {
                        //You'll need to get the question ID or just use the description here instead for comparison
                        if(question.id!=reader.GetInt32(reader.GetOrdinal("questionId"))
                        {
                            category.question.Add(question);
                            question = new Question();
                            question.description = reader.GetString(reader.GetOrdinal("description"));
                        }
                    }
                    else
                    {
                        question = new Question();
                        question.description = reader.GetString(reader.GetOrdinal("description"));
                    }                        

                    if(category!=null)
                    {
                        if(category.id!=reader.GetInt32(reader.GetOrdinal("categoryId"))
                        {
                            quiz.categories.Add(category);
                            category = new Category();
                            category.id = reader.GetInt32(reader.GetOrdinal("categoryId"));
                            category.name = reader.GetString(reader.GetOrdinal("catName"));
                        }
                    }
                    else
                    {
                        category = new Category();
                        category.id = reader.GetInt32(reader.GetOrdinal("categoryId"));
                        category.name = reader.GetString(reader.GetOrdinal("catName"));
                    }

                    answer = new Answer();
                    answer.description = reader.GetString(reader.GetOrdinal("description"));
                    question.Answers.Add(answer);

                }
            }
            if(question!=null)
            {
                category.question.Add(question);
            }
            if(category!=null)
            {
                quiz.categories.Add(category);
            }
Comments