1、首先在 Oracle 数据库中新建个表用于测试使用
create table table1
(
c_int int ,
c_date date ,
c_char varchar2(50)
);
2、C# 程序实现代码
代码参考自博文:https://www.cnblogs.com/isline/archive/2010/08/31/1813722.html
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApplication1
{
class Program
{
static void Main()
{
Console.WriteLine($"{DateTime.Now.ToString()} ----- 正在初始化中...");
var recordCount = 1000000; //批量插入100w条数据
using (var cmd = new OracleCommand())
{
cmd.CommandText = " insert into table1 (c_int, c_date, c_char) values (:c_int, :c_date, :c_char) ";
cmd.ArrayBindCount = recordCount; //指定单次需要处理的条数
Console.WriteLine($"{DateTime.Now.ToString()} ----- 正在定义参数...");
var c_int = new int[recordCount];
var c_date = new DateTime[recordCount];
var c_char = new string[recordCount];
cmd.Parameters.Add(new OracleParameter("c_int" , OracleDbType.Int32 , c_int , ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter("c_date", OracleDbType.Date , c_date, ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter("c_char", OracleDbType.Varchar2, c_char, ParameterDirection.Input));
Console.WriteLine($"{DateTime.Now.ToString()} ----- 正在组装参数数据...");
for (int i = 0; i < recordCount; i++)
{
c_int[i] = (i + 1);
c_date[i] = DateTime.Now;
c_char[i] = "批处理测试";
}
Console.WriteLine($"{DateTime.Now.ToString()} ----- 正在建立数据库连接...");
using (var con = new OracleConnection())
{
con.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.11)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=Test)));User ID=CRM;Password=123456;Persist Security Info=True;Pooling=false;Pooling=True;Min Pool Size=1;Max Pool Size=200;";
con.Open();
cmd.Connection = con;
Console.WriteLine($"{DateTime.Now.ToString()} ----- 正在执行批处理操作...");
var startTime = DateTime.Now;
var count = cmd.ExecuteNonQuery(); //执行批处理操作,并返回影响行数
var endTime = DateTime.Now;
Console.WriteLine($"{DateTime.Now.ToString()} ----- 执行完毕!测试 {recordCount} 条数据,影响 {count} 条、耗时 {(endTime - startTime).ToString("hh\\:mm\\:ss\\.fffffff")}");
}
}
Console.WriteLine($"{DateTime.Now.ToString()} ----- 按任意键结束。");
Console.ReadKey();
}
}
}
3、查看运行结果
PS:本次用于测试的 (数据库端) 计算机配置为 CPU i7-7代、RAM8GB、机械硬盘(非固态),要是能换上固态硬盘的话、那么速度上应该还会有很大的提升空间的! ^ v ^