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来处理

开发环境:.NET Framework4.5、.NET Core 3.1

开发工具: Visual Studio 2019

实现代码:

//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;            }        }
//OracleBulkCopyusing 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;        }
//ArrayBindCountusing 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秒多一点。