Oracle批量数据导入

 

                //记录数

           int recc = models.Count;
            string[] PHONE = new string[recc];
            string[] STATUS = new string[recc];
            string[] PROVINCE = new string[recc];
            DateTime[] ORDERTIME = new DateTime[recc];
            // 为了传递参数,不可避免的要使用参数,下面会连续定义三个
            // 从名称可以直接看出每个参数的含义,不在每个解释了
            Oracle.DataAccess.Client.OracleParameter[] param = new Oracle.DataAccess.Client.OracleParameter[4];
            Oracle.DataAccess.Client.OracleParameter phoneParam =new Oracle.DataAccess.Client.OracleParameter("PHONE" ,Oracle.DataAccess.Client.OracleDbType.Varchar2);
            phoneParam.Direction = ParameterDirection.Input;
            phoneParam.Value = PHONE;
            param[0] = phoneParam;


            Oracle.DataAccess.Client.OracleParameter statusParam =new Oracle.DataAccess.Client.OracleParameter("STATUS" , Oracle.DataAccess.Client.OracleDbType.Varchar2);
            statusParam.Direction = ParameterDirection.Input;
            statusParam.Value = STATUS;
            param[1] = statusParam;

            Oracle.DataAccess.Client.OracleParameter provinceParam =

new Oracle.DataAccess.Client.OracleParameter("PROVINCE" , Oracle.DataAccess.Client.OracleDbType.Varchar2);
            provinceParam.Direction = ParameterDirection.Input;
            provinceParam.Value = STATUS;
            param[2] = provinceParam;

            Oracle.DataAccess.Client.OracleParameter ordertimeParam =

new Oracle.DataAccess.Client.OracleParameter("ORDERTIME" , Oracle.DataAccess.Client.OracleDbType.Date);
            ordertimeParam.Direction = ParameterDirection.Input;
            ordertimeParam.Value = ORDERTIME;
            param[3] = ordertimeParam;
            //在下面的循环中,先把数组定义好,而不是直接生成SQL
            for(int i = 0 ; i < recc ; i++)
            {
                PHONE[i] = models[i].PHONE;
                STATUS[i] = models[i].STATUS;
                PROVINCE[i] = models[i].PROVINCE;
                ORDERTIME[i] =DateTime.Parse(models[i].ORDERTIME);
            }
            DbHelperOra.ExecuteSqlBat(recc , strSql.ToString() , param);

 

         /// <summary>
        /// 批量插入数据
        /// </summary>
        /// <param name="recordCount">记录数</param>
        /// <param name="sql">sql</param>
        /// <param name="param">参数</param>
        public static void ExecuteSqlBat(int recordCount , string sql , params Oracle.DataAccess.Client.OracleParameter[] param)
        {
            Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connectionString);
            Oracle.DataAccess.Client.OracleCommand command = new Oracle.DataAccess.Client.OracleCommand();
            command.Connection = conn;
            conn.Open();
            Oracle.DataAccess.Client.OracleTransaction tx = conn.BeginTransaction();
            try
            {
                command.Transaction = tx;
                //这个参数需要指定每次批插入的记录数
                command.ArrayBindCount = recordCount;
                //用到的是数组,而不是单个的值,这就是它独特的地方
                command.CommandText = sql;

                for(int i = 0 ; i < param.Length ; i++)
                {
                    command.Parameters.Add(param[i]);
                }

                //这个调用将把参数数组传进SQL,同时写入数据库
                command.ExecuteNonQuery();
                tx.Commit();
            }
            catch(Exception ex)
            {
                tx.Rollback();
                throw ex;
            }
        }