public List <Object> GetJoinedTableData() { ContosoUniversityEntities contextObj = new ContosoUniversityEntities(); List <Object> newTab = new List <object>(); var joinedTab = contextObj.Students.Join(contextObj.Enrollments, stud => stud.StudentID, enr => enr.StudentID, (stud, enr) => enr); //Joining two tables by StudentID var groupedTab = from tab in joinedTab group tab by new { tab.Date, tab.Student.LastName, tab.Student.FirstName, tab.Student.Email, tab.Student.StudentID } into grpTab select new { ID = grpTab.Key.StudentID, Date = grpTab.Key.Date, FirstName = grpTab.Key.FirstName, LastName = grpTab.Key.LastName, Email = grpTab.Key.Email, Count = grpTab.Select(cache => cache).Count() }; foreach (var entry in groupedTab) { newTab.Add(new //Creating new anonymous object in order to change the date for ShortDateString { ID = entry.ID, Date = entry.Date.ToShortDateString(), FullName = string.Format("{0} {1}", entry.FirstName, entry.LastName), Email = entry.Email, Count = entry.Count, }); } return(newTab); }
public void DeleteStudent(int id) { ContosoUniversityEntities contextObj = new ContosoUniversityEntities(); contextObj.Students.Remove(contextObj.Students.First(stud => stud.StudentID == id)); contextObj.SaveChanges(); }
public ActionResult List() { var db = new ContosoUniversityEntities(); var data = db.Course.ToList(); return(View(data)); }
private static void AddNewRecord(ContosoUniversityEntities db) { db.Course.Add(new Course() { Title = "Hello 1", Department = db.Department.Find(5) }); }
private static void DeleteData(ContosoUniversityEntities db) { foreach (var item in db.Course.Where(p => p.CourseID >= 11 && p.CourseID <= 18).ToList()) { //db.Course.Remove(new Course() { CourseID = item.CourseID }); db.Course.Remove(item); } db.Database.ExecuteSqlCommand("DELETE FROM dbo.Course WHERE CourseID >= @p0 AND CourseID <= @p1", 11, 18); }
private static void 多對多關聯新增(ContosoUniversityEntities db) { var c = db.Course.Find(1); //c.Person.Add(db.Person.Find(3)); c.Person.Add(new Person() { FirstName = "AA", LastName = "BB", Discriminator = "123" }); }
private static void UpdateData(ContosoUniversityEntities db) { foreach (var item in db.Course.ToList()) { item.Credits += 1; } var course = db.Course.Find(5); course.Credits = 5; course.Department = db.Department.Find(2); }
private void AddingNewEnrollment(int studentId, int courseId) { Enrollment newEnrollment = new Enrollment(); ContosoUniversityEntities contextObj = new ContosoUniversityEntities(); //Adding new entry to Enrollment newEnrollment.CourseID = courseId; newEnrollment.Date = DateTime.Now; newEnrollment.StudentID = studentId; contextObj.Enrollments.Add(newEnrollment); contextObj.SaveChanges(); }
private void AddingNewStudent(string firstName, string lastName, DateTime birthDate, string email = "Has not specified") { Student newStudent = new Student(); ContosoUniversityEntities contextObj = new ContosoUniversityEntities(); //Adding new Student newStudent.FirstName = firstName; newStudent.LastName = lastName; newStudent.BirthDate = birthDate; newStudent.Email = email; contextObj.Students.Add(newStudent); contextObj.SaveChanges(); }
private static void QueryData(ContosoUniversityEntities db) { db.Configuration.ProxyCreationEnabled = false; var depts = db.Department.Include("Course").ToList(); foreach (var dept in depts) { Console.WriteLine("部門: " + dept.Name); foreach (var course in dept.Course) { Console.WriteLine("\t" + course.Title); } } }
public override void OnActionExecuting(ActionExecutingContext filterContext) { using (var db = new ContosoUniversityEntities()) { filterContext.Controller.ViewBag.DepartmentList = (from p in db.Department select new DepartmentCreationVM() { DepartmentId = p.DepartmentID, Name = p.Name, Budget = p.Budget, StartDate = p.StartDate }).ToList(); } base.OnActionExecuting(filterContext); }
public void InsertNewEntry(string firstName, string lastName, DateTime birthDate, string course, string email = "Has not specified") { int courseId, studentId = 0; Student newStudent = new Student(); Enrollment newEnrollment = new Enrollment(); ContosoUniversityEntities contextObj = new ContosoUniversityEntities(); var student = (from stud in contextObj.Students where stud.FirstName == firstName && stud.LastName == lastName && stud.BirthDate == birthDate && stud.Email == email select stud).FirstOrDefault(); //In case student doesn't exist if (student as Student == null) { AddingNewStudent(firstName, lastName, birthDate, email); //Getting the lastly added student ID foreach (var stud in contextObj.Students) { studentId = stud.StudentID; } //Finding Course ID courseId = (from crs in contextObj.Courses where crs.CourseName == course select crs).FirstOrDefault().CourseID; AddingNewEnrollment(studentId, courseId); } //In case Student allready exist else { studentId = (student as Student).StudentID; //Finding Course ID courseId = (from crs in contextObj.Courses where crs.CourseName == course select crs).FirstOrDefault().CourseID; AddingNewEnrollment(studentId, courseId); } }
public void UpdateStudentData(int id, string name, string email = null) { ContosoUniversityEntities contextObj = new ContosoUniversityEntities(); Student student = contextObj.Students.First(stud => stud.StudentID == id); if (name != null) { string[] arr = name.Split(' '); if (arr.Length > 1) { student.FirstName = arr[0]; student.LastName = arr[1]; student.Email = email; contextObj.SaveChanges(); } } }
public List <object> GetStudents(string name) { ContosoUniversityEntities contextObj = new ContosoUniversityEntities(); string[] arr; List <object> studentsInfo = new List <object>(); if (!String.IsNullOrEmpty(name)) { arr = name.Split(' '); if (arr.Length > 1) { var firstName = arr[0]; var lastName = arr[1]; var students = (from stud in contextObj.Students where stud.FirstName == firstName && stud.LastName == lastName select stud).ToList <Student>(); if (students != null) { foreach (Student stud in students) { var student = new //Creating new anonymous object in order to change the date for ShortDateString { FirstName = stud.FirstName, LastName = stud.LastName, Email = stud.Email, BirthDate = stud.BirthDate.ToShortDateString(), StudentID = stud.StudentID }; studentsInfo.Add(student); } } } } return(studentsInfo); }
public ActionResult List(IList <CourseBatchViewModel> data) { var db = new ContosoUniversityEntities(); if (ModelState.IsValid) { foreach (var item in data) { var dbitem = db.Course.Find(item.CourseID); dbitem.Title = item.Title; dbitem.Credits = item.Credits; } db.SaveChanges(); //return Json(data); return(RedirectToAction("List")); } else { return(View(db.Course.ToList())); } }
public List<ObjectPageProperty> FindByObjectPage(string objectName, int pageType) { var _entities = new ContosoUniversityEntities(); return _entities.Set<ObjectPageProperty>().Where(obj => obj.ObjectPage.ObjectName == objectName && obj.ObjectPage.PageType == pageType).ToList(); }
static void Main(string[] args) { Console.WriteLine(DateTime.Now + "\t" + "Started."); using (var db = new ContosoUniversityEntities()) { Console.WriteLine(DateTime.Now + "\t" + "Query Started."); db.Database.Log = (log) => { Console.WriteLine(log); }; //QueryData(db); db.Database.Log = null; //AddNewRecord(db); //UpdateData(db); //DeleteData(db); //多對多關聯新增(db); var c = db.Course.Find(1); Console.WriteLine(db.Entry(c).State); c.Credits++; Console.WriteLine(db.Entry(c).State); var ce = db.Entry(c); Console.WriteLine("修改前: " + ce.OriginalValues.GetValue <int>("Credits")); Console.WriteLine("修改後: " + ce.CurrentValues.GetValue <int>("Credits")); var cc = new Course() { Title = "Hello 2", Department = db.Department.Find(5) }; db.Course.Add(cc); var ce2 = db.Entry(cc); if (ce2.State == System.Data.Entity.EntityState.Added) { ce2.Entity.CreatedOn = DateTime.Now; } db.SaveChanges(); Console.WriteLine(db.Entry(c).State); //Console.WriteLine(DateTime.Now + "\t" + "SaveChanges Started."); //try //{ // db.SaveChanges(); //} //catch (DbEntityValidationException ex) //{ // throw ex; //} //Console.WriteLine(DateTime.Now + "\t" + "Query Started."); //QueryData(db); } Console.WriteLine(DateTime.Now + "\t" + "Ended."); }