select
* from products where categoryid=1
|
var query = from p in ctx.Products where p.CategoryID == 1 select p;
foreach (var p in query)
p.UnitsInStock = Convert.ToInt16(p.UnitsInStock - 1);
ctx.SubmitChanges(); //
在这里设断点
|
update
products
set
unitsinstock = unitsinstock -2, unitprice= unitprice + 1
where
categoryid = 1
|
[Column(Storage="_UnitsInStock", DbType="SmallInt", UpdateCheck = UpdateCheck.Never)]
[Column(Storage="_UnitPrice", DbType="Money", UpdateCheck = UpdateCheck.Never)]
|
var query = from p in ctx.Products where p.CategoryID == 1 select p;
foreach (var p in query)
p.UnitsInStock = Convert.ToInt16(p.UnitsInStock - 1);
try
{
ctx.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException)
{
foreach (ObjectChangeConflict cc in ctx.ChangeConflicts)
{
Product p = (Product)cc.Object;
Response.Write(p.ProductID + "<br/>");
cc.Resolve(RefreshMode.OverwriteCurrentValues); //
放弃当前更新,所有更新以原先更新为准
}
}
ctx.SubmitChanges();
|
cc.Resolve(RefreshMode.KeepCurrentValues); //
放弃原先更新,所有更新以当前更新为准
|
cc.Resolve(RefreshMode.KeepChanges); //
原先更新有效,冲突字段以当前更新为准
|
foreach
(ObjectChangeConflict cc in ctx.ChangeConflicts)
{
Product p = (Product)cc.Object;
foreach (MemberChangeConflict mc in cc.MemberConflicts)
{
string currVal = mc.CurrentValue.ToString();
string origVal = mc.OriginalValue.ToString();
string databaseVal = mc.DatabaseValue.ToString();
MemberInfo mi = mc.Member;
string memberName = mi.Name;
Response.Write(p.ProductID + " " + mi.Name + " " + currVal + " " + origVal +" "+ databaseVal + "<br/>");
if (memberName == "UnitsInStock")
mc.Resolve(RefreshMode.KeepCurrentValues); //
放弃原先更新,所有更新以当前更新为准
else if (memberName == "UnitPrice")
mc.Resolve(RefreshMode.OverwriteCurrentValues); //
放弃当前更新,所有更新以原先更新为准
else
mc.Resolve(RefreshMode.KeepChanges); //
原先更新有效,冲突字段以当前更新为准
}
}
|
ctx.SubmitChanges(ConflictMode.FailOnFirstConflict);
|
ctx.Customers.Add(new Customer { CustomerID = "abcdf", CompanyName = "zhuye" });
ctx.Customers.Add(new Customer { CustomerID = "abcde", CompanyName = "zhuye" });
ctx.SubmitChanges();
|
if (ctx.Connection != null) ctx.Connection.Open();
DbTransaction tran = ctx.Connection.BeginTransaction();
ctx.Transaction = tran;
try
{
CreateCustomer(new Customer { CustomerID = "abcdf", CompanyName = "zhuye" });
CreateCustomer(new Customer { CustomerID = "abcde", CompanyName = "zhuye" });
tran.Commit();
}
catch
{
tran.Rollback();
}
private void CreateCustomer(Customer c)
{
ctx.Customers.Add(c);
ctx.SubmitChanges();
}
|
using (TransactionScope scope = new TransactionScope())
{
CreateCustomer(new Customer { CustomerID = "abcdf", CompanyName = "zhuye" });
CreateCustomer(new Customer { CustomerID = "abcde", CompanyName = "zhuye" });
scope.Complete();
}
|
事务是一个原子的工作单位,必须完整的完成单位里的所有工作,要么全部执行,要么全部都不执行。如果提交事务,则事务执行成功;如果回滚事务,则事务执行失败。 事务具备4个基本特性--ACID(原子性、一致性、孤立性和持久性)。
在Linq to SQL中,有三种方法创建事务:
- 如果没有指定任何事务,那么当调用SubmitChanges方法时,DataContext会默认创建一个事务。
- 使用TransactionScope创建轻量级事务
- 给DataContext的Transaction属性指定事务
下面我用代码分别来说明这几种创建事务的方法,以Northwind数据库为例,先来看看直接使用SubmitChanges:
NorthwindDataContext ctx = new NorthwindDataContext(); Customer c1 = new Customer { CustomerID = "TESTA", CompanyName = "testa's company" }; Customer c2 = new Customer { CustomerID = "TESTBC", CompanyName = "testb's company" }; ctx.Customers.Add(c1); ctx.Customers.Add(c2); ctx.SubmitChanges();
上面这段代码中,先创建了两个Customer对象然后添加到DataContext里面,其中的c2的CustomerID赋值为"TESTBC",长度为六个字符,而数据库中该字段约束为5个字符长度,这样在SubmitChanges的时候应该会有异常抛出。果然在执行的时候抛出了SqlException,提示字符将被截断。
再通过Sql Server管理器可以看到上面这两条数据都没有被插入到数据库中。通过Reflector可以发现在SubmitChanges的时候,Linq to SQL默认创建了一个孤立级别为Read Committed的事务(它表示已提交的更新在事务间是可见的,具体有哪些孤立级别可以参考ADO.NET相关资料):
public virtual void SubmitChanges(ConflictMode failureMode) { ... transaction = this.provider.Connection.BeginTransaction(IsolationLevel.ReadCommitted); this.provider.Transaction = transaction; }
如果不想使用默认的事务设置,比如想改变事务的孤立级别,我们可以给DataContext的Transaction属性赋值,以此使用自定义的事务。
ctx.Transaction = ctx.Connection.BeginTransaction(System.Data.IsolationLevel.Serializable); try { ctx.SubmitChanges(); ctx.Transaction.Commit(); } catch { ctx.Transaction.Rollback(); throw; } finally { ctx.Transaction = null; }
最后一种方式是通过TransactionScope创建轻量级事务,就像在ADO.NET中使用一样:
using (TransactionScope scope = new TransactionScope()) { ctx.SubmitChanges(); scope.Complete(); }
上面的例子看起来似乎多此一举,因为在SubmitChanges中会创建默认的事务,但是改成下面这样,就只能使用自定义的事务了:
using (TransactionScope scope = new TransactionScope()) { ctx.ExecuteCommand("exec ...."); ctx.ExecuteCommand("exec ...."); ctx.ExecuteCommand("exec ...."); ctx.SubmitChanges(); scope.Complete(); }
不管ExecuteCommand里面执行了哪些操作,我们都能够指明这些行为和SubmitChanges处于同一个事务中。