protected void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                //use EG 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 EF
                        s = (from objs in db.Students
                             where objs.StudentID == StudentID
                             select objs).FirstOrDefault();
                    }

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

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

                    //run the update or insert
                    db.SaveChanges();

                    //redirect
                    Response.Redirect("students.aspx");
                }
            }
            catch (Exception err)
            {
                Server.Transfer("/error.aspx");
            }
        }
        protected void GetStudent()
        {
            //populate form wih existing student record
            Int32 StudentID = Convert.ToInt32(Request.QueryString["StudentID"]);

            try
            {
                //connect to db via EF
                using (comp2007Entities db = new comp2007Entities())
                {
                    Student s = (from objs in db.Students
                                 where objs.StudentID == StudentID
                                 select objs).FirstOrDefault();

                    //map the student properties to the form controls
                    txtLastName.Text = s.LastName;
                    txtFirstMidName.Text = s.FirstMidName;
                    txtEnrollmentDate.Text = s.EnrollmentDate.ToShortDateString();

                    //map the student properties to the form controls if we found a match
                    if (s != null)
                    {
                        txtLastName.Text = s.LastName;
                        txtFirstMidName.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 });

                    grdCourses.DataSource = objE.ToList();
                    grdCourses.DataBind();
                }
            }
            catch (Exception err)
            {
                Server.Transfer("/error.aspx");
            }
        }
        protected void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                //use EG to connect to SQL Server
                using (comp2007Entities db = new comp2007Entities())
                {
                    //use the Student Model to save the new record

                    //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 EF
                        s = (from objs in db.Departments
                             where objs.DepartmentID == DepartmentID
                             select objs).FirstOrDefault();
                    }

                    s.Name = txtDepartmentName.Text;
                    s.Budget = Convert.ToDecimal(txtBudget.Text);

                    //call add only if we have no student ID
                    if (DepartmentID == 0)
                    {
                        db.Departments.Add(s);
                    }

                    db.SaveChanges();

                    //redirect
                    Response.Redirect("departments.aspx");
                }
            }
            catch (Exception err)
            {
                Server.Transfer("/error.aspx");
            }
        }
        protected void GetDepartments()
        {
            //populate form wih existing student record
            Int32 DepartmentID = Convert.ToInt32(Request.QueryString["DepartmentID"]);

            try
            {
                //connect to db via EF
                using (comp2007Entities db = new comp2007Entities())
                {
                    Department s = (from objs in db.Departments
                                    where objs.DepartmentID == DepartmentID
                                    select objs).FirstOrDefault();

                    //map the student properties to the form controls
                    txtDepartmentName.Text = s.Name;
                    txtBudget.Text = s.Budget.ToString();

                    //map the student properties to the form controls if we found a match
                    if (s != null)
                    {
                        txtDepartmentName.Text = s.Name;
                        txtBudget.Text = s.Budget.ToString();
                    }

                    //courses - this code goes in the same method that populates the student form but below the existing code that's already in GetDepartments()
                    var objE = (from c in db.Courses
                                join d in db.Departments on c.DepartmentID equals d.DepartmentID
                                where c.DepartmentID == DepartmentID
                                select new { c.DepartmentID, c.Title, c.Credits });

                    grdCourses.DataSource = objE.ToList();
                    grdCourses.DataBind();
                }
            }
            catch (Exception err)
            {
                Server.Transfer("/error.aspx");
            }
        }
        protected void GetCourses()
        {
            try
            {
                //connect to EF
                using (comp2007Entities db = new comp2007Entities())
                {
                    String SortString = Session["SortColumn"].ToString() + " " + Session["SortDirection"].ToString();

                    //query the students table using EF and LINQ
                    var Courses = from s in db.Courses
                                  select new { s.CourseID, s.Title, s.Credits, s.Department.Name };

                    //bind the result to the gridview
                    grdCourses.DataSource = Courses.AsQueryable().OrderBy(SortString).ToList();
                    grdCourses.DataBind();
                }
            }
            catch (Exception err)
            {
                Server.Transfer("/error.aspx");
            }
        }
        protected void GetStudents()
        {
            try
            {
                //connect to EF
                using (comp2007Entities db = new comp2007Entities())
                {
                    String SortString = Session["SortColumn"].ToString() + " " + Session["SortDirection"].ToString();

                    //query the students table using EF and LINQ
                    var Students = from s in db.Students
                                   select new { s.StudentID, s.LastName, s.FirstMidName, s.EnrollmentDate };

                    //bind the result to the gridview
                    grdStudents.DataSource = Students.AsQueryable().OrderBy(SortString).ToList();
                    grdStudents.DataBind();
                }
            }
            catch (Exception err)
            {
                Server.Transfer("/error.aspx");
            }
        }
        protected void grdCourses_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            //get selected record id
            Int32 EnrollmentID = Convert.ToInt32(grdCourses.DataKeys[e.RowIndex].Values["EnrollmentID"]);

            try
            {
                using (comp2007Entities db = new comp2007Entities())
                {
                    //get selected record
                    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
                    GetDepartments();
                }
            }
            catch (Exception err)
            {
                Server.Transfer("/error.aspx");
            }
        }
        protected void grdCourses_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 CourseID = Convert.ToInt32(grdCourses.DataKeys[selectedRow].Values["CourseID"]);

            try
            {
                //use EF to remove the selected student from the db
                using (comp2007Entities db = new comp2007Entities())
                {
                    Course s = (from objs in db.Courses
                                where objs.CourseID == CourseID
                                select objs).FirstOrDefault();

                    //do the delete
                    db.Courses.Remove(s);
                    db.SaveChanges();
                }

                //refresh the grid
                GetCourses();
            }
            catch (Exception err)
            {
                Server.Transfer("/error.aspx");
            }
        }