1

Linq2Sql does not support table-valued parameters for stored procedures (1, 2).

Because of this, I'm adding an ugly support for table-valued parameters in my DataContext (just to keep things consistent and still be able to call my procedures from within the same data context).

I call this.Connection.CreateCommand(), cast it to SqlCommand (because it's going to be SqlCommand anyway to support the TVP), manually list all parameters including the SqlDbType.Structured ones, and ExecuteNonQuery it.

This works fine.
But.
Inside my helper method I have to open the context connection because it may be closed. Because I manually open connection, do I have to manually close it?

I've been through these:
Do I have to close the SQL Connection manually if I use Linq?
When should I dispose of a data context
When the connection to database starts if i use DataContext?

but they don't actually answer my question. I understand the connection is closed when result are enumerated, but it's more complex than that. Calling a method is not a read-only operation, as opposed to enumerating results.
Besides, if there's an ambient transaction, DataContext will definitely not close the connection after the first method call:

using (var ambient_tran = new System.Transactions.TransactionScope())
{
    using (var dx = new SomeDataContext()) 
    {
        dx.CallMethodOne(foo, bar);
        dx.CallMethodTwo(bar, baz); // executed on same connection and same transaction,
                                    // can see uncommitted data entered by CallMethodOne
    }

    ambient_tran.Complete();
};

Because of all this, I'm not sure what is the right thing to do in regard of the connection.
Am I just to open connection and leave DataContext manage its lifetime, or is there a pattern to follow?

EDIT:

Experiments show that if I just leave it open, it behaves fine and automatically respects an ambient transaction, if any. However, I would prefer a solid advice on that.

Community
  • 1
  • 1
GSerg
  • 76,472
  • 17
  • 159
  • 346

1 Answers1

-1

If you do not need distributed transactions, then avoid using System.Trasactions. Instead, see my TextMetal project, the sample code, and pay attention to the Plumbing namespace. If you have questions, email me. No MSDTC slight of hand, just proper generated ADO.NET/LINQ to SQL, a unit of work pattern, and some well engineered data access code!

If there is a compelling reason to use System.Transactions, then I advise you still close the connection as soon as possible as the transaction coordinator will manage the commit/rollback after the connection is released to the pool.

Daniel P. Bullington
  • 643
  • 5
  • 10