StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL atomic transaction not being atomic
text
Body
copied!<p>Below is the code for two calls to two stored procedures that I'm working on. I thought I had this set up to either do an all or nothing atomic transaction, however if there is an error in the second stored procedure call the first will still execute. I'm still new to writing these in C#, so any help would be appreciated. Thx!</p> <p>I should also mention something else this is doing that I find odd: If an exception is thrown and the program goes into the "catch" block, it will still run the code in the "finally" block. My understanding was that if an exception is thrown, the code in the "catch" block is all that will be executed. </p> <p>EDIT Thanks to responses below I've fixed the catch/finally confusion and added an ExecuteNonQuery() call so the first stored procedure gets called as well. However, the first stored procedure, when it is called, needs to be called an executed first before the second stored procedure can do it's work. Is this something that can be accomplished in an atomic transaction or do they have to be run separately? </p> <pre><code>try { cm = Dts.Connections["serverName"]; sqlConn = (SqlConnection)cm.AcquireConnection(Dts.Transaction); sqlTrans = sqlConn.BeginTransaction("QueueUpdates"); if (dummyIndicator.Equals("Y")) { string temp = retrievedMessage.Substring(203, 17); int newNumber = (int)(long.Parse(temp) / 777); SqlParameter newNum = new SqlParameter("@newNum", num.Value); SqlParameter oldNum = new SqlParameter("@oldNum", newNumber); sqlComm = new SqlCommand("DB.dbo.sp_UpdateNumber", sqlConn, sqlTrans); sqlComm.CommandType = CommandType.StoredProcedure; sqlComm.Parameters.Add(newNum); sqlComm.Parameters.Add(oldNum); sqlComm.Transaction = sqlTrans; sqlComm.ExecuteNonQuery(); } //Update records according to queue messages sqlComm = new SqlCommand("DB.dbo.sp_AgentIdAprCheck", sqlConn, sqlTrans); sqlComm.CommandType = CommandType.StoredProcedure; sqlComm.Parameters.Add(num); sqlComm.Parameters.Add(addOrUpdate); sqlComm.Parameters.Add(companyCode); sqlComm.Parameters.Add(agentID); sqlComm.Parameters.Add(firstName); sqlComm.Parameters.Add(lastName); sqlComm.Parameters.Add(suffix); sqlComm.Parameters.Add(taxIdType); sqlComm.Parameters.Add(entityType); sqlComm.Parameters.Add(corporateName); sqlComm.Parameters.Add(outNewNumber); sqlComm.Parameters.Add(outCurrentNumber); sqlComm.Parameters.Add(outOperator); sqlComm.Parameters.Add(outDate); sqlComm.Parameters.Add(returnVal); sqlComm.ExecuteNonQuery(); sqlTrans.Commit(); if (addOrUpd.Equals("ADD")){recordsAdded++;} else{recordsUpdated++;} } catch (Exception ex) { _sqlDataErrors++; swLog.WriteLine("Message not updated: " + retrievedMessage); swLog.WriteLine("Error: " + ex.ToString()); sqlTrans.Rollback(); } finally { cm.ReleaseConnection(sqlConn); } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload