LINQ to SQL语句之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 = "[phone]",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 customer-facing
                            analogues to sprockets and cogs." };
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