LINQ to SQL语句之Insert/Update/Delete操作
这篇我们来讨论Insert/Update/Delete操作这个在我们的程序中最为常用了我们直接看例子
Insert/Update/Delete操作
Insert
简单形式
说明new一个对象使用InsertOnSubmit方法将其加入到对应的集合中使用SubmitChanges()提交到数据库
NorthwindDataContextdb =newNorthwindDataContext();varnewCustomer =newCustomer{
CustomerID =MCSFT
CompanyName =Microsoft
ContactName =John Doe
ContactTitle =Sales Manager
Address = Microsoft Way
City =Redmond
Region =WA
PostalCode =
Country =USA
Phone =()
Fax =null};
dbCustomersInsertOnSubmit(newCustomer);
dbSubmitChanges();一对多关系
说明Category与Product是一对多的关系提交Category(一端)的数据时LINQ to SQL会自动将Product(多端)的数据一起提交
varnewCategory =newCategory{
CategoryName =Widgets
Description =Widgets are the ……};varnewProduct =newProduct{
ProductName =Blue Widget
UnitPrice = M
Category = newCategory
};
dbCategoriesInsertOnSubmit(newCategory);
dbSubmitChanges();多对多关系
说明在多对多关系中我们需要依次提交
varnewEmployee =newEmployee{
FirstName =Kira
LastName =Smith};varnewTerritory =newTerritory{
TerritoryID =
TerritoryDescription =Anytown
Region = dbRegionsFirst()
};varnewEmployeeTerritory =newEmployeeTerritory{
Employee = newEmployee
Territory = newTerritory
};
dbEmployeesInsertOnSubmit(newEmployee);
dbTerritoriesInsertOnSubmit(newTerritory);
dbEmployeeTerritoriesInsertOnSubmit(newEmployeeTerritory);
dbSubmitChanges();Override using Dynamic CUD
说明CUD就是CreateUpdateDelete的缩写下面的例子就是新建一个ID(主键)为的Region不考虑数据库中有没有ID为的数据如果有则替换原来的数据没有则插入（不知道这样说对不对大家指点一下）
RegionnwRegion =newRegion()
{
RegionID =
RegionDescription =Rainy};
dbRegionsInsertOnSubmit(nwRegion);
dbSubmitChanges();Update
说明更新操作先获取对象进行修改操作之后直接调用SubmitChanges()方法即可提交注意这里是在同一个DataContext中对于不同的DataContex看下面的讲解
简单形式
Customercust =
dbCustomersFirst(c => cCustomerID ==ALFKI);
custContactTitle =Vice President;
dbSubmitChanges();多个项
varq =frompindbProductswherepCategoryID == selectp;foreach(varpinq)
{
pUnitPrice += M;
}
dbSubmitChanges();Delete
简单形式
说明调用DeleteOnSubmit方法即可
OrderDetailorderDetail =
dbOrderDetailsFirst
(c => cOrderID == && cProductID == );
dbOrderDetailsDeleteOnSubmit(orderDetail);
dbSubmitChanges();一对多关系
说明Order与OrderDetail是一对多关系首先DeleteOnSubmit其OrderDetail(多端)其次DeleteOnSubmit其Order(一端)因为一端是主键
varorderDetails =fromoindbOrderDetailswhereoOrderCustomerID ==WARTH&&
oOrderEmployeeID == selecto;varorder =
(fromoindbOrderswhereoCustomerID ==WARTH&& oEmployeeID == selecto)First();foreach(OrderDetailodinorderDetails)
{
dbOrderDetailsDeleteOnSubmit(od);
}
dbOrdersDeleteOnSubmit(order);
dbSubmitChanges();Inferred Delete(推断删除)
说明Order与OrderDetail是一对多关系在上面的例子我们全部删除CustomerID为WARTH和EmployeeID为 的数据那么我们不须全部删除呢？例如Order的OrderID为的OrderDetail有很多但是我们只要删除ProductID为的OrderDetail这时就用Remove方法
Orderorder = dbOrdersFirst(x => xOrderID == );OrderDetailod =
orderOrderDetailsFirst(d => dProductID == );
orderOrderDetailsRemove(od);
dbSubmitChanges();Update with Attach
说明在对于在不同的DataContext之间使用Attach方法来更新数据例如在一个名为tempdb的NorthwindDataContext中查询出Customer和Order在另一个NorthwindDataContext中Customer的地址更新为 First AveOrder的CustomerID 更新为CHOPS
Customerc;List<Order> deserializedOrders =newList<Order>();CustomerdeserializedC;using(NorthwindDataContexttempdb =newNorthwindDataContext())
{
c = tempdbCustomersSingle(c => cCustomerID ==ALFKI);
deserializedC =newCustomer{
Address = cAddress
City = cCity
CompanyName = cCompanyName
ContactName = cContactName
ContactTitle = cContactTitle
Country = cCountry
CustomerID = cCustomerID
Fax = cFax
Phone = cPhone
PostalCode = cPostalCode
Region = cRegion
};Customertempcust =
tempdbCustomersSingle(c => cCustomerID ==ANTON);foreach(OrderointempcustOrders)
{
deserializedOrdersAdd(newOrder{
CustomerID = oCustomerID
EmployeeID = oEmployeeID
Freight = oFreight
rderDate = oOrderDate
rderID = oOrderID
RequiredDate = oRequiredDate
ShipAddress = oShipAddress
ShipCity = oShipCity
ShipName = oShipName
ShipCountry = oShipCountry
ShippedDate = oShippedDate
ShipPostalCode = oShipPostalCode
ShipRegion = oShipRegion
ShipVia = oShipVia
});
}
}using(NorthwindDataContextdb =newNorthwindDataContext())
{//对Customer更新不能写错dbCustomersAttach(deserializedC);
deserializedCAddress = First Ave;//对Order全部
