none
Sprocs, Linq and Transactions RRS feed

  • Question

  • I have to use stored procs for data changes because I'm using WCF with a web-based front end.

    I have:

    public void Method1(...)
    {
    MyDataContext myDataContext = new MyDataContext();

    myDataContext.UpdateCustomer(...);
    myDataContext.InsertOrder(...);
    }

    Question:  Do these two operations need to be wrapped in a DbTransaction, or will the data context automatically enroll them in the same one for me?  Said another way, If either procedure call fails, will the other operation roll back?

    I hope the latter, because I'm finding that LTS's behavior following the commit of a DbTransaction and close of the DataContext connection causes all sorts of nasty problems.  (So does not closing a connection.) 

    If one does the following:

    context.Connection.Open();

    tx = context.Connection.BeginTrans();

    (do some work here...)

    tx.Commit

    failure, tx.Rollback();

    context.Connection.Close()

    AND THEN follows up in a separte method with a call to context.Refresh() to get the updated timestamps, IDs, etc., you get an exception.  It's like the context is forever toast afterwards.  Unfortunately, the only code I've seen shows that one must open the context's Connection explicitly each time.

    <Sigh>

    I'm getting nostalgic for ADO.NET.  Things were so much more transparent...

    Thanks in advance,

    Ian
    Tuesday, October 28, 2008 6:43 PM