Muiltiple Stored Procedure Within Transaction

[Leon]

How can I code a stored procedure "within my codebehind page" that execute
two or more stored procedures within a transaction?
if so how would I pass values to each parameter?

i.e.
Begin Tran
exec sp1
exec sp2
Commit Tran
.....of course some error checking.

[Rob]

On Thu, 14 Oct 2004 09:09:56 -0500, "Leon" <vn*****@msn.com> wrote:

How can I code a stored procedure "within my codebehind page" that execute
two or more stored procedures within a transaction?
if so how would I pass values to each parameter?

i.e.
Begin Tran
exec sp1
exec sp2
Commit Tran
....of course some error checking.

Fairly straight-forward:

// 1) Create your database connection
IDbConnection dbConn = new
(System.Data.SqlClient.SqlConnection("connection string");

// 2) Create a transaction object
IDbTransaction dbTrans = myConn .BeginTransaction();

// 3) Execute your stored procedures, passing transaction object as
parameter
IDbCommand cmd = dbConn .CreateCommand();
cmd.CommandText = "stored procedure name";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = dbTrans;

// 4) Add any parameters here
IDataParameter parameter = cmd.CreateParameter();
parameter.ParameterName = CreateCollectionParameterName("param name");
parameter.DbType = DbType.(whatever);
parameter.Value = (whatever);
cmd.Parameters.Add(parameter);

// 5) Execute command
cmd.ExecuteNonQuery(); // or cmd.ExecuteReader or cmd.ExecuteScalar

// 6) Repeat 3), 4) & 5) for next procedure

// 7) Commit the transaction if sucessful otherwise rollback
if (success)
dbTrans.Commit()
else
dbTrans.Rollback()

[Leon]

I'm sorry, I want all of my transaction code within my stored procedure.

"Rob" <**Please post reply to gr*****@nowhere.com> wrote in message
news:lo********************************@4ax.com...

On Thu, 14 Oct 2004 09:09:56 -0500, "Leon" <vn*****@msn.com> wrote:

How can I code a stored procedure "within my codebehind page" that
execute
two or more stored procedures within a transaction?
if so how would I pass values to each parameter?

i.e.
Begin Tran
exec sp1
exec sp2
Commit Tran
....of course some error checking.

Fairly straight-forward:

// 1) Create your database connection
IDbConnection dbConn = new
(System.Data.SqlClient.SqlConnection("connection string");

// 2) Create a transaction object
IDbTransaction dbTrans = myConn .BeginTransaction();

// 3) Execute your stored procedures, passing transaction object as
parameter
IDbCommand cmd = dbConn .CreateCommand();
cmd.CommandText = "stored procedure name";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = dbTrans;

// 4) Add any parameters here
IDataParameter parameter = cmd.CreateParameter();
parameter.ParameterName = CreateCollectionParameterName("param name");
parameter.DbType = DbType.(whatever);
parameter.Value = (whatever);
cmd.Parameters.Add(parameter);

// 5) Execute command
cmd.ExecuteNonQuery(); // or cmd.ExecuteReader or cmd.ExecuteScalar

// 6) Repeat 3), 4) & 5) for next procedure

// 7) Commit the transaction if sucessful otherwise rollback
if (success)
dbTrans.Commit()
else
dbTrans.Rollback()