【笔记】【LINQ编程技术内幕】第十六章 更新匿名关系型数据

插入数据

class Program
{
	static void Main(string[] args)
	{
		Northwind northwind = new Northwind();
		Table<Customer> customers = northwind.GetTable<Customer>();
		
		Customer customer = new Customer();
		customer.CustomerID = "DUSTY";
		customer.CompanyName = "Dusty's Cellars";
		customer.ContactName = "Dena Swanson";
		customer.ContactTitle = "Catering Manager";
		customer.Address = "1839 Grand River Avenue";
		customer.City = "Okenos";
		customer.Region = "MI";
		customer.PostalCode = "48864";
		customer.Country = "US";
		customer.Phone = "(517)349-5150";
		customer.Fax = "(517)349-8416";
		
		// 插入到内存数据集
		customers.InsertOnSubmit(customer);
		
		// 更新到数据库
		northwind.SubmitChanges();
	}
}

public class Northwind : DataContext
{
	private static readonly string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=True;";

	public Northwind() : base(connectionString)
	{
		Log = Console.Out;
	}
}

[Table(Name="Customers")]
  public class Customer
  {
    [Column(IsPrimaryKey=true)]
    public string CustomerID{get;set;}

    [Column()]
    public string CompanyName{get;set;}

	[Column()]
	public string ContactName{get;set;}

	[Column()]
	public string ContactTitle{get;set;}

	[Column()]
	public string Address{get;set;}

	[Column()]
	public string City{get;set;}

	[Column()]
	public string Region{get;set;}

	[Column()]
	public string PostalCode{get;set;}

	[Column()]
	public string Country{get;set;}

	[Column()]
	public string Phone{get;set;}

	[Column()]
	public string Fax{get;set;}
}

这里实际上有些浪费资源，因为GetTable将返回Customers数据，而当你直线进行插入操作的时候，这些数据其实一掉用处也没有。你也可以选择使用DataContext.ExecuteCommand，并将存储过程的名字及其参数传递给ExecuteCommand.

删除数据

class Program
{
	static void Main(string[] args)
	{
		Northwind northwind = new Northwind();
		Table<Customer> customers = northwind.GetTable<Customer>();
		
		try
		{
			// 获取需要删除的数据
			var remove = customers.Single(cust => cust.CustomerID == "DUSTY");
			// 从列表中删除数据
			customers.DeleteOnSubmit(remove);
			// 更新到数据库
			northwind.SubmitChanges();
		}
		catch
		{
			Console.WriteLine("Nothing to do.");
		}
	}
}

public class Northwind : DataContext
{
	private static readonly string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=True;";

	public Northwind() : base(connectionString)
	{
		Log = Console.Out;
	}
}

[Table(Name="Customers")]
  public class Customer
  {
    [Column(IsPrimaryKey=true)]
    public string CustomerID{get;set;}

    [Column()]
    public string CompanyName{get;set;}

	[Column()]
	public string ContactName{get;set;}

	[Column()]
	public string ContactTitle{get;set;}

	[Column()]
	public string Address{get;set;}

	[Column()]
	public string City{get;set;}

	[Column()]
	public string Region{get;set;}

	[Column()]
	public string PostalCode{get;set;}

	[Column()]
	public string Country{get;set;}

	[Column()]
	public string Phone{get;set;}

	[Column()]
	public string Fax{get;set;}
}

更新数据

class Program
{
	static void Main(string[] args)
	{
		Northwind northwind = new Northwind();
		Table<Customer> customers = northwind.GetTable<Customer>();
		
		try
		{
			// 获取需要更新的数据
			var changeOne = customers.Single(cust => cust.CustomerID == "ALFKI");
			// 修改数据
			changeOne.ContactName = "Joe Swanson";
			// 更新到数据库
			northwind.SubmitChanges();
		}
		catch
		{
			Console.WriteLine("Nothing to do.");
		}
	}
}

public class Northwind : DataContext
{
	private static readonly string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=True;";

	public Northwind() : base(connectionString)
	{
		Log = Console.Out;
	}
}

