protected void GetDepartment()
        {
            //connect
            using (DefaultConnection conn = new DefaultConnection())
            {
                //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;
            }
        }
        protected void btnSave_Click(object sender, EventArgs e)
        {
            //connect
            using (DefaultConnection conn = new DefaultConnection())
            {
                //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");
            }
        }
        protected void btnSave_Click(object sender, EventArgs e)
        {
            //connect
            using (DefaultConnection conn = new DefaultConnection())
            {
                //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");
            }
        }
        protected void btnSave_Click(object sender, EventArgs e)
        {
            //connect
            using (DefaultConnection conn = new DefaultConnection())
            {
                //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");
            }
        }
        protected void GetDepartments()
        {
            //connect using our connection string from web.config and EF context class
            using (DefaultConnection conn = new DefaultConnection())
            {

                //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();
            }
        }
        protected void grdDepCourses_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            //get the selected Course id
            Int32 CourseID = Convert.ToInt32(grdDepCourses.DataKeys[e.RowIndex].Values["CourseID"]);

            using (DefaultConnection conn = new DefaultConnection())
            {

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

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

                GetDepartment();

            }
        }
        protected void GetCourse()
        {
            //connect
            using (DefaultConnection conn = new DefaultConnection())
            {
                //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();
            }
        }
        protected void grdDepartments_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            //connect
            using (DefaultConnection conn = new DefaultConnection())
            {
                //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();

            }
        }
        protected void grdStudents_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            //connect
            using (DefaultConnection conn = new DefaultConnection())
            {
                //get the selected department id
                Int32 StudentID = Convert.ToInt32(grdStudents.DataKeys[e.RowIndex].Values["StudentID"]);

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

                //delete
                conn.Students.Remove(s);
                conn.SaveChanges();

                //update the grid
                GetStudents();

            }
        }
        protected void btnAdd_Click(object sender, EventArgs e)
        {
            using (DefaultConnection conn = new DefaultConnection())
            {

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

                Enrollment objE = new Enrollment();

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

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

                //refresh
                GetStudents();

            }
        }
        protected void ddlDepartment_SelectedIndexChanged(object sender, EventArgs e)
        {
            using (DefaultConnection conn = new DefaultConnection())
            {
                //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);
            }
        }
        protected void GetDepartments()
        {
            //connect
            using (DefaultConnection conn = new DefaultConnection())
            {
                //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();

            }
        }
        protected void grdStudents_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            //get the selected enrollment id
            Int32 EnrollmentID = Convert.ToInt32(grdStudents.DataKeys[e.RowIndex].Values["EnrollmentID"]);

            using (DefaultConnection conn = new DefaultConnection())
            {

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

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

                GetStudents();

            }
        }
        protected void GetStudents()
        {
            using (DefaultConnection conn = new DefaultConnection())
            {
                //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;
            }
        }
        protected void GetStudents()
        {
            //connect
            using (DefaultConnection conn = new DefaultConnection())
            {
                //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;
            }
        }