位置:首页 > 软件编程 > C#笔记
C# 浅析Oracle批量写入数据用法示例
日期:2023-01-04 人气:

大家好,对C# 浅析Oracle批量写入数据用法示例感兴趣的小伙伴,下面一起跟随三零脚本的小编来看看C# 浅析Oracle批量写入数据用法示例的例子吧。

往数据库批量写入数据,这个功能使用频率相对还是比较高的,特别是在做一些导入等功能的时候。net的程序大部分都是使用的sqlserver或者mysql数据库,oracle相对少一些。不过说到Oracle批量写入数据,我只想吐槽一句: .Net苦Oracle久矣。

由于一直使用的是.Net Framework,所以我感觉Oracle在批量写入这一块很不友好。之前有使用过两种方式,但是弊端太明显。分别是: OracleDataAdapter.Update(DataTable dataTable)和Oracle.DataAccess.Client下的OracleBulkCopy,以下简单说下:

第一种感觉就是只是提供了一个批量提交的方式,在效率方面,并没有什么提升;

第二种对Oracle环境配置的什么的有要求,所以我在使用的时候,把类似精简oracle的一些文件放了进去(可能是这个原因),在初始化和Open的时候依然会卡顿一下。即便如此,这个方法依然很快。但是弊端基本无解,如非无奈,尽量不要使用(但是我们确实是无奈之举,所以依然使用了一段时间,并采用了以下方式尽量避免这个问题,采用的方式是:批量写入一个无主键的临时表,然后把这个临时表在业务、事务中使用)。

弊端1:没有事务,只有一个内部事务(UseInternalTransaction),单纯的用来保证此次提交数据的一次性而已。

弊端2:会破坏主键,即便数据主键重复依然可以写入成功。导致表结构混乱,引发一系列问题!!!

之前有在SqlSugar中看到Oracle的批量提交,他在备注有表明以上弊端,但是奇怪的是他同时标注了只支持.Net Core,但其实.Net Framework也是可以用。而且我没明白既然只支持.Net Core,为什么不用我下面要写的第三种方式

再然后偶尔在网上发现了第三种方式: .Net Core下,基于Oracle.ManagedDataAccess.Client中的ArrayBindCount,测试后发现,无上述弊端,效率比OracleBulkCopy更优秀。但是如果要继承到老项目中的话,建议写个插件或者Web Api来处理

/**
 * Oracle批量写入数据
 * @arrange (三零.脚本) www.q3060.com
 **/
//OracleDataAdapter.Update(DataTable dataTable)方式
using System.Data.OracleClient;
public static int BulkCopy(DataTable dataTable) {
	int result = 0;


	List<string> sql_column = new List<string>();
	List<string> sql_para = new List<string>();

	List<OracleParameter> paras = new List<OracleParameter>();
	foreach(DataColumn column in dataTable.Columns) {
		sql_column.Add(column.ColumnName);
		sql_para.Add(":" + column.ColumnName);

		OracleParameter para = new OracleParameter(column.ColumnName, ConvertOracleDbType(column.DataType));
		para.SourceColumn = column.ColumnName;
		paras.Add(para);
	}

	using(OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString)) {
		conn.Open();
		string sql = $"insert into {dataTable.TableName}({string.Join(",", sql_column)}) values ({string.Join(",", sql_para)})";
		OracleCommand cmd = new OracleCommand(sql, conn);
		cmd.Parameters.AddRange(paras.ToArray());
		OracleDataAdapter adapter = new OracleDataAdapter();
		adapter.InsertCommand = cmd;
		result = adapter.Update(dataTable);
		conn.Close();
	}
	return result;
}

public static OracleType ConvertOracleDbType(Type type) {
	switch(type.Name.ToLower()) {
		case "decimal":
		return OracleType.Number;
		case "string":
		return OracleType.VarChar;
		case "datetime":
		return OracleType.DateTime;
		default:
		return OracleType.VarChar;
	}
}

代码:

/**
 * Oracle批量写入数据
 * @arrange (三零.脚本) www.q3060.com
 **/
//OracleBulkCopy
using Oracle.DataAccess.Client;
public static int BulkCopy(DataTable dataTable) {

int result = 0;

using(OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString)) {
	conn.Open();
	OracleBulkCopy oracleBulkCopy = new OracleBulkCopy(conn, OracleBulkCopyOptions.UseInternalTransaction);
	oracleBulkCopy.DestinationTableName = dataTable.TableName;

	foreach(DataColumn column in dataTable.Columns) {
		oracleBulkCopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping(column.ColumnName, column.ColumnName));
	}

	oracleBulkCopy.WriteToServer(dataTable);
	conn.Close();
}
return result;
}

代码:

/**
 * Oracle批量写入数据
 * @arrange (三零.脚本) www.q3060.com
 **/
//ArrayBindCount
using Oracle.ManagedDataAccess.Client;
public static OracleDbType ConvertOracleDbType(Type type) {
	switch(type.Name.ToLower()) {
		case "decimal":
		return OracleDbType.Decimal;
		case "string":
		return OracleDbType.Varchar2;
		case "datetime":
		return OracleDbType.Date;
		default:
		return OracleDbType.Varchar2;
	}
}
public static dynamic InitList(Type type) {
	switch(type.Name.ToLower()) {
		case "decimal":
		return new List<decimal>();
		case "string":
		return new List<string>();
		case "datetime":
		return new List<DateTime>();
		default:
		return new List<string>();
	}
}
public static void AddValue(dynamic list, Type type, object value) {
	switch(type.Name.ToLower()) {
		case "decimal":
		list.Add(Convert.ToDecimal(value));
		break;
		case "string":
		list.Add(Convert.ToString(value));
		break;
		case "datetime":
		list.Add(Convert.ToDateTime(value));
		break;
		default:
		list.Add(Convert.ToString(value));
		break;
	}
}

public static int BulkCopy(DataTable dataTable) {

	string connStr = "";
	int result = 0;

	List<string> sql_column = new List<string>();
	List<string> sql_para = new List<string>();

	List<OracleParameter> paras = new List<OracleParameter>();
	foreach(DataColumn column in dataTable.Columns) {

		sql_column.Add(column.ColumnName);
		sql_para.Add(":" + column.ColumnName);

		dynamic list = InitList(column.DataType);

		foreach(DataRow dr in dataTable.Rows) {
			AddValue(list, column.DataType, dr[column]);
		}
		OracleParameter para = new OracleParameter(column.ColumnName, ConvertOracleDbType(column.DataType));
		para.Value = list.ToArray();
		paras.Add(para);
	}
	using(var connection = new OracleConnection(connStr)) {
		connection.Open();
		string sql = $"insert into {dataTable.TableName}({string.Join(",", sql_column)}) values ({string.Join(",", sql_para)})";
		OracleCommand cmd = new OracleCommand(sql, connection);
		cmd.Parameters.AddRange(paras.ToArray());
		cmd.ArrayBindCount = dataTable.Rows.Count;
		result = cmd.ExecuteNonQuery();
		connection.Close();
	}
	return result;
}

总结:

连接Oracle的字符串尽量写成Data Source=IP:Port/DB;User ID=USER;password=PWD;即便配置了tns文件。

上面的方式有做了一下效率比对(各个机器配置等不一致,仅供参考):    

测试数据为5万条,3列,第一种等了很久没写完,直接断掉了;第二种和第三种都是4秒多一点。

您可能感兴趣的文章