protected void GetDepartment()
        {
            try
            {
                //connect
                using (DefaultConnectionEF conn = new DefaultConnectionEF())
                {
                    //get the department id
                    Int32 DepartmentID = Convert.ToInt32(Request.QueryString["DepartmentID"]);

                    //get department info
                    var d = (from dep in conn.Departments
                             where dep.DepartmentID == DepartmentID
                             select dep).FirstOrDefault();

                    //populate the form
                    txtName.Text = d.Name;
                    txtBudget.Text = d.Budget.ToString();

                    var objC = (from c in conn.Courses
                                where c.DepartmentID == DepartmentID
                                select new { c.Title, c.DepartmentID, c.CourseID, c.Credits });

                    grdDepCourses.DataSource = objC.ToList();
                    grdDepCourses.DataBind();

                    //show the course panel
                    pnlCourses.Visible = true;
                }
            }
            catch (Exception e)
            {
                Response.Redirect("~/error.aspx");
            }
        }
        protected void btnAdd_Click(object sender, EventArgs e)
        {
            try
            {
                using (DefaultConnectionEF conn = new DefaultConnectionEF())
                {

                    Int32 StudentID = Convert.ToInt32(Request.QueryString["StudentID"]);
                    Int32 CourseID = Convert.ToInt32(ddlCourse.SelectedValue);

                    Enrollment objE = new Enrollment();

                    objE.StudentID = StudentID;
                    objE.CourseID = CourseID;

                    conn.Enrollments.Add(objE);
                    conn.SaveChanges();

                    //refresh
                    GetStudents();

                }
            }
            catch (Exception exc)
            {
                Response.Redirect("~/error.aspx");
            }
        }
        protected void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                //connect
                using (DefaultConnectionEF conn = new DefaultConnectionEF())
                {
                    //instantiate a new student object in memory
                    Student s = new Student();

                    //decide if updating or adding, then save
                    if (Request.QueryString.Count > 0)
                    {
                        Int32 StudentID = Convert.ToInt32(Request.QueryString["StudentID"]);

                        s = (from stu in conn.Students
                             where stu.StudentID == StudentID
                             select stu).FirstOrDefault();
                    }

                    //fill the properties of our object from the form inputs
                    s.FirstMidName = txtFirstName.Text;
                    s.LastName = txtLastName.Text;
                    s.EnrollmentDate = Convert.ToDateTime(txtEnrollDate.Text);

                    if (Request.QueryString.Count == 0)
                    {
                        conn.Students.Add(s);
                    }
                    conn.SaveChanges();

                    //redirect to updated departments page
                    Response.Redirect("students.aspx");
                }
            }
            catch (Exception ex)
            {
                Response.Redirect("~/error.aspx");
            }
        }
        protected void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                //connect
                using (DefaultConnectionEF conn = new DefaultConnectionEF())
                {
                    //instantiate a new student object in memory
                    Course c = new Course();

                    //decide if updating or adding, then save
                    if (!String.IsNullOrEmpty(Request.QueryString["CourseID"]))
                    {
                        Int32 CourseID = Convert.ToInt32(Request.QueryString["CourseID"]);

                        c = (from crs in conn.Courses
                             where crs.CourseID == CourseID
                             select crs).FirstOrDefault();
                    }

                    //fill the properties of our object from the form inputs
                    c.Title = txtTitle.Text;
                    c.Credits = Convert.ToInt32(txtCredits.Text);
                    c.DepartmentID = Convert.ToInt32(ddlDepartment.SelectedValue);

                    if (Request.QueryString.Count == 0)
                    {
                        conn.Courses.Add(c);
                    }
                    conn.SaveChanges();

                    //redirect to updated departments page
                    Response.Redirect("Courses.aspx");
                }
            }
            catch (Exception ex)
            {
                Response.Redirect("~/error.aspx");
            }
        }
        protected void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                //connect
                using (DefaultConnectionEF conn = new DefaultConnectionEF())
                {
                    //instantiate a new deparment object in memory
                    Department d = new Department();

                    //decide if updating or adding, then save
                    if (Request.QueryString.Count > 0)
                    {
                        Int32 DepartmentID = Convert.ToInt32(Request.QueryString["DepartmentID"]);

                        d = (from dep in conn.Departments
                             where dep.DepartmentID == DepartmentID
                             select dep).FirstOrDefault();
                    }

                    //fill the properties of our object from the form inputs
                    d.Name = txtName.Text;
                    d.Budget = Convert.ToDecimal(txtBudget.Text);

                    if (Request.QueryString.Count == 0)
                    {
                        conn.Departments.Add(d);
                    }
                    conn.SaveChanges();

                    //redirect to updated departments page
                    Response.Redirect("departments.aspx");
                }
            }
            catch (Exception ex)
            {
                Response.Redirect("~/error.aspx");
            }
        }
        protected void GetDepartments()
        {
            try
            {
                //connect using our connection string from web.config and EF context class
                using (DefaultConnectionEF conn = new DefaultConnectionEF())
                {

                    //use link to query the Departments model
                    var deps = from d in conn.Departments
                               select d;

                    //append the current direction to the sort column
                    String sort = Session["SortColumn"].ToString() + " " + Session["SortDirection"].ToString();
                    grdDepartments.DataSource = deps.AsQueryable().OrderBy(sort).ToList();
                    grdDepartments.DataBind();
                }
            }
            catch (Exception e)
            {
                Response.Redirect("~/error.aspx");
            }
        }
        protected void GetCourses()
        {
            try
            {
                //connect using our connection string from web.config and EF context class
                using (DefaultConnectionEF conn = new DefaultConnectionEF())
                {

                    //use link to query the Departments model
                    var courses = (from c in conn.Courses
                                   select new { c.CourseID, c.Title, c.Credits, c.Department.Name });

                    //append the current direction to the sort column
                    String sort = Session["SortColumn"].ToString() + " " + Session["SortDirection"].ToString();
                    grdCourses.DataSource = courses.AsQueryable().OrderBy(sort).ToList();
                    grdCourses.DataBind();
                }
            }
            catch (Exception e)
            {
                Response.Redirect("~/error.aspx");
            }
        }
        protected void ddlDepartment_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                using (DefaultConnectionEF conn = new DefaultConnectionEF())
                {
                    //store selected department id
                    Int32 DepartmentID = Convert.ToInt32(ddlDepartment.SelectedValue);

                    var objC = from c in conn.Courses
                               where c.DepartmentID == DepartmentID
                               orderby Title
                               select c;

                    //bind to the course dropdown
                    ddlCourse.DataSource = objC.ToList();
                    ddlCourse.DataBind();

                    //add default options to the 2 dropdowns
                    ListItem newItem = new ListItem("-Select-", "0");
                    ddlCourse.Items.Insert(0, newItem);
                }
            }
            catch (Exception excpt)
            {
                Response.Redirect("~/error.aspx");
            }
        }
        protected void grdCourses_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            //get the selected enrollment id
            Int32 EnrollmentID = Convert.ToInt32(grdCourses.DataKeys[e.RowIndex].Values["EnrollmentID"]);

            try
            {
                using (DefaultConnectionEF conn = new DefaultConnectionEF())
                {

                    Enrollment objE = (from en in conn.Enrollments
                                       where en.EnrollmentID == EnrollmentID
                                       select en).FirstOrDefault();

                    conn.Enrollments.Remove(objE);
                    conn.SaveChanges();

                    GetStudents();

                }
            }
            catch (Exception exception)
            {
                Response.Redirect("~/error.aspx");
            }
        }
        protected void GetStudents()
        {
            try
            {
                //connect
                using (DefaultConnectionEF conn = new DefaultConnectionEF())
                {
                    //get the student id
                    Int32 StudentID = Convert.ToInt32(Request.QueryString["StudentID"]);

                    //get student info
                    var s = (from stu in conn.Students
                             where stu.StudentID == StudentID
                             select stu).FirstOrDefault();

                    if (s != null)
                    {
                        //populate the form
                        txtFirstName.Text = s.FirstMidName;
                        txtLastName.Text = s.LastName;
                        txtEnrollDate.Text = s.EnrollmentDate.ToString("yyyy-MM-dd");
                    }

                    var objE = (from en in conn.Enrollments
                                join c in conn.Courses on en.CourseID equals c.CourseID
                                join d in conn.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();

                    ddlDepartment.ClearSelection();
                    ddlCourse.ClearSelection();

                    //fill departments dropdown
                    var deps = (from dep in conn.Departments
                                orderby dep.Name
                                select dep);

                    //populate the form
                    ddlDepartment.DataSource = deps.ToList();
                    ddlDepartment.DataBind();

                    //add default options to the 2 dropdowns
                    ListItem newItem = new ListItem("-Select-", "0");
                    ddlDepartment.Items.Insert(0, newItem);
                    ddlCourse.Items.Insert(0, newItem);

                    //show the course panel
                    pnlCourses.Visible = true;
                }
            }
            catch (Exception e)
            {
                Response.Redirect("~/error.aspx");
            }
        }
        protected void grdDepartments_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            try
            {
                //connect
                using (DefaultConnectionEF conn = new DefaultConnectionEF())
                {
                    //get the selected department id
                    Int32 DepartmentID = Convert.ToInt32(grdDepartments.DataKeys[e.RowIndex].Values["DepartmentID"]);

                    var d = (from dep in conn.Departments
                             where dep.DepartmentID == DepartmentID
                             select dep).FirstOrDefault();

                    //delete
                    conn.Departments.Remove(d);
                    conn.SaveChanges();

                    //update the grid
                    GetDepartments();

                }
            }
            catch (Exception ex)
            {
                Response.Redirect("~/error.aspx");
            }
        }
        protected void GetCourse()
        {
            try
            {
                //connect
                using (DefaultConnectionEF conn = new DefaultConnectionEF())
                {
                    //get the course id
                    Int32 CourseID = Convert.ToInt32(Request.QueryString["CourseID"]);

                    //get student info
                    var c = (from crs in conn.Courses
                             where crs.CourseID == CourseID
                             select crs).FirstOrDefault();

                    //populate the form
                    txtTitle.Text = c.Title;
                    txtCredits.Text = c.Credits.ToString();
                    ddlDepartment.SelectedValue = c.DepartmentID.ToString();
                }
            }
            catch (Exception e)
            {
                Response.Redirect("~/error.aspx");
            }
        }
        protected void GetStudents()
        {
            try
            {
                using (DefaultConnectionEF conn = new DefaultConnectionEF())
                {
                    //Get courseID
                    Int32 CourseID = Convert.ToInt32(Request.QueryString["CourseID"]);

                    //fill students grid
                    var stu = (from s in conn.Students
                               join en in conn.Enrollments on s.StudentID equals en.StudentID
                               where en.CourseID == CourseID
                               orderby s.LastName
                               select new { en.EnrollmentID, s.FirstMidName, s.LastName, s.StudentID, s.EnrollmentDate }).Distinct();

                    //populate the form
                    grdStudents.DataSource = stu.ToList();
                    grdStudents.DataBind();

                    //fill students dropdown
                    var allstu = (from s in conn.Students
                                  join en in conn.Enrollments on s.StudentID equals en.StudentID
                                  orderby s.LastName
                                  select new { en.EnrollmentID, s.FirstMidName, s.LastName, s.StudentID, s.EnrollmentDate, DisplayField = (s.LastName + ", " + s.FirstMidName) }).Distinct();

                    ddlStudent.DataTextField = "DisplayField";

                    //populate the form
                    ddlStudent.DataSource = allstu.ToList();
                    ddlStudent.DataBind();

                    //add default options to the dropdown
                    ListItem newItem = new ListItem("-Select-", "0");
                    ddlStudent.Items.Insert(0, newItem);

                    pnlStudents.Visible = true;
                }
            }
            catch (Exception e)
            {
                Response.Redirect("~/error.aspx");
            }
        }
        protected void GetDepartments()
        {
            try
            {
                //connect
                using (DefaultConnectionEF conn = new DefaultConnectionEF())
                {
                    //get the student id
                    Int32 CourseID = Convert.ToInt32(Request.QueryString["CourseID"]);

                    //get student info
                    var d = (from dep in conn.Departments
                             orderby dep.Name
                             select dep);

                    //populate the form
                    ddlDepartment.DataSource = d.ToList();
                    ddlDepartment.DataBind();

                }
            }
            catch (Exception e)
            {
                Response.Redirect("~/error.aspx");
            }
        }
        protected void grdCourses_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            try
            {
                //connect
                using (DefaultConnectionEF conn = new DefaultConnectionEF())
                {
                    //get the selected department id
                    Int32 CourseID = Convert.ToInt32(grdCourses.DataKeys[e.RowIndex].Values["CourseID"]);

                    var c = (from crs in conn.Courses
                             where crs.CourseID == CourseID
                             select crs).FirstOrDefault();

                    //delete
                    conn.Courses.Remove(c);
                    conn.SaveChanges();

                    //update the grid
                    GetCourses();

                }
            }
            catch (Exception ex)
            {
                Response.Redirect("~/error.aspx");
            }
        }
        protected void grdDepCourses_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            //get the selected Course id
            Int32 CourseID = Convert.ToInt32(grdDepCourses.DataKeys[e.RowIndex].Values["CourseID"]);

            try
            {
                using (DefaultConnectionEF conn = new DefaultConnectionEF())
                {

                    Course objC = (from c in conn.Courses
                                   where c.CourseID == CourseID
                                   select c).FirstOrDefault();

                    conn.Courses.Remove(objC);
                    conn.SaveChanges();

                    GetDepartment();

                }
            }
            catch (Exception exception)
            {
                Response.Redirect("~/error.aspx");
            }
        }