Mixing ADO.NET transactions and Stored Procedure Transactions

[James Walker]

Hi there,

I'm writing an application that calls a number of updates to a SQL
Server database, wrapped in an ADO.NET transaction. One of the stored
procedures implements T-SQL transactions - what i was wondering is if
the T-SQL encounters an error and ROLLBACK TRANSACTION is called, will
the fact that this error has occurred pass back to the ADO.NET
transaction so that the other updates are rolled back too? or will it
be suppressed? Do i need to call a RAISERROR in my T-SQL error
trapping routine to make this work?

My current ADO.NET code looks like this:

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlTransaction theTran = conn.BeginTransaction();
try
{
SqlDataAdapter da = new SqlDataAdapter();

// do first tran from command calling stored proc
sqlCommand1.Connection = conn;
sqlCommand1.Transaction = theTran;
da.UpdateCommand = sqlCommand1;
da.Update(theTable);

// do second tran from command calling stored proc - if
this
// stored proc has transactions and fail will this raise an
// error and rollback all the transactions?
sqlCommand2.Connection = conn;
sqlCommand2.Transaction = theTran;
da.UpdateCommand = sqlCommand2;
da.Update(theTable);

theTran.Commit();
}
catch
{
theTran.Rollback();
}
conn.Close();
}


the error trapping bit of the SQL proc looks like this:

BEGIN TRANSACTION
INSERT INTO StockLevels (fielda, fieldb) VALUES (@fielda, @fieldb);
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION -- if this gets called will ADO.NET know
about it and rollback?
RETURN -1
END

INSERT INTO StockTransaction (field1, field2) VALUES (@field1,
@field2);
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
return -1
END

COMMIT TRANSACTION
RETURN 1

Hope this makes sense!!! Many thanks

James

 

[Uri Dimant]

James
If you have BEGIN TRAN within the stored procedure do you really need to
define begin tran method in ADO.NET code?
if a Transact-SQL statement raises a run-time error, the entire transaction
is terminated and rolled back.