/*********************Insert,delete,update,attach操作練習**********************************/
??????????? //簡單插入形式
??????????? var student = new T_Student
??????????? {
??????????????? stuNumber = "20091777",
??????????????? stuName = "張航",
??????????????? stuAge = 23,
??????????????? stuSex = "男"
??????????? };

??????????? ctx.T_Student.InsertOnSubmit(student);
??????????? ctx.SubmitChanges();
??????????? //一對多形式，或者多對多形式，需要按照主外健關系依次插入

??????????? var student1 = new T_Student
??????????? {
??????????????? stuNumber = "2009177",
??????????????? stuName = "謝亮",
??????????????? stuAge = 23,
??????????????? stuSex = "男"
??????????? };
??????????? var score1 = new T_Score
??????????? {
??????????????? stuNumber = student1.stuNumber,
??????????????? courceNumber = "001",
??????????????? score = 78
??????????? };
??????????? var score2 = new T_Score
??????????? {
??????????????? stuNumber = student1.stuNumber,
??????????????? courceNumber = "002",
??????????????? score = 78
??????????? };

? 　　　? ctx.T_Student.InsertOnSubmit(student1);
??????????? ctx.T_Score.InsertOnSubmit(score1);
??????????? ctx.T_Score.InsertOnSubmit(score2);
??????????? ctx.SubmitChanges();

??????????? //更愛張航的年齡為21,這種涉及到單項或者更改項很少的，可以用下面的方式更改

??????????? var list = ctx.T_Student.First(s=>s.stuName=="張航");
??????????? list.stuAge = 21;
??????????? ctx.T_Student.InsertOnSubmit(list);
??????????? ctx.SubmitChanges();

??????????? //讓學生表中各個學生的年齡加一歲
??????????? var list1 = from t in ctx.T_Student
???????????????????????? select t;
??????????? foreach (var item in list1)
??????????? {
??????????????? item.stuAge = item.stuAge + 1;
??????????? }
??????????? ctx.T_Student.InsertAllOnSubmit(list1);
??????????? ctx.SubmitChanges();

??????????? //刪除學號為2091721，課程編號為002的學生
??????????? var list2 = ctx.T_Score.First(s => s.stuNumber == "2091721"&&s.courceNumber=="002");
??????????? ctx.T_Score.DeleteOnSubmit(list2);
??????????? ctx.SubmitChanges();
??????????? //刪除學號為黃陽的個人信息及分數信息
??????????? var list3 = ctx.T_Student.First(s=>s.stuName=="黃陽");
??????????? var list4 = from a in ctx.T_Score
??????????????????????? where a.T_Student.stuName == "黃陽"
??????????????????????? select a;
??????????? foreach (var item in list4)
??????????? {
??????????????? ctx.T_Score.DeleteOnSubmit(item);
??????????? }
??????????? ctx.T_Student.DeleteOnSubmit(list3);
??????????? ctx.SubmitChanges();
??????????? //還可以推理刪除，推理刪除需要用Remove方法。由于表的設計不是很完善，這里就不給演示了
?????????? ?
??????????? //對于在不同的上下文間，需要使用attach方法進行更新，如在一個上下文中查出一個叫黃陽的學生和它的各科成績，在另一個上下文中將其年齡改為19，將其所有學科科目成績改為33分
??????????? T_Student student2;
??????????? List<T_Score> listScore = new List<T_Score>();
??????????? using (DB_StudentDataContext ctx1 = new DB_StudentDataContext())
??????????? {
??????????????? var stu = ctx1.T_Student.First(s=>s.stuName=="黃陽");
??????????????? student2 = new T_Student {
??????????????????? stuNumber=stu.stuNumber,
??????????????????? stuName=stu.stuName,
??????????????????? stuAge=stu.stuAge,
??????????????????? stuSex=stu.stuSex
??????????????? };

??????????????? var scores = from k in ctx1.T_Score
???????????????????????????? where k.T_Student.stuName == "黃陽"
???????????????????????????? select k;
??????????????? foreach (var item in scores)
??????????????? {
??????????????????? listScore.Add(item);
??????????????? }
??????????? }

??????????? ctx.T_Student.Attach(student2);
??????????? student.stuSex = "19";

??????????? ctx.T_Score.AttachAll(listScore);
??????????? foreach (var item in listScore)
??????????? {
??????????????? item.score = 33;
??????????? }

??????????? ctx.SubmitChanges();

??????????? //使用attach進行刪除，更改，插入

??????????? T_Student student3 = null;
??????????? using (DB_StudentDataContext ctx2 = new DB_StudentDataContext())
??????????? {
??????????????? DataLoadOptions dlo = new DataLoadOptions();
??????????????? dlo.LoadWith<T_Student>(s => s.T_Score);
??????????????? ctx2.DeferredLoadingEnabled = false;
??????????????? student3 = ctx2.T_Student.First(s=>s.stuName=="黃陽");
??????????? }
??????????? T_Score scores1 = student3.T_Score.First();
??????????? T_Score scores2 = student3.T_Score.First(s => Convert.ToInt32(s.stuNumber) > Convert.ToInt32(scores1.stuNumber));

??????????? using (DB_StudentDataContext ctx3 = new DB_StudentDataContext())
??????????? {
??????????????? ctx3.T_Student.Attach(student3);
??????????????? ctx3.T_Score.AttachAll(student3.T_Score.ToList());
??????????????? scores1.score = 80;
??????????????? student3.T_Score.Remove(scores2);

??????????????? T_Score score3 = new T_Score {
??????????????????? stuNumber = "2009177",
??????????????????? courceNumber = "003",
??????????????????? score=88
??????????????? };
??????????????? student3.T_Score.Add(score3);

??????????????? ctx3.SubmitChanges();

Linq無聊練習系列7----Insert,delete,update,attach操作練習