Transactions in LINQ To SQL

Question

I am using LINQ To SQL and calling stored procedures to insert the new rows.

1. Does the code below demonstrate an appropriate set of steps?
2. I want to insert (other fours) only if all four inserts are passed otherwise rollback all four inserts.

public bool InsertNewInquiry(Inquiry inq)
{
    using (var transaction = new TransactionScope())
    {
        using (DataContextDataContext dc = conn.GetContext())
        { 
            foreach (Officer po in inq.Recipients)
            {
                int results1 =  (int)dc.**spOffice_Insert**(po.Id,po.Name).ReturnValue;
            }
            foreach (Lookups tags in inq.Tags)
            {
                int results2 =  (int)dc.**spTags_Insert**(po.Id,po.Name).ReturnValue;
            }

            foreach (Phone phone in inq.Phone)
            {
                int results3 =  (int)dc.**spPhone_Insert**(po.Id,po.Name).ReturnValue;
            }

            int results4 =  (int)dc.spInquiry_Insert(
                            inq.Id
                            ,inq.StatusId
                            ,inq.PriorityId
                            ,inq.Subject).ReturnValue;                  


          if (results1 != 0 && results2 != 0 && results3 != 0 && results4 != 0)
          {
              transaction.Complete();
              return true;
          }
          return false;

           /* old code:
            transaction.Complete();    
            if (results == 0)
                return false;
            else
                return true;  */
        }
    }
}

I have tried the above code, but it does not insert the foreach insert (results1...results3)

Any idea how I can make this work as desired?

Answer 1

Check this post where TransactionScope and LinqToSQL are used together

Answer 2

TransactionScope is the way to go in my opinion. I had a similar question regard transactions in Linq To SQL a while ago. Although it is not the same issue, the responses I received should provide you with some useful information.

Based on what I can tell from your code your not accounting for the four return results before calling transaction.Complete();. The Complete() statement will commit you transaction so you want to make sure everything is correct before calling that similar to how you would use COMMIT in SQL.

Answer 3

Consider a solution where:

• change the scope of your results
• if a 0 result is a condition for any of your 3 foreach that you absolutely want to rollback for, don't increment as shown below. Simply check for 0, and break out of the foreach.

int res1=0, res2=0, res3=0, res4=0;
foreach (Officer po in inq.Recipients)
{
    res1 += (int)dc.**spOffice_Insert**(po.Id,po.Name).ReturnValue;
}
foreach (Lookups tags in inq.Tags)
{
    res2 += (int)dc.**spTags_Insert**(po.Id,po.Name).ReturnValue;
}
foreach (Phone phone in inq.Phone)
{
    res3 += (int)dc.**spPhone_Insert**(po.Id,po.Name).ReturnValue;
}
res4 += (int)dc.spInquiry_Insert(inq.Id,inq.StatusId,inq.PriorityId,inq.Subject)
                 .ReturnValue;                  

//no results are zero
if (res1 != 0 && res2 != 0 && res3 != 0 && res4 != 0)
{
    transaction.Complete();
    return true;
}
return false;