2 screen333 Screen333 于 2014.03.08 22:17 提问

c#.net 调用oracle存储过程,不能正常回滚

本人是个小菜,刚接触.net不久,使用事务不熟练,导致回滚失败。下面是代码:

enter code here
  using (OracleConnection conn = new OracleConnection(strConn))
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            OracleCommand cmd = conn.CreateCommand();

            OracleTransaction ot = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            cmd.Connection = conn;
            cmd.Transaction = ot;
            //cmd.Transaction = ot;
            //OracleTransaction ot = conn.BeginTransaction();

            try
            {
                foreach (XmlNode node in itemNodes)
                {
                    rcptGroupId = Convert.ToString(node.SelectSingleNode("RcptGroupID").InnerText);
                    amt = node.SelectSingleNode("Price").InnerText;

                    OracleParameter[] pm = new OracleParameter[14];

                    pm[0] = new OracleParameter("cardno", OracleType.VarChar, 20);
                    pm[0].Direction = ParameterDirection.Input;
                    pm[0].Value = cardNo;

                    pm[1] = new OracleParameter("rcptgroupid", OracleType.VarChar, 100);
                    pm[1].Direction = ParameterDirection.Input;
                    pm[1].Value = rcptGroupId;

                    pm[2] = new OracleParameter("amt", OracleType.Number);
                    pm[2].Direction = ParameterDirection.Input;
                    pm[2].Value = float.Parse(amt);

                    pm[3] = new OracleParameter("secrityno", OracleType.VarChar, 32);
                    pm[3].Direction = ParameterDirection.Input;
                    pm[3].Value = secrityNo;

                    pm[4] = new OracleParameter("cardserno", OracleType.VarChar, 32);
                    pm[4].Direction = ParameterDirection.Input;
                    pm[4].Value = cardSerNo;

                    pm[5] = new OracleParameter("userid", OracleType.VarChar, 4);
                    pm[5].Direction = ParameterDirection.Input;
                    pm[5].Value = userID;

                    pm[6] = new OracleParameter("rcptno", OracleType.VarChar, 20);
                    pm[6].Direction = ParameterDirection.Output;

                    pm[7] = new OracleParameter("resultcode", OracleType.Int16);
                    pm[7].Direction = ParameterDirection.Output;

                    pm[8] = new OracleParameter("errormsg", OracleType.VarChar, 200);
                    pm[8].Direction = ParameterDirection.Output;

                    //add 2013.8.1
                    pm[9] = new OracleParameter("Bank", OracleType.VarChar, 20);
                    pm[9].Direction = ParameterDirection.Input;
                    pm[9].Value = bank;

                    pm[10] = new OracleParameter("BankNo", OracleType.VarChar, 20);
                    pm[10].Direction = ParameterDirection.Input;
                    pm[10].Value = bankno;

                    pm[11] = new OracleParameter("TransMoney", OracleType.VarChar, 10);
                    pm[11].Direction = ParameterDirection.Input;
                    pm[11].Value = decimal.Parse(TransMoney);

                    pm[12] = new OracleParameter("CashTraceNo", OracleType.VarChar, 30);
                    pm[12].Direction = ParameterDirection.Input;
                    pm[12].Value = CashTraceNo;

                    pm[13] = new OracleParameter("TerminalId", OracleType.VarChar, 16);
                    pm[13].Direction = ParameterDirection.Input;
                    pm[13].Value = TerminalId;

                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "pkg_autobillservice.proc_autoopbillcharge2";
                    cmd.Parameters.AddRange(pm);
                    cmd.ExecuteNonQuery();

                    rcptNoList.Add(Convert.ToString(pm[6].Value));
                }
                ot.Commit();

            }
            catch (Exception ex)
            {
                ot.Rollback();
                return string.Format(result, "1", ex.Message, "", rcptGroupId);
            }
            finally
            {
                cmd.Dispose();
                ot.Dispose();

            }
        }

调用存储过程,第一次错误了能回滚,再执行一次到cmd.ExecuteNonQuery()数据就已经提交了。
这样写在循环第二次的时候还会报错,错误为:System.Data.OracleClient.OracleExceptionMessage=ORA-06550: 第 1 行, 第 7 列: PLS-00703: 列表中具有指定参数的多个实例。
一天了还是没有法解决,求大家帮忙看下。

Csdn user default icon
上传中...
上传图片
插入图片