0

Folks I ran into a bit of code and I'm a tad confused about whats going on.

I'm working to refactor the code to instead process a handful of SqlCommands rather than the single SqlCommand that it currently works with. It's my hope all SqlCommands can be processed under ONE transaction.

Each SqlCommand is a Stored Procedure, so in effect my transaction would call one (or many) Stored Procedures - first off, is that even possible?

Regardless, here's the code block:

public virtual void Execute()
{
  using (SqlConnection conn = new SqlConnection(ConnectionString))
  {
    SqlCommand storedProcedure = new SqlCommand(BlahBah, conn);
    storedProcedure.CommandType = CommandType.StoredProcedure;

    conn.Open();
    **conn.BeginTransaction(IsolationLevel.ReadUncommitted).Commit();**

    storedProcedure.ExecuteNonQuery();
    conn.Close();

  }
}

In particular the highlighted statement that sets a Transaction on the Connection object, appended with a ".Commit()".

The actual source code has no ROLLBACK, nor anywhere is there a COMMIT. Am I essentially seeing some sort of AutoCommit? Does it even make sense to have a TRANSACTION here if for example the DB doesn't require TRANSACTIONal processing?

Perhaps more important to my refactoring efforts, would something like this make sense? That's to ask, if I processed 10 Stored Procedures and the last one threw an error, would there be an auto ROLLBACK on all 10?

Here's where I want to land:

public virtual void ExecuteTest()
{
  using (SqlConnection conn = new SqlConnection(ApplicationConfig.DbConnectInfo.ConnectionString))
  {
    var errorText = string.Empty;
      conn.Open();
      conn.BeginTransaction(IsolationLevel.ReadUncommitted).Commit();

      foreach (var storedProcedure in StoredProcedures)
      {
        storedProcedure.Connection = conn;
        storedProcedure.ExecuteNonQuery();
      }
      conn.Close();
  }
}

EDIT: this MS link suggests the approach will work:

SqlConnection.BeginTransaction Method (IsolationLevel)

Thank you for your interest.

Bill Roberts
  • 1,127
  • 18
  • 30
  • Am I missing something? You're starting a transaction and then immediately committing that transaction, before you've even called any of the stored procedures. – Brendan Green Feb 24 '17 at 02:11
  • When you say that "you" are starting a transaction, please recall I'm refactoring existing code and I am confused about it. That ".Commit()" at the end didn't make sense to me, hence my asking about it. Again, asking if there's any purpose for it. You however are suggesting that it's just bad code I gather? Anyway, you see it my target example because again, really wasn't sure what it's purpose was. I was asking with it appended, was there some sort of AutoCommit going on. In summary, I was asking about ".Commit()", which I found per the original code. – Bill Roberts Feb 24 '17 at 08:40

0 Answers0