oracle ODP.NET 批量插入或更新

使用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<stringobject[]> 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 }
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值