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

问题描述

我有一张code，涉及多个插入，但需要执行的SubmitChanges方法之前，我完成插入其他表中，这样我可以AQUIRE的ID。我一直在寻找通过互联网和无法找到如何在LINQ创建一个事务SQL。我已经在我想要的交易发生在code将意见。

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是我的DataContext

Forgot to mention that ydc is my datacontext

推荐答案

包裹了整个事情的一个TransactionScope。呼叫transaction.Complete（），在您要提交的地步。如果code退出没有完成（）被调用，该交易将被回滚块。然而，看着@ s_ruchit的答案，并重新审视你的code，你很可能改写这个不要求一个TransactionScope之后。第一个例子使用的TransactionScope你的code原样。第二个例子做了​​一些小的改动，但完成同样的目的。

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屋！