[Table(Name="Customers")]
  public class Customer
  {
    [Column(IsPrimaryKey=true)]
    public string CustomerID{get;set;}

    [Column()]
    public string CompanyName{get;set;}

	[Column()]
	public string ContactName{get;set;}

	[Column()]
	public string ContactTitle{get;set;}

	[Column()]
	public string Address{get;set;}

	[Column()]
	public string City{get;set;}

	[Column()]
	public string Region{get;set;}

	[Column()]
	public string PostalCode{get;set;}

	[Column()]
	public string Country{get;set;}

	[Column()]
	public string Phone{get;set;}

	[Column()]
	public string Fax{get;set;}
}

存储过程

在执行LINQ的过程中，底层自动生成了动态查询。可以用存储过程来替换这些默认行为，只需要在你的自定义数据上下文中提供Insert、Update和Delete方法即可。例如，为了使用你自己的存储过程替换默认的自动生成的SQL语句，应该按照以下步骤进行操作：

• 定义一个存储过程或直接使用一个现有的
• 在自定义的DataContext类中实现一个私有方法UpdateCustomer，它可以接收一个Customer对象
• 在自定义的DataContext类中实现一个共有方法UpdateCustomer，其参数未执行更新时所需的那些参数
• 在第一个UpdateCustomer中调用第二个UpdateCustomer
• 给哪个复杂点的UpdateCustomer方法添加上FunctionAttribute和Name参数(指示使用哪个存储过程)
• 给每个参数加上ParameterAttribute，并定义参数名和DbType

创建存储过程

CREATE PROCEDURE dbo.UpdateCustomer(
		@CustomerID nchar(5),
		@CompanyName NVarChar(40),
		@ContactName NVarChar(30),
		@ContactTitle NVarChar(30),
		@Address NVarChar(60),
		@City NVarChar(15),
		@Region NVarChar(15),
		@PostalCode NVarChar(20),
		@Country NVarChar(15),
		@Phone NVarChar(24),
		@Fax NVarChar(24)
	)
AS
BEGIN
	UPDATE Customers
	SET CompanyName = @CompanyName,
	ContactName = @ContactName,
	ContactTitle = @ContactTitle,
	Address = @Address,
	City = @City,
	Region = @Region,
	PostalCode = @PostalCode,
	Country = @Country,
	Phone = @Phone,
	Fax = @Fax
	WHERE CustomerID = @CustomerID
	
	RETURN @@ROWCOUNT
END
GO

class Program
{
	static void Main(string[] args)
	{
		Northwind northwind = new Northwind();
		Table<Customer> customers = northwind.GetTable<Customer>();
		
		try
		{
			// 获取需要更新的数据
			var changeOne = customers.Single(cust => cust.CustomerID == "ALFKI");
			// 修改数据
			changeOne.ContactName = "Joe Swanson1";
			// 更新到数据库
			northwind.SubmitChanges();
		}
		catch(Exception ex)
		{
			Console.WriteLine("Nothing to do.");
			Console.WriteLine(ex.Message);
		}
	}
}

public class Northwind : DataContext
{
	private static readonly string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=True;";

	public Northwind() : base(connectionString)
	{
		Log = Console.Out;
	}
	
	private void UpdateCustomer(Customer obj)
	{
		this.UpdateCunstomer(obj.CustomerID,
		                     obj.CompanyName,
							 obj.ContactName,
							 obj.ContactTitle,
							 obj.Address,
							 obj.City,
							 obj.Region,
							 obj.PostalCode,
							 obj.Country,
							 obj.Phone,
							 obj.Fax);
	}

