問題描述

我有一段代碼涉及多個插入，但需要在我完成插入其他表之前執(zhí)行 submitchanges 方法，以便我可以獲取 Id.我一直在網(wǎng)上搜索，但找不到如何在 linq to sql 中創(chuàng)建事務(wù).我已經(jīng)在代碼中我希望交易發(fā)生的地方添加了注釋.

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 是我的數(shù)據(jù)上下文

Forgot to mention that ydc is my datacontext

推薦答案

將整個事情包裹在 TransactionScope 中.在您要提交的位置調(diào)用 transaction.Complete().如果代碼在沒有調(diào)用 Complete() 的情況下退出塊，事務(wù)將被回滾.但是，在查看@s_ruchit 的答案并重新檢查您的代碼后，您可能可以將其重寫為不需要 TransactionScope.第一個示例將 TransactionScope 與您的代碼一起使用.第二個例子做了一些小改動，但實現(xiàn)了相同的目的.

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 的地方是當您從數(shù)據(jù)庫中讀取一個值并使用它為正在添加的對象設(shè)置新值時.在這種情況下，LINQ 事務(wù)不會覆蓋第一次讀取，只會覆蓋新值的稍后提交.由于您使用讀取的值來計算寫入的新值，因此您需要將讀取包裝在同一個事務(wù)中，以確保另一個讀取器不會計算相同的值并避免您的更改.在您的情況下，您只是在進行寫入，因此標準 LINQ 事務(wù)應(yīng)該可以工作.

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

這篇關(guān)于如何創(chuàng)建 LINQ to SQL 事務(wù)?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！