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()
