Whats the best way to handle sql transactions using child objects? In the vb ebook, there is mention of passing a SqlTransaction around. Anyone have any examples?
There are many ways of handling transactions. Here's just one example of manually controlling the transaction. From DataPortal_Update...
using (DbConnection conn = DataFactory.CreateConnection())
{
conn.ConnectionString = DataFactory.ConnectionString;
conn.Open();
using (DbTransaction tran = conn.BeginTransaction())
{
try
{
ExecuteUpdateCommand(tran);
UpdateChildren(tran);
tran.Commit();
}
catch (DbException ex)
{
tran.Rollback();
throw;
}
}
}
private int ExecuteUpdateCommand(DbTransaction tran)
{
using (DbCommand cmd = DataFactory.CreateCommand())
{
cmd.Connection = tran.Connection;
cmd.Transaction = tran;
cmd.CommandText = "usp_User_Update";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(DataFactory.CreateParameter("@ID", this.Id));
<etc>
int result = cmd.ExecuteNonQuery();
return result;
}
}
private void UpdateChildren(DbTransaction tran)
{
AuthorizedBranches.Update(tran, this);
AssignedRoles.Update(tran, this);
}
Obviously, there are other options as well, such as the DTC and the new TransactionScope option.
Thanks for the examples. How does the TransactionalScope property effect updates? I'm unclear how this works:
<Transactional(TransactionalTypes.TransactionScope)> _
End
SubLikewise, if you're using SQL 2000, keep in mind that without a couple of workarounds, your transaction will always run in the DTC, since SQL 2000 doesn't support promotable transactions. (See http://www.lhotka.net/Articles.aspx?id=39c79955-ddc9-42c7-a657-d5c2ed49975e for more details).
If you were to use TransactionScope, mark the method with the appropriate attribute and then pass your connection object between parent and child (instead of the transaction object as in my example).
Copyright (c) Marimer LLC