ASP.NET 中Oracle UPDATE命令更新0行，但是指令在PL/SQL中可以正常更新表

Question

下面这个是我在这个功能里用到的操作数据库的方法：

public int cmd_Execute_Orcl(string strSql, params OracleParameter[] paramArray)
{
    OracleCommand myCmd = new OracleCommand();
    try
    {
        myCmd.Connection = myOrclConn;
        myCmd.CommandText = strSql;

        foreach (OracleParameter temp in paramArray)
        {
            myCmd.Parameters.Add(temp);
        }
        ConnectionManage(true);
        int i = myCmd.ExecuteNonQuery();
        myCmd.Parameters.Clear();
        return i;
    }
    catch (Exception ex)
    {
        throw (ex);
    }
    finally 
    {
        myCmd.Dispose();
        ConnectionManage(false);
    }
}

ConnectionManage() 是控制数据连接的开关的，就不放上来了，下面是有问题的Edit方法：

public string Edit_DAL(string id, string jh, string jz, string jd, string wd, string gzwz, string qxlx, string sx, string jx, string jb, string cfdd, string zjqssj, string zjzzsj, string bz)
    {
        string sql = "update JH set jh = :jh, jz = :jz, wd = :wd, jd = :jd, gzwz = :gzwz, qxlx = :qxlx, sx = :sx, jx = :jx, jb = :jb, cfdd = :cfdd, zjqssj = TO_DATE(:zjqssj, 'yyyy-mm-dd hh24:mi:ss'), zjzzsj = TO_DATE(:zjzzsj, 'yyyy-mm-dd hh24:mi:ss'), bz = :bz where ID = :idy";
        string result = null;
        {
            String[] temp1 = zjqssj.Split('/');
            String[] temp2 = zjzzsj.Split('/');

            string tempString = "";

            if (temp1[2].Length < 2)
            {
                temp1[2] = temp1[2].Insert(0, "0");
            }

            
            for (int i = 0; i < temp1.Length; i++)
            {
                if (i != 0)
                {
                    temp1[i] = temp1[i].Insert(0, "/");
                }
                tempString += temp1[i].ToString();
            }

            zjqssj = tempString;
            tempString = "";

            if (temp2[2].Length < 2)
            {
                temp2[2] = temp2[2].Insert(0, "0");
            }

            for (int i = 0; i < temp2.Length; i++)
            {
                if (i != 0)
                {
                    temp2[i] = temp2[i].Insert(0, "/");
                }
                tempString += temp2[i].ToString();
            }

            zjzzsj = tempString;
            tempString = "";
        }
        OracleParameter[] pars ={
                                new OracleParameter(":idy",id),
                                new OracleParameter(":jh",jh),
                                new OracleParameter(":jz",jz),
                                new OracleParameter(":jd",jd),
                                new OracleParameter(":wd",wd),
                                new OracleParameter(":gzwz",gzwz),
                                new OracleParameter(":qxlx",qxlx),
                                new OracleParameter(":sx",sx),
                                new OracleParameter(":jx",jx),
                                new OracleParameter(":jb",jb),
                                new OracleParameter(":cfdd",cfdd),
                                new OracleParameter(":zjqssj",(zjqssj.Replace('/', '-') + " 00:00:00")),
                                new OracleParameter(":zjzzsj",(zjzzsj.Replace('/', '-') + " 00:00:00")),
                                new OracleParameter(":bz",bz)
                            };
        try
        {
            SqlHelper.cmd_Execute_Orcl(sql, pars);
            //SqlHelper.cmd_Execute_Orcl("COMMIT")
            result = "ok";
        }
        catch (Exception ex)
        {
            result = "no" + "=" + ex.Message + "\n" + ex.StackTrace;
        }
        return result;
    }

问题就在于完全没有报错，但是都是0行更新，参数也检查过，作为Key的ID的值也能对应上，以此SELECT能得到想要修改的行，然后我尝试过在PL/SQL里直接运行SQL命令，是可以对数据行进行更新的，对这个表的删除操作调用的也是这个cmd_Execute_Orcl方法，没有问题，对另一个表的Edit操作也是调用的同样的方法，也没问题，下面我贴一下这个没问题的代码供参考：

