0 Replies Latest reply: Jul 22, 2012 8:00 AM by 950950 RSS

    I want to ecute multi stored procedure in one transaction..

    950950
      In one transaction, can I excute multi stored procedure?
      if sp2 fire exception then sp1 should be rollbacked?



      OracleConnection conn = null;
      OracleTransaction tran = null;
      OracleCommand cmd = null;

      try
      {
      conn = new OracleConnection(ConnectionString);
      cmd = new OracleCommand();
      conn.Open();
      tran = conn.BeginTransaction();



      //sp 1
      cmd.CommandText = "sp_Insert";
      cmd.CommandType=CommandType.StoredProcedure;

      cmd.Parameters.Add(new OracleParameter("id",DbType.Varchar2,"test1"));
      int retval = cmd.ExecuteNonQuery();

      cmd.Parameters.Clear();



      // sp 2
      cmd.CommandText = "sp_update";
      cmd.CommandType=CommandType.StoredProcedure;

      cmd.Parameters.Add(new OracleParameter("id",DbType.Varchar2,"test2"));
      retval = cmd.ExecuteNonQuery();






      tran.Commit();
      }
      catch (Exception ex)
      {

      tran.Rollback();
      }