I have a parent object (part of a DAL) that contains, amongst other things, a collection (List<t>
) of child objects.
When I'm saving the object back to the DB, I enter/update the parent, and then loop through each child. For maintainability, I've put all the code for the child into a separate private method.
I was going to use standard ADO Transactions, but on my travels, I stumbled across the TransactionScope object, which I believe will enable me to wrap all DB interaction in the parent method (along with all interaction in the child method) in one transaction.
So far so good..?
So the next question is how to create and use connections within this TransactionScope. I have heard that using multiple connections, even if they are to the same DB can force TransactionScope into thinking that it is a distributed transaction (involving some expensive DTC work).
Is the case? Or is it, as I seem to be reading elsewhere, a case that using the same connection string (which will lend itself to connection pooling) will be fine?
More practically speaking, do I...
- Create separate connections in the parent & child (albeit with the same connection string)
- Create a connection in the parent an pass it through as a parameter (seems clumsy to me)
- Do something else...?
UPDATE:
While it appears I would be OK using my usual .NET3.5+ and SQL Server 2008+, another part of this project will be using Oracle (10g) so I might as well practice a technique that can be used consistently across projects.
So I'll simply pass the connection through to the child methods.
Option 1 Code Sample:
using (TransactionScope ts = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.Connection.Open();
cmd.CommandType = CommandType.StoredProcedure;
try
{
//create & add parameters to command
//save parent object to DB
cmd.ExecuteNonQuery();
if ((int)cmd.Parameters["@Result"].Value != 0)
{
//not ok
//rollback transaction
ts.Dispose();
return false;
}
else //enquiry saved OK
{
if (update)
{
enquiryID = (int)cmd.Parameters["@EnquiryID"].Value;
}
//Save Vehicles (child objects)
if (SaveVehiclesToEPE())
{
ts.Complete();
return true;
}
else
{
ts.Dispose();
return false;
}
}
}
catch (Exception ex)
{
//log error
ts.Dispose();
throw;
}
}
}
}