C# & ASP.NET MVC: How to populate a data object model within another model

Question

I have a model that handles questions to display on an ASP.NET MVC form. One of the properties within my Question model is the type of question the item is (QuestionType); this is its own separate model class, however my problem is I'm having trouble reading the QuestionType values from the database and then updating the QuestionType property within my Question model class.

For example, I'd like to be able to do the following, but I don't know how to populate my QuestionType correctly:

string myQuestionTypeName = model.Question.QuestionType.Name;

Question model class:

public class Question
{
    public int QuestionId { get; set; }
    public string Name { get; set; }
    public QuestionType QuestionType { get; set; }    
}

QuestionType model class:

public class QuestionType
{
    public int QuestionTypeId { get; set; }
    public string Name { get; set; }
}

Question data - pulls from my Question database table.

My stored procedure returns:

• QuestionId
• Name
• QuestionTypeId

Code:

public List<Question> PullList()
{
    List<Question> questionList = new List<question>();

    using (Sql sql = new Sql(Sql.Connection.MyDbConnectionName, "storedProc_PullQuestions"))
    {
        using (var ds = sql.GetDataSet())
        {
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                Question question = new Question();
                question.QuestionId = dr.Field<int>("QuestionId");
                question.Name = dr.Field<string>("Name");
                question.QuestionType = //??? Not sure what to do here.  I have the "QuestionTypeId" from the stored procedure, but not sure how to populate this object based on that id.

                questionList.Add(question);
            }
        }
    }

    return questionList;
}

QuestionType data - pulls from my QuestionType table; my stored procedure returns:

• QuestionTypeId
• Name

Code:

public List<QuestionType> PullList()
{
    List<QuestionType> questionTypeList = new List<QuestionType>();

    using (Sql sql = new Sql(Sql.Connection.MyDbConnectionName, "storedProc_PullQuestionTypes"))
    {
        using (var ds = sql.GetDataSet())
        {
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                QuestionType questionType = new QuestionType();
                questionType.QuestionTypeId = dr.Field<int>("QuestionTypeId");
                questionType.Name = dr.Field<string>("Name");

                questionTypeList.Add(questionType);
            }
        }
    }

    return questionTypeList;
}

Thanks for any help you can provide

Answer 1

You have to join 2 tables in your select query of stored procedure and after this

foreach (DataRow dr in ds.Tables[0].Rows)
{
     Question question = new Question();
     question.QuestionType = new QuestionType();
     question.QuestionId = dr.Field<int>("QuestionId");
     question.Name = dr.Field<string>("Name");
      question.QuestionType.QuestionTypeId = dr.Field<int>"QuestionTypeId");
     question.QuestionType.Name = dr.Field<string>("QuestionTypeName");

     questionList.Add(question);
}