.net批量插入到oracle数据库--三种方式对比，亲测

根据上篇的内容，简单测试了三种方式的对比，需要使用的，请自行根据需求优化。。。

上篇文字网址：https://www.cnblogs.com/ggll611928/p/17897005.html

 

三种方式：

1、常规方式，通过sql语句循环执行 

2、使用OracleBulkCopy

3、使用ArrayBind

 

后面：增加了扩展，使用存储过程+  ArrayBind

具体步骤如下：

 

1、创建测试表

CREATE TABLE T_TEST
(
  ID      NUMBER(9) not null,
  NAME    NVARCHAR2(30) not null,
  AGE     NUMBER(2),
  CREATEDATE DATE,
  REMARK  NVARCHAR2(50)
);
COMMENT ON COLUMN T_TEST.ID IS '测试编号';
COMMENT ON COLUMN T_TEST.NAME IS '测试姓名';
COMMENT ON COLUMN T_TEST.AGE IS '创建时间';
COMMENT ON COLUMN T_TEST.CREATEDATE IS '测试年龄';
COMMENT ON COLUMN T_TEST.REMARK IS '测试描述';

ALTER TABLE T_TEST ADD CONSTRAINT PK_T_TEST PRIMARY KEY (ID);

 

2、创建数据源

使用DataTable，模拟数据源

        /// <summary>
        /// 获取测试数据源
        /// </summary>
        /// <returns></returns>
        public DataTable GetTestTable(int type)
        {
            //创建数据源
            DataTable dt = new DataTable("t_test");
            dt.Columns.Add("id", typeof(int));
            dt.Columns.Add("name", typeof(string));
            dt.Columns.Add("age", typeof(int));
            dt.Columns.Add("createdate", typeof(DateTime));
             
            int i, k;
            if (type == 1)
            {
                i = 1;
                k = 100000;
            }
            else if (type == 2)
            {
                i = 100001;
                k = 200000;
            }
            else
            {
                i = 200001;
                k = 300000;
            }

            //添加数据到 DataTable  
            for (; i <= k; i++)
            { 
                DataRow row = dt.NewRow();
                row["id"] = i;
                row["name"] = i+ "-" + i;
                row["age"] = 18;
                row["createdate"] = DateTime.Now;
                dt.Rows.Add(row);
            }
            return dt;
        }

 

3、编写三种方式

