LINQ to SQL语句之Insert/Update/Delete操作
这篇我们来讨论Insert/Update/Delete操作。这个在我们的程序中最为常用了。我们直接看例子。
Insert/Update/Delete操作
Insert
1.简单形式
说明：new一个对象，使用InsertOnSubmit方法将其加入到对应的集合中，使用SubmitChanges()提交到数据库。
NorthwindDataContextdb =newNorthwindDataContext();varnewCustomer =newCustomer{
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();2.一对多关系
说明：Category与Product是一对多的关系，提交Category(一端)的数据时，LINQ to SQL会自动将Product(多端)的数据一起提交。
varnewCategory =newCategory{
CategoryName ="Widgets",
Description ="Widgets are the ……"};varnewProduct =newProduct{
ProductName ="Blue Widget",
UnitPrice = 34.56M,
Category = newCategory
};
db.Categories.InsertOnSubmit(newCategory);
db.SubmitChanges();3.多对多关系
说明：在多对多关系中，我们需要依次提交。
varnewEmployee =newEmployee{
FirstName ="Kira",
LastName ="Smith"};varnewTerritory =newTerritory{
TerritoryID ="12345",
TerritoryDescription ="Anytown",
Region = db.Regions.First()
};varnewEmployeeTerritory =newEmployeeTerritory{
Employee = newEmployee,
Territory = newTerritory
};
db.Employees.InsertOnSubmit(newEmployee);
db.Territories.InsertOnSubmit(newTerritory);
db.EmployeeTerritories.InsertOnSubmit(newEmployeeTerritory);
db.SubmitChanges();4.Override using Dynamic CUD
说明：CUD就是Create、Update、Delete的缩写。下面的例子就是新建一个ID(主键)为32的Region，不考虑数据库中有没有ID为32的数据，如果有则替换原来的数据，没有则插入。（不知道这样说对不对。大家指点一下）
RegionnwRegion =newRegion()
{
RegionID = 32,
RegionDescription ="Rainy"};
db.Regions.InsertOnSubmit(nwRegion);
db.SubmitChanges();Update
说明：更新操作，先获取对象，进行修改操作之后，直接调用SubmitChanges()方法即可提交。注意，这里是在同一个DataContext中，对于不同的DataContex看下面的讲解。
1.简单形式
Customercust =
db.Customers.First(c => c.CustomerID =="ALFKI");
cust.ContactTitle ="Vice President";
db.SubmitChanges();2.多个项
varq =frompindb.Productswherep.CategoryID == 1selectp;foreach(varpinq)
{
p.UnitPrice += 1.00M;
}
db.SubmitChanges();Delete
1.简单形式
说明：调用DeleteOnSubmit方法即可。
OrderDetailorderDetail =
db.OrderDetails.First
(c => c.OrderID == 10255 && c.ProductID == 36);
db.OrderDetails.DeleteOnSubmit(orderDetail);
db.SubmitChanges();2.一对多关系
说明：Order与OrderDetail是一对多关系，首先DeleteOnSubmit其OrderDetail(多端)，其次DeleteOnSubmit其Order(一端)。因为一端是主键。
varorderDetails =fromoindb.OrderDetailswhereo.Order.CustomerID =="WARTH"&&
o.Order.EmployeeID == 3selecto;varorder =
(fromoindb.Orderswhereo.CustomerID =="WARTH"&& o.EmployeeID == 3selecto).First();foreach(OrderDetailodinorderDetails)
{
db.OrderDetails.DeleteOnSubmit(od);
}
db.Orders.DeleteOnSubmit(order);
db.SubmitChanges();3.Inferred Delete(推断删除)
说明：Order与OrderDetail是一对多关系，在上面的例子，我们全部删除CustomerID为WARTH和EmployeeID为3 的数据，那么我们不须全部删除呢？例如Order的OrderID为10248的OrderDetail有很多，但是我们只要删除ProductID为11的OrderDetail。这时就用Remove方法。
Orderorder = db.Orders.First(x => x.OrderID == 10248);OrderDetailod =
order.OrderDetails.First(d => d.ProductID == 11);
order.OrderDetails.Remove(od);
db.SubmitChanges();Update with Attach
说明：在对于在不同的DataContext之间，使用Attach方法来更新数据。例如在一个名为tempdb的NorthwindDataContext中，查询出Customer和Order，在另一个NorthwindDataContext中，Customer的地址更新为123 First Ave，Order的CustomerID 更新为CHOPS。
Customerc1;List<Order> deserializedOrders =newList<Order>();CustomerdeserializedC1;using(NorthwindDataContexttempdb =newNorthwindDataContext())
{
c1 = tempdb.Customers.Single(c => c.CustomerID =="ALFKI");
deserializedC1 =newCustomer{
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
};Customertempcust =
tempdb.Customers.Single(c => c.CustomerID =="ANTON");foreach(Orderointempcust.Orders)
{
deserializedOrders.Add(newOrder{
CustomerID = o.CustomerID,
EmployeeID = o.EmployeeID,
Freight = o.Freight,
rderDate = o.OrderDate,
rderID = 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(NorthwindDataContextdb2 =newNorthwindDataContext())
{//对Customer更新，不能写错db2.Customers.Attach(deserializedC1);
deserializedC1.Address ="123 First Ave";//对Order全部