使用OracleDataAdapter和OracleBulkCopy解决大批量的数据处理

前言

在工作中往往会出现需要使用Excel来进行数据大批量的数据处理，而对于我们开发者来说，当数据量过大时处理Excel文件和将处理好的Excel文件导入数据库中需要耗费大量的时间，所以在开发过程中需要使用不同的方法来进行开发，让代码的执行时间尽可能的缩短。

问题的出现原因与对应的解决方案

当需要将一个含有大量数据的Excel文件解析并插入到数据库中，因为数据量的原因导致执行过于耗时，所以经过多方位搜寻和大佬同事的指引我选择通过以下三种方案来解决数据量过大的问题

1. 将解析好的Excel文件通过一个List集合保存起来，然后使用Dapper一条一条的插入到数据库中
2. 使用微软自己的工具OracleDataAdapter进行数据的处理
3. 使用OracleBulkCopy方法

对应的数据库格式和Excel文件模板

Oracle数据库
TESTPERSON表

NAME	PASSWORD	TIME
刘德华	123	2023/4/18 08:42:56

Excel文件中数据

郭富城	123
张学友	123
黎明	123

方案一

解析Excel文件，将处理好的文件存入List集合中

// 测试用的实体类
public class TestPerson(){
	private string name;
	private string password;
}

// 测试Service层
public string TestService(IFormFile file){
	// Excel文件的扩展名
	string fileExpand = file.FileName.Substring(file.FileName.LastIndexOf("."));
	
	IWorkbook workbook = null;
	// 根据不同的文件扩展名选择不同的工具类来解析Excel文件
	if(".XLSX".Equals(fileExpand.ToUpper())){
		workbook = new XSSFWorkBook(file.OpenReadStream());
	}else if(".XLS".Equals(fileExpand.ToUpper())){
		workbook = new HSSFWorkBook(file.OpenReadStream());
	}
	
	// 获取第0个工作簿
    ISheet sheet = workbook.GetSheetAt(0);
    int start_row = 0,len = sheet.LastRowNum;
    List<TestPerson> list = new List<TestPerson>();
    
    // 将数据解析后存入对象，再将对象存入集合
    while(start_row < len){
		TestPerson tp = new TestPerson();
		tp.name = sheet.GetRow(start_row).GetCell(0).ToString();
		tp.password = sheet.GetRow(start_row).GetCell(1).ToString();
		list.Add(tp);
		start_row++;
	}
}

方案的执行主要语句

// 执行循环插入
list.ForEach(item => {
	string insert = $@"insert into TESTPERSON values (:name,:password,sysdate)";
		
	_sqlDapper.ExcuteNonQuery(insert,new {name = item.name,password = item.password});
});

优劣
优点：编写过程简单，只需要进行使用简单的循环解析和插入就可以执行完成
缺点：因为使用了大量的循环和插入所以导致执行的时间超出预期，故不考虑此方法

方案二

1.解析Excel文件，将每一条Excel数据作为一个对象进行保存，再将每一个对象存入集合
2.将存了数据的集合转换成DataTable类型
3.使用OracleDataAdapter对转换后的DataTable类型的数据进行处理

// 将List集合转换成DataTable类型的方法（也可以直接将Excel文件解析成DataTable类型的数据）
public DataTable ConvertToDataTable<T>(IList<T> data)
{
	DataTable table = new DataTable();
    PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    foreach (PropertyInfo prop in props)
    {
		table.Columns.Add(prop.Name, prop.PropertyType);
    }
    foreach (T item in data)
    {
        DataRow row = table.NewRow();
    foreach (PropertyInfo prop in props)
    {
    	row[prop.Name] = prop.GetValue(item, null);
    }
    	table.Rows.Add(row);
    }
    return table;
}       

