Go homepage(回首页) Upload pictures (上传图片) Write articles (发文字帖)
The author:(作者)归海一刀published in(发表于) 2014/1/30 1:03:28 LINQ体验(9)——LINQ,to,SQL语句之Insert、Update、Delete操作
我们继续讲解LINQ语句,这篇我们来讨论Insert/Update/Delete操作。这个在我们的程序中最为常用了。我们直接看例子。
说明:new一个对象,使用InsertOnSubmit方法将其加入到对应的集合中,使用SubmitChanges()提交到数据库。
NorthwindDataContext db = new NorthwindDataContext();var newCustomer = new Customer { CustomerID = "MCSFT",CompanyName = "Microsoft",ContactName = "John Doe",ContactTitle = "Sales Manager",Address = "1 Microsoft Way",City = "Redmond",Region = "WA",PostalCode = "98052",Country = "USA",Phone = "(425) 555-1234",Fax = null };db.Customers.InsertOnSubmit(newCustomer);db.SubmitChanges();
说明:Category与Product是一对多的关系,提交Category(一端)的数据时,LINQ to SQL会自动将Product(多端)的数据一起提交。
var newCategory = new Category { CategoryName = "Widgets", Description = "Widgets are the customer-facinganalogues to sprockets and cogs." };var newProduct = new Product { ProductName = "Blue Widget", UnitPrice = 34.56M, Category = newCategory };db.Categories.InsertOnSubmit(newCategory);db.SubmitChanges();
说明:在多对多关系中,我们需要依次提交。
var newEmployee = new Employee { FirstName = "Kira", LastName = "Smith" };var newTerritory = new Territory { TerritoryID = "12345", TerritoryDescription = "Anytown",Region = db.Regions.First() };var newEmployeeTerritory = new EmployeeTerritory { Employee = newEmployee, Territory = newTerritory };db.Employees.InsertOnSubmit(newEmployee);db.Territories.InsertOnSubmit(newTerritory);db.EmployeeTerritories.InsertOnSubmit(newEmployeeTerritory);db.SubmitChanges();
说明:CUD就是Create、Update、Delete的缩写。下面的例子就是新建一个ID(主键)为32的Region,不考虑数据库中有没有ID为32的数据,如果有则替换原来的数据,没有则插入。(不知道这样说对不对。大家指点一下)
Region nwRegion = new Region() { RegionID = 32, RegionDescription = "Rainy" };db.Regions.InsertOnSubmit(nwRegion);db.SubmitChanges();
说明:更新操作,先获取对象,进行修改操作之后,直接调用SubmitChanges()方法即可提交。注意,这里是在同一个DataContext中,对于不同的DataContex看下面的讲解。
Customer cust = db.Customers.First(c => c.CustomerID == "ALFKI");cust.ContactTitle = "Vice President";db.SubmitChanges();
var q = from p in db.Productswhere p.CategoryID == 1select p;foreach (var p in q){p.UnitPrice += 1.00M;}db.SubmitChanges();
说明:调用DeleteOnSubmit方法即可。
OrderDetail orderDetail = db.OrderDetails.First(c => c.OrderID == 10255 && c.ProductID == 36);db.OrderDetails.DeleteOnSubmit(orderDetail);db.SubmitChanges();
说明:Order与OrderDetail是一对多关系,首先DeleteOnSubmit其OrderDetail(多端),其次DeleteOnSubmit其Order(一端)。因为一端是主键。
var orderDetails =from o in db.OrderDetailswhere o.Order.CustomerID == "WARTH" && o.Order.EmployeeID == 3select o;var order =(from o in db.Orderswhere o.CustomerID == "WARTH" && o.EmployeeID == 3select o).First();foreach (OrderDetail od in orderDetails){db.OrderDetails.DeleteOnSubmit(od);}db.Orders.DeleteOnSubmit(order);db.SubmitChanges();
说明:Order与OrderDetail是一对多关系,在上面的例子,我们全部删除CustomerID为WARTH和EmployeeID为3 的数据,那么我们不须全部删除呢?例如Order的OrderID为10248的OrderDetail有很多,但是我们只要删除ProductID为11的OrderDetail。这时就用Remove方法。
Order order = db.Orders.First(x => x.OrderID == 10248);OrderDetail od = order.OrderDetails.First(d => d.ProductID == 11);order.OrderDetails.Remove(od);db.SubmitChanges();
说明:在对于在不同的DataContext之间,使用Attach方法来更新数据。例如在一个名为tempdb的NorthwindDataContext中,查询出Customer和Order,在另一个NorthwindDataContext中,Customer的地址更新为123 First Ave,Order的CustomerID 更新为CHOPS。
Customer c1;List deserializedOrders = new List();Customer deserializedC1;using (NorthwindDataContext tempdb = new NorthwindDataContext()){c1 = tempdb.Customers.Single(c => c.CustomerID == "ALFKI");deserializedC1 = new Customer { Address = c1.Address, City = c1.City,CompanyName=c1.CompanyName,ContactName=c1.ContactName,ContactTitle=c1.ContactTitle, Country=c1.Country,CustomerID=c1.CustomerID, Fax=c1.Fax,Phone=c1.Phone, PostalCode=c1.PostalCode, Region=c1.Region};Customer tempcust = tempdb.Customers.Single(c => c.CustomerID == "ANTON");foreach (Order o in tempcust.Orders){deserializedOrders.Add(new Order {CustomerID=o.CustomerID, EmployeeID=o.EmployeeID,Freight=o.Freight,OrderDate=o.OrderDate,OrderID=o.OrderID,RequiredDate=o.RequiredDate,ShipAddress=o.ShipAddress,ShipCity=o.ShipCity,ShipName=o.ShipName,ShipCountry=o.ShipCountry,ShippedDate=o.ShippedDate,ShipPostalCode=o.ShipPostalCode, ShipRegion=o.ShipRegion,ShipVia=o.ShipVia});}}using (NorthwindDataContext db2 = new NorthwindDataContext()){//对Customer更新,不能写错db2.Customers.Attach(deserializedC1);deserializedC1.Address = "123 First Ave";//对Order全部更新db2.Orders.AttachAll(deserializedOrders);foreach (Order o in deserializedOrders){o.CustomerID = "CHOPS";}db2.SubmitChanges();}
说明:在不同的DataContext中,实现插入、更新、删除。看下面的一个例子:
Customer cust = null;using (NorthwindDataContext tempdb = new NorthwindDataContext()){cust = tempdb.Customers.First(x => x.CustomerID == "ALFKI");}Order orderA = cust.Orders.First();Order orderB = cust.Orders.First(x => x.OrderID > orderA.OrderID);using (NorthwindDataContext db2 = new NorthwindDataContext()){db2.Customers.Attach(cust);db2.Orders.AttachAll(cust.Orders.ToList());//更新Customer的Phone.cust.Phone = "2345 5436";//更新OrderA的ShipCity.orderA.ShipCity = "Redmond";//删除OrderB.cust.Orders.Remove(orderB);//添加一个新的Order到Customer中.Order orderC = new Order() { ShipCity = "New York" };cust.Orders.Add(orderC);//提交执行db2.SubmitChanges();}
下篇介绍乐观事务和冲突。
赞