高手请进~~

Question

public void ExecuteSqlTran(string connectionString, string[] sql, CommandType cmdType)
        {
            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.CommandType = cmdType;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    foreach (string s in sql)
                    {
                        cmd.CommandText = s;
                        cmd.ExecuteNonQuery();
                    }
                    tx.Commit();
                }
                catch (System.Data.OracleClient.OracleException ex)
                {
                    tx.Rollback();
                    throw new Exception(ex.Message);
                }
            }
        }
这是个sqlhelper中的一个方法，这个方法可以执行多条sql语句或者存储过程，但是有个缺点，存储过程不能带有参数，请高手给改造一下~~

Answer 1

你在后面在传一个关于参数的数组啊，比如sqlparmatters[]aa

Answer 2

//try:
public void ExecuteSqlTran(string connectionString, string[] sql, CommandType cmdType, 
        OracleParameter[][] paras)
    {
        using (OracleConnection conn = new OracleConnection(connectionString))
        {
            conn.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandType = cmdType;
            OracleTransaction tx = conn.BeginTransaction();
            cmd.Transaction = tx;
            try
            {
                for (int i = 0; i < sql.Length; i++)
                {
                    cmd.CommandText = sql[i];
                    if (paras[i] != null && paras[i].Length != "")
                    {
                        cmd.Parameters.AddRange(paras[i]);
                    }
                    cmd.ExecuteNonQuery();
                }
                tx.Commit();
            }
            catch (System.Data.OracleClient.OracleException ex)
            {
                tx.Rollback();
                throw new Exception(ex.Message);
            }
        }
    }

Answer 3

//手误
if (paras[i] != null && paras[i].Length != "")
红色部分改为>0

Answer 4

是sqlserver的 
对不起 ，我的疏忽 ，弄成orcale的了 
应该是下面这个 ：public void ExecuteSqlTran(string connectionString, string[] sql, CommandType cmdType)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = cmdType;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    foreach (string s in sql)
                    {
                        cmd.CommandText = s;
                        cmd.ExecuteNonQuery();
                    }
                    tx.Commit();
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    tx.Rollback();
                    throw new Exception(ex.Message);
                }
            }
        }~~

Answer 5

 /// <summary>
        ///使用提供的参数，执行有结果集返回的数据库操作命令
        /// 并返回SqlDataReader对象
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="commandType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="commandText">存储过程名称或者T-SQL命令行<</param>
        /// <returns>返回SqlDataReader对象</returns>
        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionString);            //我们在这里使用一个 try/catch,因为如果PrepareCommand方法抛出一个异常，我们想在捕获代码里面关闭
            //connection连接对象，因为异常发生datareader将不会存在，所以commandBehaviour.CloseConnection
            //将不会执行。
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, null);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }
  /// <summary>
        /// 为即将执行准备一个命令
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <param name="conn">SqlConnection对象</param>
        /// <param name="trans">SqlTransaction对象</param>
        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="cmdText">存储过程名称或者T-SQL命令行, e.g. Select * from Products</param>
        /// <param name="cmdParms">SqlParameters to use in the command</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {            if (conn.State != ConnectionState.Open)
                conn.Open();            cmd.Connection = conn;
            cmd.CommandText = cmdText;            if (trans != null)
                cmd.Transaction = trans;            cmd.CommandType = cmdType;            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

Answer 6

谢谢11楼 ，我想要的是1无返回值，2可以执行多条sql语句或者存储过程，3，存储过程可以带参数，刚才卧贴出来的是没有参数的，请高手们给写出一个来，小弟谢谢啦~~

Answer 7

王SIR已经出答案！自己慢慢调试呗！