CODEWITHSUNDEEP
c#.netsql-servertransactionssqlcommand
sharptooth
sharptooth

Reputation: 170509

Does SqlCommand with StoredProcedure type provide an outer transaction?

I have this code for invoking a stored procedure:

 //SqlConnection connection = ...;

 using (SqlCommand command = connection.CreateCommand())
 {
     command.CommandType = CommandType.StoredProcedure; 
     command.CommandText = "usp_MyProc";
     command.ExecuteNonQuery();
 }

and the stored procedure is as follows:

CREATE PROCEDURE [dbo].[usp_MyProc]
AS
BEGIN
BEGIN TRAN
    -- SOME STATEMENTS IN HERE
COMMIT TRAN
RETURN 0
END

Is the transaction controlled by those BEGIN TRAN and COMMIT TRAN statements or is there an outer transaction somehow induced by the SqlCommand class?

Upvotes: 0

Views: 288

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300719

A SqlCommand does not generate an automatic transaction.

Whether the stored procedure transaction has control depends whether there is already an open transaction at the point when the SQLCommand is executed.

If there is no open transaction, then the BEGIN TRANS..COMMIT TRANS in the stored procedure controls the transaction.

Upvotes: 1

Related Questions