Nested Transaction between ADO.NET and T-SQL
-
I am writing an application that calls numbers of stored procedure wrapped in an ADO.NET transaction. Some of the stored procedure implements T-SQL transactions. Is it safe to call Stored Procedure like this or there is potential of data corruption. Have anyone seen any document or article that explain how this works. Sample code using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlTransaction theTran = conn.BeginTransaction(); try { // create command object // set Command Parameters // Set Command Parameters values // EXECUTE Stored Procedure 1 // create command object // set Command Parameters // Set Command Parameters values // EXECUTE Stored Procedure 2 .... theTran.Commit(); } catch { theTran.Rollback(); } conn.Close(); } Stored Procedure Create Procedure InsABC @NAME varchar(50), @Address varchar(100) as BEGIN BEGIN TRANSACTION .... -- Do INSERT ... .... -- In case of error rollback ... COMMIT TRANSACTION END
-
I am writing an application that calls numbers of stored procedure wrapped in an ADO.NET transaction. Some of the stored procedure implements T-SQL transactions. Is it safe to call Stored Procedure like this or there is potential of data corruption. Have anyone seen any document or article that explain how this works. Sample code using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlTransaction theTran = conn.BeginTransaction(); try { // create command object // set Command Parameters // Set Command Parameters values // EXECUTE Stored Procedure 1 // create command object // set Command Parameters // Set Command Parameters values // EXECUTE Stored Procedure 2 .... theTran.Commit(); } catch { theTran.Rollback(); } conn.Close(); } Stored Procedure Create Procedure InsABC @NAME varchar(50), @Address varchar(100) as BEGIN BEGIN TRANSACTION .... -- Do INSERT ... .... -- In case of error rollback ... COMMIT TRANSACTION END
Why don't you create a single StoreProcedure that is called by client. Inside this SP, call all other procedures. Sanjay Sansanwal www.sansanwal.com
-
Why don't you create a single StoreProcedure that is called by client. Inside this SP, call all other procedures. Sanjay Sansanwal www.sansanwal.com
No, I can't combine all the SP, some times same SP is called multiple times depending on Data. Its all dynamic based on data which comes from client application
-
I am writing an application that calls numbers of stored procedure wrapped in an ADO.NET transaction. Some of the stored procedure implements T-SQL transactions. Is it safe to call Stored Procedure like this or there is potential of data corruption. Have anyone seen any document or article that explain how this works. Sample code using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlTransaction theTran = conn.BeginTransaction(); try { // create command object // set Command Parameters // Set Command Parameters values // EXECUTE Stored Procedure 1 // create command object // set Command Parameters // Set Command Parameters values // EXECUTE Stored Procedure 2 .... theTran.Commit(); } catch { theTran.Rollback(); } conn.Close(); } Stored Procedure Create Procedure InsABC @NAME varchar(50), @Address varchar(100) as BEGIN BEGIN TRANSACTION .... -- Do INSERT ... .... -- In case of error rollback ... COMMIT TRANSACTION END
This isn't a problem. You can nest transactions inside each other. Just make sure that they all get committed or rolledback properly otherwise you might get errors saying that the transaction count before starting some SP is 1 and afterwards is 2 (because you forgot to commit or rollback the transaction before the SP exited)
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
-
This isn't a problem. You can nest transactions inside each other. Just make sure that they all get committed or rolledback properly otherwise you might get errors saying that the transaction count before starting some SP is 1 and afterwards is 2 (because you forgot to commit or rollback the transaction before the SP exited)
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
Thanks Colin, I have written an application just as proof of concept and it works fine. But, my boss think its not safe to do like this and he refering to Online Book Which say :- ---------------------- Specifying Transaction Boundaries You can identify when SQL Server transactions start and end with Transact-SQL statements or API functions and methods. Transact-SQL statements Use the BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, ROLLBACK WORK, and SET IMPLICIT_TRANSACTIONS statements to delineate transactions. These are primarily used in DB-Library applications and in Transact-SQL scripts, such as the scripts that are run using the osql command prompt utility. API functions and methods Database APIs such as ODBC, OLE DB, and ADO contain functions or methods used to delineate transactions. These are the primary mechanisms used to control transactions in a SQL Server application. Each transaction must be managed by only one of these methods. Using both methods on the same transaction can lead to undefined results. For example, you should not start a transaction using the ODBC API functions, and then use the Transact-SQL COMMIT statement to complete the transaction. This would not notify the SQL Server ODBC driver that the transaction was committed. In this case, use the ODBC SQLEndTran function to end the transaction.-------------- As per this document I am not suppose to mix transaction between ADO.NET and T-SQL. And I am not mixing transactions instead, I am nesting it. Is there is any published article or document online which can help me explaning to my BOSS? Thanks Sanjeev
-
Thanks Colin, I have written an application just as proof of concept and it works fine. But, my boss think its not safe to do like this and he refering to Online Book Which say :- ---------------------- Specifying Transaction Boundaries You can identify when SQL Server transactions start and end with Transact-SQL statements or API functions and methods. Transact-SQL statements Use the BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, ROLLBACK WORK, and SET IMPLICIT_TRANSACTIONS statements to delineate transactions. These are primarily used in DB-Library applications and in Transact-SQL scripts, such as the scripts that are run using the osql command prompt utility. API functions and methods Database APIs such as ODBC, OLE DB, and ADO contain functions or methods used to delineate transactions. These are the primary mechanisms used to control transactions in a SQL Server application. Each transaction must be managed by only one of these methods. Using both methods on the same transaction can lead to undefined results. For example, you should not start a transaction using the ODBC API functions, and then use the Transact-SQL COMMIT statement to complete the transaction. This would not notify the SQL Server ODBC driver that the transaction was committed. In this case, use the ODBC SQLEndTran function to end the transaction.-------------- As per this document I am not suppose to mix transaction between ADO.NET and T-SQL. And I am not mixing transactions instead, I am nesting it. Is there is any published article or document online which can help me explaning to my BOSS? Thanks Sanjeev
What the book is saying, and you've already realised, is that if you start a transaction in ADO.NET you must complete it in ADO.NET. If you start a transaction in T-SQL you must complete it in T-SQL. You can of course nest transactions inside each other. For example (in this pseudo code)
Start transaction 1
Start transaction 2
-- Do stuff
End transaction 2
End transaction 1The above is perfectly fine. Transaction 1 and 2 can both be in T-SQL, or both be in ADO.NET or Transaction 1 can be in ADO.NET with Transaction 2 being in T-SQL. Think of it like loops, you can start a for loop and call into another method that perhaps has another for loop inside it. The for loop in the inner method must complete before the for loop in the outer method can complete. Does this make sense? As for a published article... I am not sure about that. The text you've quoted seems perfectly reasonable to me. How competant is your boss? (Hmmm... maybe that is a bad question) How technical is your boss? Could he understand it in a way similar to the for loop analogy? If he is less technical, how about a Russian doll analogy? (The Russian dolls that are hollow and one fits inside the other, they split at the waist so you can enclose one doll around another. Pretend that the base is a start transaction, the head is the end transaction, and each size of doll is a different technology. You must obviously match the correct base with the correct head otherwise they won't fit together properly.) Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
-
What the book is saying, and you've already realised, is that if you start a transaction in ADO.NET you must complete it in ADO.NET. If you start a transaction in T-SQL you must complete it in T-SQL. You can of course nest transactions inside each other. For example (in this pseudo code)
Start transaction 1
Start transaction 2
-- Do stuff
End transaction 2
End transaction 1The above is perfectly fine. Transaction 1 and 2 can both be in T-SQL, or both be in ADO.NET or Transaction 1 can be in ADO.NET with Transaction 2 being in T-SQL. Think of it like loops, you can start a for loop and call into another method that perhaps has another for loop inside it. The for loop in the inner method must complete before the for loop in the outer method can complete. Does this make sense? As for a published article... I am not sure about that. The text you've quoted seems perfectly reasonable to me. How competant is your boss? (Hmmm... maybe that is a bad question) How technical is your boss? Could he understand it in a way similar to the for loop analogy? If he is less technical, how about a Russian doll analogy? (The Russian dolls that are hollow and one fits inside the other, they split at the waist so you can enclose one doll around another. Pretend that the base is a start transaction, the head is the end transaction, and each size of doll is a different technology. You must obviously match the correct base with the correct head otherwise they won't fit together properly.) Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
Thanks for the reply I Hope my boss will understand this.
-
Thanks for the reply I Hope my boss will understand this.
Good Luck! :-D
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!