public string Edit(string OriginalId, string EditUserAccount, string EditUserName, string EditUserMobile, string EditDeptId, string EditRoleId, string EditRoleName)
    {
        string sql = "update AuthUser set UserAccount=:EditUserAccount, UserName=:EditUserName, UserMobile=:EditUserMobile, DepartmentId=:EditDeptId, RID=:EditRoleId, RoleName=:EditRoleName where ID=:OriginalId";
        OracleParameter[] pars ={
                                new OracleParameter(":EditUserAccount",EditUserAccount),
                                new OracleParameter(":EditUserName",EditUserName),
                                new OracleParameter(":EditUserMobile",EditUserMobile),
                                new OracleParameter(":EditDeptId",EditDeptId),
                                new OracleParameter(":EditRoleId",EditRoleId),
                                new OracleParameter(":EditRoleName",EditRoleName),
                                new OracleParameter(":OriginalId",OriginalId),
                            };
        try
        {
            SqlHelper.cmd_Execute_Orcl(sql, pars);
            return "ok";
        }
        catch (Exception ex)
        {
            string test = ex.Message;
            return "no";
        }
    }

在目标表里ID是作为主键的，默认值SYS_GUID()，数据类型nvarchar2，ZJQSSJ和ZJZZSJ是DATE，其他都是varchar2，我也有尝试过再执行一次COMMIT，依然没有解决问题，问题出在哪里也还没有头绪……

Answer 1

是不是语法有问题。