	[Function(Name = "dbo.UpdateCustomer")]
	public int UpdateCunstomer(
		[Parameter(Name = "CustomerID",   DbType = "NChar(5)")    ] string customerID,
		[Parameter(Name = "CompanyName",  DbType = "NVarChar(40)")] string companyName,
		[Parameter(Name = "ContactName",  DbType = "NVarChar(30)")] string contactName,
		[Parameter(Name = "ContactTitle", DbType = "NVarChar(30)")] string contactTitle,
		[Parameter(Name = "Address",      DbType = "NVarChar(60)")] string address,
		[Parameter(Name = "City",         DbType = "NVarChar(15)")] string city,
		[Parameter(Name = "Region",       DbType = "NVarChar(15)")] string region,
		[Parameter(Name = "PostalCode",   DbType = "NVarChar(20)")] string postalCode,
		[Parameter(Name = "Country",      DbType = "NVarChar(15)")] string country,
		[Parameter(Name = "Phone",        DbType = "NVarChar(24)")] string phone,
		[Parameter(Name = "Fax",          DbType = "NVarChar(24)")] string fax)
	{
		IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), 
		                                               customerID,
													   companyName,
													   contactName,
													   contactTitle,
													   address,
													   city,
													   region,
													   postalCode,
													   country,
													   phone,
													   fax);
		return ((int)(result.ReturnValue));
	}
}

[Table(Name="Customers")]
  public class Customer
  {
    [Column(IsPrimaryKey=true)]
    public string CustomerID{get;set;}

    [Column()]
    public string CompanyName{get;set;}

	[Column()]
	public string ContactName{get;set;}

	[Column()]
	public string ContactTitle{get;set;}

	[Column()]
	public string Address{get;set;}

	[Column()]
	public string City{get;set;}

	[Column()]
	public string Region{get;set;}

	[Column()]
	public string PostalCode{get;set;}

	[Column()]
	public string Country{get;set;}

	[Column()]
	public string Phone{get;set;}

	[Column()]
	public string Fax{get;set;}
}

事务

class Program
{
	static void Main(string[] args)
	{
		Northwind northwind = new Northwind();
		Table<Customer> customers = northwind.GetTable<Customer>();
		Table<Order> orders = northwind.GetTable<Order>();
		
		using (TransactionScope scope = new TransactionScope())
		{
			var removeCustomers = from customer in customers
			                      where customer.CustomerID.ToString().ToUpper() == "PAUL1"
								  select customer;
			var removeOrders = from order in orders
			                   where order.CustomerID.ToString().ToUpper() == "PAUL1"
							   select order;
			if (removeOrders.Any())
			{
				orders.DeleteAllOnSubmit(removeOrders);
				northwind.SubmitChanges();
			}
			customers.DeleteAllOnSubmit(removeCustomers);
			northwind.SubmitChanges();
			scope.Complete();
		}
	}
}

public class Northwind : DataContext
{
	private static readonly string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=True;";

	public Northwind() : base(connectionString)
	{
		Log = Console.Out;
	}
}

[Table(Name="Customers")]
  public class Customer
  {
    [Column(IsPrimaryKey=true)]
    public string CustomerID{get;set;}
}

[Table(Name = "Orders")]
public class Order
{
	[Column(IsPrimaryKey = true)]
	public string OrderID { get; set; }

	[Column()]
	public string CustomerID { get; set; }
}

冲突

首先需要熟悉一些术语，包括并发、并发冲突、并发控制、乐观并发、悲观并发以及冲突 解决。并发是指同时更新；并发控制是指用于接矩并发问题的技术；乐观并发语序可能发生冲突是指的并发修改了悲观并发使用锁来避免存在冲突的并发修改；冲突解决是解决冲突的处理过程。
显然，悲观记录锁机制能够轻松解决这个问题，因为它直接阻止了并发。悲观并发的问题是，它的可扩展性不太好。记录锁是很昂贵的，因为它将保持连接。在拥有大量用户的Internet领域，锁住记录将导致可扩展性变得很差。
这样的话，只好使用乐观并发。这种发方允许同时发生的修改操作，当冲突发生时再去解决它。LINQ支持乐观并发。
LINQ时通过异常机制来支持并发的。LINQ支持映射，而这些映射可以通过ColumnAttribute的UpdateCheck参数来标记哪些需要在更新时进行并发冲突检测。当两个用于以冲突的方式修改相同的数据时，LINQ将抛出一个异常，然后由程序员来指出要图和解决这些冲突数据。解决冲突的常用办法是，将两个版本的数据都提供给用户，然后由用户来决定保留哪一个。

为SubmitChanges指明冲突处理模式

