Transactions - Is This Right?

[JaedenRuiner]

Okay,

I'm doing a lot of SQL statment direct manipulation of my database, creating SqlConnections, SqlCommands, Executing the commands, then disposing of both and doing it all over again. Occasionally an error occurs in the process (as we know is apt to happen) and I want to cancel all the changes made. Some of the changes are easy, because, well they are Temp Tables so I remove them automatically. But others I cannot just "Delete from X", because there is other data to preserve.

Given the nature of this, Can I like...enter the entire Process and Put a Marker, like BeginTransaction() or what not, and then on Any Kick out due to an Exception, do Transaction.Rollback() or if it was successful, do Transaction.Commit, or would i have to do it per operation, etc.

Thanks

 

[Tom]

Not sure what you mean by per operation but yes you can use transactions (rollback, commit ) on multiple inserts/tables as long as you dont break in the middle of it to run say a seperate query.

The Pro ADO.NET 2.0 book offers some good examples of doing this.

 

[JaedenRuiner]

Not sure what you mean by per operation but yes you can use transactions (rollback, commit ) on multiple inserts/tables as long as you dont break in the middle of it to run say a separate query.

I"m not quite sure I get the meaning of that.

If you check some of my other posts you can see a better descriptor of my operation, but I'm doing:

A Select Into, 2 updates, A Select Into, Delete, Select Into, 3 Deletes, Select Into, 2 Delete, Insert Into, Select to Fill a DataTable on my end, then while cycling through that datatable's records, (through a dataview) I run an update statement per entry (on a different table) with occasional Insert Statement via a stored procedure.

Each statement is being formulated and Generated within a parsing Object I've designed, and for their execution opens a unique Connection object each time, sometimes two connection objects for SqlBulkCopy() operations.

My Question was, do I thus need to "Start A Transaction" before Each Command, and if there is an error then execute the rollback, or can I do a Global - "mark any changes from here on out and if I don't commit at the end toast them bye-bye"

That's what I meant by the per operation.

Thanks

 

[cjard]

Youre asking us to comment on the atomicity of an operation only you can know the atomicity of

A transaction makes things atomic.. you can decide that you have 9 things to do, and their atomicity is 3-4-2.. Youd wrap the first 3 things in one transaction, the next 4 in another etc

Only you can know your design and at what point you can take up from where you left off. Try to minimise transaction length (time to complete) if possible as it creates fewer problems overall

My Question was, do I thus need to "Start A Transaction" before Each Command, and if there is an error then execute the rollback, or can I do a Global - "mark any changes from here on out and if I don't commit at the end toast them bye-bye"

You'll have to read up on how transactioning systems work. You can do either, and there are certain occasions when you'd do one or the other. Generally doing a massive number of operations in one transaction requires the db to maintain huge undo-lists and this can be as much of a problem in itself if the system is heavily used while transactions are taking place

 

[JaedenRuiner]

I see.

So basically, since each of my "Select Into" statements are simply creating temporary tables that I remove anyway no matter success or failure (in a finally section) it is not wise to utilize a Transaction there. But later when I do my insert into statement, which is involved on a table that I can't just "drop" it because it is actually holding real data, for those statements it's better to wrap them up.

I get it now. Thanks. Now on to msdn,

 

[cjard]

I see.

So basically, since each of my "Select Into" statements are simply creating temporary tables that I remove anyway no matter success or failure (in a finally section) it is not wise to utilize a Transaction there.

Indeed there is no need for the db to have to maintain an undo list for this operation

But later when I do my insert into statement, which is involved on a table that I can't just "drop" it because it is actually holding real data, for those statements it's better to wrap them up.

Well, yes, but it goes further than that.. Transaction is not just about removing values added. Lets take a bank money transfer:

UPDATE tblAccounts SET balance = balance - 100 WHERE accNum = 123456
UPDATE tblAccounts SET balance = balance + 100 WHERE accNum = 234567


Now you want both these to succeed or neither to succeed. i.e. you want them to be ATOMIC. Two ops that behave as one simultaneous op. Thats particularly when you use transactions. To make things atomic

 

[JaedenRuiner]

So far So Good, I quite understand the atomic nature of the transaction, but now it comes time for the...logistics..

My Execute method looks something like this:

      If _db.Connected Then
         _cmd = New SqlParser(_db)
         ''Load XLS File and Import XLS Data into Temp Table
         If LoadXLSIntoTable() AndAlso ImportToTempTable() Then
            ''Remove Negatives and Validate Uniques
            If ProcessNegatives() AndAlso ProcessDataErrors() Then
               ''Export Processed AutoBook Data & Generate DB BOL Entries
               If ExportAutobook() Then
                  If InsertBOLs() Then
                     ''Process Calculated Emails
                    Return ProcessEmails()
                 End If
              End If
           End If
        End If
     End If

Now each of those functions that I am calling in turn are part of what you might consider "Atomic" interactions with the database. However up-until the InsertBOLs and ProcessEmails function calls, I do not need to roll them into an Atomic transation. However, anything that DOES occur in the INsertBOLs (an Insert Into Table1 Statement) or in the ProcessEmails (multiple successive Update Table1 Statements with Interspersed Insert Into Table2 Statements via a StoredProcedure) must be protected so if at any moment an error occurs, I can rollback everything that happened.

Now that you get the where...I'm now confused by the how.
SqlConnection.BeginTransation() seems pretty straitforward, but do i need to use that specific Connection for all my Sql Commands to be within that transaction?

InsertBOLs and ProcessEmails() both use my SqlParser object, and Every time the command is executed it Creates a SqlConnection (2 connections for SqlBulkCopy operations) uses the connection then closes it when finished. If I want these to be transaction protected must I then use the same connection for all of them?

or (what I'm hoping is the way I can do it):

 ...snip...
  _Trans = _connection.BeginTransaction()
try
                  If InsertBOLs() Then
                     ''Process Calculated Emails
                    Return ProcessEmails()
                 End If
catch
  _Trans.Rollback 
finally
  _Trans.Commit
end try

<edit>

Alright that doesn't work. Each Connection must have its own transaction associated with it. So now the Question becomes, is that what Transaction Naming is for, so that I can have Multiple Pooled Connections that all are based on the same transaction "Name"?

Thanks