views:

7387

answers:

3

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;

EDIT: Forgot to mention that ydc is my datacontext

+11  A: 

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.

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.

Example 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:

    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;
    }
tvanfosson
Not sure about the syntax , do I have put my code in this scope : using (TransactionScope ts = new TransactionScope()) {//my code}
Drahcir
I added it to your code and pasted as an example.
tvanfosson
It worked, thanks
Drahcir
You will Require DTC service to run on the deployment machine to perform transactions using TransactionScope. Keep that in considertation. DTC: Distributed Transaction Co-ordinator Service.
this. __curious_geek
@s_ruchit -- actually I'm not sure that with LINQ it will get promoted to a distributed transaction. Since the data context uses the same connection for all the submits I think it will stay local. This is different than when using TableAdapters.
tvanfosson
@tvanfosson -- If you are using TransactionScope, you will surely require DTC service but in case of LinqToSql it performs all operations on Single Connection so it does not depend on DTC. I prefer TransactionScope on 2nd priority since it has dependancy on DTC.
this. __curious_geek
@s_ruchit -- I'm pretty sure that it won't promote to DTC unless there is more than one connection involved. I spent a lot of time debugging a TableAdapter application that used TransactionScope and would only fail under certain conditions. We eventually determined that it was a firewall issue...
tvanfosson
...and that DTC needed to be opened when more than one table adapter was involved (there was conditional logic inside the scope). When only one table adapter was involved it didn't promote and we didn't see the problems. The worst part was we didn't discover until we went to production...
tvanfosson
...because only the production server was firewalled from my web server.
tvanfosson
+3  A: 

No need to Explicit Transaction Imeplmentation while using LINQ to SQL. All DB operations are transacted by default.

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 propery integrity maintained across tables.

Read an article By Scott Guthrie here.

LinkText: http://weblogs.asp.net/scottgu/archive/2007/07/11/linq-to-sql-part-4-updating-our-database.aspx

Hope this helps..

Ruchit S.

this. __curious_geek
But needed an ID that was generated on the first submit changes, didn't know otherwise how to aquire the id.
Drahcir
Visit the article written by ScottGu. You can associate without the ID. That's the beauty of LINQ-to-SQL. If you're not doind that way, you're not leveraging the Linq-To-Sql. I recommend you to read ScottGu's article. You can find the link in my Answer.
this. __curious_geek
Yes. It looks like you could rewrite this to not require an outer transaction. I'll update my answer.
tvanfosson
A: 

I had a chance to visit this link..provides a good explanation of how to perform transaction in LINQ to SQL...

http://www.a2zmenu.com/LINQ/Maintain%20Transaction%20in%20LINQ%20to%20SQL.aspx

emenu