SubmitChanges方法有一个ConflictMode参数。ConflictMode是一个枚举，可以是FailOnFirstConflict或ContinueOnConflict。如果该参数为FailOnFirstConflict，当两个指出现了冲突时，更新将立即停止。如果该参数为ContinueOnConflict，所有冲突将会被累积，当整个更新过程结束时才返回全部的冲突，更新操作也同时停止。
无论是否制定了ConflictMode枚举，发生并发冲突时都会抛出一个ChangeConflictException。

public class Northwind : DataContext
{
	private static readonly string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=True;";

	public Northwind() : base(connectionString)
	{
		Log = Console.Out;
	}
	
	public Table<Shipper> Shippers
	{
		get {return this.GetTable<Shipper>();}
	}
}

[Table(Name="Shippers")]
public class Shipper
{
    [Column(IsPrimaryKey=true)]
    public int ShipperID{get;set;}

	[Column()]
	public string CompanyName { get; set; }

	[Column()]
	public string Phone { get; set; }
}

class Program
{
	static void Main(string[] args)
	{
		Northwind northwind = new Northwind();
        var one = northwind.Shippers.Single(r => r.ShipperID == 1);
        one.CompanyName = "test";
        northwind.SubmitChanges();
        
		BackgroundWorker worker1 = new BackgroundWorker();
		worker1.DoWork += new DoWorkEventHandler(worker1_DoWork);

		BackgroundWorker worker2 = new BackgroundWorker();
		worker2.DoWork += new DoWorkEventHandler(worker1_DoWork);

		worker1.RunWorkerAsync("test2");
		worker2.RunWorkerAsync("test3");
	}

	static void worker1_DoWork(object sender, DoWorkEventArgs e)
	{
		Northwind northwind = new Northwind();
		var one = northwind.Shippers.Single(r => r.ShipperID == 1);
		one.CompanyName = (string)e.Argument;

		northwind.SubmitChanges(ConflictMode.FailOnFirstConflict);
	}
}

捕获并解决并发冲突

单用户引用程序或哪些机会不会出现多个用户同时更新相同记录的引用程序可以使用悲观并发。只需要简单低认为“最后更新记录的用户才拥有正确的信息”即可。如果多个用户都要更新记录，那么你就需要通过捕获ChangeConflictException来处理冲突。注意，有些列在更新时可能无需冲突检测。

忽略某些列上的冲突检测
ColumnAttribute支持一个命名参数UpdateCheck。如果使用了UpdateCheck，则LINQ将根据该值来判断在冲突检测时如何对待这个列。UpdateCheck的值由Always、Never和WhenChange。

[Table(Name="Shippers")]
public class Shipper
{
    [Column(IsPrimaryKey=true)]
    public int ShipperID{get;set;}
	
	/// <summary>只有在它被修改之后才会被检测是否出现了冲突</summary>
	[Column(UpdateCheck = UpdateCheck.WhenChanged)]
	public string CompanyName { get; set; }

	/// <summary>永远不会被检测</summary>
	[Column(UpdateCheck = UpdateCheck.Never)]
	public string Phone { get; set; }
}

获取冲突的信息

static void worker1_DoWork(object sender, DoWorkEventArgs e)
{
	Northwind northwind = new Northwind();
	var one = northwind.Shippers.Single(r => r.ShipperID == 1);
	// 需要确保当前数据库中的该条数据的CompanyName既不等于Speedy Distressed，也不等于Speedy Xpress
	// 否则不会引发异常
	one.CompanyName = (string)e.Argument;

	System.Threading.Thread.Sleep(2500);
	try
	{
		northwind.SubmitChanges(ConflictMode.FailOnFirstConflict);
	}
	catch (ChangeConflictException ex)
	{
		Console.WriteLine("Error : " + ex.Message);
	}
}

确定与冲突相关的实体和表
和冲突有关的两个元素是：表和实体推向。该信息由DataContext.ChangeConflicits集合以及DataContext。Mapping集合得到的。