public OracleCommand GetOracleCommand(string cmd){
	/* 
		创建OracleConnection
		_connectionString表示是连接字符串
	*/
	OracleConnection oracleConn= new OracleConnection(_connectionString);
	oracleConn.Open();
	// 使用查询的文本和SqlConnection初始化SqlCommand类的新实例
	OracleCommand command = new OracleCommand(cmd,oracleConn);
	return command;
}

此方案是参考了该博客后修改而来
其中的第三种方法给我的启示

// 调用方法将集合转换成DataTable
DataTable dt = ConvertToDataTable<TestPerson>(list);
// 创建OracleDataAdapter对象
OracleDataAdapter adapter = new OracleDataAdapter();
// SQL语句
string insert = $@"insert into TESTPERSON values (:name,:password,sysdate)";

OracleCommand command = GetOracleCommand(insert);

adapter.InsertCommand = command;
// 获取或设置一个为文本类型的值，用来解释CommandText属性
adapter.InsertCommand.CommandType = CommandType.Text;
// 获取或设置要在数据源中执行的Transact-SQL语句表名或存储过程
adapter.InsertCommand.CommandText = insert;

// 对应的数据库字段映射
OracleParameter[] insertParams = {
	new OracleParameter(":name",OracleDbType.Varchar2,100,"NAME"),
	new OracleParameter(":password",OracleDbType.Varchar2,100,"PASSWORD")
}
adapter.InsertCommand.Parameters.AddRange(insertParams);
adapter.Update(dt);

在微软的官方文档中使用的是SQL Server，所以它的官方文档使用的是SqlDataAdapter，但是如果使用Oracle可以使用OracleDataAdapter，两者没有什么区别，所使用的API也大多一样。
以下介绍参考于微软官方
OracleDataAdapter:
是作为Oracle和DataSet之间的适配器，用于获取和存储资料。
OracleDataAdapter.InserCommand属性
获取或设置一个Transact-SQL语句的存储过程，以在数据源中插入新纪录

OracleCommand:
表示对Oracle数据库执行的一个Transact-SQL语句或存储过程

优劣
优点：相较于方案一，得到了很大的速度提升。并且是使用的微软自带的原生API进行编译，使用起来较为方便
缺点：暂时未发现，后续如有其他的问题出现再补充

方案三(推荐)

前两个步骤基本和方案二相同，只是在后续的数据源数据处理的时候使用的是OralceBulkCopy

public OracleBulkCopy GetBulkCopy(){
	OracleConnection oracleConn = new OracleConnection(_connection);
	oracleConn.Open();
	OracleBulk bulkCopy = new OracleBulkCopy(oracleConn);
	return bulkCopy;
}

OracleBulkCopy bulkCopy = _sqlDapper.GetBulkCopy();
// 设置目标表的名称 如果未提供任何值则为null
bulkCopy.DestinattionTableName = "TESTPERSON"
// 每次执行的行数
bulkCopy.BatchSize = 1000;
// 将dt中的所有数据复制到bulkCopy对象的DestinattionTableName属性指定的目标表中
bulkCopy.WriteToServer(dt);

OracleBulkCopy可以用于将数据从一个表移动另一个表，无论是在单个服务器上还是在服务器之间，数据源不限于Oralce；可以使用任何数据源，只要数据可以加载到DataTable实例或使用实例读取IDataReader

如果在DestinationTableName调用时WriteToServer尚未设置，则会引发一个 ArgumentNullException。如果在 DestinationTableName 操作运行时修改 WriteToServer ，则更改不会影响当前操作。 下次调用方法时WriteToServer，将使用新DestinationTableName值

当处理完 BatchSize 行或没有更多行要发送到目标数据源时，即表示已完成。
零 (默认) 表示每个 WriteToServer 操作都是单个处理

注意
在使用方案二和三时，DataTable中的数据要和数据库中表的字段相对应，数据类型也要对应

处理大批的数据还是推荐使用方案三，因为相对其他两个方案更直接，不需要对数据库做过多的操作。
在效率上：三 > 二 > 一