protected void GetDepartment()
        {
            // Populate form with existing department record
            Int32 DepartmentID = Convert.ToInt32(Request.QueryString["DepartmentID"]);

            using (comp2007Entities db = new comp2007Entities())
            {
                Department d = (from objS in db.Departments
                                where objS.DepartmentID == DepartmentID
                                select objS).FirstOrDefault();

                if (d != null)
                {
                    txtName.Text = d.Name;
                    txtBudget.Text = Convert.ToString(d.Budget);
                }

                // Populate Courses that belong to this department in table below form
                var objE = (from c in db.Courses
                            join dept in db.Departments on c.DepartmentID equals dept.DepartmentID
                            where dept.DepartmentID == DepartmentID
                            select new { c.CourseID, c.Title, c.Credits });

                pnlCourses.Visible = true;

                grdCourses.DataSource = objE.ToList();
                grdCourses.DataBind();
            }
        }
        protected void GetStudent()
        {
            // Populate form with existing student record
            Int32 StudentID = Convert.ToInt32(Request.QueryString["StudentID"]);

            using (comp2007Entities db = new comp2007Entities())
            {
                Student s = (from objS in db.Students
                             where objS.StudentID == StudentID
                             select objS).FirstOrDefault();

                if (s != null)
                {
                    txtLastName.Text = s.LastName;
                    txtFirstName.Text = s.FirstMidName;
                    txtEnrollmentDate.Text = s.EnrollmentDate.ToString("yyyy-MM-dd");
                }

                //enrollments - this code goes in the same method that populates the student form but below the existing code that's already in GetStudent()
                var objE = (from en in db.Enrollments
                            join c in db.Courses on en.CourseID equals c.CourseID
                            join d in db.Departments on c.DepartmentID equals d.DepartmentID
                            where en.StudentID == StudentID
                            select new { en.EnrollmentID, en.Grade, c.Title, d.Name});

                pnlCourses.Visible = true;

                grdCourses.DataSource = objE.ToList();
                grdCourses.DataBind();

            }
        }
        protected void btnSave_Click(object sender, EventArgs e)
        {
            // Use EF to connect to SQL Server
            using (comp2007Entities db = new comp2007Entities())
            {
                // Use the Department Model to save the new record
                Department d = new Department();
                Int32 DepartmentID = 0;

                // Check the QueryString for an ID so we can determine add / update
                if (Request.QueryString["DepartmentID"] != null)
                {
                    // Get the ID from the URL
                    DepartmentID = Convert.ToInt32(Request.QueryString["DepartmentID"]);

                    // Get the current student from the Enity Framework
                    d = (from objS in db.Departments
                         where objS.DepartmentID == DepartmentID
                         select objS).FirstOrDefault();

                }
                d.Name = txtName.Text;
                d.Budget = Convert.ToDecimal(txtBudget.Text);

                // Call add only if we have no department ID
                if (DepartmentID == 0)
                {
                    db.Departments.Add(d);
                }
                db.SaveChanges();

                // Redirect to the updated departments page
                Response.Redirect("departments.aspx");
            }
        }
        protected void grdCourses_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            // Store which row was clicked
            Int32 selectedRow = e.RowIndex;

            // Get the selected CourseID using the grid's Data Key collection
            Int32 CourseID = Convert.ToInt32(grdCourses.DataKeys[selectedRow].Values["CourseID"]);

            // Use Enity Framework to remove the selected student from the DB
            using (comp2007Entities db = new comp2007Entities())
            {
                // Check for enrollments in the course, and remove them. Otherwise, it'll throw a foreign key exception
                foreach (Enrollment en in db.Enrollments)
                {
                    if (en.CourseID == CourseID)
                    {
                        db.Enrollments.Remove(en);
                    }
                }

                Course c = (from objS in db.Courses
                                where objS.CourseID == CourseID
                                select objS).FirstOrDefault(); // Using First would get an error if no data comes back, FirstOrDefault won't throw an error

                // Do the delete
                db.Courses.Remove(c);
                db.SaveChanges();
            }

            // Refresh the grid
            GetCourses();
        }
        protected void GetCourses()
        {
            // Connect to EF
            using (comp2007Entities db = new comp2007Entities())
            {
                String sortString = Session["SortColumn"].ToString() + " " + Session["SortDirection"].ToString();
                // Query the Courses table, using the Enity Framework
                var Courses = from c in db.Courses
                              select new { c.CourseID, c.Title, c.Credits, c.Department.Name };

                grdCourses.DataSource = Courses.AsQueryable().OrderBy(sortString).ToList();
                grdCourses.DataBind();
            }
        }
        protected void GetStudents()
        {
            // Connect to EF
            using (comp2007Entities db = new comp2007Entities())
            {
                String sortString = Session["SortColumn"].ToString() + " " + Session["SortDirection"].ToString();
                // Query the Students table, using the Enity Framework
                var Students = from s in db.Students
                               select s;

                // Bind the result to the gridview
                grdStudents.DataSource = Students.AsQueryable().OrderBy(sortString).ToList();
                grdStudents.DataBind();
            }
        }
        protected void btnSave_Click(object sender, EventArgs e)
        {
            // Use EF to connect to SQL Server
            using (comp2007Entities db = new comp2007Entities())
            {
                // Use the Course Model to save the new record
                Course c = new Course();
                Int32 CourseID = 0;
                Int32 DepartmentID = Convert.ToInt32(ddlDepartment.SelectedValue);

                // Check the QueryString for an ID so we can determine add / update
                if (Request.QueryString["CourseID"] != null)
                {
                    // Get the ID from the URL
                    CourseID = Convert.ToInt32(Request.QueryString["CourseID"]);

                    // Get the current Course from the Enity Framework
                    c = (from objS in db.Courses
                         where objS.CourseID == CourseID
                         select objS).FirstOrDefault();

                }
                c.Title = txtTitle.Text;
                c.Credits = Convert.ToInt32(txtCredits.Text);
                c.DepartmentID = DepartmentID;

                // Call add only if we have no Course ID
                if (CourseID == 0)
                {
                    db.Courses.Add(c);
                }
                db.SaveChanges();

                // Redirect to the updated courses page
                Response.Redirect("courses.aspx");
            }
        }
        protected void btnAdd_Click(object sender, EventArgs e)
        {
            Int32 StudentID = Convert.ToInt32(ddlAddStudent.SelectedValue);
            Int32 CourseID = Convert.ToInt32(Request.QueryString["CourseID"]);
            Boolean alreadyExists = false;
            Enrollment en = new Enrollment();

            using (comp2007Entities db = new comp2007Entities())
            {
                // Loop through the enrollment table, and check to see if the student is already signed up for this course
                foreach (Enrollment enroll in db.Enrollments)
                {
                    if (enroll.StudentID == StudentID && enroll.CourseID == CourseID)
                    {
                        alreadyExists = true;
                    }
                }
                // If the enrollment doesn't exist, add the student to the course.
                if (!alreadyExists)
                {
                    en.StudentID = StudentID;
                    en.CourseID = CourseID;

                    db.Enrollments.Add(en);

                    db.SaveChanges();
                    warningLabel.Visible = false;
                }
                else
                {
                    warningLabel.Visible = true;
                }
            }

            GetCourse();
        }
        protected void btnSave_Click(object sender, EventArgs e)
        {
            // Use EF to connect to SQL Server
            using (comp2007Entities db = new comp2007Entities())
            {
                // Use the Student Model to save the new record
                Student s = new Student();
                Int32 StudentID = 0;

                // Check the QueryString for an ID so we can determine add / update
                if (Request.QueryString["StudentID"] != null)
                {
                    // Get the ID from the URL
                    StudentID = Convert.ToInt32(Request.QueryString["StudentID"]);

                    // Get the current student from the Enity Framework
                    s = (from objS in db.Students
                         where objS.StudentID == StudentID
                         select objS).FirstOrDefault();

                }
                s.LastName = txtLastName.Text;
                s.FirstMidName = txtFirstName.Text;
                s.EnrollmentDate = Convert.ToDateTime(txtEnrollmentDate.Text);

                // Call add only if we have no student ID
                if (StudentID == 0)
                {
                    db.Students.Add(s);
                }
                db.SaveChanges();

                // Redirect to the updated students page
                Response.Redirect("students.aspx");
            }
        }
        protected void grdCourses_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            Int32 CourseID = Convert.ToInt32(grdCourses.DataKeys[e.RowIndex].Values["CourseID"]);

            using (comp2007Entities db = new comp2007Entities())
            {
                // Check for any enrollments in the course to be deleted, and remove them. Otherwise, it'll
                // throw a foreign key exception
                foreach (Enrollment en in db.Enrollments)
                {
                    if (en.CourseID == CourseID)
                    {
                        db.Enrollments.Remove(en);
                    }
                }

                // Find the selected course
                Course c = (from objS in db.Courses
                            where objS.CourseID == CourseID
                            select objS).FirstOrDefault(); // Using First would get an error if no data comes back, FirstOrDefault won't throw an error

                // Do the delete
                db.Courses.Remove(c);
                db.SaveChanges();

                //Refresh the data on the page
                GetDepartment();
            }
        }
        protected void grdDepartments_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            // Store which row was clicked
            Int32 selectedRow = e.RowIndex;

            // Get the selected DepartmentID using the grid's Data Key collection
            Int32 DepartmentID = Convert.ToInt32(grdDepartments.DataKeys[selectedRow].Values["DepartmentID"]);

            // Use Enity Framework to remove the selected student from the DB
            using (comp2007Entities db = new comp2007Entities())
            {
                // Need to clear out any courses inside of Department, and need to clear out any enrollments inside of each course. Otherwise,
                // i'll get a foreign key restraint error.
                foreach (Course c in db.Courses)
                {
                    if (c.DepartmentID == DepartmentID)
                    {
                        foreach (Enrollment en in db.Enrollments)
                        {
                            if (en.CourseID == c.CourseID)
                            {
                                db.Enrollments.Remove(en);
                            }
                        }
                        db.Courses.Remove(c);
                    }
                }

                Department d = (from objS in db.Departments
                                where objS.DepartmentID == DepartmentID
                                select objS).FirstOrDefault(); // Using First would get an error if no data comes back, FirstOrDefault won't throw an error

                // Do the delete
                db.Departments.Remove(d);
                db.SaveChanges();
            }

            // Refresh the grid
            GetDepartments();
        }
        protected void grdStudents_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            // Store which row was clicked
            Int32 selectedRow = e.RowIndex;

            // Get the selected StudentID using the grid's Data Key collection
            Int32 StudentID = Convert.ToInt32(grdStudents.DataKeys[selectedRow].Values["StudentID"]);

            // Use Enity Framework to remove the selected student from the DB
            using (comp2007Entities db = new comp2007Entities())
            {
                Student s = (from objS in db.Students
                             where objS.StudentID == StudentID
                             select objS).FirstOrDefault(); // Using First would get an error if no data comes back, FirstOrDefault won't throw an error

                // Do the delete
                db.Students.Remove(s);
                db.SaveChanges();
            }

            // Refresh the grid
            GetStudents();
        }
        protected void GetCourse()
        {
            // Populate form with existing Course record
            Int32 CourseID = Convert.ToInt32(Request.QueryString["CourseID"]);

            using (comp2007Entities db = new comp2007Entities())
            {
                Course c = (from objS in db.Courses
                                where objS.CourseID == CourseID
                                select objS).FirstOrDefault();

                Department selectedItem = (from objD in db.Departments
                                       where c.DepartmentID == objD.DepartmentID
                                       select objD).FirstOrDefault();

                if (c != null)
                {
                    txtTitle.Text = c.Title;
                    txtCredits.Text = Convert.ToString(c.Credits);
                    ddlDepartment.SelectedValue = selectedItem.DepartmentID.ToString();
                }

                //enrollments - This will show who is enrolled in the course
                var objE = (from en in db.Enrollments
                            join st in db.Students on en.StudentID equals st.StudentID
                            where en.CourseID == CourseID
                            select new { en.EnrollmentID, st.StudentID, st.LastName, st.FirstMidName, st.EnrollmentDate });

                pnlCourses.Visible = true;

                grdStudents.DataSource = objE.ToList();
                grdStudents.DataBind();

            }
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            // If save wasn't clicked AND we have a CourseID in the URL
            if ((!IsPostBack))
            {
                using (comp2007Entities db = new comp2007Entities()) {
                    // Add available departments to a dropdown list
                    foreach (Department d in db.Departments)
                    {
                        ddlDepartment.Items.Add(new ListItem(d.Name, d.DepartmentID.ToString()));
                    }

                }
                if (Request.QueryString.Count > 0)
                {
                    GetCourse();
                    using (comp2007Entities db = new comp2007Entities())
                    {
                        // Add available students to a drop down list
                        foreach (Student s in db.Students)
                        {
                            ddlAddStudent.Items.Add(new ListItem(s.LastName + ", " + s.FirstMidName, s.StudentID.ToString()));
                        }
                    }
                }
            }
        }
        protected void grdStudents_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            // Get the student's enrollment ID
            Int32 EnrollmentID = Convert.ToInt32(grdStudents.DataKeys[e.RowIndex].Values["EnrollmentID"]);

            using (comp2007Entities db = new comp2007Entities())
            {
                Enrollment objE = (from en in db.Enrollments
                                   where en.EnrollmentID == EnrollmentID
                                   select en).FirstOrDefault();

                //Delete
                db.Enrollments.Remove(objE);
                db.SaveChanges();

                //Refresh the data on the page
                GetCourse();
            }
        }