sql server odbc: Nested Transaction between ADO.NET and T-SQL -- sql server odbc

Nested Transaction between ADO.NET and T-SQL

SK
8/25/2004 5:25:03 PM

sql server odbc:

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

Re: Nested Transaction between ADO.NET and T-SQL

Gert E.R. Drapers
9/7/2004 2:47:17 PM

This will work OK, the COMMIT in a nested proc will get deferred till the
root transaction COMMIT, so only ROLLBACK statements inside the nested code
really influence the behavior.

GertD@SQLDev.Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.

[quoted text, click to view]