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全部
From:http://tw.wingwit.com/Article/program/net/201311/12904.html
