根据上篇的内容，简单测试了三种方式的对比，需要使用的，请自行根据需求优化。。。
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、运行结果
执行完后，数据库中该表的总条数
