Navigating a many-to-many Tables relationship in a Dataset (Optimization)

Question

Hello,

I have a Dataset that contains tables from a university database, two(actually more) of these tables contain a many to many relationship, specifically I have a,

Students table (Contains information about students) Courses table (Contains information about the courses) TakesCourses table (Which is an intermediary table between the Students and Courses tables, every student can have many courses)

I want to input a "student ID" and fetch all the records from the Courses table according to the records found in TakesCourses table.

My code WORKS and it is as follows:

string stdInfo = string.Empty;

DataRow[] drStudent = null;
DataRow drCourses = null;
DataRow[] drStdCrs = null;

drStudent = universityDS.Tables["Students"]
    .Select(string.Format("StudentID='{0}'", txtStudentID.Text));

stdInfo += string.Format("Student {0} {1}:\nTaking Courses:",
    drStudent[0]["FirstName"].ToString().Trim(),
    drStudent[0]["LastName"].ToString().Trim());

drStdCrs = drStudent[0].GetChildRows(
    universityDS.Relations["FK_TakesCourses_Students"]);

//Can I optimize here? Is there a better way to code this
if (drStdCrs.Length > 0)
{
    for (int i = 0; i < drStdCrs.Length; i++)
    {
        drCourses = drStdCrs[i].GetParentRow(
            universityDS.Relations["FK_TakesCourses_Courses"]);

        stdInfo += string.Format("\nCourse: {0}",
            drCourses["CourseName"]);
    }               
}

MessageBox.Show(stdInfo);

My question is, how can I optimize the code after the comments? is there a better way to write this?

(Note: I am a recovering developer, trying to refresh my skills)

Answer 1

If the version of the .NET framework you are using (or able to use based on the project) supports LINQ2SQL I HIGHLY recommend you take the time to learn LINQ as it will make your life a thousand times easier both when dealing with SQL and in every day coding with .NET.

In this day and age I would avoid in-line SQL unless it is your only resort, as it's often punishable by death in most establishments :¬(.

Answer 2

I totally agree with JDoig. Here is an example of how your code might look like while using LINQ to SQL:

string stdInfo = string.Empty;
string studentId = txtStudentID.Text;

using (var db = new CourseDataContext())
{
    Student student = (
        from student in db.Students
        where student.StudentID == studentId
        select student).Single();

    stdInfo += string.Format("Student {0} {1}:\nTaking Courses:",
        student.FirstName, student.LastName);

    var courseNames =
        from taken in student.TakesCourses
        select taken.Course.CourseName;

    foreach (string courseName in courseNames)
    {
        stdInfo += string.Format("\nCourse: {0}", courseNames);
    }
}

MessageBox.Show(stdInfo);

As you will see it takes far less code and does a much better job in showing the intent of the code.