private static void Practice01()
{
using (var db = new ContosoUniversityEntities())
{
var result = from item in db.Course
where item.Title.Contains("Git")
select item;
foreach (var item in result)
{
Console.WriteLine(item.CourseID + "\t" + item.Title);
}
}
}
/// <summary>
/// 練習導覽屬性
/// </summary>
private static void Practice02()
{
using (var db = new ContosoUniversityEntities())
{
var result = from item in db.Department
select item;
foreach (var department in result)
{
Console.WriteLine(department.DepartmentID + "\t" + department.Name);
foreach (var course in department.Course)
{
Console.WriteLine("\t" + course.CourseID + "\t" + course.Title);
}
}
}
}
private static void Practice13()
{
using (var db = new ContosoUniversityEntities())
{
var courses = db.Course;
db.Database.Log = (msg) => {
Trace.WriteLine(msg);
};
foreach (var item in db.vwCourse)
{
Console.WriteLine(item.Title);
}
var course = courses.Find(1);
course.Credits = CreditType.Meduim;
db.SaveChanges();
}
}
//---------------Day2--------------
/// <summary>
/// 目的: 練習將int轉成enum
/// </summary>
private static void Practice11()
{
using (var db = new ContosoUniversityEntities())
{
var courses = db.Course;
foreach (var course in courses)
{
Console.WriteLine(course.Credits + "\t" + course.Title);
}
}
}
/// <summary>
/// 練習 Entity Framework 使用 檢視表 的各種注意事項
/// </summary>
private static void Practice12()
{
using (var db = new ContosoUniversityEntities())
{
foreach (var course in db.vwCourse)
{
Console.WriteLine(course.Title);
}
}
}
/// <summary>
/// 目的: 測試使用timestamp的並行模式 - 練習 Entity Framework 的並行模式 (Concurrency Mode) 使用方式
/// 準備:
/// 1. DB中新增type為timestamp的RowVersion欄位
/// 2. 更新資料庫模型
/// 3. 將RowVersion的並行模式設為Fixed
/// 測試
/// 1. 將course某一筆的credit改為777, build成功後,將執行環境copy到某資料夾, 然後執行 -> exe1
/// 2. 將course某一筆的credit改為666, build成功後執行 -> exe2
/// 3. 在exe1的window按任一鍵, 先更新好credit
/// 4. 在exe2的window按任一鍵, 會發生DbupdateConcurrency exception
/// </summary>
private static void Practice09()
{
using (var db = new ContosoUniversityEntities())
{
var course = db.Course.Find(1);
course.Credits = CreditType.Low;
Console.ReadKey();
db.SaveChanges();
}
}
/// <summary>
/// 以store procedure取得要查尋的title的course - 練習 Entity Framework 的 Stored Procedure 匯入與設定
/// </summary>
private static void Practice10()
{
using (var db = new ContosoUniversityEntities())
{
var courses = db.GetCourse("%GIT%");
foreach (var course in courses)
{
Console.WriteLine(course.DepartmentName + "\t" + course.Title);
}
}
}
/// <summary>
/// 目的: 使用離線模式修改資料
/// </summary>
private static void Practice08()
{
Course course;
using (var db = new ContosoUniversityEntities())
{
course = db.Course.Find(1);
course.Credits = CreditType.Low;
}
//即使attach了, 但沒有修改state, EF還是不會對DB作Update
using (var db = new ContosoUniversityEntities())
{
db.Course.Attach(course);
db.SaveChanges();
Console.WriteLine("Before attached and change state, Credits={0}", db.Course.Find(1).Credits);
}
using (var db = new ContosoUniversityEntities())
{
db.Entry(course).State = System.Data.Entity.EntityState.Modified; //=db.Course.Attach(course);
db.SaveChanges();
Console.WriteLine("After attached, Credits={0}", db.Course.Find(1).Credits);
}
}
/// <summary>
/// 目的: 使用 db.ChangeTracker.Entities() 實作在修改資料後 SaveChanges() 時自動填入 ModifiedOn 欄位的內容
/// </summary>
private static void Practice07()
{
using (var db = new ContosoUniversityEntities())
{
var course = db.Course.Find(1);
course.Credits = CreditType.Low;
var entry = db.Entry(course);
Console.WriteLine("Original Value=" + entry.OriginalValues.GetValue<int>("Credits"));
Console.WriteLine("Current Value=" + entry.CurrentValues.GetValue<int>("Credits"));
Console.WriteLine("State:" + "\t" + entry.State);
db.SaveChanges();
}
}
//---------------Day1--------------
/// <summary>
/// 使用 db.Database.SqlQuery 執行任意查詢語法，並透過自訂類別取得資料
/// 1. 使用SQL Management Tool的 "在編輯器中設計查詢", 直接有圖表來點選要輸出的資料,下方會輸出SQL command
/// 2. 設計一個新的class以做為回傳資料的對應繫結
/// 3. 將1的command複製後, 以db.Database.SqlQuery<T>以SQL command query來取得資料
/// </summary>
private static void Practice05()
{
var sql = @"SELECT Course.CourseID, Course.Title, Department.Name AS DepartmentName FROM Course INNER JOIN Department ON Course.DepartmentID = Department.DepartmentID";
using (var db = new ContosoUniversityEntities())
{
var result = db.Database.SqlQuery<Course2>(sql);
foreach (var item in result)
{
Console.WriteLine(item.CourseID + "\t" + item.Title + "\t" + item.DepartmentName);
}
}
}
/// <summary>
/// 練習多對多的新增方式
/// Requirement: Course=7的課程沒有Instructor, 需要新增Person=5當成它的instructor
/// Todo
/// 1: 修改course導覽屬性的Person為instructors, 可讀性較高
/// 2. 對course做新增instructor
/// </summary>
private static void Practice04()
{
using (var db = new ContosoUniversityEntities())
{
var course = db.Course.Find(7);
course.Instructors.Add(db.Person.Find(5));
db.SaveChanges();
}
}
/// <summary>
/// 實驗StoreGeratedPattern (Identity, Computed, None)
/// </summary>
private static void Practice03()
{
using (var db = new ContosoUniversityEntities())
{
var c1 = new Course()
{
Title = "Test",
DepartmentID = 1,
Credits = CreditType.Low,
ModifyOn = DateTime.Now
};
db.Course.Add(c1);
db.SaveChanges();
}
}