如何创建 LINQ to SQL 事务? [英] How to create a LINQ to SQL Transaction?

问题描述

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

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.

    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;

忘记提到 ydc 是我的数据上下文

Forgot to mention that ydc is my datacontext

推荐答案

将整个事情包裹在 TransactionScope 中.在您要提交的位置调用 transaction.Complete().如果代码在没有调用 Complete() 的情况下退出块，事务将被回滚.但是，在查看@s_ruchit 的答案并重新检查您的代码后，您可能可以将其重写为不需要 TransactionScope.第一个示例将 TransactionScope 与您的代码一起使用.第二个例子做了一些小改动，但实现了相同的目的.

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 TransactionScope with your code as is. The second example makes some minor changes, but accomplishes the same purpose.

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

A place where you would need to use the TransactionScope is 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.

示例 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;

示例 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;
    }

这篇关于如何创建 LINQ to SQL 事务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！