根据上篇的内容，简单测试了三种方式的对比，需要使用的，请自行根据需求优化。。。
上篇文字网址：https://www.cnblogs.com/ggll611928/p/17897005.html
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、运行结果
执行完后，数据库中该表的总条数
