private static void UpdateDepartment(ContosoUniversityEntities db) { var dept = db.Department.Find(InsertedId); dept.Name = "John"; db.SaveChanges(); }
static void Main(string[] args) { using (var db = new ContosoUniversityEntities()) { db.Database.Log = Console.WriteLine; var department = db.Department.Include(p => p.Course); foreach (var dept in department) { Console.WriteLine(dept.Name); foreach (var item in dept.Course) { Console.WriteLine("\t" + item.Title); } } //QueryCourse(db); //InsertDepartment(db); //UpdateDepartment(db); //RemoveDepartment(db); } }
static void Main(string[] args) { using (var db = new ContosoUniversityEntities()) { using (var trans = db.Database.BeginTransaction()) { try { db.Database.Log = Console.WriteLine; var department = db.GetDepartment(); foreach (var item in department) { Console.WriteLine(item.Name); } //db.SaveChanges(); //trans.Commit(); } catch (Exception ex) { trans.Rollback(); System.Diagnostics.Debug.WriteLine(ex.ToString()); } } } //using (var db = new ContosoUniversityEntities()) //{ // db.Database.Log = Console.WriteLine; // var dept = db.Department.Find(57); // Console.WriteLine($"{dept.DepartmentID}\t{dept.Name}"); //} System.Console.Read(); }
private static void ManyToManyAddWithExceptionHandling(ContosoUniversityEntities db) { var course = db.Course.Find(1); course.Instructors.Add(new Person() { FirstName = "Will", LastName = "Huang", HireDate = DateTime.Now //Discriminator = "" }); try { db.SaveChanges(); } catch (DbEntityValidationException ex) { StringBuilder sb = new StringBuilder(); foreach (var eve in ex.EntityValidationErrors) { foreach (var ve in eve.ValidationErrors) { sb.AppendLine($"欄位 {ve.PropertyName} 發生錯誤: {ve.ErrorMessage}"); } } throw new Exception(sb.ToString(), ex); } }
private static void DeleteCourse(ContosoUniversityEntities db) { var c = db.Course.Find(9); db.Course.Remove(c); db.SaveChanges(); }
private static void PracticeCRUD(ContosoUniversityEntities db) { var dept = new Department() { Name = "酷奇資訊", Budget = 18000, StartDate = new DateTime(2019, 6, 1, 0, 0, 0), UpdatedOn = DateTime.Now }; dept.Course.Add(new Course() { Title = "ASP.NET MVC 5 開發實戰", Credits = Credits.VeryGood, Department = dept }); dept.Course.Add(new Course() { Title = "Angular 7 開發實戰", Credits = Credits.VeryGood, Department = dept }); Console.WriteLine("------ 新增部門與課程資料 ------"); db.Department.Add(dept); db.SaveChanges(); Console.WriteLine("------ 取得新增後的 DepartmentID ------"); Console.WriteLine("新部門 ID = " + dept.DepartmentID); Console.WriteLine("------ 重新查詢之前新增的部門資料 ------"); var new_dept = db.Department.Find(dept.DepartmentID); Console.WriteLine("部門名稱: " + new_dept.Name); Console.WriteLine("------ 查詢該部門所有課程資料 ------"); foreach (var course in new_dept.Course) { course.Credits = Credits.VeryGood; Console.WriteLine("\t" + course.Title + "\t" + course.Credits); } new_dept.UpdatedOn = DateTime.Now; Console.WriteLine("------ 更新該部門所有課程的 Credits 分數 ------"); db.SaveChanges(); db.Course.RemoveRange(new_dept.Course); db.Department.Remove(new_dept); Console.WriteLine("------ 移除課程資料與部門資料 ------"); db.SaveChanges(); }
private static void RemoveDepartmentByState(ContosoUniversityEntities db) { db.Entry(new Department() { DepartmentID = 56 }).State = EntityState.Deleted; db.SaveChanges(); }
private static void AsNoTracking(ContosoUniversityEntities db) { var data = db.Course.AsNoTracking(); foreach (var item in data) { Console.WriteLine(item.Title); } }
private static void UpdateCourse(ContosoUniversityEntities db) { var items = db.Course.Where(p => p.Title.Contains("Git")); foreach (var item in items) { item.MyCredits += 1; } db.SaveChanges(); // (執行此行程式,進行一次交易) 整批commit / rollback to db }
private static void SelectCourseWithRelation(ContosoUniversityEntities db) { var c = from p in db.Course select p; foreach (var item in c) { Console.WriteLine(item.Department.Name + "\t" + item.Title); } }
private static void QueryCourse(ContosoUniversityEntities db) { var data = from p in db.Course select p; foreach (var item in data) { Console.WriteLine(item.CourseID); Console.WriteLine(item.Title); Console.WriteLine(); } }
private static void DemoEntityEntryRemove(ContosoUniversityEntities db) { //var dept = db.Department.Find(22); //db.Department.Remove(dept); var dept = db.Department.Attach(new Department() { DepartmentID = 22 }); db.Entry(dept).State = EntityState.Deleted; db.SaveChanges(); }
private static void GetCourse_Git(ContosoUniversityEntities db) { //var data = db.Course.Where(p => p.Title.Contains("Git")).ToList(); var data = (from p in db.Course where p.Title.Contains("Git") select p).ToList(); foreach (var item in data) { Console.WriteLine(item.Title + "\t" + item.Department.Name); } }
static void Main(string[] args) { using (var db = new ContosoUniversityEntities()) { QueryCourse(db); InsertDepartment(db); UpdateDepartment(db); RemoveDepartment(db); } }
private static void GetDepartment(ContosoUniversityEntities db) { var data = (from p in db.Department.Include("Course") select p); foreach (var dept in data) { Console.WriteLine(dept.Name); foreach (var course in dept.Course) { Console.WriteLine("\t" + course.Title); } } }
private static void InsertDepartment(ContosoUniversityEntities db) { var dept = new Department() { Name = "Will", Budget = 100, StartDate = DateTime.Now }; db.Department.Add(dept); db.SaveChanges(); InsertedId = dept.DepartmentID; }
private static void SelectDepartmentWithRelation(ContosoUniversityEntities db) { var dept = from p in db.Department select p; foreach (var item in dept) { Console.WriteLine(item.Name); foreach (var c in item.Course) { Console.WriteLine("\t" + c.Title); } } }
private static void SelectCourseByGitOrderByCredits(ContosoUniversityEntities db) { var c = from p in db.Course where p.Title.StartsWith("Git") orderby p.Credits descending select new { p.Title }; foreach (var item in c) { Console.WriteLine(item.Title); } }
private static void AddCourse(ContosoUniversityEntities db) { var c = new Course() { Title = "Entity Framework 6.1", MyCredits = 100, CreatedOn = DateTime.Now, UpdatedOn = DateTime.Now }; c.Department = db.Department.Find(2); db.Course.Add(c); db.SaveChanges(); }
static void Main(string[] args) { using (var db = new ContosoUniversityEntities()) { db.Database.Log = Console.WriteLine; #region 延遲載入設定 //db.Configuration.LazyLoadingEnabled = false; // 關閉代理屬性 //db.Configuration.ProxyCreationEnabled = false; #endregion 延遲載入設定 db.Department.Add(new Department() { Budget = 100, Name = "TEST", StartDate = DateTime.Now.AddDays(+2) }); db.SaveChanges(); var department = db.Department.Include(c => c.Course).AsNoTracking(); foreach (var dept in department) { Console.WriteLine(dept.Name); foreach (var _cou in dept.Course) { Console.WriteLine(string.Format("\t{0}-{1}", _cou.CourseID, _cou.Title)); } } //QueryCourse(db); //InsertDepartment(db); //UpdateDepartment(db); //RemoveDepartment(db); Console.WriteLine("請輸入任意鍵繼續˙˙˙˙˙"); Console.ReadKey(); } }
static void Main(string[] args) { using (var db = new ContosoUniversityEntities()) { // 印出sql指令 db.Database.Log = (sql) => { Console.WriteLine(sql); }; //GetCourse_Git(db); //GetDepartment(db); //AddCourse(db); //UpdateCourse(db); //DeleteCourse(db); var one = db.Course.Find(6); //// 改狀態 //db.Entry(one).State = System.Data.Entity.EntityState.Modified; //// 依狀態,所以執行update sql //db.SaveChanges(); //var c = new Course() { CourseID = 7 }; //// 改狀態 //db.Entry(c).State = System.Data.Entity.EntityState.Deleted; //db.Course.Remove(c); //// 依狀態,所以執行delete sql //db.SaveChanges(); // 這邊是看狀態的變化 //Console.WriteLine(db.Entry(one).State); //one.MyCredits += 1; //Console.WriteLine(db.Entry(one).State); ////db.Entry(one).State = System.Data.Entity.EntityState.Deleted; //db.SaveChanges(); //Console.WriteLine(db.Entry(one).State); var data = db.GetCourse("%Git%"); foreach (var item in data) { Console.WriteLine(item.Title); } Console.ReadLine(); // 有這一行,console才不會馬上就被關掉 } }
private static void DemoAttachAndEntityState(ContosoUniversityEntities db) { var dept = new Department() { DepartmentID = 17, Name = "酷奇資訊2", Budget = 18000, StartDate = new DateTime(2019, 6, 1, 0, 0, 0), UpdatedOn = DateTime.Now }; db.Department.Attach(dept); dept.Name = "3"; db.SaveChanges(); }
private static void PracticeLazyLoading(ContosoUniversityEntities db) { db.Configuration.LazyLoadingEnabled = false; db.Configuration.ProxyCreationEnabled = false; var data = from p in db.Course.Include(p => p.Department) select new { CourseTitle = p.Title, DeptName = p.Department.Name }; foreach (var item in data) { Console.WriteLine(item.CourseTitle); Console.WriteLine(item.DeptName); Console.WriteLine(); } }
static void Main(string[] args) { using (var db = new ContosoUniversityEntities()) { db.Database.Log = Console.WriteLine; //var department = db.Department.Include(x => x.Course); //foreach (var dept in department) //{ // Console.WriteLine(dept.Name); // foreach (var item in dept.Course) // { // Console.WriteLine("\t" + item.Title); // } //} var d = db.Department.Find(25); d.Name = "John" + DateTime.Now; Console.ReadLine(); db.SaveChanges(); //var d = db.Department.Find(1); //d.Name += "!"; //db.SaveChanges(); //QueryCourse(db); //InsertDepartment(db); //UpdateDepartment(db); //RemoveDepartment(db); } }
static void Main(string[] args) { using (var db = new ContosoUniversityEntities()) { // db's log to console db.Database.Log = Console.WriteLine; //db.Configuration.LazyLoadingEnabled = false; //db.Configuration.ProxyCreationEnabled = false; //var dept = db.Department.Include(p => p.Course); //foreach (var d in dept) //{ // Console.WriteLine(d.Name); // foreach (var c in d.Course) // { // Console.WriteLine(c.Title); // } //} var data = db.Database.SqlQuery <CourseSqlModel>("select CourseID, Title, Credits from Course where CourseID > @p0", 2); foreach (var item in data) { Console.WriteLine(item.Title); } //QueryCourse(db); //InsertDepartment(db); //UpdateDepartment(db); //RemoveDepartment(db); } }
private static void Main(string[] args) { using (var db = new ContosoUniversityEntities()) { db.Database.Log = Console.WriteLine; var c = new Course() { Title = "Entity Framework 6.2", Credits = CourseCredits.Better, DepartmentID = 1 }; db.Course.Add(c); db.SaveChanges(); var item = db.Course.Find(c.CourseID); Console.WriteLine(item.Title + " " + item.Credits.ToString()); //db.Configuration.LazyLoadingEnabled = false; //db.Configuration.ProxyCreationEnabled = false; //var dept = db.Department.Find(1); //var dept = db.Course.Find(1); //dept.Title += "!"; //db.Entry(dept).State = EntityState.Added; //db.SaveChanges(); //var dept = db.Department.Find(20); //dept.Name = "John" + DateTime.Now; ////db.Entry(dept).State = EntityState.Added; //Console.ReadLine(); //db.SaveChanges(); //var department = db.Department.Include(p => p.Course); //foreach (var dept in department) //{ // Console.WriteLine(dept.Name); // foreach (var item in dept.Course) // { // Console.WriteLine("\t" + item.Title); // } //} //QueryCourse(db); //InsertDepartment(db); //UpdateDepartment(db); //RemoveDepartment(db); //var items = db.GetDepartment(); //foreach (var item in items) //{ // Console.WriteLine(item.Name); //} } }
static void Main(string[] args) { using (var db = new ContosoUniversityEntities()) { //把db所有的過程寫在console上 db.Database.Log = Console.WriteLine; var data = db.Database.SqlQuery <Course>("select [CourseID],[Title],[Credits] from [dbo].[Course] where CourseID >= @p0", 2); foreach (var item in data) { Console.WriteLine(item.Title); } //var c = new Course() //{ // Title = "Test1", // Credits = CourseCredits.Poor, // DepartmentID = 1 //}; //db.Course.Add(c); //db.SaveChanges(); //是否使用延遲載入 //db.Configuration.LazyLoadingEnabled = false; //是否使用代理物件 //db.Configuration.ProxyCreationEnabled = false; //注意ORM的N+1地雷 //var dept = db.Department.Find(1); //導覽屬性,一併把相關的Course查出來 //var dept = db.Department.Find(1);//.Include(x => x.Course); //dept.Name = "AA"; //db.SaveChanges(); //var dept = db.GetDepartment(); //foreach (var item in dept) //{ // Console.WriteLine(item.Name); // //Console.WriteLine("==========================="); // //foreach (var item1 in item.Course) // //{ // // Console.WriteLine(item1.Title); // //} //} //QueryCourse(db); //InsertDepartment(db); //UpdateDepartment(db); //RemoveDepartment(db); } }
static void Main(string[] args) { using (var db = new ContosoUniversityEntities()) { db.Database.Log = Console.WriteLine; var data = db.Database.SqlQuery <MyCourse>( "SELECT [CourseID],[Title],[Credits] FROM [dbo].[Course] WHERE CourseID <= @p0", 2); foreach (var item in data) { Console.WriteLine(item.Title + item.Credits); } //var c = new Course() //{ // Title = "Entity Framework 6.2", // Credits = CourseCredits.Awesome | CourseCredits.Better, // DepartmentID = 1 //}; //db.Course.Add(c); //db.SaveChanges(); //var a = db.Course.Find(c.CourseID); ////(a.Credits & CourseCredits.Awesome) == CourseCredits.Awesome //Console.WriteLine(a.Credits.ToString()); //var dept = db.Department.Find(32); //dept.Name = "John"+DateTime.Now; //Console.ReadLine(); //db.SaveChanges(); //var items = db.GetDepartment(1); //foreach (var item in items) //{ // Console.WriteLine(item.Name); //} //var dept = db.Department.Find(33); //db.Entry(dept).State = EntityState.Deleted; //db.Entry(new Department() { DepartmentID = 33 }).State = EntityState.Deleted; //db.SaveChanges(); //db.Configuration.LazyLoadingEnabled = false; //db.Configuration.ProxyCreationEnabled = false; //var department = db.Department.AsNoTracking(); //foreach (var dept in department) //{ // Console.WriteLine(dept.Name); // foreach (var item in dept.Course) // { // Console.WriteLine("\t" + item.Title); // } //} //QueryCourse(db); //InsertDepartment(db); //UpdateDepartment(db); //RemoveDepartment(db); } }
private static void RemoveDepartment(ContosoUniversityEntities db) { db.Department.Remove(db.Department.Find(InsertedId)); db.SaveChanges(); }
static void Main(string[] args) { using (var db = new ContosoUniversityEntities()) { db.Database.Log = (msg) => { Console.ForegroundColor = ConsoleColor.Yellow; Console.WriteLine(msg); Console.ResetColor(); }; //SelectCourseByGitOrderByCredits(db); //SelectCourseWithRelation(db); //Console.WriteLine("--"); //SelectDepartmentWithRelation(db); //PracticeCRUD(db); //PracticeLazyLoading(db); //ManyToManyAddWithExceptionHandling(db); //AsNoTracking(db); //DemoAttachAndEntityState(db); //var dept = new Department() //{ // DepartmentID = 17, // Name = "酷奇資訊2", // Budget = 18000, // StartDate = new DateTime(2019, 6, 1, 0, 0, 0), // UpdatedOn = DateTime.Now //}; //DemoEntityEntryRemove(db); //var dept = db.Department.Add(new Department() //{ // Name = "酷奇資訊3", // Budget = 18000, // StartDate = new DateTime(2019, 6, 1, 0, 0, 0) //}); //db.SaveChanges(); //var dept = db.Department.Find(1); //dept.Budget += 1; //Console.ReadKey(); //db.SaveChanges(); var data = db.GetCourse("Git"); foreach (var item in data) { Console.WriteLine(item.Title); } } }