private void GetStudents()
{
// See how we can use a using statement rather than try-catch (this will close and dispose the connection similarly to a finally block
using (thisConnection = new SqlConnection(WebConfigurationManager.ConnectionStrings["Comp229Assign03"].ConnectionString))
{
SqlCommand comm = new SqlCommand("Select st.*, e.StudentID, e.Grade, e.CourseID, c.CourseID, " +
"c.Title from Students st" +
" join Enrollments e on st.StudentID = e.StudentID " +
"join Courses c on e.CourseID = c.CourseID " +
"where c.CourseID = @courseID;", thisConnection);
comm.Parameters.AddWithValue("@courseID", Int32.Parse(Session["courseID"].ToString()));
SqlCommand commAdd = new SqlCommand("Select distinct st.StudentID, FirstMidName + ' ' + LastName as FullName" +
" from Students st " +
"WHERE st.StudentID NOT IN " +
"( SELECT e.StudentID FROM Enrollments e " +
"where e.CourseID = @courseID);", thisConnection);
commAdd.Parameters.AddWithValue("@courseID", Int32.Parse(Session["courseID"].ToString()));
try
{
thisConnection.Open();
//display students in the course
SqlDataReader reader = comm.ExecuteReader();
StudentInfo.DataSource = reader;
StudentInfo.DataBind();
reader.Close();
//display students not in the course
studentList.DataSource = commAdd.ExecuteReader();
studentList.DataTextField = "FullName";
studentList.DataValueField = "StudentID";
studentList.DataBind();
studentList.Items.Insert(0, new ListItem("----- Select Student's Name -----", ""));
}
catch (Exception ex)
{
dbErrorMessage.Text = ex.Message;
}
finally
{
thisConnection.Close();
}
}
}
