/// <summary>
/// Remove selected Row
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ButtonDrop_Click(object sender, EventArgs e)
{
/* foreach (DataGridViewRow row in dataGridViewRegistration.SelectedRows)
* {
* dataGridViewRegistration.Rows.RemoveAt(row.Index);
* }*/
StudentRegistrationEntities context = new StudentRegistrationEntities();
context.Students.Load();
context.Courses.Load();
context.Database.Log = (s => Debug.Write(s));
context.SaveChanges();
// get the students and courses, and include nav properties
var students = context.Students.Include("Courses").ToList();
var courses = context.Courses.Include("Students").ToList();
foreach (DataGridViewRow row in dataGridViewRegistration.SelectedRows)
{
StudentCourseRegistration registration = row.DataBoundItem as StudentCourseRegistration;
// find the student in the db
Student student = students.Find(s => s.StudentId == registration.StudentID);
// find the course in teh db
Course course = courses.Find(c => c.CourseNumber == registration.CourseNumber && c.DepartmentId == registration.course.DepartmentId);
student.Courses.Remove(course);
}
context.SaveChanges();
context.Dispose();
UpdateRegistration();
}
/// <summary>
/// Register selected student and course
/// and add to the registration table
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ButtonRegister_Click(object sender, EventArgs e)
{
StudentRegistrationEntities context = new StudentRegistrationEntities();
context.Students.Load();
context.Courses.Load();
context.Database.Log = (s => Debug.Write(s));
context.SaveChanges();
var students = context.Students.Include("Courses").ToList();
var courses = context.Courses.Include("Students").ToList();
// check if bothe student and course are selected
if (dataGridViewStudent.SelectedRows.Count == 0 || dataGridViewCourse.SelectedRows.Count == 0)
{
MessageBox.Show("No students or Courses selected");
return;
}
// get the selected students and keep in a list
List <Student> studentsToRegister = new List <Student>();
foreach (DataGridViewRow row in dataGridViewStudent.SelectedRows)
{
Student student = row.DataBoundItem as Student;
studentsToRegister.Add(students.Find(s => s.StudentId == student.StudentId));
}
List <Course> courseToRegister = new List <Course>();
foreach (DataGridViewRow row in dataGridViewCourse.SelectedRows)
{
Course course = row.DataBoundItem as Course;
courseToRegister.Add(courses.Find(c => c.CourseId == course.CourseId && c.DepartmentId == course.DepartmentId));
}
foreach (Course c in courseToRegister)
{
foreach (Student s in studentsToRegister)
{
c.Students.Add(s);
}
}
context.SaveChanges();
UpdateRegistration();
context.Dispose();
}
/// <summary>
/// The form is initially created, but loaded each time it is shown.
/// So make sure the context is created in the Load event.
///
/// This is the handler for the Load event.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void AddOrUpdateCourseForm_Load(object sender, EventArgs e)
{
this.Tag = null;
// Get the context and load the inventory table
context = new StudentRegistrationEntities();
context.Database.Log = s => Debug.Write(s);
context.SaveChanges();
context.Departments.Load();
context.Courses.Load();
// bind the listbox of Department to the Department table.
listBoxCourses.DataSource = context.Courses.Local.ToBindingList();
listBoxDepartment.DataSource = context.Departments.Local.ToBindingList();
// no department is selected to start
listBoxCourses.SelectedIndex = -1;
listBoxDepartment.SelectedIndex = -1;
// set all textboxes to blank
textBoxCourseName.ResetText();
textBoxCourseNumber.ResetText();
}
public StudentRegistrationMainForm()
{
InitializeComponent();
this.Text = "Student Registration using AddUpdate Forms";
context = new StudentRegistrationEntities();
context.Database.Log = (s => Debug.Write(s));
context.SeedDatabase();
context.SaveChanges();
this.Load += (s, e) => StudentRegistratioMainForm_Load();
// set the event handlers for buttons
AddOrUpdateStudent addOrUpdateStudentForm = new AddOrUpdateStudent();
buttonStudent.Click += (s, e) => AddOrUpdateForm <Student>(dataGridViewStudent, addOrUpdateStudentForm);
AddOrUpdateDepartment addOrUpdateDepartmentForm = new AddOrUpdateDepartment();
buttonDepartment.Click += (s, e) => AddOrUpdateForm <Department>(dataGridViewDepartment, addOrUpdateDepartmentForm);
AddOrUpdateCourse addOrUpdateCourseForm = new AddOrUpdateCourse();
buttonCourse.Click += (s, e) => AddOrUpdateForm <Course>(dataGridViewCourse, addOrUpdateCourseForm);
buttonRegister.Click += ButtonRegister_Click;
buttonDrop.Click += ButtonDrop_Click;
}
/// <summary>
/// Update the db with the new department data
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ButtonUpdateDepartment_Click(object sender, EventArgs e)
{
// the selection contains the entity to update, so make
// sure it is done.
if (!(listBoxAddOrUpdateDepartment.SelectedItem is Department department))
{
MessageBox.Show("Department to be updated must be selected");
return;
}
string originalDepartment = department.DepartmentId.ToString();
department.DepartmentCode = textBoxDepartmentCode.Text;
department.DepartmentName = textBoxDepartmentName.Text;
if (department.InfoIsInvalid())
{
MessageBox.Show("department information is missing.");
return;
}
if (originalDepartment != department.DepartmentId.ToString() && department.DepartmentExists())
{
MessageBox.Show("DepartmentId already exists: " + department.DepartmentId);
return;
}
// now update the db
try
{
context.SaveChanges();
}
catch (Exception ex)
{
MessageBox.Show("Cannot add Department to database" + ex.InnerException.InnerException.Message);
return;
}
this.Tag = department.DepartmentId; // so parent form can lookup id and reload
this.DialogResult = DialogResult.OK;
context.Dispose();
Close(); // this will not dispose the form on hide!
}
protected void btnSubmitCourse_Click(object sender, EventArgs e)
{
Course course = new Course(txtCourseNumber.Text, txtCourseName.Text, int.Parse(txtWeeklyHours.Text));
using (StudentRegistrationEntities entityContext = new StudentRegistrationEntities())
{
entityContext.Courses.Add(course);
entityContext.SaveChanges();
}
}
/// <summary>
/// Update the db with the new course data
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ButtonUpdateCourse_Click(object sender, EventArgs e)
{
if (!(listBoxCourses.SelectedItem is Course course))
{
MessageBox.Show("Courses to be updated must be selected");
return;
}
// update the entity
int deptId = listBoxDepartment.SelectedIndex + 1;
string originalCourse = course.CourseId.ToString();
course.CourseName = textBoxCourseName.Text;
course.CourseNumber = Convert.ToInt32(textBoxCourseNumber.Text);
course.DepartmentId = deptId;
if (course.InfoIsInvalid())
{
MessageBox.Show("course information is missing.");
return;
}
if (originalCourse != course.CourseId.ToString() && course.CourseExists())
{
MessageBox.Show("course already exists: " + course.CourseId + 1 + "" + course.CourseNumber);
return;
}
try
{
context.SaveChanges();
context.Departments.Load();
context.Courses.Load();
// bind the listbox of Department to the Department table.
listBoxCourses.DataSource = context.Courses.Local.ToBindingList();
listBoxDepartment.DataSource = context.Departments.Local.ToBindingList();
}
catch (Exception ex)
{
MessageBox.Show("Cannot add Department to database" + ex.InnerException.InnerException.Message);
return;
}
this.Tag = course.CourseId; // so parent form can lookup id and reload
this.DialogResult = DialogResult.OK;
context.Dispose();
Close(); // this will not dispose the form on hide!
}
protected void AddStudent_Click(object sender, EventArgs e)
{
if (drpCourseOfferingList.SelectedValue != "-1")
{
using (StudentRegistrationEntities entityContext = new StudentRegistrationEntities())
{
List <Student> studentList = entityContext.Students.ToList <Student>();
var student = (from c in studentList
where c.StudentNum == txtStudentNumber.Text
select c).FirstOrDefault <Student>();
if (student == null)
{
if (rbFullTime.Checked)
{
student = new FullTimeStudent(txtStudentNumber.Text, txtStudentName.Text);
}
else if (rbPartTime.Checked)
{
student = new PartTimeStudent(txtStudentNumber.Text, txtStudentName.Text);
}
else if (rbCoop.Checked)
{
student = new CoopStudent(txtStudentNumber.Text, txtStudentName.Text);
}
entityContext.Students.Add(student);
entityContext.SaveChanges();
}
CourseOffering courseOffering = GetCourseOfferingFromDropdown(entityContext);
List <Student> registeredStudents = courseOffering.Students.ToList <Student>();
if (!registeredStudents.Exists(x => x.Number == student.Number))
{
courseOffering.Students.Add(student);
entityContext.SaveChanges();
}
}
}
}
protected void btnSubmitCourseOffering_Click(object sender, EventArgs e)
{
using (StudentRegistrationEntities entityContext = new StudentRegistrationEntities())
{
var course = (from c in entityContext.Courses
where c.CourseID == drpCourse.SelectedValue
select c).FirstOrDefault <Course>();
CourseOffering courseOffering = new CourseOffering(course, int.Parse(drpYears.SelectedValue), drpSemester.SelectedValue);
entityContext.CourseOfferings.Add(courseOffering);
entityContext.SaveChanges();
}
drpCourse.SelectedIndex = drpYears.SelectedIndex = drpSemester.SelectedIndex = 0;
}
private StudentRegistrationEntities context; // save DB context here
public FormStudentRegistration()
{
InitializeComponent();
// set up the database
context = new StudentRegistrationEntities();
// set up database log to write to output window in VS
context.Database.Log = s => Debug.Write(s);
context.SaveChanges();
// delete db if exists, then create
context.Database.Delete();
context.Database.Create();
}
/// <summary>
/// Method to add registration
/// </summary>
/// <param name="students"> datagridview from which a student was selected</param>
/// <param name="courses"> datagridview from which a course was selected</param>
private void RegisterStudent(DataGridView students, DataGridView courses)
{
// if one was not selected- error
if (students.SelectedRows.Count == 0 || courses.SelectedRows.Count == 0)
{
MessageBox.Show("Please Select a Student and a Course for Registration!");
}
else
{
// using unit-of-work context
using (StudentRegistrationEntities context = new StudentRegistrationEntities())
{
// foreach of the students
foreach (DataGridViewRow row in students.SelectedRows)
{
// check that it is a student - not really required (since mostly display!)
// but just in case
if (row.DataBoundItem is Student student)
{
// go through all selected courses
foreach (DataGridViewRow rowCourse in courses.SelectedRows)
{
// check that it is a course - not really required (since mostly display!)
// but just in case
if (rowCourse.DataBoundItem is Course course)
{
// get course and student from the context
var courseFromContext = context.Courses.Find(course.CourseId);
var studentFromContext = context.Students.Find(student.StudentId);
// add the course to the student
//could've been other way around
studentFromContext.Courses.Add(courseFromContext);
context.SaveChanges(); // save changes
}
}
}
}
}
}
// reload the display of the registrations
ReloadRegistrationView();
}
public MainForm()
{
InitializeComponent();
this.Text = "Student Using Add/Update Forms App using EF Code First from DB library";
context = new StudentRegistrationEntities();
context.Database.Log = (s => Debug.Write(s));
context.SeedDatabase();
context.SaveChanges();
this.Load += (s, e) => MainForm_Load();
StudentForm studentForm = new StudentForm();
buttonStudent.Click += (s, e) => AddOrUpdateForm <Student>(dataGridViewStudents, studentForm);
DepartmentForm departmentForm = new DepartmentForm();
buttonDepartment.Click += (s, e) => AddOrUpdateForm <Department>(dataGridViewDepartment, departmentForm);
}
/// <summary>
/// Method to remove all students from the department given the departmentID
/// </summary>
/// <param name="departmentId"> department id to remove </param>
/// <param name="studentsDisplay"> datagridview of students to display changes</param>
private static void RemoveStudentsFromDepartment(int departmentId, DataGridView studentsDisplay)
{
// using unit-of-work context
using (StudentRegistrationEntities context = new StudentRegistrationEntities())
{
// get the students in that department
var students = context.Students.Where(x => x.DepartmentId == departmentId).ToList();
// for all- set their department and departmentId to null
foreach (Student st in students)
{
st.Department = null;
st.DepartmentId = null;
}
// save changes
context.SaveChanges();
}
// refresh the display to show changes
studentsDisplay.DataSource = Controller <StudentRegistrationEntities, Student> .SetBindingList();
studentsDisplay.Refresh();
}
/// <summary>
/// Method to drop a registration
/// </summary>
/// <param name="registrations"> datagridview to be processed</param>
private void DropFromCourse(DataGridView registrations)
{
// if none is selected - error!
if (registrations.SelectedRows.Count == 0)
{
MessageBox.Show("Please Select a Registration to be dropped!");
}
else
{
// using unit-of-work context
using (StudentRegistrationEntities context = new StudentRegistrationEntities())
{
// for every selected row
foreach (DataGridViewRow row in registrations.SelectedRows)
{
// find the student based on ID
int selectedStudentID = Convert.ToInt32(row.Cells[3].Value);
Student selectedStudent = context.Students.Find(selectedStudentID);
// look for Department ID
string departmentCode = Convert.ToString(row.Cells[0].Value);
Department selectedDepartment = context.Departments.SingleOrDefault(department => department.DepartmentCode == departmentCode);
int selectedDepartmentId = selectedDepartment.DepartmentId;
// look for Course ID
int courseNumber = Convert.ToInt32(row.Cells[1].Value);
Course selectedCourse = context.Courses.SingleOrDefault(course => course.CourseNumber == courseNumber && course.DepartmentId == selectedDepartmentId);
// remove the course from the student
selectedStudent.Courses.Remove(selectedCourse);
context.SaveChanges(); // save changes
}
}
// reload the display to show changes
ReloadRegistrationView();
}
}
// initialization method to zero out the database tabels,
// then seed Students, Courses, and Departments with initial data
private void createRegistrationDataTables()
{
// set up database log to write to output window in VS
context.Database.Log = (s => Debug.Write(s));
context.SaveChanges();
// zero out the db tables
// load fully, clear, then save back
context.Students.Load();
context.Departments.Load();
context.Courses.Load();
context.Courses.Local.Clear();
context.Departments.Local.Clear();
context.Students.Local.Clear();
context.SaveChanges();
// finally, reseed students to 0
context.Database.ExecuteSqlCommand($"DBCC CHECKIDENT('Students', RESEED, 0)");
// seed student data into Students table
List <Student> students = new List <Student>()
{
new Student {
StudentFirstName = "Svetlana", StudentLastName = "Rostov", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Claire", StudentLastName = "Bloome", StudentMajor = "ACCT"
},
new Student {
StudentFirstName = "Sven", StudentLastName = "Baertschi", StudentMajor = "MKTG"
},
new Student {
StudentFirstName = "Cesar", StudentLastName = "Chavez", StudentMajor = "FINC"
},
new Student {
StudentFirstName = "Debra", StudentLastName = "Manning", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Fadi", StudentLastName = "Hadari", StudentMajor = "ACCT"
},
new Student {
StudentFirstName = "Hanyeng", StudentLastName = "Fen", StudentMajor = "MKTG"
},
new Student {
StudentFirstName = "Hugo", StudentLastName = "Victor", StudentMajor = "FINC"
},
new Student {
StudentFirstName = "Lance", StudentLastName = "Armstrong", StudentMajor = "MKTG"
},
new Student {
StudentFirstName = "Terry", StudentLastName = "Matthews", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Eugene", StudentLastName = "Fei", StudentMajor = "FINC"
},
new Student {
StudentFirstName = "Michael", StudentLastName = "Thorson", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Simon", StudentLastName = "Li", StudentMajor = "CSIS"
},
};
context.Students.AddRange(students);
// seed departments data
List <Department> departments = new List <Department>()
{
new Department {
DepartmentId = "CSIS", DepartmentName = "Computing Studies"
},
new Department {
DepartmentId = "ACCT", DepartmentName = "Accounting"
},
new Department {
DepartmentId = "MKTG", DepartmentName = "Marketing"
},
new Department {
DepartmentId = "FINC", DepartmentName = "Finance"
},
};
context.Departments.AddRange(departments);
// seed courses data
List <Course> courses = new List <Course>()
{
new Course {
CourseId = 101, CourseName = "Programming I", Department = departments[0]
},
new Course {
CourseId = 102, CourseName = "Programming II", Department = departments[0]
},
new Course {
CourseId = 101, CourseName = "Accounting I", Department = departments[1]
},
new Course {
CourseId = 102, CourseName = "Accounting II", Department = departments[1]
},
new Course {
CourseId = 101, CourseName = "Corporate Finance", Department = departments[3],
},
};
context.Courses.AddRange(courses);
context.SaveChanges();
// set up initial registration
// note that we can add a student to a course or
// add a course to a student, both will work and set up proper links
courses[0].Students.Add(students[0]);
courses[0].Students.Add(students[1]);
courses[1].Students.Add(students[0]);
courses[4].Students.Add(students[0]);
students[2].Courses.Add(courses[2]);
context.SaveChanges();
// show the tables
// don't show the foreign key objects
dataGridViewStudents.DataSource = context.Students.Local.ToBindingList();
dataGridViewStudents.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dataGridViewStudents.Columns["Courses"].Visible = false;
dataGridViewCourses.DataSource = context.Courses.Local.ToBindingList();
dataGridViewCourses.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dataGridViewCourses.Columns["Department"].Visible = false;
dataGridViewCourses.Columns["Students"].Visible = false;
dataGridViewDepartments.DataSource = context.Departments.Local.ToBindingList();
dataGridViewDepartments.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dataGridViewDepartments.Columns["Courses"].Visible = false;
dataGridViewStudentRegistration.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
// set up registration gridview
updateRegistration();
context.SaveChanges();
}
private void createRegistrationDataTables()
{
// students data
List <Student> students = new List <Student>()
{
new Student {
StudentFirstName = "Svetlana", StudentLastName = "Rostov", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Claire", StudentLastName = "Bloome", StudentMajor = "ACCT"
},
new Student {
StudentFirstName = "Sven", StudentLastName = "Baertschi", StudentMajor = "MKTG"
},
new Student {
StudentFirstName = "Cesar", StudentLastName = "Chavez", StudentMajor = "FINC"
},
new Student {
StudentFirstName = "Debra", StudentLastName = "Manning", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Fadi", StudentLastName = "Hadari", StudentMajor = "ACCT"
},
new Student {
StudentFirstName = "Hanyeng", StudentLastName = "Fen", StudentMajor = "MKTG"
},
new Student {
StudentFirstName = "Hugo", StudentLastName = "Victor", StudentMajor = "FINC"
},
new Student {
StudentFirstName = "Lance", StudentLastName = "Armstrong", StudentMajor = "MKTG"
},
new Student {
StudentFirstName = "Terry", StudentLastName = "Matthews", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Eugene", StudentLastName = "Fei", StudentMajor = "FINC"
},
new Student {
StudentFirstName = "Michael", StudentLastName = "Thorson", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Simon", StudentLastName = "Li", StudentMajor = "CSIS"
},
};
// departments data
List <Department> departments = new List <Department>()
{
new Department {
DepartmentId = "CSIS", DepartmentName = "Computing Studies"
},
new Department {
DepartmentId = "ACCT", DepartmentName = "Accounting"
},
new Department {
DepartmentId = "MKTG", DepartmentName = "Marketing"
},
new Department {
DepartmentId = "FINC", DepartmentName = "Finance"
},
};
// courses data
List <EF_Classes.Course> courses = new List <EF_Classes.Course>()
{
new EF_Classes.Course {
CourseId = 101, CourseDepartmentId = "CSIS", CourseName = "Programming I"
},
new EF_Classes.Course {
CourseId = 102, CourseDepartmentId = "CSIS", CourseName = "Programming II"
},
new EF_Classes.Course {
CourseId = 101, CourseDepartmentId = "ACCT", CourseName = "Accounting I"
},
new EF_Classes.Course {
CourseId = 102, CourseDepartmentId = "ACCT", CourseName = "Accounting II"
},
new EF_Classes.Course {
CourseId = 101, CourseDepartmentId = "FINC", CourseName = "Corporate Finance"
},
};
// registration data - note consists of a registration object, and a student id.
// if the students table is set up with a different autoincrement that starting at 1, this won't work
//List<Registration> registrations = new List<Registration>()
//{
// new Registration { RegisteredCourse = courses[0], StudentId = 1 },
// new Registration { RegisteredCourse = courses[0], StudentId = 2 },
// new Registration { RegisteredCourse = courses[1], StudentId = 1 },
// new Registration { RegisteredCourse = courses[4], StudentId = 1 },
//}
context.Database.Log = s => Debug.Write(s);
context.SaveChanges();
context.Students.RemoveRange(context.Students);
context.SaveChanges();
context.Database.ExecuteSqlCommand($"DBCC CHECKIDENT('Students', RESEED, 0)");
foreach (Student s in students)
{
context.Students.Add(s);
}
context.SaveChanges();
dataGridViewStudents.DataSource = context.Students.ToList();
//dataGridViewStudents.Refresh();
//foreach (Student s in context.Students)
// Console.WriteLine(s);
//var query =
// from s in context.Students
// where s.StudentMajor == "ACCT"
// select s;
//Console.WriteLine($"Found {query.Count()} students in {query.First<Student>().StudentMajor}");
//foreach (Student s in query)
// Console.WriteLine(s);
//Console.ReadLine();
}
/// <summary>
/// Seed tables with data
/// </summary>
private void SeedRegistrationDataTables()
{
// seed student data into Students table
List <Student> students = new List <Student>()
{
new Student {
StudentFirstName = "Svetlana", StudentLastName = "Rostov", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Claire", StudentLastName = "Bloome", StudentMajor = "ACCT"
},
new Student {
StudentFirstName = "Sven", StudentLastName = "Baertschi", StudentMajor = "MKTG"
},
new Student {
StudentFirstName = "Cesar", StudentLastName = "Chavez", StudentMajor = "FINC"
},
new Student {
StudentFirstName = "Debra", StudentLastName = "Manning", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Fadi", StudentLastName = "Hadari", StudentMajor = "ACCT"
},
new Student {
StudentFirstName = "Hanyeng", StudentLastName = "Fen", StudentMajor = "MKTG"
},
new Student {
StudentFirstName = "Hugo", StudentLastName = "Victor", StudentMajor = "FINC"
},
new Student {
StudentFirstName = "Lance", StudentLastName = "Armstrong", StudentMajor = "MKTG"
},
new Student {
StudentFirstName = "Terry", StudentLastName = "Matthews", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Eugene", StudentLastName = "Fei", StudentMajor = "FINC"
},
new Student {
StudentFirstName = "Michael", StudentLastName = "Thorson", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Simon", StudentLastName = "Li", StudentMajor = "CSIS"
},
};
context.Students.AddRange(students);
// seed departments data
List <Department> departments = new List <Department>()
{
new Department {
DepartmentId = "CSIS", DepartmentName = "Computing Studies"
},
new Department {
DepartmentId = "ACCT", DepartmentName = "Accounting"
},
new Department {
DepartmentId = "MKTG", DepartmentName = "Marketing"
},
new Department {
DepartmentId = "FINC", DepartmentName = "Finance"
},
};
context.Departments.AddRange(departments);
// seed courses data
List <Course> courses = new List <Course>()
{
new Course {
CourseId = 101, CourseDepartmentId = "CSIS", CourseName = "Programming I"
},
new Course {
CourseId = 102, CourseDepartmentId = "CSIS", CourseName = "Programming II"
},
new Course {
CourseId = 101, CourseDepartmentId = "ACCT", CourseName = "Accounting I"
},
new Course {
CourseId = 102, CourseDepartmentId = "ACCT", CourseName = "Accounting II"
},
new Course {
CourseId = 101, CourseDepartmentId = "FINC", CourseName = "Corporate Finance"
},
};
context.Courses.AddRange(courses);
context.SaveChanges();
// set up initial registration
// note that we can add a student to a course or
// add a course to a student, both will work and set up proper links
courses[0].Students.Add(students[0]);
courses[0].Students.Add(students[1]);
courses[1].Students.Add(students[0]);
courses[4].Students.Add(students[0]);
students[2].Courses.Add(courses[2]);
context.SaveChanges();
// need Load() for BindingList, which allows the gridviews to be sorted/edited
// and sync'd to database
context.Students.Load();
context.Courses.Load();
// show the Students and Courses tables
dataGridViewStudents.DataSource = context.Students.Local.ToBindingList();
dataGridViewCourses.DataSource = context.Courses.Local.ToBindingList();
// set up registration gridview
UpdateRegistration();
context.SaveChanges();
}
/// <summary>
/// zero out the db tables, then seed all tables with initial data
/// </summary>
public static void SeedDatabase(this StudentRegistrationEntities context)
{
// set up database log to write to output window in VS
context.Database.Log = (s => Debug.Write(s));
// reset the db
context.Database.Delete();
context.Database.Create();
context.SaveChanges();
// another way to reinitialize the database, resetting everything and zeroing out data
//Database.SetInitializer(new DropCreateDatabaseAlways<StudentRegistrationEntities>());
//context.Database.Initialize(true);
context.Departments.Load();
context.Students.Load();
context.Courses.Load();
// seed departments data
List <Department> departmentsList = new List <Department>()
{
new Department {
DepartmentCode = "CSIS", DepartmentName = "Computing Studies"
},
new Department {
DepartmentCode = "ACCT", DepartmentName = "Accounting"
},
new Department {
DepartmentCode = "MKTG", DepartmentName = "Marketing"
},
new Department {
DepartmentCode = "FINC", DepartmentName = "Finance"
},
};
// use a dictionary to set the department fields in Students and Courses
Dictionary <string, Department> departments = departmentsList.ToDictionary(x => x.DepartmentCode, x => x);
context.Departments.AddRange(departments.Values);
context.SaveChanges();
// seed student data into Students table
List <Student> students = new List <Student>()
{
new Student {
StudentFirstName = "Svetlana", StudentLastName = "Rostov", Department = departments["CSIS"]
},
new Student {
StudentFirstName = "Claire", StudentLastName = "Bloome", Department = departments["ACCT"]
},
new Student {
StudentFirstName = "Sven", StudentLastName = "Baertschi", Department = departments["ACCT"]
},
new Student {
StudentFirstName = "Cesar", StudentLastName = "Chavez", Department = departments["FINC"]
},
new Student {
StudentFirstName = "Debra", StudentLastName = "Manning", Department = departments["CSIS"]
},
new Student {
StudentFirstName = "Fadi", StudentLastName = "Hadari", Department = departments["ACCT"]
},
new Student {
StudentFirstName = "Hanyeng", StudentLastName = "Fen", Department = departments["ACCT"]
},
new Student {
StudentFirstName = "Hugo", StudentLastName = "Victor", Department = departments["FINC"]
},
new Student {
StudentFirstName = "Lance", StudentLastName = "Armstrong", Department = departments["ACCT"]
},
new Student {
StudentFirstName = "Terry", StudentLastName = "Matthews", Department = departments["CSIS"]
},
new Student {
StudentFirstName = "Eugene", StudentLastName = "Fei", Department = departments["FINC"]
},
new Student {
StudentFirstName = "Michael", StudentLastName = "Thorson", Department = departments["CSIS"]
},
new Student {
StudentFirstName = "Simon", StudentLastName = "Li", Department = departments["CSIS"]
},
};
context.Students.AddRange(students);
// seed courses data
List <Course> courses = new List <Course>()
{
new Course {
CourseNumber = 101, CourseName = "Programming I", Department = departments["CSIS"]
},
new Course {
CourseNumber = 102, CourseName = "Programming II", Department = departments["CSIS"]
},
new Course {
CourseNumber = 101, CourseName = "Accounting I", Department = departments["ACCT"]
},
new Course {
CourseNumber = 102, CourseName = "Accounting II", Department = departments["ACCT"]
},
new Course {
CourseNumber = 101, CourseName = "Corporate Finance", Department = departments["FINC"]
},
};
context.Courses.AddRange(courses);
context.SaveChanges();
// set up initial registration
// note that we can add a student to a course or
// add a course to a student, both will work and set up proper links
courses[0].Students.Add(students[0]);
courses[0].Students.Add(students[1]);
courses[1].Students.Add(students[0]);
courses[4].Students.Add(students[0]);
students[2].Courses.Add(courses[2]);
context.SaveChanges();
}
static private void CreateRegistrationDataTables()
{
// create db context and all dbset tables
// enable database logging (sql statements) in output window
context.Database.Log = s => Debug.Write(s); // a delegate that writes a message to output
context.Database.Delete();
context.Database.Create();
WriteLine("Database tables have been cleared, check them in SQL Server Explorer then hit enter");
ReadLine();
// students data
List <Student> students = new List <Student>()
{
new Student {
StudentFirstName = "Svetlana", StudentLastName = "Rostov", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Claire", StudentLastName = "Bloome", StudentMajor = "ACCT"
},
new Student {
StudentFirstName = "Sven", StudentLastName = "Baertschi", StudentMajor = "MKTG"
},
new Student {
StudentFirstName = "Cesar", StudentLastName = "Chavez", StudentMajor = "FINC"
},
new Student {
StudentFirstName = "Debra", StudentLastName = "Manning", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Fadi", StudentLastName = "Hadari", StudentMajor = "ACCT"
},
new Student {
StudentFirstName = "Hanyeng", StudentLastName = "Fen", StudentMajor = "MKTG"
},
new Student {
StudentFirstName = "Hugo", StudentLastName = "Victor", StudentMajor = "FINC"
},
new Student {
StudentFirstName = "Lance", StudentLastName = "Armstrong", StudentMajor = "MKTG"
},
new Student {
StudentFirstName = "Terry", StudentLastName = "Matthews", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Eugene", StudentLastName = "Fei", StudentMajor = "FINC"
},
new Student {
StudentFirstName = "Michael", StudentLastName = "Thorson", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Simon", StudentLastName = "Li", StudentMajor = "CSIS"
},
};
// departments data
List <Department> departments = new List <Department>()
{
new Department {
DepartmentId = "CSIS", DepartmentName = "Computing Studies"
},
new Department {
DepartmentId = "ACCT", DepartmentName = "Accounting"
},
new Department {
DepartmentId = "MKTG", DepartmentName = "Marketing"
},
new Department {
DepartmentId = "FINC", DepartmentName = "Finance"
},
};
// courses data
List <Course> courses = new List <Course>()
{
new Course {
CourseId = 101, CourseDepartmentId = "CSIS", CourseName = "Programming I"
},
new Course {
CourseId = 102, CourseDepartmentId = "CSIS", CourseName = "Programming II"
},
new Course {
CourseId = 101, CourseDepartmentId = "ACCT", CourseName = "Accounting I"
},
new Course {
CourseId = 102, CourseDepartmentId = "ACCT", CourseName = "Accounting II"
},
new Course {
CourseId = 101, CourseDepartmentId = "FINC", CourseName = "Corporate Finance"
},
};
// register students for a few courses
students[0].Courses.Add(courses[0]);
students[1].Courses.Add(courses[0]);
students[0].Courses.Add(courses[1]);
students[0].Courses.Add(courses[4]);
// populate the database tables
context.Students.AddRange(students);
context.Departments.AddRange(departments);
context.Courses.AddRange(courses);
context.SaveChanges();
// Let's look at all of the students who have registered for courses
WriteLine("Students registered for courses");
foreach (Student student in context.Students)
{
if (student.Courses.Count > 0)
{
WriteLine(student);
WriteLine("Registered for:");
foreach (Course course in student.Courses)
{
WriteLine($"\t{course.CourseId} {course.CourseDepartmentId} {course.CourseName}");
}
}
}
// now let's look at all of the courses offered by each department
// notice how we didn't need to initialize these!
WriteLine("Courses offered by Departments");
foreach (Department department in context.Departments)
{
if (department.Courses.Count > 0)
{
WriteLine($"{department.DepartmentId} {department.DepartmentName}");
foreach (Course course in department.Courses)
{
WriteLine($"\t{course.CourseId} {course.CourseName}");
}
}
}
// let's look at all students with an accounting major
// Examine the SQL output, and notice that a SELECT statement is generated for this
WriteLine("All students with ACCT major");
var studentsInAccounting =
from student in context.Students
where student.StudentMajor == "ACCT"
select student;
WriteLine($"Found {studentsInAccounting.Count()} students in {studentsInAccounting.First<Student>().StudentMajor}");
foreach (Student student in studentsInAccounting)
{
WriteLine(student);
}
ReadLine();
}
// initialization method to zero out the database tabels,
// then seed Students, Courses, and Departments with initial data
private void createRegistrationDataTables()
{
// set up database log to write to output window in VS
context.Database.Log = s => Debug.Write(s);
context.SaveChanges();
// zero out the database tables
// first clear the list of students linked to courses
foreach (Course c in context.Courses)
{
c.Students.Clear();
}
// now zero the courses and departments
context.Courses.RemoveRange(context.Courses);
context.Departments.RemoveRange(context.Departments);
context.SaveChanges();
// finally, zero the students and reseed to 0
context.Students.RemoveRange(context.Students);
context.SaveChanges();
context.Database.ExecuteSqlCommand($"DBCC CHECKIDENT('Students', RESEED, 0)");
// seed student data into Students table
List <Student> students = new List <Student>()
{
new Student {
StudentFirstName = "Svetlana", StudentLastName = "Rostov", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Claire", StudentLastName = "Bloome", StudentMajor = "ACCT"
},
new Student {
StudentFirstName = "Sven", StudentLastName = "Baertschi", StudentMajor = "MKTG"
},
new Student {
StudentFirstName = "Cesar", StudentLastName = "Chavez", StudentMajor = "FINC"
},
new Student {
StudentFirstName = "Debra", StudentLastName = "Manning", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Fadi", StudentLastName = "Hadari", StudentMajor = "ACCT"
},
new Student {
StudentFirstName = "Hanyeng", StudentLastName = "Fen", StudentMajor = "MKTG"
},
new Student {
StudentFirstName = "Hugo", StudentLastName = "Victor", StudentMajor = "FINC"
},
new Student {
StudentFirstName = "Lance", StudentLastName = "Armstrong", StudentMajor = "MKTG"
},
new Student {
StudentFirstName = "Terry", StudentLastName = "Matthews", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Eugene", StudentLastName = "Fei", StudentMajor = "FINC"
},
new Student {
StudentFirstName = "Michael", StudentLastName = "Thorson", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Simon", StudentLastName = "Li", StudentMajor = "CSIS"
},
};
context.Students.AddRange(students);
// seed departments data
List <Department> departments = new List <Department>()
{
new Department {
DepartmentId = "CSIS", DepartmentName = "Computing Studies"
},
new Department {
DepartmentId = "ACCT", DepartmentName = "Accounting"
},
new Department {
DepartmentId = "MKTG", DepartmentName = "Marketing"
},
new Department {
DepartmentId = "FINC", DepartmentName = "Finance"
},
};
context.Departments.AddRange(departments);
// seed courses data
List <Course> courses = new List <Course>()
{
new Course {
CourseId = 101, CourseDepartmentId = "CSIS", CourseName = "Programming I"
},
new Course {
CourseId = 102, CourseDepartmentId = "CSIS", CourseName = "Programming II"
},
new Course {
CourseId = 101, CourseDepartmentId = "ACCT", CourseName = "Accounting I"
},
new Course {
CourseId = 102, CourseDepartmentId = "ACCT", CourseName = "Accounting II"
},
new Course {
CourseId = 101, CourseDepartmentId = "FINC", CourseName = "Corporate Finance"
},
};
context.Courses.AddRange(courses);
context.SaveChanges();
// set up initial registration
// note that we can add a student to a course or
// add a course to a student, both will work and set up proper links
courses[0].Students.Add(students[0]);
courses[0].Students.Add(students[1]);
courses[1].Students.Add(students[0]);
courses[4].Students.Add(students[0]);
students[2].Courses.Add(courses[2]);
context.SaveChanges();
// need Load() for BindingList, which allows the gridviews to be sorted/edited
// and sync'd to database
context.Students.Load();
context.Courses.Load();
// show the Students and Courses tables
dataGridViewStudents.DataSource = context.Students.Local.ToBindingList();
dataGridViewCourses.DataSource = context.Courses.Local.ToBindingList();
// set up registration gridview
updateRegistration();
context.SaveChanges();
}
/// <summary>
/// zero out the db tables, then seed all tables with initial data
/// </summary>
private void SeedRegistrationDataTables()
{
// set up database log to write to output window in VS
context.Database.Log = (s => Debug.Write(s));
context.Database.Delete();
context.Database.Create();
context.SaveChanges();
// reinitialize the database, resetting everything and zeroing out data
//Database.SetInitializer(new DropCreateDatabaseAlways<StudentRegistrationEntities>());
//context.Database.Initialize(true);
// zero out the db tables
// load fully, clear, then save back
// EF sets CascadeOnDelete, so we don't have to worry about deleting
// student's registered courses first
context.Students.Load();
// below is a more difficult way to initialized the tables by hand
// reset ident so student ID numbering starts at 1
// only reset if the table was previously empty
//if (context.Students.Count() > 0)
//{
// context.Database.ExecuteSqlCommand($"DBCC CHECKIDENT('[dbo].[Students]', RESEED, 0)");
// Debug.WriteLine("Identity seed on Students table reset");
//}
//context.Departments.Load();
//context.Courses.Load();
//context.Courses.Local.Clear();
//context.Departments.Local.Clear();
//context.Students.Local.Clear();
//context.SaveChanges();
// seed student data into Students table
List <Student> students = new List <Student>()
{
new Student {
StudentFirstName = "Svetlana", StudentLastName = "Rostov", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Claire", StudentLastName = "Bloome", StudentMajor = "ACCT"
},
new Student {
StudentFirstName = "Sven", StudentLastName = "Baertschi", StudentMajor = "MKTG"
},
new Student {
StudentFirstName = "Cesar", StudentLastName = "Chavez", StudentMajor = "FINC"
},
new Student {
StudentFirstName = "Debra", StudentLastName = "Manning", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Fadi", StudentLastName = "Hadari", StudentMajor = "ACCT"
},
new Student {
StudentFirstName = "Hanyeng", StudentLastName = "Fen", StudentMajor = "MKTG"
},
new Student {
StudentFirstName = "Hugo", StudentLastName = "Victor", StudentMajor = "FINC"
},
new Student {
StudentFirstName = "Lance", StudentLastName = "Armstrong", StudentMajor = "MKTG"
},
new Student {
StudentFirstName = "Terry", StudentLastName = "Matthews", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Eugene", StudentLastName = "Fei", StudentMajor = "FINC"
},
new Student {
StudentFirstName = "Michael", StudentLastName = "Thorson", StudentMajor = "CSIS"
},
new Student {
StudentFirstName = "Simon", StudentLastName = "Li", StudentMajor = "CSIS"
},
};
context.Students.AddRange(students);
// seed departments data
List <Department> departments = new List <Department>()
{
new Department {
DepartmentId = "CSIS", DepartmentName = "Computing Studies"
},
new Department {
DepartmentId = "ACCT", DepartmentName = "Accounting"
},
new Department {
DepartmentId = "MKTG", DepartmentName = "Marketing"
},
new Department {
DepartmentId = "FINC", DepartmentName = "Finance"
},
};
context.Departments.AddRange(departments);
// seed courses data
List <Course> courses = new List <Course>()
{
new Course {
CourseId = 101, CourseName = "Programming I", Department = departments[0]
},
new Course {
CourseId = 102, CourseName = "Programming II", Department = departments[0]
},
new Course {
CourseId = 101, CourseName = "Accounting I", Department = departments[1]
},
new Course {
CourseId = 102, CourseName = "Accounting II", Department = departments[1]
},
new Course {
CourseId = 101, CourseName = "Corporate Finance", Department = departments[3],
},
};
context.Courses.AddRange(courses);
context.SaveChanges();
// set up initial registration
// note that we can add a student to a course or
// add a course to a student, both will work and set up proper links
courses[0].Students.Add(students[0]);
courses[0].Students.Add(students[1]);
courses[1].Students.Add(students[0]);
courses[4].Students.Add(students[0]);
students[2].Courses.Add(courses[2]);
context.SaveChanges();
// show the tables
// don't show the foreign key objects
dataGridViewStudents.DataSource = context.Students.Local.ToBindingList();
dataGridViewStudents.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dataGridViewStudents.Columns["Courses"].Visible = false;
dataGridViewCourses.DataSource = context.Courses.Local.ToBindingList();
dataGridViewCourses.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dataGridViewCourses.Columns["Department"].Visible = false;
dataGridViewCourses.Columns["Students"].Visible = false;
dataGridViewDepartments.DataSource = context.Departments.Local.ToBindingList();
dataGridViewDepartments.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dataGridViewDepartments.Columns["Courses"].Visible = false;
dataGridViewStudentRegistration.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
// set up registration gridview
UpdateRegistration();
context.SaveChanges();
}