oracle command @,OracleCommand SQL参数绑定_苏苏苏苏大霖的博客-CSDN博客 我对以下参数的绑定有问题。该连接有效，因为我没有使用参数对其进行了测试。但是，执行查询之前的值仍然使用’@userName’而不是’jsmith’。问题是什么？这不是解决绑定的正确方法吗？public static String GetFullName(String domainUser){DataTable dT;String fullName = "";OracleConnection db ... https://blog.csdn.net/weixin_42402664/article/details/116510722

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using System.Linq;
using System.Web;

namespace WebServer
{
    public class OracleHepler
    {
        /// <summary>
        /// 在构造函数中初始化其内容
        /// </summary>
        public static string _connectionString;

        //public static SqlConnection _sqlConnection = new SqlConnection(new SqlHelper()._connectionString);new SqlHelper()
        public OracleHepler()
        {
            _connectionString = ConfigurationManager.ConnectionStrings["oracleConnection"].ConnectionString;
        }

        /// <summary>
        /// 根据_connectionString生成SqlConnection
        /// </summary>
        /// <returns></returns>
      
        public OracleConnection GetConnection()
        {
            OracleConnection con = new OracleConnection(_connectionString);
            try
            {
                con.Open();
            }
            catch
            {
                con = null;
            }
            return con;
        }

        #region ExecuteNonQuery
        /// <summary>
        /// 对连接执行Transact-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
       
        public static int ExecuteNonQuery(string commandText, bool isProcedure, params OracleParameter[] paras)
        {
            OracleConnection con = new OracleConnection(_connectionString);
            OracleCommand cmd = new OracleCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            cmd.Parameters.Clear();
            foreach (OracleParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }
            try
            {
                con.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 对连接执行Transact-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
      
        public static int ExecuteNonQuery(string commandText, params OracleParameter[] paras)
        {
            return ExecuteNonQuery(commandText, false, paras);
        }

        /// <summary>
        /// 对连接执行Transact-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
   
        public static int ExecuteNonQuery(OracleTransaction trans, string commandText, bool isProcedure, params OracleParameter[] paras)
        {

            OracleConnection con = trans.Connection;
            OracleCommand cmd = new OracleCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            cmd.Parameters.Clear();
            foreach (OracleParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (trans == null)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 对连接执行Transact-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
     
        public int ExecuteNonQuery(OracleTransaction trans, string commandText, params OracleParameter[] paras)
        {
            return ExecuteNonQuery(trans, commandText, false, paras);
        }
        #endregion

        #region ExecuteQueryScalar
        /// <summary>
        /// 执行查询，并返回查询结果集中的第一行第一列，忽略其它行或列
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
      
        public static object ExecuteQueryScalar(string commandText, bool isProcedure, params OracleParameter[] paras)
        {
            OracleConnection con = new OracleConnection(_connectionString);
            OracleCommand cmd = new OracleCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            foreach (OracleParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            try
            {
                con.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 执行查询，并返回查询结果集中的第一行第一列，忽略其它行或列
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
       
        public static object ExecuteQueryScalar(string commandText, params OracleParameter[] paras)
        {
            return ExecuteQueryScalar(commandText, false, paras);
        }

        /// <summary>
        /// 执行查询，并返回查询结果集中的第一行第一列，忽略其它行或列
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public static object ExecuteQueryScalar(OracleTransaction trans, string commandText, bool isProcedure, params OracleParameter[] paras)
        {
            OracleConnection con = trans.Connection;
            OracleCommand cmd = new OracleCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }
            cmd.Parameters.Clear();
            foreach (OracleParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (trans == null)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 执行查询，并返回查询结果集中的第一行第一列，忽略其它行或列
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
      
        public static object ExecuteQueryScalar(OracleTransaction trans, string commandText, params OracleParameter[] paras)
        {
            return ExecuteQueryScalar(trans, commandText, false, paras);
        }
        #endregion

        #region ExecuteDataReader
        /// <summary>
        /// 执行SQL，并返回结果集的只前进数据读取器
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
     
        public static OracleDataReader ExecuteDataReader(string commandText, bool isProcedure, params OracleParameter[] paras)
        {
            OracleConnection con = new OracleConnection(_connectionString);
            OracleCommand cmd = new OracleCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            foreach (OracleParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                con.Close();
                throw;
            }

        }

        /// <summary>
        /// 执行SQL，并返回结果集的只前进数据读取器
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
      
        public OracleDataReader ExecuteDataReader(string commandText, params OracleParameter[] paras)
        {
            return ExecuteDataReader(commandText, false, paras);
        }

        /// <summary>
        /// 执行SQL，并返回结果集的只前进数据读取器
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
     
        public static OracleDataReader ExecuteDataReader(OracleTransaction trans, string commandText, bool isProcedure, params OracleParameter[] paras)
        {
            OracleConnection con = trans.Connection;
            OracleCommand cmd = new OracleCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            cmd.Parameters.Clear();
            foreach (OracleParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                if (trans == null)
                {
                    con.Close();
                }
                throw;
            }

        }

        /// <summary>
        /// 执行SQL，并返回结果集的只前进数据读取器
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
       
        public static OracleDataReader ExecuteDataReader(OracleTransaction trans, string commandText, params OracleParameter[] paras)
        {
            return ExecuteDataReader(trans, commandText, false, paras);
        }
        #endregion

        #region ExecuteDataSet
        /// <summary>
        /// 执行SQL，并返回DataSet结果集
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
       
        public static DataSet ExecuteDataSet(string commandText, bool isProcedure, params OracleParameter[] paras)
        {
            OracleConnection con = new OracleConnection(_connectionString);
            OracleCommand cmd = new OracleCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            foreach (OracleParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            try
            {
                con.Open();
                OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 执行SQL，并返回DataSet结果集
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
      
        public static DataSet ExecuteDataSet(string commandText, params OracleParameter[] paras)
        {
            return ExecuteDataSet(commandText, false, paras);
        }

        /// <summary>
        /// 执行SQL，并返回DataSet结果集
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
       
        public static DataSet ExecuteDataSet(OracleTransaction trans, string commandText, bool isProcedure, params OracleParameter[] paras)
        {
            OracleConnection con = trans.Connection;
            OracleCommand cmd = new OracleCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            cmd.Parameters.Clear();
            foreach (OracleParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (trans == null)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 执行SQL，并返回DataSet结果集
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
      
        public static DataSet ExecuteDataSet(OracleTransaction trans, string commandText, params OracleParameter[] paras)
        {
            return ExecuteDataSet(trans, commandText, false, paras);
        }
        #endregion

        #region ExecuteDataTable

        /// <summary>
        /// 执行SQL，并返回DataTable结果集
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
       
        public static DataTable ExecuteDataTable(string commandText, bool isProcedure, params OracleParameter[] paras)
        {
            OracleConnection con = new OracleConnection(_connectionString);
            OracleCommand cmd = new OracleCommand(commandText, con);
            cmd.CommandTimeout = 1800;

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            foreach (OracleParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }

                OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                DataTable ds = new DataTable();
                adapter.Fill(ds);

                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 执行SQL，并返回DataTable结果集
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
       
        public static DataTable ExecuteDataTable(string commandText, params OracleParameter[] paras)
        {
            return ExecuteDataTable(commandText, false, paras);
        }

        /// <summary>
        /// 执行SQL，并返回DataTable结果集
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
    
        public static DataTable ExecuteDataTable(OracleTransaction trans, string commandText, bool isProcedure, params OracleParameter[] paras)
        {
            OracleConnection con = trans.Connection;
            OracleCommand cmd = new OracleCommand(commandText, con);
            cmd.CommandTimeout = 1800;

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            cmd.Parameters.Clear();
            foreach (OracleParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                DataTable ds = new DataTable();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (trans == null)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 执行SQL，并返回DataTable结果集
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SqLParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
       
        public static DataTable ExecuteDataTable(OracleTransaction trans, string commandText, params OracleParameter[] paras)
        {
            return ExecuteDataTable(trans, commandText, false, paras);
        }

        #endregion

        #region GetRowData
      
        public static DataRow GetRowData(string Sql)
        {
            DataRow DR = null;
            try
            {
                DataTable DT = ExecuteDataTable(Sql);
                if (DT.Rows.Count > 0)
                {
                    DR = DT.Rows[0];
                }
                return DR;
            }
            catch
            {
                return DR;
            }
        }
        #endregion
    }
}