3.1 方式一：常规方式

        /// <summary>
        /// 批量处理插入数据，使用常规方式
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <returns></returns>
        public int Insert(DataTable dt)
        {
            int count = 0;
            string conString = orcHelper.GetConn();
            using (OracleConnection conn = new OracleConnection(conString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                using (OracleTransaction transaction = conn.BeginTransaction())
                {
                    using (OracleCommand cmd = conn.CreateCommand())
                    { 
                        cmd.Transaction = transaction;
                        string sql = @"insert into t_test(id, name, age, createdate) values(:id, :name, :age, :createdate)";
                        foreach (DataRow dw in dt.Rows)
                        {
                            OracleParameter[] parametersList = new OracleParameter[]
                            {
                                new OracleParameter(":id", int.Parse(dw["id"].ToString())),
                                new OracleParameter(":name", dw["name"].ToString()),
                                new OracleParameter(":age",int.Parse(dw["age"].ToString())),
                                new OracleParameter(":createdate",DateTime.Parse(dw["createdate"].ToString())),
                            };
                            cmd.CommandText = sql;
                            cmd.CommandType = CommandType.Text;
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddRange(parametersList);

                            try
                            {
                                count = cmd.ExecuteNonQuery();
                                if (count < 0) { 
                                    transaction.Rollback();
                                    return count;
                                }
                            }
                            catch (Exception)
                            {
                                transaction.Rollback();
                                return count;
                            }
                        }
                        transaction.Commit();
                        return count;
                    }
                }
            }
        }

 

3.2 方式二：使用OracleBulkCopy

        /// <summary>
        /// 批量处理插入数据，使用OracleBulkCopy
        /// </summary>
        /// <param name="dt">数据源</param> 
        public bool InsertOracleBulkCopy(DataTable dt)
        { 
            string conString = orcHelper.GetConn();
            using (OracleConnection conn = new OracleConnection(conString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                using (OracleTransaction transaction = conn.BeginTransaction())
                {
                    //创建 OracleBulkCopy 对象，并指定数据库连接信息 
                    using (OracleBulkCopy bulkCopy = new OracleBulkCopy(conn))
                    { 
                        //数据库表名称
                        bulkCopy.DestinationTableName = dt.TableName;      
                        //指定批量插入的行数 
                        bulkCopy.BatchSize = dt.Rows.Count;             

                        //指定 DataTable 和数据表的列名映射关系
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            bulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                        }
                        try
                        {  
                            //将数据源添加到 OracleBulkCopy 对象中
                            bulkCopy.WriteToServer(dt);              
                            transaction.Commit();
                            return true; 
                        }
                        catch (Exception)
                        {
                            transaction.Rollback();
                            return false; 
                        }
                    }
                } 
            } 
        }

 

3.3 方式三：使用ArrayBind

        /// <summary>
        /// 批量处理插入数据，使用ArrayBind
        /// <param name="dt">数据源</param>
        /// </summary>
        public int InsertArrayBind(DataTable dt)
        {
            string conString = orcHelper.GetConn();
            using (OracleConnection conn = new OracleConnection(conString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                using (OracleTransaction transaction = conn.BeginTransaction())
                {
                    int recordCount = dt.Rows.Count, i = 0, count = 0;

                    using (OracleCommand cmd = conn.CreateCommand())
                    {
                        cmd.Transaction = transaction;
                        cmd.CommandText = "insert into t_test(id, name, age, createdate) values(:id, :name, :age, :createdate)";
                        //指定单次需要处理的条数
                        cmd.ArrayBindCount = recordCount;
                        int[] p_col1 = new int[recordCount];
                        string[] p_col2 = new string[recordCount];
                        int[] p_col3 = new int[recordCount];
                        DateTime[] p_col4 = new DateTime[recordCount];

                        cmd.Parameters.Add(new OracleParameter("id", OracleDbType.Int32, p_col1, ParameterDirection.Input));
                        cmd.Parameters.Add(new OracleParameter("name", OracleDbType.Varchar2, p_col2, ParameterDirection.Input));
                        cmd.Parameters.Add(new OracleParameter("age", OracleDbType.Int32, p_col3, ParameterDirection.Input));
                        cmd.Parameters.Add(new OracleParameter("createdate", OracleDbType.Date, p_col4, ParameterDirection.Input));

                        foreach (DataRow dr in dt.Rows)
                        {
                            p_col1[i] = Convert.ToInt32(dr["id"].ToString());
                            p_col2[i] = dr["name"].ToString();
                            p_col3[i] = Convert.ToInt32(dr["age"].ToString());
                            p_col4[i] = Convert.ToDateTime(dr["createdate"].ToString());
                            i++;
                        }

                        try
                        {  
                            count = cmd.ExecuteNonQuery();
                            if (count > 0) { transaction.Commit(); } 
                        }
                        catch (Exception)
                        {
                            transaction.Rollback(); 
                        } 
                        return count;
                    }
                }
            }
        }

 

4、调用三种方式，进行测试

只需调用下面的方法，即可测试三种方式的对比效果

        /// <summary>
        /// 测试批量插入
        /// </summary>
        private void TestInsert()
        { 
            int count;
            long totalTime;
            string message = "", result;
            var sw = new Stopwatch();
             
            #region 1、使用常规方式进行批量添加数据
            DataTable dataTable = sysService.GetTestTable(1);
            message += "三种方式对比，每种循环的总记录数：" + dataTable.Rows.Count + "\n";

            sw.Start();
            count = sysService.Insert(dataTable);
            sw.Stop();
            totalTime = sw.ElapsedMilliseconds; 
            result = count > 0 ? "成功" : "失败";
            message += "\n方式一：常规方式--" + result + "，  使用总时间：" + totalTime ;

            #endregion

            #region 2、使用OracleBulkCopy进行批量添加数据 
            dataTable = sysService.GetTestTable(2); 

            sw.Start();
            bool isOk = sysService.InsertOracleBulkCopy(dataTable);
            sw.Stop();
            totalTime = sw.ElapsedMilliseconds; 
            result = isOk ? "成功" : "失败";
            message += "\n方式二：OracleBulkCopy--" + result + "，  使用总时间：" + totalTime ;

            #endregion

            #region 3、使用ArrayBind进行批量添加数据
            dataTable = sysService.GetTestTable(3); 

            sw.Start();
            count = sysService.InsertArrayBind(dataTable);
            sw.Stop();
            totalTime = sw.ElapsedMilliseconds; 
            result = count>0 ? "成功" : "失败";
            message += "\n方式三：ArrayBind--" + result + "，  使用总时间：" + totalTime;

            #endregion
             
            MessageBox.Show(message, "GrowlMsg"); 
        }

 

5、运行结果

 

执行完后，数据库中该表的总条数

 

重点注意：数据的完整性及合理性，如主键数据必须唯一，日期必须是日期格式等。

 

6、扩展：存储过程

使用存储过程 + ArrayBind，批量修改数据

 

6.1 创建测试用的存储过程

主要实现修改数据

CREATE OR REPLACE PROCEDURE p_test_yy( 
   p_Id    IN  t_test.ID%TYPE,
   p_Name  IN  t_test.NAME%type,
   p_ErrCode    OUT NUMBER,
   p_ErrText    OUT VARCHAR2
     ) AS
     v_cnt    NUMBER;
begin
   
       select count(*) into v_cnt from t_test where id = p_Id;

         if v_cnt is null or v_cnt = 0 then
                p_ErrCode := -1;
                p_ErrText :='编号：'||p_Id||'-信息不存在！';
                return;
         end if;
         
         -- 模拟错误数据
         if p_Id = 10 or p_Id = 11 then 
                p_ErrCode := -1;
              p_ErrText :='编号：'||p_Id||'-重复！';
                return;
         end if;
         
         update t_test set createdate=sysdate where id = p_Id;
          p_ErrCode := p_Id;
          p_ErrText := '编号：'||p_Id||'-修改日期成功！';
         commit;
             
    exception
  when others then
     p_ErrCode := -ABS(sqlcode); 
     P_ErrText := '发生错误，错误信息为：'||SUBSTR(SQLERRM, 1, 200);
     rollback;
         
end p_test_yy;

 

6.2 重要代码

        /// <summary>
        /// 批量处理数据，使用存储过程 + ArrayBind
        /// <param name="dt">数据源</param>
        /// </summary>
        public int InsertArrayBindProcedure(DataTable dt)
        {
            string conString = orcHelper.GetConn();
            using (OracleConnection conn = new OracleConnection(conString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                using (OracleTransaction transaction = conn.BeginTransaction())
                {
                    int recordCount = dt.Rows.Count, count = 0;

                    using (OracleCommand cmd = conn.CreateCommand())
                    {
                        cmd.Transaction = transaction;
                        cmd.CommandText = "p_test_yy";
                        cmd.BindByName = true;
                        cmd.CommandType = CommandType.StoredProcedure;

                        //指定单次需要处理的条数
                        cmd.ArrayBindCount = recordCount;
                        var columnId = dt.AsEnumerable().Select(row => row.Field<object>("id")).ToArray();
                        var columnName = dt.AsEnumerable().Select(row => row.Field<object>("name")).ToArray();

                        cmd.Parameters.Add(new OracleParameter("p_Id", OracleDbType.Int32, columnId, ParameterDirection.Input));
                        cmd.Parameters.Add(new OracleParameter("p_Name", OracleDbType.Varchar2, columnName, ParameterDirection.Input));
                        cmd.Parameters.Add(
                            new OracleParameter("p_ErrCode", OracleDbType.Int32, ParameterDirection.Output)
                            { ArrayBindSize = new int[] { recordCount } });
                        cmd.Parameters.Add(
                            new OracleParameter("p_ErrText", OracleDbType.Varchar2, ParameterDirection.Output)
                            { ArrayBindSize = new int[] { recordCount } });

                        try
                        {
                            count = cmd.ExecuteNonQuery();
                            //正常获取p_ErrCode的全部数据
                            object objCode = cmd.Parameters["p_ErrCode"].Value; 
                            OracleDecimal[] oDecimalCodes = (OracleDecimal[]) objCode;
                            //只能获取p_ErrText的第一条数据
                            object objText = cmd.Parameters["p_ErrText"].Value;
                            OracleString[] oDecimalTexts = (OracleString[])objText;

                            int codeCount = oDecimalCodes.AsEnumerable().ToList().Where(t => t < 1).Count();
                            if (codeCount > 0)
                            {
                                count = 0;
                                transaction.Rollback();
                                return count;
                            }

                            count = oDecimalCodes.Count();
                            transaction.Commit(); 
                        }
                        catch (Exception)
                        {
                            transaction.Rollback();
                        }
                        return count;
                    }
                }
            }
        }

 

6.3 调用

其中GetTestTable，在上面右介绍，此处不再说明

        /// <summary>
        /// 测试存储过程批量处理数据
        /// </summary>
        private void TestProcedure() {
            int count;
            long totalTime;
            string message = "", result;
            var sw = new Stopwatch();

            DataTable dataTable = sysService.GetTestTable(1);
            message += "循环的总记录数：" + dataTable.Rows.Count + "\n";

            sw.Start();
            count = sysService.InsertArrayBindProcedure(dataTable);
            sw.Stop();
            totalTime = sw.ElapsedMilliseconds;
            result = count > 0 ? "成功" : "失败";
            message += "\n测试存储过程批量处理数据--" + result + "，  使用总时间：" + totalTime;

            MessageBox.Show(message, "GrowlMsg");
        }

 

6.4 调试

根据下图，可以看到

存储过程返回的第一个参数p_ErrCode，可以正常获取全部数据

但是，第二个参数p_ErrText，只能获取第一条的数据

原因目前还没弄清除，有需求的自己研究。。。

故需要使用存储过程批量处理数据时，执行前一定确定数据的完整性，这样批量执行就不会出错！！！