oracle ODP.NET 批量插入或更新

最新推荐文章于 2022-05-10 18:14:21 发布
fighter_yy
最新推荐文章于 2022-05-10 18:14:21 发布
阅读量2.2k
点赞数
分类专栏： oracle
oracle 专栏收录该内容
5 篇文章 0 订阅
订阅专栏
使用ADO.NET提供的功能访问Oracle库要进行批量数据的插入或者更新的时间太慢了，对于10W级别的数据基本上都在5分钟左右；然而利用ODP.Net本身提供的一种数组参数功能来实现批量操作, 结果整个插入或更新的时间大大缩减到5秒以下，同时还支持包含Blob数据的批量处理，其代码如下：
标签： Oracle C# Studio Windows
代码片段(1)[全屏查看所有代码]

1. [文件] code.cs ~ 7KB 下载(65) 跳至 [1] [全屏预览]

 
       001 ﻿/// <summary>
 
       002 ///  批量插入数据
 
       003 /// </summary>
 
       004 /// <param name="tableName">表名称</param>
 
       005 /// <param name="columnRowData">键-值存储的批量数据：键是列名称，值是该列对应的数据集合</param>
 
       006 /// <returns></returns>
 
       007 public int BatchInsert(string tableName, Dictionary<string,object[]> columnRowData)
 
       008 {
 
       009     if (string.IsNullOrEmpty(tableName))
 
       010     {
 
       011         throw new ArgumentNullException("tableName", "必须指定批量插入的表名称");
 
       012     }
 
       013  
 
       014     if (columnRowData == null || columnRowData.Count < 1)
 
       015     {
 
       016         throw new ArgumentException("必须指定批量插入的字段名称","columnRowData");
 
       017     }
 
       018  
 
       019     int iResult = 0;
 
       020     string[] dbColumns = columnRowData.Keys.ToArray();
 
       021     StringBuilder sbCmdText = new StringBuilder();
 
       022     if (columnRowData.Count > 0)
 
       023     {
 
       024         // 准备插入SQL
 
       025         sbCmdText.AppendFormat("INSERT INTO {1} (", tableName);
 
       026         sbCmdText.Append(string.Join(",", dbColumns));
 
       027         sbCmdText.Append(") VALUES (");
 
       028         sbCmdText.Append(":" + string.Join(", :", dbColumns));
 
       029         sbCmdText.Append(") ");
 
       030  
 
       031         using (OracleConnection conn = new OracleConnection("数据库连接字符信息"))
 
       032         {
 
       033             using (OracleCommand cmd = _oraDbConn.CreateCommand())
 
       034             {
 
       035                 // 绑定批处理的行数
 
       036                 cmd.ArrayBindCount = columnRowData.Values.First().Length; // 很重要
 
       037                 cmd.BindByName = true;
 
       038                 cmd.CommandType = CommandType.Text;
 
       039                 cmd.CommandText = sbCmdText.ToString();
 
       040                 cmd.CommandTimeout = 600; // 10分钟
 
       041                 // 创建参数
 
       042                 OracleParameter oraParam;
 
       043                 List<IDbDataParameter> cacher = new List<IDbDataParameter>();
 
       044                 OracleDbType dbType = OracleDbType.Object;
 
       045                 foreach (string colName in dbColumns)
 
       046                 {
 
       047                     dbType = this.GetOracleDbType(columnRowData[colName][0]);
 
       048                     oraParam = new OracleParameter(colName, dbType);
 
       049                     oraParam.Direction = ParameterDirection.Input;
 
       050                     oraParam.OracleDbTypeEx = dbType;
 
       051  
 
       052                     oraParam.Value = columnRowData[colName];
 
       053                     cmd.Parameters.Add(oraParam);
 
       054                 }
 
       055  
 
       056                 // 执行批处理
 
       057                 var trans  = conn.BeginTransaction();
 
       058                 try
 
       059                 {
 
       060                     cmd.Transaction = trans;
 
       061                     iResult = cmd.ExecuteNonQuery();
 
       062                     trans.Commit();
 
       063                 }
 
       064                 catch(Exception dbex)
 
       065                 {
 
       066                     trans.Rollback();
 
       067                     throw dbex;
 
       068                 }
 
       069             }
 
       070         }
 
       071     }
 
       072  
 
       073     return iResult;
 
       074 }
 
       075  
 
       076  
 
       077 ///  批量更新数据
 
       078 /// </summary>
 
       079 /// <param name="tableName">表名称</param>
 
       080 /// <param name="keyColumName">主键列名称</param>
 
       081 /// <param name="columnRowData">键-值存储的批量数据：键是列名称，值是该列对应的数据集合</param>
 
       082 /// <returns></returns>
 
       083 public int BatchUpdate(string tableName,string keyColumName, Dictionary<string, object[]> columnRowData)
 
       084 {
 
       085     if (string.IsNullOrEmpty(tableName))
 
       086     {
 
       087         throw new ArgumentNullException("tableName", "必须指定批量插入的表名称");
 
       088     }
 
       089  
 
       090     if (string.IsNullOrEmpty(tableName))
 
       091     {
 
       092         throw new ArgumentNullException("keyColumName", "必须指定批量插入表的主键列名称");
 
       093     }
 
       094  
 
       095     if (columnRowData == null || columnRowData.Count < 1)
 
       096     {
 
       097         throw new ArgumentException("必须指定批量插入的字段名称","columnRowData");
 
       098     }
 
       099  
 
       100     int iResult = 0;
 
       101     string[] dbColumns = columnRowData.Keys.ToArray();
 
       102     StringBuilder sbCmdText = new StringBuilder();
 
       103     if (columnRowData.Count > 0)
 
       104     {
 
       105         // 准备更新SQL
 
       106         sbCmdText.AppendFormat("update {0} set  ", tableName);
 
       107         foreach(var col in dbColumns)
 
       108         {
 
       109             if (keyColumName.Equals(col,StringComparison.OrdinalIgnoreCase))
 
       110             {
 
       111                 continue;
 
       112             }
 
       113             sbCmdText.AppendFormat("{0} = :{0} ,", col);
 
       114         }
 
       115         sbCmdText.Remove(sbCmdText.Length - 1, 1);
 
       116         sbCmdText.AppendFormat(" where {0} = :{0}",keyColumName);
 
       117         
 
       118         using (OracleConnection conn = new OracleConnection("数据库连接字符信息"))
 
       119         {
 
       120             using (OracleCommand cmd = _oraDbConn.CreateCommand())
 
       121             {
 
       122                 // 绑定批处理的行数
 
       123                 cmd.ArrayBindCount = columnRowData.Values.First().Length; // 很重要
 
       124                 cmd.BindByName = true;
 
       125                 cmd.CommandType = CommandType.Text;
 
       126                 cmd.CommandText = sbCmdText.ToString();
 
       127                 cmd.CommandTimeout = 600; // 10分钟
 
       128                 // 创建参数
 
       129                 OracleParameter oraParam;
 
       130                 List<IDbDataParameter> cacher = new List<IDbDataParameter>();
 
       131                 OracleDbType dbType = OracleDbType.Object;
 
       132                 foreach (string colName in dbColumns)
 
       133                 {
 
       134                     dbType = this.GetOracleDbType(columnRowData[colName][0]);
 
       135                     oraParam = new OracleParameter(colName, dbType);
 
       136                     oraParam.Direction = ParameterDirection.Input;
 
       137                     oraParam.OracleDbTypeEx = dbType;
 
       138  
 
       139                     oraParam.Value = columnRowData[colName];
 
       140                     cmd.Parameters.Add(oraParam);
 
       141                 }
 
       142  
 
       143                 // 执行批处理
 
       144                 var trans = conn.BeginTransaction();
 
       145                 try
 
       146                 {
 
       147                     cmd.Transaction = trans;
 
       148                     iResult = cmd.ExecuteNonQuery();
 
       149                     trans.Commit();
 
       150                 }
 
       151                 catch (Exception dbex)
 
       152                 {
 
       153                     trans.Rollback();
 
       154                     throw dbex;
 
       155                 }
 
       156             }
 
       157         }
 
       158     }
 
       159  
 
       160     return iResult;
 
       161 }
 
       162  
 
       163  
 
       164 /// <summary>
 
       165 ///  根据数据类型获取OracleDbType
 
       166 /// </summary>
 
       167 /// <param name="value"></param>
 
       168 /// <returns></returns>
 
       169 private OracleDbType GetOracleDbType(object value)
 
       170 {
 
       171     OracleDbType dataType = OracleDbType.Object;
 
       172     if (value is string)
 
       173     {
 
       174         dataType = OracleDbType.Varchar2;
 
       175     }
 
       176     else if (value is DateTime)
 
       177     {
 
       178         dataType = OracleDbType.TimeStamp;
 
       179     }
 
       180     else if (value is int || value is short)
 
       181     {
 
       182         dataType = OracleDbType.Int32;
 
       183     }
 
       184     else if (value is long)
 
       185     {
 
       186         dataType = OracleDbType.Int64;
 
       187     }
 
       188     else if (value is decimal || value is double)
 
       189     {
 
       190         dataType = OracleDbType.Decimal;
 
       191     }
 
       192     else if (value is Guid)
 
       193     {
 
       194         dataType = OracleDbType.Varchar2;
 
       195     }
 
       196     else if (value is bool || value is Boolean)
 
       197     {
 
       198         dataType = OracleDbType.Byte;
 
       199     }
 
       200     else if (value is byte[])
 
       201     {
 
       202         dataType = OracleDbType.Blob;
 
       203     }
 
       204     else if (value is char)
 
       205     {
 
       206         dataType = OracleDbType.Char;
 
       207     }
 
       208  
 
       209     return dataType;
 
       210 }