存储过程事务处理

1:写带多输入参数的存储过程(用事物处理 )

ALTER proc [dbo].[test_trans]
(@Id varchar(50),
@Name Nvarchar(50))
as
begin tran
delete from classMgr where id='0002'
insert into classMgr(id,Name) values(@Id,@Name)
declare @regionerror int
select @regionerror=@@ERROR;
if(@regionerror=0)
  commit tran
  else
  rollback tran

 

 

 2:asp.net 执行存储带多输入参数的存储过程

 string connectString = "server=.;database=test;uid=sa;pwd=2009@ABCDEF";
            SqlConnection conn = new SqlConnection(connectString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "test_trans";
                cmd.Connection = conn;
                SqlParameter[] pars = new SqlParameter[]{
            new SqlParameter ("@Id",SqlDbType.VarChar,50),
            new SqlParameter("@Name",SqlDbType.NVarChar,50)
            };
                pars[0].Value = "0009";
                pars[1].Value = "wangyonghua";
                foreach (SqlParameter par in pars)
                {
                    cmd.Parameters.Add(par);
                }
                //cmd.Transaction = conn.BeginTransaction();
                cmd.ExecuteNonQuery();
                //cmd.Transaction.Commit();
            }
            catch (Exception err)
            {
                //cmd.Transaction.Rollback();

                throw new Exception(err.Message + err.StackTrace);
            }
            finally
            {
                MessageBox.Show("成功");
                conn.Dispose();
            }