If I'm correct from your question I understood that u might be using ADO.NET directly for connecting to database
if u are using ADO.Net
public IEnumerable<instructor> GetInstructorsADONET()
{
var connString = ConfigurationManager.ConnectionStrings["SchoolContext"].ConnectionString;
List<instructor> instructors = new List<instructor>();
using (var conn = new SqlConnection(connString))
{
conn.Open();
var cmd = new SqlCommand("SELECT Person.PersonID, Person.LastName, Person.FirstName, Course.Title, Course.Credits " +
"FROM Course INNER JOIN " +
"CourseInstructor ON Course.CourseID = CourseInstructor.CourseID RIGHT OUTER JOIN " +
"Person ON CourseInstructor.PersonID = Person.PersonID " +
"WHERE (Person.Discriminator = 'Instructor') " +
"ORDER BY Person.PersonID", conn);
var reader = cmd.ExecuteReader();
int currentPersonID = 0;
Instructor currentInstructor = null;
while (reader.Read())
{
var personID = Convert.ToInt32(reader["PersonID"]);
if (personID != currentPersonID)
{
currentPersonID = personID;
if (currentInstructor != null)
{
instructors.Add(currentInstructor);
}
currentInstructor = new Instructor();
currentInstructor.LastName = reader["LastName"].ToString();
currentInstructor.FirstMidName = reader["FirstName"].ToString();
}
if (reader["Title"] != DBNull.Value)
{
var course = new Course();
course.Title = reader["Title"].ToString();
course.Credits = Convert.ToInt32(reader["Credits"]);
currentInstructor.Courses.Add(course);
}
}
if (currentInstructor != null)
{
instructors.Add(currentInstructor);
}
reader.Close();
cmd.Dispose();
}
return instructors;
}</instructor></instructor></instructor>
To avoid more code to be written and tested you can use ORM(object Relational Mapping)
public IEnumerable<Instructor> GetInstructors()
{
List<Instructor> instructors;
using (var db = new SchoolContext())
{
instructors = db.Instructors.Include("Courses").ToList();
}
return instructors;
}
Its not compulsory to use Entity framework depending upon your application and for any plans to migrate the data.
Refer this links
http://msdn.microsoft.com/en-us/library/ms178359(v=vs.110).aspx#orm[
^]
http://msdn.microsoft.com/en-us/library/jj653752(v=vs.110).aspx[
^]