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);
}
}
}
