.net 如何创建 LINQ to SQL 事务？

How to create a LINQ to SQL Transaction?

提问by Drahcir

I have a piece of code that involves multiple inserts but need to execute submitchanges method before I finish inserting in other tables so that I can aquire an Id. I have been searching through the internet and couldnt find how to create a transaction in linq to sql. I have put comments in the code where I want the transaction to take place.

我有一段涉及多个插入的代码，但在我完成插入其他表之前需要执行 submitchanges 方法，以便我可以获取一个 Id。我一直在网上搜索，但找不到如何在 linq to sql 中创建事务。我已经在代码中我希望交易发生的地方添加了注释。

    var created = false;
    try
    {
        var newCharacter = new Character();
        newCharacter.characterName = chracterName;
        newCharacter.characterLevel = 1;
        newCharacter.characterExperience = 0;
        newCharacter.userUsername = userUsername;
        newCharacter.characterClassID = ccslst[0].characterClassID;
        //Open transaction


            ydc.Characters.InsertOnSubmit(newCharacter);
            ydc.SubmitChanges();

            foreach (var ccs in ccslst)
            {
                var cs = new CharacterStat();
                cs.statId = ccs.statID;                        
                cs.statValue = ccs.statValue;
                cs.characterID = newCharacter.characterID;
                ydc.CharacterStats.InsertOnSubmit(cs);
            }                    


            var ccblst = ydc.ClassBodies.Where(cb => cb.characterClassID == newCharacter.characterClassID);
            foreach (var ccb in ccblst)
            {
                var charBody = new CharacterBody();
                charBody.bodyId = ccb.bodyId;
                charBody.bodyPartId = ccb.bodyPartId;
                charBody.characterID = newCharacter.characterID;
                ydc.CharacterBodies.InsertOnSubmit(charBody);
            }
            ydc.SubmitChanges();      
            created = true;
        //Commit transaction
        }
        catch (Exception ex)
        {
            created = false;
            //transaction Rollback;                    
        }
        return created;

EDIT: Forgot to mention that ydc is my datacontext

编辑：忘了提到 ydc 是我的数据上下文

回答by tvanfosson

Wrap the whole thing in a TransactionScope. Call transaction.Complete()at the point where you want to commit. If the code exits the block without Complete()being called, the transaction will be rolled back. However, after looking at @s_ruchit's answer and re-examining your code, you could probably rewrite this to not require a TransactionScope. The first example uses the TransactionScopewith your code as is. The second example makes some minor changes, but accomplishes the same purpose.

将整个内容包装在一个TransactionScope. transaction.Complete()在您要提交的点调用。如果代码在没有Complete()被调用的情况下退出块，事务将被回滚。但是，在查看@s_ruchit 的答案并重新检查您的代码之后，您可能可以将其重写为不需要TransactionScope. 第一个示例TransactionScope按原样将与您的代码一起使用。第二个示例进行了一些小的更改，但实现了相同的目的。

A place where you would need to use the TransactionScopeis when you are reading a value from the database and using it to set a new value on an object being added. In this case the LINQ transaction won't cover the first read, just the later submit of the new value. Since you are using the value from the read to calculate a new value for the write, you need the read to be wrapped in the same transaction to ensure that another reader doesn't calculate the same value and obviate your change. In your case you are only doing writes so the standard LINQ transaction should work.

TransactionScope当您从数据库中读取值并使用它为正在添加的对象设置新值时，您需要使用is的地方。在这种情况下，LINQ 事务不会覆盖第一次读取，只会覆盖新值的稍后提交。由于您使用读取的值来计算写入的新值，因此您需要将读取包装在同一个事务中，以确保另一个读取器不会计算相同的值并避免您的更改。在您的情况下，您只是在进行写入，因此标准 LINQ 事务应该可以工作。

Example 1:

示例 1：

var created = false;

using (var transaction = new TransactionScope())
{
    try
    {
        var newCharacter = new Character();
        newCharacter.characterName = chracterName;
        newCharacter.characterLevel = 1;
        newCharacter.characterExperience = 0;
        newCharacter.userUsername = userUsername;
        newCharacter.characterClassID = ccslst[0].characterClassID;

        ydc.Characters.InsertOnSubmit(newCharacter);
        ydc.SubmitChanges();

        foreach (var ccs in ccslst)
        {
            var cs = new CharacterStat();
            cs.statId = ccs.statID;                        
            cs.statValue = ccs.statValue;
            cs.characterID = newCharacter.characterID;
            ydc.CharacterStats.InsertOnSubmit(cs);
        }                    

        var ccblst = ydc.ClassBodies.Where(cb => cb.characterClassID == newCharacter.characterClassID);
        foreach (var ccb in ccblst)
        {
            var charBody = new CharacterBody();
            charBody.bodyId = ccb.bodyId;
            charBody.bodyPartId = ccb.bodyPartId;
            charBody.characterID = newCharacter.characterID;
            ydc.CharacterBodies.InsertOnSubmit(charBody);
        }
        ydc.SubmitChanges();      
        created = true;

        transaction.Complete();
    }
    catch (Exception ex)
    {
        created = false;
    }
}
return created;

Example 2:

示例 2：

    try
    {
        var newCharacter = new Character();
        newCharacter.characterName = chracterName;
        newCharacter.characterLevel = 1;
        newCharacter.characterExperience = 0;
        newCharacter.userUsername = userUsername;
        newCharacter.characterClassID = ccslst[0].characterClassID;

        ydc.Characters.InsertOnSubmit(newCharacter);

        foreach (var ccs in ccslst)
        {
            var cs = new CharacterStat();
            cs.statId = ccs.statID;                        
            cs.statValue = ccs.statValue;
            newCharacter.CharacterStats.Add(cs);
        }                    

        var ccblst = ydc.ClassBodies.Where(cb => cb.characterClassID == newCharacter.characterClassID);
        foreach (var ccb in ccblst)
        {
            var charBody = new CharacterBody();
            charBody.bodyId = ccb.bodyId;
            charBody.bodyPartId = ccb.bodyPartId;
            newCharacter.CharacterBodies.Add(charBody);
        }
        ydc.SubmitChanges();      
        created = true;
    }
    catch (Exception ex)
    {
        created = false;
    }

回答by this. __curious_geek

You do not need to do explicit Transaction Implementation while using LINQ to SQL. All DB operations are wrapped in a transaction by default.

使用 LINQ to SQL 时不需要执行显式事务实现。默认情况下，所有数据库操作都包含在一个事务中。

Ex:

前任：

AppDataContext db = new AppDataContext();

<In memory operation 1 on db>
<In memory operation 2 on db>
<In memory operation 3 on db>
<In memory operation 4 on db>

db.SubmitChanges();

All operations between db DataContext initialization and db.SubmitChanges() are wrapped around a Database Transaction by .Net ensuring your database to be in consistent and with property integrity maintained across tables.

db DataContext 初始化和 db.SubmitChanges() 之间的所有操作都由 .Net 围绕数据库事务进行包装，以确保您的数据库保持一致并保持跨表的属性完整性。

Read an article By Scott Guthriehere:- http://weblogs.asp.net/scottgu/archive/2007/07/11/linq-to-sql-part-4-updating-our-database.aspx

在此处阅读Scott Guthrie的文章：- http://weblogs.asp.net/scottgu/archive/2007/07/11/linq-to-sql-part-4-updating-our-database.aspx