Quick Transaction Question.

If in .NET I open a connection to my database, then use some sql text to start a transaction, then reuse that same open connection to call several stored procedures (using SqlCommand with CommandType.StoredProcedure), before ending the transaction. Will that run as a single transaction that can be rolled back? or are the stored procedure calls unable to roll-back after each one completes?

the stored procedures are themselves atomic and would need their own rollbacks. your use of transaction would work if all your statements were sql strings executing one after the other. -- jp

|||

Did a bit of research, and it appears there is a BeginTransaction method that can be used as part of the SqlConnection object. Which allowed me to start a transaction on a connection, then call serveral stored procedures using a SqlCommand object set to CommandType.StoredProcedure, and it all works as a single transaction, rolling everything back if any of then calls fail (internally or enternally). Here is a quick code snippet:

1protected void btnSave_Click(object sender, EventArgs e) {2//Create connection string for SQL query3 String strConnect;4 strConnect = WebConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;56//Generate call to stored procedure7 SqlConnection con =new SqlConnection(strConnect);8 SqlTransaction trans =null;910//Make Calls11string myNull =null;12try {13 con.Open();14 trans = con.BeginTransaction();15int ret1 = spCall(con, trans,"two");16int ret3 = spCall(con, trans, myNull);17int ret2 = spCall(con, trans,"three");18 trans.Commit();19 Master.Message.CssClass ="Text_Message";20 Master.Message.Text = ret1.ToString();21 Master.Message.Visible =true;22 }23catch (Exception sql) {24 Master.Message.CssClass ="Text_Error";25 Master.Message.Text = sql.Message;26 Master.Message.Visible =true;27if (null != trans) {28 trans.Rollback();29 }30 }31finally {32 con.Close();33 con.Dispose();34 }35 }3637protected int spCall(SqlConnection myConn, SqlTransaction myTrans,string myParam) {3839//Generate call to stored procedure40 SqlCommand storedProcCommand =new SqlCommand("spTest", myConn);41 storedProcCommand.CommandType = CommandType.StoredProcedure;4243//Build SQL parameter list44 storedProcCommand.Parameters.AddWithValue("@.value", myParam);4546//Return code47 SqlParameter retParam = storedProcCommand.Parameters.Add("@.ReturnValue", SqlDbType.Int);48 retParam.Direction = ParameterDirection.ReturnValue;4950//Bind to transaction51 storedProcCommand.Transaction = myTrans;5253//Run stored procedure54int retCode = 0;55 SqlDataReader Reader = storedProcCommand.ExecuteReader();56 retCode = (int)storedProcCommand.Parameters["@.ReturnValue"].Value;57 Reader.Close();5859return retCode;60 }

 
1CREATE PROCEDURE [dbo].[spTest]2--Parameters3 @.valuevarchar(50) =null4AS56BEGIN78SET NOCOUNT ON;910INSERT INTO tbTest11 (12 [value]13 )14VALUES15 (16 @.value17 )1819RETURN@.@.Identity2021END
My first call inserts okay, the second call fails because nulls are not accepted by [value] in the table definition., and the third call never happens due to the exception thrown by the second call, which forced a rollback of the entire transaction, of which each stored procedure is a call of. Did a fair amount of testing and everything appears to be in order, if anyone notices anything I overlooked and or that could be problematic, please let me know.