private static void 離線模式資料操作()
{
var c = new Course()
{
CourseID = 20, Title = "123", DepartmentID = 1, Credits = 1
};
using (var db = new ContosoUniversityEntities())
{
Console.WriteLine(db.Entry(c).State);
db.Course.Attach(c);
Console.WriteLine(c.Title);
Console.WriteLine(db.Entry(c).State);
db.Course.ToList();
var tt = db.Course.Find(20);
Console.WriteLine(tt.Title);
//db.Course.Add(c);
//c.Title = "321";
//Console.WriteLine(db.Entry(c).State);
//db.SaveChanges();
}
using (var db = new ContosoUniversityEntities())
{
//c.Title = "111111";
//db.Entry(c).State = EntityState.Modified;
//db.SaveChanges();
}
}
private static void 取得物件實體狀態(ContosoUniversityEntities db)
{
db.Database.Log = Console.WriteLine;
var c = db.Course.ToList().LastOrDefault();
Console.WriteLine(c.Title + "\t" + db.Entry(c).State);
c.Credits += 1;
Console.WriteLine(c.Title + "\t" + db.Entry(c).State);
//db.SaveChanges();
db.Course.Remove(c);
Console.WriteLine(c.Title + "\t" + db.Entry(c).State);
//db.SaveChanges();
//db.Entry(c).State = System.Data.Entity.EntityState.Deleted;
//db.SaveChanges();
//db.Entry(c).State = System.Data.Entity.EntityState.Modified;
//db.SaveChanges();
//db.Entry(c).State = System.Data.Entity.EntityState.Added;
//db.SaveChanges();
//var c = db.Course.First();
//c.Title = "TEST 123";
////c.ModifiedOn = DateTime.Now;
//if (db.Entry(c).State == System.Data.Entity.EntityState.Modified)
//{
// var ce = db.Entry(c);
// var v1 = ce.CurrentValues.GetValue<string>("Title");
// v1 = c.Title;
// v1 = ce.Entity.Title;
// var v2 = ce.OriginalValues.GetValue<string>("Title");
// foreach (var prop in ce.OriginalValues.PropertyNames)
// {
// //ce.OriginalValues.GetValue<string>(prop);
// }
// Console.WriteLine("New Value: " + v1 + "\r\nOld Value: " + v2);
// ce.CurrentValues.SetValues(new
// {
// ModifiedOn = DateTime.Now
// });
//}
}
private static void 呼叫預存程序()
{
using (var db = new ContosoUniversityEntities())
{
var data = db.Get部門名稱與課程數量統計(4);
foreach (var item in data)
{
Console.WriteLine(item.Name + "\t" + item.CourseCount);
}
}
}
static void Main(string[] args)
{
//離線模式資料操作();
//呼叫預存程序();
using (var db = new ContosoUniversityEntities())
{
db.Database.Log = (msg) =>
{
//File.AppendAllText(@"G:\test.txt", msg);
Debug.WriteLine(msg);
};
db.Configuration.LazyLoadingEnabled = false;
var data = db.Course
.Where(p => p.CourseType.HasFlag(CourseType.前端));
foreach (var item in data)
{
Console.WriteLine("------------------------------------");
Console.WriteLine(item.Title);
var refLink = db.Entry(item).Reference(p => p.Department);
if (!refLink.IsLoaded)
{
refLink.Load();
}
Console.WriteLine(item.Department.Name);
Console.WriteLine("------------------------------------");
Console.WriteLine();
}
//var c = db.Course.Find(5);
//c.CourseType = CourseType.前端 | CourseType.後端;
//db.SaveChanges();
//Console.WriteLine(db.Course.Find(7).CourseType);
//db.Department.Add(new Department()
//{
// Name = "TEST0515",
// Budget = 123.45M,
// StartDate = DateTime.Now.AddDays(-29),
// InstructorID = 4
//});
//db.SaveChanges();
}
//Console.ReadKey();
}
static void Main(string[] args)
{
#region mark
#region demo1 利用EF撈資料
//using (var db = new ContosoUniversityEntities())
//{
//foreach (var item in db.Course)
//{
// Console.WriteLine(item.Title + "\t" + item.DepartmentID);
//}
//}
#endregion
#region demo2 利用EF撈資料(1對多)
//using (var db = new ContosoUniversityEntities())
//{
//foreach (var item in db.Department)
//{
// Console.WriteLine(item.Name);
// foreach (var course in item.Course)
// {
// Console.WriteLine("\t" + course.Title);
// }
//}
//}
#endregion
#region dmmo3 CU data
//using (var db = new ContosoUniversityEntities())
//{
////新增
//var newCouse = new Course()
//{
// CourseID = 0,
// Title = "EF",
// Credits = 1,
// DepartmentID = 2,
// CreateOn = DateTime.Now.AddDays(1)
//};
//db.Course.Add(newCouse);
////更新
//var c = db.Course.Find(8);
//c.ModifyOn = DateTime.Now;
//db.SaveChanges();
//}
#endregion
#region demo4 trace sql
//using (var db = new ContosoUniversityEntities())
//{
//db.Database.Log = (msg) =>
//{
// Console.WriteLine(msg);
//};
//Console.WriteLine("\t");
//}
#endregion
#region demo5 entry.state
//using (var db = new ContosoUniversityEntities())
//{
//var c = db.Course.Find(8);
//var ce = db.Entry(c);
//Console.WriteLine(ce.State);
////db.Course.Remove(c);
////直接變動狀態也可進行資料異動
//c.Title = "EF2";
//db.SaveChanges();
////複製
////db.Entry(c).State = System.Data.Entity.EntityState.Added;
//}
#endregion
#region demo6 離線
//var d = new Course()
//{
// CourseID = 0,
// Title = "EF",
// Credits = 1,
// DepartmentID = 2,
// CreateOn = DateTime.Now.AddDays(1)
//};
//using (var db1 = new ContosoUniversityEntities())
//{
// db1.Course.Attach(d);//離線物件變成連線，可以用attach，會有快取的狀況
// db1.Entry(d).State = System.Data.Entity.EntityState.Added;
// db1.SaveChanges();
//}
#endregion
#region demo7 離線(2個生命週期
//var c = new Course();
//using (var db = new ContosoUniversityEntities())
//{
// c = db.Course.Find(8);
// c.Credits += 1;
//}
// using (var db1 = new ContosoUniversityEntities())
//{
// db1.Entry(c).State = System.Data.Entity.EntityState.Modified;
// db1.SaveChanges();
//}
#endregion
#region demo8 預存程序對應
//using (var db = new ContosoUniversityEntities())
//{
// db.Database.Log = Console.WriteLine;
// var c = db.Department.Find(2);
// db.Entry(c).State = System.Data.Entity.EntityState.Added;
// db.SaveChanges();
//}
#endregion
#region demo 9 列舉
//using (var db = new ContosoUniversityEntities())
//{
//新增複選列舉
//var c = db.Course.Find(8);
//c.Credits = CourseCredit.中級 | CourseCredit.高級;
//db.SaveChanges();
//查詢含有特定列舉, use flag
// var d = db.Course.Where(p => p.Credits.HasFlag(CourseCredit.低級) || p.Credits.HasFlag(CourseCredit.中級));
// foreach (var item in d)
// {
// Console.WriteLine(item.CourseID + ":" + item.Credits + "\t");
// }
//}
#endregion
#endregion
using (var db = new ContosoUniversityEntities())
{
//新增複選列舉
//var c = db.Course.Find(8);
//c.Credits = CourseCredit.中級 | CourseCredit.高級;
//db.SaveChanges();
//查詢含有特定列舉, use flag
var d = db.Course.Where(p => p.Credits.HasFlag(CourseCredit.低級) || p.Credits.HasFlag(CourseCredit.中級));
foreach (var item in d)
{
Console.WriteLine(item.CourseID + ":" + item.Credits + "\t");
}
}
}
private static void EF基本操作練習(ContosoUniversityEntities db)
{
db.Configuration.ProxyCreationEnabled = false;
// 讀取所有 dbo.Course 中的資料
foreach (var item in db.Course)
{
Console.WriteLine(item.Title);
}
// 用 LINQ 篩選資料
//var data = from p in db.Course
// where p.Title != Guid.NewGuid().ToString()
// select p;
//foreach (var item in data)
//{
// Console.WriteLine(item.Title);
//}
//// 新增 Course 資料
//var c = new Course()
//{
// Title = "Git Test 2",
// Credits = 4,
// DepartmentID = 1
//};
////c.Department = db.Department.Find(2);
//db.Course.Add(c);
// 取得 Inserted ID
//Console.WriteLine(c.CourseID);
//db.SaveChanges();
//Console.WriteLine(c.CourseID);
//Console.ReadLine();
// 刪除一筆資料
//db.Course.Remove(db.Course.Find(12));
//// 批次更新資料
//foreach (var item in db.Course)
//{
// item.Credits += 1;
//}
//db.SaveChanges();
//var one = db.Course.Include("Department").FirstOrDefault(p => p.CourseID == 1);
//Console.WriteLine(one.Title + "\t" + one.Department.Name);
//foreach (var dept in db.Department)
//{
// Console.WriteLine(dept.Name);
// foreach (var course in dept.Course)
// {
// Console.WriteLine("\t" + course.Title);
// }
//}
// var sql = @"SELECT Department.DepartmentID, Department.Name,
// (SELECT COUNT(*) FROM dbo.Course) as CourseCount
// FROM Department";
// var data = db.Database.SqlQuery<DeptCourseCount>(sql);
//var data = db.vwDeptCourseCount;
//foreach (var item in data)
//{
// Console.WriteLine(item.Name + "\t" + item.CourseCount);
//}
//db.Course.Where(p => p.CourseID == 1);
// AutoMapper
//var c = db.Course.Create();
//c.DepartmentID = 1;
//c.Title = "123123123";
//db.Course.Add(c);
//db.SaveChanges();
//var data = db.Course.AsNoTracking();
//foreach (var item in data)
//{
// Console.WriteLine(item.Title);
//}
}