static void worker1_DoWork(object sender, DoWorkEventArgs e)
	{
		Northwind northwind = new Northwind();
		var one = northwind.Shippers.Single(r => r.ShipperID == 1);
		one.CompanyName = (string)e.Argument;

		System.Threading.Thread.Sleep(2500);
		try
		{
			northwind.SubmitChanges(ConflictMode.FailOnFirstConflict);
		}
		catch (ChangeConflictException ex)
		{
			Console.WriteLine("Error : " + ex.Message);

			foreach (ObjectChangeConflict conflict in northwind.ChangeConflicts)
			{
				MetaTable meta = northwind.Mapping.GetTable(conflict.Object.GetType());
				Console.WriteLine("Table  : {0}", meta.TableName);
				Console.WriteLine("Object : {0}", conflict.Object);
			}
		}
	}

比较成员冲突的各个状态

static void worker1_DoWork(object sender, DoWorkEventArgs e)
{
	Northwind northwind = new Northwind();
	var one = northwind.Shippers.Single(r => r.ShipperID == 1);
	one.CompanyName = (string)e.Argument;

	System.Threading.Thread.Sleep(2500);
	try
	{
		northwind.SubmitChanges(ConflictMode.FailOnFirstConflict);
	}
	catch (ChangeConflictException ex)
	{
		Console.WriteLine("Error : " + ex.Message);

		foreach (ObjectChangeConflict conflict in northwind.ChangeConflicts)
		{
			MetaTable meta = northwind.Mapping.GetTable(conflict.Object.GetType());
			Console.WriteLine("Table  : {0}", meta.TableName);
			Console.WriteLine("Object : {0}", conflict.Object);

			foreach (MemberChangeConflict member in conflict.MemberConflicts)
			{
				var current = member.CurrentValue;
				var original = member.OriginalValue;
				var database = member.DatabaseValue;

				Console.WriteLine("Current  : {0}", current);
				Console.WriteLine("Original : {0}", original);
				Console.WriteLine("Database : {0}", database);
			}
		}
	}
}

将解决过的冲突写入数据库
冲突解决的最后一个问题就是指出要保留那个纸并用正确的值处理受影响的元素。ObjectChangeConflict.Resolve方法可以完成这个任务。
System.Data.Lingq.RefreshMode枚举共有三个可选项：OverwriteCurrentValues、KeepCurrentValues以及KeepChanges。OverwriteCurrentValues使用数据库中的值重写对象状态。KeepCurrentValues使用映射对象的状态去更新数据库中的值。KeepChanges将合并数据库值和对象值。

static void worker1_DoWork(object sender, DoWorkEventArgs e)
{
	Northwind northwind = new Northwind();
	var one = northwind.Shippers.Single(r => r.ShipperID == 1);
	one.CompanyName = (string)e.Argument;

	System.Threading.Thread.Sleep(2500);
	try
	{
		northwind.SubmitChanges(ConflictMode.FailOnFirstConflict);
	}
	catch (ChangeConflictException ex)
	{
		Console.WriteLine("Error : " + ex.Message);

		foreach (ObjectChangeConflict conflict in northwind.ChangeConflicts)
		{
			MetaTable meta = northwind.Mapping.GetTable(conflict.Object.GetType());
			Console.WriteLine("Table  : {0}", meta.TableName);
			Console.WriteLine("Object : {0}", conflict.Object);

			foreach (MemberChangeConflict member in conflict.MemberConflicts)
			{
				var current = member.CurrentValue;
				var original = member.OriginalValue;
				var database = member.DatabaseValue;

				Console.WriteLine("Current  : {0}", current);
				Console.WriteLine("Original : {0}", original);
				Console.WriteLine("Database : {0}", database);

				string input = Console.ReadLine();
				int value = Convert.ToInt32(input);
				switch (value)
				{
					case 1:
						conflict.Resolve(RefreshMode.KeepCurrentValues);
						break;
					case 2:
						conflict.Resolve(RefreshMode.OverwriteCurrentValues);
						break;
					case 3:
						conflict.Resolve(RefreshMode.KeepChanges);
						break;
					default:
						throw;
				}
			}
		}
	}

	northwind.SubmitChanges(ConflictMode.FailOnFirstConflict);
}