views:

7

answers:

0

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...