protected void ExecuteButton_Click(object sender, EventArgs e)
{
using (SchoolEntities context = new SchoolEntities())
{
RowsAffectedLabel.Text = context.ExecuteStoreCommand("UPDATE Course SET Credits = Credits * {0}", CreditsMultiplierTextBox.Text).ToString();
}
}
public void Initialize(Person connectedPerson)
{
ConnectedPerson = connectedPerson;
ResetAll();
if (HasRequiredPermissions)
{
// Get the school data and use to it to populate this View Model's data
SchoolEntities schoolData = new SchoolEntities();
// Create a list of all the classes in the school
schoolData.Classes.ToList().ForEach(currClass => AvailableClasses.Add(currClass.classID, currClass.className));
AvailableHomeroomClasses.Add(FIELD_NOT_SET, "לא מוגדר");
schoolData.Classes.Where(currClass => currClass.Teachers.Count() == 0).ToList()
.ForEach(currClass => AvailableHomeroomClasses.Add(currClass.classID, currClass.className));
// Create a list of all the parents in the school
AvailableParents.Add(FIELD_NOT_SET, "לא מוגדר");
schoolData.Persons.Where(p => p.isParent).ToList()
.ForEach(parent => AvailableParents.Add(parent.personID, parent.firstName + " " + parent.lastName));
// Create a list of all the students in the school
schoolData.Persons.Where(p => p.isStudent).ToList()
.ForEach(student => AvailableStudents.Add(student.personID, student.firstName + " " + student.lastName));
// Create a list of all the courses in the school
schoolData.Courses.Where(course => course.isHomeroomTeacherOnly == false).ToList()
.ForEach(course => AvailableCoursesMustChoose.Add(course.courseID, course.courseName));
AvailableCourses.Add(FIELD_NOT_SET, "לא מוגדר");
AvailableCoursesMustChoose.ToList().ForEach(course => AvailableCourses.Add(course.Key, course.Value));
}
}
/// <summary>
/// Reset data in preparation for a new year (deleting studentds, scores, messages, events...)
/// </summary>
private void PrepareNewYear()
{
if (HasRequiredPermissions)
{
// Ask for confirmation again as this is a dangerous action
bool confirmationResult = _messageBoxService.ShowMessage("תהליך זה ימחק מידע על התלמידים הקיימים. האם להמשיך?",
"Are you sure?", MessageType.ACCEPT_CANCEL_MESSAGE, MessagePurpose.INFORMATION);
if (confirmationResult)
{
// Delete all yearly information (student users, events, scores, lessons...)
SchoolEntities mySchool = new SchoolEntities();
// Delete students
foreach (Person person in mySchool.Persons.Where(p => p.isStudent))
{
person.User.isDisabled = true;
mySchool.Persons.Remove(person);
}
mySchool.Students.RemoveRange(mySchool.Students);
mySchool.Events.RemoveRange(mySchool.Events);
mySchool.Messages.RemoveRange(mySchool.Messages);
mySchool.Grades.RemoveRange(mySchool.Grades);
mySchool.Lessons.RemoveRange(mySchool.Lessons);
mySchool.SaveChanges();
_refreshDataCommand.Execute(null);
_messageBoxService.ShowMessage("מידע עודכן!", "Saved Changes", MessageType.OK_MESSAGE, MessagePurpose.INFORMATION);
}
}
}
public void Initialize(Person connectedPerson)
{
ConnectedPerson = connectedPerson;
// Initalize all lists
Courses = new ObservableDictionary <int, string>();
Classes = new ObservableDictionary <int, string>();
StudentsGrades = new ObservableCollection <GradedStudent>();
_schoolData = new SchoolEntities();
SelectedCourse = NOT_ASSIGNED;
SelectedClass = NOT_ASSIGNED;
// Get the teacher's data
Teacher _teacherInformation = ConnectedPerson.Teacher;
if (_teacherInformation != null)
{
// Only gathering courses here -> classes depend on the selected course
GatherTeacherCourses(_teacherInformation);
}
else
{
// Report there is some issue with the teacher in the Database (probably user has teacher permissions but isn't a teacher
_messageBoxService.ShowMessage("תקלה בהרשאות המורה", "תקלה", MessageType.OK_MESSAGE, MessagePurpose.ERROR);
}
}
/// <summary>
/// Metodo que devuelve la lista de personas almacenadas en la base de datos
/// </summary>
/// <returns>Devuelve un List de Person</returns>
public static List<Person> getAll()
{
List<Person> lstPersonas = new List<Person>();
try
{
using (SchoolEntities db = new SchoolEntities())
{
var consulta = from e in db.Person
select e;
lstPersonas = consulta.ToList();
/*Person per;
foreach (Person p in consulta)
{
per = new Person();
per.PersonID = p.PersonID;
per.FirstName = p.FirstName;
per.LastName = p.LastName;
per.HireDate = p.HireDate;
per.EnrollmentDate = p.EnrollmentDate;
lstPersonas.Add(per);
}*/
}
}
catch (SqlException sqlex)
{
throw sqlex;
}
catch (Exception ex)
{
throw ex;
}
return lstPersonas;
}
// GET: Student
public ActionResult Index()
{
var students = new List <StudentManagementSystem.Models.Student>();
using (var db = new SchoolEntities())
{
//method 1: create new list from full list returned from database with only the desired records
students = db.Students.Where(x => x.Removed == null).ToList();
//method 2: query the database for only the desired records
students = (from s in db.Students
where s.Removed == null
select s).ToList();
}
var studentVMs = new List <StudentVM>();
foreach (Student student in students)
{
var studentVM = new StudentVM();
studentVM.ID = student.ID;
studentVM.FirstName = student.FirstName;
studentVM.LastName = student.LastName;
studentVMs.Add(studentVM);
}
return(View(studentVMs));
}
/// <summary>
/// Reset all the fields and selections in the form
/// </summary>
private void ResetAll()
{
_schoolData = new SchoolEntities();
AvailableSearchChoices = new ObservableDictionary <int, string>();
EventsTableData = new ObservableCollection <EventData>();
Recipients = new ObservableDictionary <int, string>();
PossibleLocations = new ObservableCollection <string>(_schoolData.Rooms.Select(room => "חדר " + room.roomName).ToList());
PossibleEvents =
new ObservableCollection <string>(new List <string>()
{
"מבחן", "טיול", "חופשה", "פגישה", "שינוי בשיעור", "טקס", "אירוע"
});
// Select student category as the default category option
SearchingStudentEvents = true;
SearchingClassEvents = false;
SearchingSchoolEvents = false;
SendingToStudent = true;
SendingToClass = false;
SendingToEveryone = false;
SelectedSearchChoice = NOT_ASSIGNED;
SelectedEvent = null;
// Get the date of Tommarow as the default
EventDatetime = DateTime.Today.AddDays(1);
EventLocation = string.Empty;
EventName = string.Empty;
EventText = string.Empty;
}
public ActionResult StudentManagement(DateTime?empty)
{
var db = new SchoolEntities();
var showst = db.People.Where(st => st.HireDate == empty);
return(View(showst));
}
public LessonManagementViewModel(Person connectedPerson, ICommand refreshDataCommand, IMessageBoxService messageBoxService)
: base(messageBoxService)
{
HasRequiredPermissions = connectedPerson.isSecretary || connectedPerson.isPrincipal;
_refreshDataCommand = refreshDataCommand;
if (HasRequiredPermissions)
{
_schoolData = new SchoolEntities();
AvailableSearchChoices = new ObservableDictionary <int, string>();
LessonsTableData = new ObservableCollection <LessonData>();
AvailableClasses = new ObservableDictionary <int, string>();
AvailableCourses = new ObservableDictionary <int, string>();
AvailableTeachers = new ObservableDictionary <int, string>();
AvailableRooms = new ObservableDictionary <int, string>();
TeacherAvailableCourses = new ObservableDictionary <int, string>();
// Fill up days and hour lists (allow 'no meeting' choice with the NOT_ASSIGNED value)
AvailableDays = new ObservableDictionary <int, string>();
AvailableDays.Add(NOT_ASSIGNED, "ללא");
for (int i = 0; i < Globals.DAY_NAMES.Length; i++)
{
// Count days as 1 to N rather than 0 to N-1
AvailableDays.Add(i + 1, Globals.DAY_NAMES[i]);
}
AvailableHours = new ObservableDictionary <int, string>();
AvailableHours.Add(NOT_ASSIGNED, "ללא");
for (int i = 0; i < Globals.HOUR_NAMES.Length; i++)
{
// Count hours as 1 to N rather than 0 to N-1
AvailableHours.Add(i + 1, Globals.HOUR_NAMES[i]);
}
}
}
/// <summary>
/// metodo que añade un curso nuevo
/// </summary>
/// <param name="c">curso a añadir</param>
/// <returns>devulve el identificador del nuevo curso o -1 si no se puede añadir</returns>
public static int Add(Course c)
{
try
{
if (c != null)
{
using(SchoolEntities db = new SchoolEntities()) {
db.Course.Add(c);
db.SaveChanges();
return c.CourseID;
}
}
else
{
return -1;
}
}
catch (SqlException sqlex)
{
return -1;
}
catch (Exception ex)
{
return -1;
}
}
private void BtnAddUser_Click(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(TxtFirstName.Text) && !string.IsNullOrEmpty(TxtLastName.Text) && !string.IsNullOrEmpty(TxtPassword.Text) && (dateEnrollmentDate.Checked || dateHireDate.Checked) && comboBoxDisc.Text != null)
{
using (var db = new SchoolEntities())
{
DateTime?dt = null;
var hireDate = (dateHireDate.Enabled == true) ? dateHireDate.Value : dt;
var enrollDate = (dateEnrollmentDate.Enabled == true) ? dateEnrollmentDate.Value : dt;
var person = new Person()
{
LastName = TxtLastName.Text, FirstName = TxtFirstName.Text, HireDate = hireDate, EnrollmentDate = enrollDate, Discriminator = comboBoxDisc.SelectedItem.ToString(), Password = TxtPassword.Text
};
db.Person.Add(person);
db.SaveChanges();
dataGridView1.DataSource = db.Person.ToList();
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Selected = true;
dataGridView1.FirstDisplayedCell = dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[0];
}
TxtFirstName.ResetText();
TxtLastName.ResetText();
TxtPassword.ResetText();
dateHireDate.ResetText();
dateEnrollmentDate.ResetText();
comboBoxDisc.SelectedItem = null;
}
else
{
MessageBox.Show("You havent entered every needed information.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
/// <summary>
/// Metodo que obtiene los datos de la persona que se corresponde con le id que se pasa por parametro.
/// </summary>
/// <param name="ID">Id de la persona que queremos obtener. Es un int</param>
/// <returns>Devuelve un objeto de tipo Person</returns>
public static Person get(int ID)
{
Person per = new Person();
try
{
using (SchoolEntities db = new SchoolEntities())
{
var consulta = from e in db.Person
where e.PersonID == ID
select e;
per = consulta.First();
}
}
catch (SqlException sqlex)
{
throw sqlex;
}
catch (Exception ex)
{
throw ex;
}
return per;
}
public void ControleerDatKimBijDePersonenZit()
{
using (var context = new SchoolEntities())
{
context.Configuration.ProxyCreationEnabled = false;
Assert.IsTrue(context.People.Any(x => x.FirstName == "Kim"));
Assert.IsInstanceOfType(context.People.First(x => x.FirstName == "Kim"), typeof(Teacher));
var kim = context.People.First(x => x.FirstName == "Kim");
if (kim is Teacher)
{
Console.WriteLine("Hooray!");
}
else
{
Assert.Fail("Kim should be a teacher!");
}
Assert.IsTrue(context.
People.
OfType<Teacher>().
Any(x => x.FirstName == "Kim"));
Assert.IsFalse(context.
People.
OfType<Student>().
Any(x => x.FirstName == "Kim"));
}
}
private void dataGridView1_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyData == Keys.Delete)
{
if (MessageBox.Show("Do you really want to delete selected row?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
// removing by selecting courseid cell to delete
// TODO: adding entry to onsite / online course and fixing courseinstructor table because its gonna crash when deleting base entries
try
{
int val;
int.TryParse(dataGridView1.SelectedRows[0].Cells[0].Value.ToString(), out val);
using (var db = new SchoolEntities())
{
var query = from o in db.Course
where o.CourseID.Equals(val)
select o;
var queryOnline = from o in db.OnlineCourse
where o.CourseID.Equals(val)
select o;
var queryOnsite = from o in db.OnsiteCourse
where o.CourseID.Equals(val)
select o;
var queryGrades = from o in db.StudentGrade
where o.CourseID.Equals(val)
select o;
db.Course.Remove(query.First());
if (queryOnline.FirstOrDefault() != null)
{
db.OnlineCourse.Remove(queryOnline.First());
}
if (queryOnsite.FirstOrDefault() != null)
{
db.OnsiteCourse.Remove(queryOnsite.First());
}
if (queryGrades.FirstOrDefault() != null)
{
db.StudentGrade.RemoveRange(queryGrades.ToArray());
}
db.SaveChanges();
dataGridView1.DataSource = db.Course.ToList();
}
}
catch (Exception ex)
{
MessageBox.Show("You cannot delete essential courses.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
private void BtnAdd_Click(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(TxtName.Text) && !string.IsNullOrEmpty(TxtBudget.Text) && !string.IsNullOrEmpty(TxtName.Text) && comboBoxInstructors.Text != null)
{
using (var db = new SchoolEntities())
{
// parsing budget if correct
decimal budget = default;
decimal.TryParse(TxtBudget.Text, out budget);
if (budget.ToString() == "0")
{
MessageBox.Show("Invalid budget value.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// parsing deptid if correct
int deptID = default;
int.TryParse(TxtId.Text, out deptID);
if (deptID.ToString() == "0")
{
MessageBox.Show("Invalid department ID value.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// getting dept admin id from name + surname
var findAdmin = from o in db.Person
where (o.FirstName + " " + o.LastName).Equals(comboBoxInstructors.Text)
select o.PersonID;
var dept = new Department()
{
Name = TxtName.Text,
Budget = budget,
DepartmentID = deptID,
StartDate = dateTimeStart.Value,
Administrator = findAdmin.First()
};
TxtId.ResetText();
TxtBudget.ResetText();
TxtName.ResetText();
dateTimeStart.ResetText();
comboBoxInstructors.SelectedItem = null;
db.Department.Add(dept);
db.SaveChanges();
dataGridViewDepartments.DataSource = db.Department.ToList();
}
}
else
{
MessageBox.Show("Please enter missing information.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public JsonResult GetSchoolPersonDetails()
{
using (SchoolEntities schoolEntities = new SchoolEntities())
{
var peoplelist = schoolEntities.People.ToList();
return(Json(peoplelist, JsonRequestBehavior.AllowGet));
}
}
public static Person getById(int id)
{
using (var context = new SchoolEntities())
{
var p = (from element in context.People where element.PersonID == id select element).First();
return(MapDTAToBo(p));
}
}
public void addPerson(string first, string last)
{
using (var context = new SchoolEntities())
{
/* Basistraining.RM.Comm.Person.Erstellen(first, last)*/;
var p = context.Person.ToList();
}
}
/// <summary>
/// Gets all the departments and related courses.
/// </summary>
public List <Department> GetDepartments()
{
using (SchoolEntities context = new SchoolEntities())
{
// Use System.Data.Objects.ObjectQuery(T).Include to eagrly load the related courses.
return(context.Departments.Include("Courses").OrderBy(d => d.Name).ToList());
}
}
public void TestTablePerTypeInheritanceOpCourse()
{
using (var context = new SchoolEntities())
{
Assert.IsTrue(context.Courses.OfType<OnsiteCourse>().Any());
Assert.IsTrue(context.Courses.OfType<OnlineCourse>().Any());
}
}
public void Initialize(Person connectedPerson)
{
// Reset all information
ConnectedPerson = connectedPerson;
AvailableUserTypes.Clear();
AvailableUsers.Clear();
AvailableClasses.Clear();
AvailableParents.Clear();
AvailableStudents.Clear();
AvailableCourses.Clear();
AvailableCoursesMustChoose.Clear();
AvailableHomeroomClasses.Clear();
if (HasRequiredPermissions)
{
_schoolData = new SchoolEntities();
// Create a list of all the editable user types
if (!CanEditManagement)
{
AvailableUserTypes.AddRange(new List <string>()
{
Globals.USER_TYPE_STUDENT, Globals.USER_TYPE_TEACHERS, Globals.USER_TYPE_PARENTS
});
}
else
{
AvailableUserTypes.AddRange(new List <string>()
{
Globals.USER_TYPE_STUDENT, Globals.USER_TYPE_TEACHERS, Globals.USER_TYPE_PARENTS,
Globals.USER_TYPE_SECRETARIES, Globals.USER_TYPE_PRINCIPAL
});
}
SelectedUserType = AvailableUserTypes[0];
// Create a list of all the classes in the school
_schoolData.Classes.ToList().ForEach(currClass => AvailableClasses.Add(currClass.classID, currClass.className));
AvailableHomeroomClasses.Add(FIELD_NOT_SET, "לא מוגדר");
_schoolData.Classes.Where(currClass => currClass.Teachers.Count() == 0).ToList()
.ForEach(currClass => AvailableHomeroomClasses.Add(currClass.classID, currClass.className));
// Create a list of all the parents in the school
AvailableParents.Add(FIELD_NOT_SET, "לא מוגדר");
_schoolData.Persons.Where(p => p.isParent).ToList()
.ForEach(parent => AvailableParents.Add(parent.personID, parent.firstName + " " + parent.lastName));
// Create a list of all the students in the school
_schoolData.Persons.Where(p => p.isStudent).ToList()
.ForEach(student => AvailableStudents.Add(student.personID, student.firstName + " " + student.lastName));
// Create a list of all the courses in the school
_schoolData.Courses.Where(course => course.isHomeroomTeacherOnly == false).ToList()
.ForEach(course => AvailableCoursesMustChoose.Add(course.courseID, course.courseName));
AvailableCourses.Add(FIELD_NOT_SET, "לא מוגדר");
AvailableCoursesMustChoose.ToList().ForEach(course => AvailableCourses.Add(course.Key, course.Value));
}
}
private void BtnAddGrade_Click(object sender, EventArgs e)
{
if (ComboBoxCourse.Text != null && ComboBoxGrades.Text != null && ComboBoxStudents.Text != null)
{
using (var db = new SchoolEntities())
{
var getCourse = from o in db.Course
where o.Title.Equals(ComboBoxCourse.Text)
select o.CourseID;
int courseID = default;
if (getCourse.Any())
{
courseID = getCourse.First();
}
var getStudent = from o in db.Person
where (o.FirstName + " " + o.LastName).Equals(ComboBoxStudents.Text)
select o.PersonID;
int studentID = default;
if (getStudent.Any())
{
studentID = getStudent.First();
}
decimal.TryParse(ComboBoxGrades.Text, out decimal studentGrade);
var grade = new StudentGrade()
{
CourseID = courseID,
StudentID = studentID,
Grade = studentGrade
};
db.StudentGrade.Add(grade);
db.SaveChanges();
DataGridViewGrades.DataSource = db.StudentGrade.ToList();
ComboBoxCourse.SelectedItem = null;
ComboBoxGrades.SelectedItem = null;
ComboBoxStudents.SelectedItem = null;
DataGridViewGrades.Rows[DataGridViewGrades.Rows.Count - 1].Selected = true;
DataGridViewGrades.FirstDisplayedCell = DataGridViewGrades.Rows[DataGridViewGrades.Rows.Count - 1].Cells[0];
}
}
else
{
MessageBox.Show("Please provide all required information.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public ActionResult GetCourses()
{
SchoolEntities db = new SchoolEntities();
var courseList = db.Courses.Select(x => new CourseList {
id = x.CourseID, Title = x.Title
}).ToList();
return(Json(courseList, JsonRequestBehavior.AllowGet));
}
public ActionResult Create(Teacher teacher)
{
using (SchoolEntities dbContext = new SchoolEntities())
{
dbContext.Teachers.Add(teacher);
dbContext.SaveChanges();
}
return(RedirectToAction("Show"));
}
static void Main(string[] args)
{
using (var db = new SchoolEntities())
{
var abc = db.Departments.ToList();
Console.WriteLine(abc);
Console.ReadKey();
}
}
// GET: Instructor
public ActionResult Instructors()
{
var db = new SchoolEntities();
var ins = from i in db.CourseInstructors
where i.CourseID == new int()
select i;
return(View(db.CourseInstructors.ToList()));
}
public Person[] GetPeople()
{
SchoolEntities db = new SchoolEntities();
var persons = from p in db.People
orderby p.PersonID
select p;
return(persons.ToArray());
}
public JsonResult Loading()
{
SchoolEntities db = new SchoolEntities();
var persons = from p in db.People
orderby p.PersonID
select p;
return(Json(persons, JsonRequestBehavior.AllowGet));
}
// GET: Escuela
public ActionResult Tabla()
{
using (SchoolEntities oTabla = new SchoolEntities())
{
var lTabla = from s in oTabla.School_Table select s;
return(View(lTabla.ToList()));
}
}
public ActionResult Create(Student student)
{
using (SchoolEntities dbContext = new SchoolEntities())
{
dbContext.Students.Add(student);
dbContext.SaveChanges();
}
return(View("Show"));
}
public void Remove(int id)
{
using (var contex = new SchoolEntities())
{
var p = contex.Person.Find(id);
contex.Person.Remove(p);
contex.SaveChanges();
}
}
public void ConnectionTest()
{
using (var context = new SchoolEntities())
{
Assert.IsNotNull(context);
Assert.IsNotNull(context.People);
}
}
public Person GetOnePerson(int id)
{
Person person = new Person();
using (var contex = new SchoolEntities())
{
person = MapperToCommen(contex.Person.Find(id));
}
return(person);
}
public void TestMethod1()
{
using (var context = new SchoolEntities())
{
foreach (var person in context.People)
{
Console.WriteLine("Name: {0} {1}", person.FirstName, person.LastName);
}
}
}
public ActionResult Show()//查的功能 show function
{
List <Teacher> showAllTeachers = null;
using (SchoolEntities dbContext = new SchoolEntities())
{
showAllTeachers = dbContext.Teachers.ToList();
}
return(View("TeacherList", showAllTeachers));
}
[HttpGet] //Delete function，删
public ActionResult Delete(int id)
{
Teacher targetTeacher = null;
using (SchoolEntities dbContext = new SchoolEntities())
{
targetTeacher = dbContext.Teachers.SingleOrDefault(m => m.Id == id);
}
return(View(targetTeacher));
}
public void ConcurrencyCheck_ShouldBe_OnAllFields()
{
using (new TransactionScope())
using (var context1 = new SchoolEntities())
using (var context2 = new SchoolEntities())
{
var p1 = context1.People.OfType<Instructor>().First();
var p2 = context2.People.Find(p1.PersonID) as Instructor;
p1.Location = "Smalle Zijde 35";
context1.SaveChanges();
p2.Location = "Kruisboog 42";
context2.SaveChanges();
}
}
public void ConcurrentUpdate_ClientWins()
{
using (new TransactionScope())
{
using (var context1 = new SchoolEntities())
using (var context2 = new SchoolEntities())
{
var p1 = context1.People.Find(1);
var p2 = context2.People.Find(1);
p1.FirstName = "UPDATE";
context1.SaveChanges();
try
{
p2.FirstName = "SOMETHING ELSE";
context2.SaveChanges();
}
catch (DbUpdateConcurrencyException ex)
{
var entry = ex.Entries.Single();
entry.OriginalValues.SetValues(entry.GetDatabaseValues());
context2.SaveChanges();
}
Assert.AreEqual(p2.FirstName, "SOMETHING ELSE");
Assert.AreEqual(EntityState.Unchanged, context2.Entry(p2).State);
context2.SaveChanges();
}
using (var context = new SchoolEntities())
{
Assert.AreEqual("SOMETHING ELSE", context.People.Find(1).FirstName);
}
}
}
/// <summary>
/// metodo que devulve un curso dado su identificador
/// </summary>
/// <param name="ID">identificador del curso que buscamos</param>
/// <returns>devulve el curso si existe</returns>
public static Course get(int ID)
{
Course curso = new Course();
try
{
using (SchoolEntities db = new SchoolEntities())
{
var consulta = from tabla in db.Course where tabla.CourseID == ID select tabla;
curso = consulta.First();
}
}
catch (SqlException sqlex)
{
throw sqlex;
}
catch (Exception ex)
{
throw ex;
}
return curso;
}
/// <summary>
/// Metodo para añadir una nueva persona. Devuelve el id de la persona insertada.
/// </summary>
/// <param name="p">Objeto Person que contiene todos los datos de la persona que se quiere insertar excepto el id que se genera automatico al insertar. </param>
/// <returns>Devuelve el id de la persona insertada si todo va bien y -1 si no se inserta</returns>
public static int add(Person p)
{
try
{
using (SchoolEntities db = new SchoolEntities())
{
db.Person.Add(p);
db.SaveChanges();
}
}
catch (SqlException sqlex)
{
return -1;
}
catch (Exception ex)
{
return -1;
}
return p.PersonID;
}
public static void Main(string[] args)
{
SchoolEntities dbContext = new SchoolEntities();
//for (int i = 1; i <= 10; i++)
//{
// Teacher teacher = new Teacher();
// teacher.Name = String.Format("Teacher #{0}", i);
// dbContext.Teachers.Add(teacher);
//}
//dbContext.SaveChanges();
//Console.WriteLine("Teacher database setup all ready!");
for (int i = 1; i <= 100; i++)
{
Student student = new Student();
student.Name = String.Format("Student #{0}", i);
int teacherId = i % 10;
student.TeacherId = teacherId == 0 ? 10 : teacherId;
dbContext.Students.Add(student);
}
dbContext.SaveChanges();
Console.WriteLine("Student database setup all ready!");
Console.ReadLine();
}
/// <summary>
/// metodo para listar los cursos
/// </summary>
/// <returns>devuelve una lista de todos los cursos</returns>
public static List<Course> getAll()
{
List<Course> lst = new List<Course>();
try
{
using (SchoolEntities db = new SchoolEntities())
{
var consulta = from tabla in db.Course select tabla;
lst = consulta.ToList();
}
}
catch (SqlException sqlex)
{
throw sqlex;
}
catch (Exception ex)
{
throw ex;
}
return lst;
}
public void LoadByKey_ShouldReturn_EntityWithKeyOrNullIfNotFound()
{
using (var context = new SchoolEntities())
{
var p = context.People.Find(1);
Assert.IsNotNull(p);
var ne = context.People.Find(12345);
Assert.IsNull(ne);
}
}
public void MockingEntityFramework_ZonderMockingFramework()
{
var data = new List<Person>
{
new Student
{
PersonID = 25,
FirstName = "Pietje",
LastName = "Puk",
}
};
var context = new SchoolEntities();
context.People = new DbSetMock<Person>(data);
var query = from p in context.People
where p.FirstName == "Kim"
select p;
Assert.IsFalse(query.Any());
Assert.IsTrue(context.People.Any(p => p.FirstName == "Pietje"));
}
public void NotExistingInstructor_ShouldBe_Added()
{
// TransactionScope om onze test database niet te vervuilen. Handig!
using (var ts = new TransactionScope())
using (var context = new SchoolEntities())
{
var person = context
.People
.OfType<Student>()
.FirstOrDefault(p => p.FirstName == "Pietje" && p.LastName == "Puk");
if (person == null)
{
person = new Student
{
FirstName = "Pietje",
LastName = "Puk",
EnrollmentDate = DateTime.Today
};
context.People.Add(person);
}
else
{
person.LastName = "Update";
}
try
{
context.SaveChanges();
}
catch (DbEntityValidationException ex)
{
Console.WriteLine(string.Join(Environment.NewLine, ex.EntityValidationErrors.SelectMany(e => e.ValidationErrors.Select(f => f.ErrorMessage))));
Assert.Fail();
}
}
}
/// <summary>
/// Create a simple db for tests to use
/// </summary>
private static void DropDatabase()
{
using (var db = new SchoolEntities())
{
if (db.Database.Exists())
{
db.Database.Delete();
}
}
}
/// <summary>
/// Create a simple db for tests to use
/// </summary>
private static void CreateDatabase()
{
using (var db = new SchoolEntities())
{
db.Database.Initialize(false);
}
SqlConnection.ClearAllPools();
}
public void GivenStudent_ShouldHave_StudentGrades()
{
using (var context = new SchoolEntities())
{
Assert.IsTrue(context.People.OfType<Student>().Any(s => s.FirstName == "Gytis" && s.Grades.Any(g => g.Course.Name == "Composition" && g.Grade == 4)));
}
}
public void GivenInstructor_ShouldNotHave_Course()
{
using (var context = new SchoolEntities())
{
Assert.IsFalse(context.People.OfType<Instructor>().Any(s => s.PersonID == 5 && s.Course.Any(g => g.Name == "Chemistry")));
}
}
public void GivenInstructor_ShouldNotBe_DynamicProxy()
{
using (var context = new SchoolEntities())
{
context.Configuration.ProxyCreationEnabled = false;
var p = context.People.OfType<Instructor>().First();
Assert.AreEqual(typeof(Instructor), p.GetType());
}
}
public void GivenInstructor_ShouldContain_Location()
{
using (var context = new SchoolEntities())
{
Assert.IsTrue(context.People.OfType<Instructor>().Any(p => p.PersonID == 1 && p.Location == "17 Smith"));
}
}
public void GivenProxyCreationDisabled_Should_LazyLoadingNotWorking()
{
using (var context = new SchoolEntities())
{
context.Configuration.ProxyCreationEnabled = false;
context.Configuration.LazyLoadingEnabled = true;
// First instructor with courses
var p = context.People.OfType<Instructor>().Where(i => i.Course.Any()).First();
// Courses not fetched because no proxy used
Assert.AreEqual(0, p.Course.Count);
}
}
public void LoadByKey_ShouldNot_InvokeQueryOnSubsequentRequest()
{
using (var context = new SchoolEntities())
{
var sb = new StringBuilder();
context.Database.Log = s => sb.Append(s);
var isaiah = context.People.First(p => p.FirstName == "Isaiah");
Console.WriteLine(sb.ToString());
Assert.AreNotEqual("", sb.ToString(), "Expecting DB lookup with First");
sb.Clear();
context.People.Find(isaiah.PersonID);
Assert.AreEqual("", sb.ToString(), "Expecting entity loaded from cache with Find");
context.People.First(p => p.PersonID == isaiah.PersonID);
Console.WriteLine(sb.ToString());
Assert.AreNotEqual("", sb.ToString(), "Expecting DB lookup with First");
}
}
public void ConcurrentUpdate_ClietnChooses()
{
using (new TransactionScope())
{
using (var context1 = new SchoolEntities())
using (var context2 = new SchoolEntities())
{
var p1 = context1.People.Find(1);
var p2 = context2.People.Find(1);
p1.FirstName =
p1.LastName = "UPDATE";
context1.SaveChanges();
try
{
p2.FirstName =
p2.LastName = "SOMETHING ELSE";
context2.SaveChanges();
}
catch (DbUpdateConcurrencyException ex)
{
var entry = ex.Entries.Single();
var current = entry.CurrentValues;
var other = entry.GetDatabaseValues();
var resolved = other.Clone();
ClientChooses(current, other, resolved);
entry.OriginalValues.SetValues(other);
entry.CurrentValues.SetValues(resolved);
context2.SaveChanges();
}
Assert.AreEqual(p2.FirstName, "UPDATE");
Assert.AreEqual(p2.LastName, "SOMETHING ELSE");
Assert.AreEqual(EntityState.Unchanged, context2.Entry(p2).State);
context2.SaveChanges();
}
using (var context = new SchoolEntities())
{
var p = context.People.Find(1);
Assert.AreEqual("UPDATE", p.FirstName);
Assert.AreEqual("SOMETHING ELSE", p.LastName);
}
}
}
/// <summary>
/// metodo que elimina un curso dado su ID
/// </summary>
/// <param name="ID">identificador del curso a eliminar</param>
/// <returns>verdadero o falso segun si tuvo exito o no</returns>
public static bool Remove(int ID)
{
try
{
using (SchoolEntities db = new SchoolEntities())
{
var consulta = from tabla in db.Course where tabla.CourseID == ID select tabla;
Course curso = consulta.First();
db.Course.Remove(curso);
db.SaveChanges();
return true;
}
}
catch (SqlException sqlex)
{
return false;
}
catch (Exception ex)
{
return false;
}
}
public void GivenListOfInstructors_ShouldContain_Instructor()
{
using (var context = new SchoolEntities())
{
Assert.IsTrue(
context.People.OfType<Instructor>().Any(p => p.FirstName == "Kim" && p.LastName == "Abercrombie"));
}
}
/// <summary>
/// Metodo para eliminar la persona que se correpsonde con el id que se pasa por parametro.
/// </summary>
/// <param name="ID">id de la persona a eliminar de tipo int</param>
/// <returns>devuelve true si se realizo correctamente y false si hubo algun problema</returns>
public static bool remove(int ID)
{
try
{
using (SchoolEntities db = new SchoolEntities())
{
var resultado = from e in db.Person
where e.PersonID == ID
select e;
Person per = resultado.First();
db.Person.Remove(per);
db.SaveChanges();
}
}
catch (SqlException sqlex)
{
throw sqlex;
}
catch (Exception ex)
{
throw ex;
}
return true;
}
public void DetachedEntities_ShouldBe_AddedBeforeSaveChanges()
{
Person pietje = null;
// Die scope werkt dus ook over contexten heen!
using (new TransactionScope())
{
using (var context = new SchoolEntities())
{
// AsNoTracking zorgt ervoor dat de ChangeTracker buitenspel wordt gezet.
pietje = context.People.AsNoTracking().First(p => p.PersonID == 33);
}
pietje.LastName = "AS NO TRACKING";
using (var context = new SchoolEntities())
{
// Slecht idee, twee pietjes in de DB
//context.People.Add(pietje);
context.Entry(pietje).State = EntityState.Modified;
context.SaveChanges();
}
}
}
public void UsingStoredProcedureOnContext()
{
using (new TransactionScope())
using (var context = new SchoolEntities())
{
context.DeletePerson(1);
}
}
public void GivenListOfInstructors_ShouldNotContain_Student()
{
using (var context = new SchoolEntities())
{
Assert.IsFalse(
context.People.OfType<Instructor>().Any(p => p.FirstName == "Gytis" && p.LastName == "Barzdukas"));
}
}
