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

這篇我們來討論Insert/Update/Delete操作。這個在我們的程式中最為常用了。我們直接看例子。

Insert/Update/Delete操作

Insert

1.簡單形式

說明：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();

2.一對多關係

說明：Category與Product是一對多的關係，提交Category(一端)的資料時，LINQ to SQL會自動將Product(多端)的資料一起提交。

var newCategory = new Category
{
    CategoryName = "Widgets",
    Description = "Widgets are the ……"
};
var newProduct = new Product
{
    ProductName = "Blue Widget",
    UnitPrice = 34.56M,
    Category = newCategory
};
db.Categories.InsertOnSubmit(newCategory);
db.SubmitChanges();

3.多對多關係

說明：在多對多關係中，我們需要依次提交。

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();

4.Override using Dynamic CUD

說明：CUD就是Create、Update、Delete的縮寫。下面的例子就是新建一個ID(主鍵)為32的Region，不考慮資料庫中有沒有ID為32的資料，如果有則替換原來的資料，沒有則插入。（不知道這樣說對不對。大家指點一下）

Region nwRegion = new Region()
{
    RegionID = 32,
    RegionDescription = "Rainy"
};
db.Regions.InsertOnSubmit(nwRegion);
db.SubmitChanges();

Update

說明：更新操作，先獲取物件，進行修改操作之後，直接呼叫SubmitChanges()方法即可提交。注意，這裡是在同一個DataContext中，對於不同的DataContex看下面的講解。

1.簡單形式

Customer cust =
    db.Customers.First(c => c.CustomerID == "ALFKI");
cust.ContactTitle = "Vice President";
db.SubmitChanges();

2.多個項

var q = from p in db.Products
        where p.CategoryID == 1
        select p;
foreach (var p in q)
{
    p.UnitPrice += 1.00M;
}
db.SubmitChanges();

Delete

1.簡單形式

說明：呼叫DeleteOnSubmit方法即可。

OrderDetail orderDetail =
    db.OrderDetails.First
    (c => c.OrderID == 10255 && c.ProductID == 36);
db.OrderDetails.DeleteOnSubmit(orderDetail);
db.SubmitChanges();

2.一對多關係

說明：Order與OrderDetail是一對多關係，首先DeleteOnSubmit其OrderDetail(多端)，其次DeleteOnSubmit其Order(一端)。因為一端是主鍵。

var orderDetails =
    from o in db.OrderDetails
    where o.Order.CustomerID == "WARTH" &&
    o.Order.EmployeeID == 3
    select o;
var order =
    (from o in db.Orders
     where o.CustomerID == "WARTH" && o.EmployeeID == 3
     select o).First();
foreach (OrderDetail od in orderDetails)
{
    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方法。

Order order = db.Orders.First(x => x.OrderID == 10248);
OrderDetail od = 
    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。

Customer c1;
List<Order> deserializedOrders = new List<Order>();
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,
            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 (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();
}

Update and Delete with Attach

說明：在不同的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();
}

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12639172/viewspace-364777/，如需轉載，請註明出處，否則將追究法律責任。