I'm upgrading an application from Subsonic 2.0.3 to the latest 2.2 version. What's the best practice way to replace the ExecuteScalarInTransaction(command, transation) method?
Here's a sample of a method I'm replacing:
public static void ActivateAccount(int parentId, int childId, int playMode)
{
SqlConnection conn1 = DataUtil.GetFirstDBConnection();
SqlConnection conn2 = DataUtil.GetSecondConnection();
SqlTransaction[] trans = new SqlTransaction[2];
try
{
SqlDataProvider provider = new SqlDataProvider();
// Get the child
Model.Person child = new Model.Person(childId);
Contact contact = new Contact(child.ContactId);
// Update Child
child.ParentId = parentId;
child.Activated = true;
conn1.Open();
trans[0] = conn1.BeginTransaction();
provider.ExecuteScalarInTransaction(Contact.GetUpdateCommand(""), tran[0]);
// Save to 2nd Database
conn2.Open();
trans[1] = conn2.BeginTransaction();
//child.User.Save();
provider.ExecuteScalarInTransaction(child.User.GetUpdateCommand(""), tran[1]);
//child.Save();
provider.ExecuteScalarInTransaction(child.GetUpdateCommand(""), tran[1]);
// we got here, finally commit
foreach (SqlTransaction ts in trans)
{
ts.Commit();
}
}
catch (Exception ex)
{
Core.LogHandler.LogError(ex);
foreach (SqlTransaction ts in trans)
{
if (ts != null)
ts.Rollback();
}
}
finally
{
DataUtil.CleanUp(trans, new SqlConnection[] {conn1, conn2});
}
*/
}
So I've been trying to replace that with the following after reading other threads on here....
SqlDataProvider firstProvider = new SqlDataProvider();
firstProvider.SetDefaultConnectionString(ConfigurationManager.ConnectionStrings["First"].ConnectionString);
SqlDataProvider secondProvider = new SqlDataProvider();
secondProvider .SetDefaultConnectionString(ConfigurationManager.ConnectionStrings["Second"].ConnectionString);
using(TransactionScope scope = new TransactionScope())
{
using(SharedDbConnectionScope scs = new SharedDbConnectionScope())
{
try
{
// Get the child
Model.Person child = new Model.Person(childId);
Contact contact = new Contact(child.ContactId);
// Update Child
child.ParentId = parentId;
child.Activated = true;
// Save to 1rst DB
firstProvider.ExecuteScalar(Contact.GetUpdateCommand(""));
// Save to 2nd Database
secondProvider.ExecuteScalar(child.User.GetUpdateCommand(""));
//child.Save();
secondProvider.ExecuteScalar(child.GetUpdateCommand(""));
}
So, is the correct approach? I've googled around and read as many Subsonic 2.2 docs as I can I couldn't find anything specific about phasing out ExecuteScalarInTransaction calls.
Thanks in advance and sorry for the length of the post...