OracleBulkCopy可以非常高效的直接将DataTable插入数据库,不需要写insert语句。
但遇到了2个关键问题:
1. 插入数据库的时候,会自动将表设置为主键失效,解决办法是先插入临时表,再将临时表拷贝至正式表。
具体OracleBulkCopy实现代码如下:
public static void TestInsert(DataTable dataTable)
{
try
{
using (OracleConnection conn = new OracleConnection(GlobalData.ConnectionStr))
{
using (OracleCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "truncate table TESTTABLE_TEMP";
cmd.ExecuteNonQuery();
OracleBulkCopy bulkCopy = new OracleBulkCopy(GlobalData.ConnectionStr, OracleBulkCopyOptions.UseInternalTransaction);
bulkCopy.BulkCopyTimeout = 99999;
bulkCopy.DestinationTableName = "TESTTABLE_TEMP";
bulkCopy.BatchSize = dataTable.Rows.Count;
foreach (DataColumn col in dataTable.Columns)
{
if (col.ColumnName.Trim() == "")
{
continue;
}
bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
if (dataTable.Rows.Count != 0)
{
bulkCopy.WriteToServer(dataTable);
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into TESTTABLE t (pk_sysuid,sales_id,price,occur_time) select sys_guid(),sales_id,price,occur_time from TESTTABLE_TEMP t1";
int result = cmd.ExecuteNonQuery();
cmd.CommandText = "truncate table TESTTABLE_TEMP";
cmd.ExecuteNonQuery();
}
conn.Close();
bulkCopy.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
2. DataTable中DateTime格式的数据,需要转换成Oracle的Date类型,否则会报{"参数绑定无效\r\n参数名: System.DateTime"}。但DataTable的自动列转换类型的表达式不支持.Net Framework以外的类型(官网说明:https://docs.microsoft.com/en-us/dotnet/api/system.data.datacolumn.expression?view=netframework-4.8#System_Data_DataColumn_Expression),所以只能自己添加列,新建为OracleDate类型,再将原DateTime类的那一列删除。