0

I know that there are a number of questions on the topic of SqlTransactions vs TransactionScopes, but I haven't seen any examples of using TransactionScopes after Sqlconnections have been opened.

I'm writing a program that will have multiple SQL calls to update a database. These commands are executed in multiple transactions according to logical blocks of work. i.e.

using (SqlConnection conn = new SqlConnection(...))
{
    conn.Open();

    using(SqlTransaction tran = conn.BeginTransaction())
    {
        string sql = ...;
        conn.Execute(sql, transaction: tran);

        sql = ...;
        conn.Execute(sql, transaction: tran)

        tran.Commit();
    }

    using(SqlTransaction tran2 = conn.BeginTransaction())
    {
        string sql2 = ...;
        conn.Execute(sql2, transaction: tran2);

        sql2 = ...;
        conn.Execute(sql2, transaction: tran2)

        tran2.Commit();
    }
}

From what I read, in general TransactionScope is preferable to SqlTransaction, but in all the examples I've seen the TransactionScope is initialized before the SqlConnection. Also, according to the comment on this answer, if the connection is opened first, then you have to enlist it in the transaction. I'm wondering if it would be better to stick with the SqlTransactions in this case, or if I should refactor?

If I should refactor, I could see possibly enlisting the connection in each transaction (although I'm not sure if a connection can be enlisted into more than 1 transaction, and I'm not even sure if something like that is possible using a TransactionScope as TransactionScope.Transaction doesn't exist and conn.EnlistTransaction(transactionScope) doesn't work.), or possibly inverting the using blocks to reinitialize the connection in each transaction. Something like this:

using (TransactionScope scope = new TransactionScope())
{
    using (SqlConnection conn = new SqlConnection(...))
    {
        string sql = ...;
        conn.Execute(sql);

        sql = ...;
        conn.Execute(sq)
    }

    scope.Complete();
}

using (TransactionScope scope2 = new TransactionScope())
{
    using (SqlConnection conn2 = new SqlConnection(...))
    {
        string sql2 = ...;
        conn2.Execute(sql2);

        sql2 = ...;
        conn2.Execute(sql2)
    }

    scope2.Complete();
}

but I'm not sure what kind of hit/help this will result in from a resource management standpoint, as the connections will be using the same connectionString. Will this cause unneeded connections? Is there a recommended structure for code of this nature?

Thank you in advance!

Brian
  • 137
  • 1
  • 6
  • If I may ask what kind of problem you are trying to solve? – jtabuloc Mar 25 '19 at 06:04
  • @jtabuloc I'm not exactly solving a problem. SQLTransactions work just fine. I'm trying to figure out what's the best way to handle this scenario in new development. If TransactionScope is recommended for new development, I'd like to know how to use it for situations like this. Or if it's not applicable for situations like this I'd like to know that too. – Brian Mar 25 '19 at 13:35
  • 1
    IMHO, since you're not trying to solve any problem I would suggest sticking on what you have right now. TransactionScope is much complex than what you think and as far as I could see you wouldn't gain much of the benefits it offers base on your scenario. Applying the best way without gaining any benefits is not the best way. – jtabuloc Mar 26 '19 at 08:04

0 Answers0