熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> .NET編程 >> 正文

LINQ to SQL語句之Insert/Update/Delete操作

2013-11-13 10:12:32  來源: .NET編程 

  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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.