0

For a long time I've been using the following structure when using TransactionScope.

using(var con = new SqlConnection(CONNECTIONSTRING))
{
    con.Open();
    foreach(var file in files)
    {
        try
        {           
            using(var tran = new TransactionScope())
            {
                using(var cmd = new SqlCommand(CMDTEXT1, con)
                {
                    //add parameters
                    //ExecuteScalar or ExecuteNonQuery 
                }
                //...repeat above as needed for other inserts
                //...run other C# methods like FTP upload
                tran.Complete();
            }
        }
        catch(Exception ex)
        {
            //log exception
        }
    }
}

Today, my FTP server was down but my SQL server was up. The error was properly caught and tran.Complete never ran. I would expect tran to rollback the changes but instead it had inserted all my commands.

Is this because the SqlConnection is not within the TransactionScope? Do I have to start a new SqlConnection for each transaction? I want to retain this structure so I can reuse the SqlConnection so I considered replacing new TransactionScope with con.BeginTransaction but I read that it doesn't allow the mixing of C# (I need to run C# methods like FTPUpload). Did I misintrepret this?

Answered by comments

Is this because the SqlConnection is not within the TransactionScope? Yes

Do I have to start a new SqlConnection for each transaction? Yes

Did I misintrepret this? I confused EntityFramework.BeginTransaction with SqlConnection.BeginTransaction.

SILENT
  • 3,916
  • 3
  • 38
  • 57
  • Do note you're starting a new transaction and running a command for every individual file. Are you saying the commands for every individual file were partially processed or that some files were processed and others were not? The latter is in line with how you're using the scope; if you really want every file or no file to be processed, you need to move the scope to encompass the `foreach`. – Jeroen Mostert Apr 12 '18 at 12:58
  • @JeroenMostert I want a transaction for every file. In case one file fails, other file uploads won't be affected. However, if any part of the file insert fails, it should rollback all changes for that specific file. – SILENT Apr 12 '18 at 13:00
  • Yeah, according to [this](https://stackoverflow.com/a/1976586/4137916) enrollment happens on the transaction level, not if you just create a command within the scope. That also [links](https://stackoverflow.com/a/3159634/4137916) to a way you can enroll the current connection. (Incidentally, there should be no real *need* to reuse the connection the way you do if this is the only code; if you opened a new one for every transaction, they would still all use the same physical connection thanks to pooling.) – Jeroen Mostert Apr 12 '18 at 13:02
  • Also, note that you can do anything you like inside a `TransactionScope`, including FTP uploads. It's just that most things are not transaction aware and will therefore not enlist in the `TransactionScope` at all (including, probably, FTP uploads). The rollback of the SQL transaction merely happens because the exception ensures `.Complete()` is never called before the scope is disposed; the FTP actions would not themselves be rolled back if the SQL commands failed, as they have no transaction support (at least, I assume they don't). – Jeroen Mostert Apr 12 '18 at 13:04
  • You are not doing it correct, check https://stackoverflow.com/questions/934316/is-there-a-way-to-use-transactionscope-with-an-existing-connection – Mrinal Kamboj Apr 12 '18 at 13:05
  • @JeroenMostert Thanks. Could not find that piece of info while searching. How about `con.BeginTransaction`? Do you know what it means by not allowing the mixing of C# ? – SILENT Apr 12 '18 at 13:06
  • You should not mix `TransactionScope` with an explicit `BeginTransaction`, because that needlessly complicates things if it works at all -- use one or the other. Beyond that, there's no general prohibition on doing things in a `TransactionScope`. (Except that, as I mentioned earlier, most things are just not aware of the scope.) – Jeroen Mostert Apr 12 '18 at 13:08
  • @JeroenMostert I will replace `TransactionScope` with `BeginTransaction` since its newer but I don't understand what not being able to mix DB operation and C# code in the transaction like found on http://vunvulearadu.blogspot.com/2014/07/entity-framework-ef-transactionscope-vs.html means. – SILENT Apr 12 '18 at 13:17
  • That blog is explicitly talking about Entity Framework, which you are not using. `Database.BeginTransaction` is its own thing. I have no idea what it means when it says you can't mix in "C# code"; you should ask the author. I don't know why EF has changed its ways, but that blog is not talking about `SqlConnection.BeginTransaction`, in any case, and its advice is not relevant to your code. – Jeroen Mostert Apr 12 '18 at 13:23

0 Answers0