ODP方式，大批量数据写入ORACLE数据库

ODP_Inserter 2 { 3 /// <summary> 4 /// 数据库连接串 5 /// </summary> 6 private string strWMSConn = string.Empty; 7 8 public string StrWMSConn 9 { 10 get 11 { 12 if (strWMSConn == string.Empty) 13 { 14 return GetConnectionString(); 15 } 16 else 17 { 18 return strWMSConn; 19 } 20 } 21 } 22 23 /// <summary> 24 /// 构造函数 25 /// </summary> 26 public OracleAccessBatcher() 27 { 28 //加载数据库连接串 29 if (strWMSConn == string.Empty) 30 { 31 GetConnectionString(); 32 } 33 } 34 35 /// <summary> 36 /// 加载数据库连接串 37 /// </summary> 38 private string GetConnectionString() 39 { 40 System.Configuration.AppSettingsReader reader = new System.Configuration.AppSettingsReader(); 41 strWMSConn = reader.GetValue("B2BDataBase", typeof(string)).ToString(); 42 return strWMSConn; 43 } 44 45 /// <summary> 46 /// 批量插入数据 47 /// </summary> 48 /// <param name="tableName">目的表名称</param> 49 /// <param name="dataTable">数据源(列名与目的表一致)</param> 50 /// <returns></returns> 51 public void BatchInsert(string tableName, DataTable dataTable) 52 { 53 if (string.IsNullOrEmpty(tableName)) 54 { 55 throw new ArgumentNullException("tableName", "必须指定批量插入的表名称"); 56 } 57 58 if (dataTable == null || dataTable.Rows.Count < 1) 59 { 60 throw new ArgumentException("必须指定批量插入的数据源", "dataTable"); 61 } 62 63 using (OracleConnection conn = new OracleConnection(strWMSConn)) 64 { 65 try 66 { 67 conn.Open(); 68 69 using (OracleCommand cmd = conn.CreateCommand()) 70 { 71 // 绑定批处理的行数 72 cmd.ArrayBindCount = dataTable.Rows.Count; 73 cmd.BindByName = true; 74 cmd.CommandType = CommandType.Text; 75 cmd.CommandText = GenerateInsertSql(cmd, tableName, dataTable); 76 cmd.CommandTimeout = 600; // 10分钟 77 78 cmd.ExecuteNonQuery(); 79 } 80 } 81 catch (Exception exp) 82 { 83 throw exp; 84 } 85 finally 86 { 87 conn.Close(); 88 } 89 } 90 91 } 92 93 /// <summary> 94 /// 批量更新数据 95 /// </summary> 96 /// <param name="tableName">目的表名</param> 97 /// <param name="keyColumns">条件列名数组(值与目的表列名一致)</param> 98 /// <param name="dataTable">数据源(列名与目的表一致)</param> 99 /// <returns></returns> 100 public int BatchUpdate(string tableName, string[] keyColumns, DataTable dataTable) 101 { 102 // 检查输入 103 if (string.IsNullOrEmpty(tableName)) 104 { 105 throw new ArgumentNullException("tableName", "必须指定批量更新的表名称"); 106 } 107 108 if (keyColumns == null || keyColumns.Length == 0) 109 { 110 throw new ArgumentException("必须指定批量更新表的条件列数组", "keyColumns"); 111 } 112 113 if (dataTable == null || dataTable.Rows.Count < 1) 114 { 115 throw new ArgumentException("必须指定批量更新的数据源", "dataTable"); 116 } 117 118 // 无需更新 119 if (keyColumns.Length >= dataTable.Columns.Count) 120 { 121 throw new ArgumentException("目的表不存在需要更新的列名", "keyColumns&dataTable"); 122 } 123 124 // 条件列是否在表列名中 125 foreach (string colName in keyColumns) 126 { 127 if (!dataTable.Columns.Contains(colName)) 128 { 129 throw new ArgumentException("用于更新条件的列名不在目的表中", "dataTable"); 130 } 131 } 132 133 int iResult = 0; 134 using (OracleConnection conn = new OracleConnection(strWMSConn)) 135 { 136 try 137 { 138 conn.Open(); 139 140 using (OracleCommand cmd = conn.CreateCommand()) 141 { 142 // 绑定批处理的行数 143 cmd.ArrayBindCount = dataTable.Rows.Count; 144 cmd.BindByName = true; 145 cmd.CommandType = CommandType.Text; 146 cmd.CommandText = GenerateUpdateSql(cmd, tableName, keyColumns, dataTable); 147 cmd.CommandTimeout = 600; // 10分钟 148 149 iResult = cmd.ExecuteNonQuery(); 150 } 151 } 152 catch (Exception exp) 153 { 154 throw exp; 155 } 156 finally 157 { 158 conn.Close(); 159 } 160 } 161 162 return iResult; 163 } 164 165 /// <summary> 166 /// 批量删除 167 /// </summary> 168 /// <param name="tableName">目标表</param> 169 /// <param name="columnName">列名(与目的表列名一致)</param> 170 /// <param name="columnValue">列值</param> 171 public void BatchDelete(string tableName, string columnName, string columnValue) 172 { 173 // 检查输入 174 if (string.IsNullOrEmpty(tableName)) 175 { 176 throw new ArgumentNullException("tableName", "必须指定批量更新的表名称"); 177 } 178 179 if (string.IsNullOrEmpty(columnName)) 180 { 181 throw new ArgumentNullException("columnValue", "必须指定删除条件的列名"); 182 } 183 184 string strCmdText = string.Format("delete from {0} where {1} = ‘{2}‘", tableName, columnName, columnValue); 185 186 using (OracleConnection conn = new OracleConnection(strWMSConn)) 187 { 188 try 189 { 190 conn.Open(); 191 192 using (OracleCommand cmd = conn.CreateCommand()) 193 { 194 // 绑定批处理的行数 195 //cmd.ArrayBindCount = dataTable.Rows.Count; 196 cmd.BindByName = true; 197 cmd.CommandType = CommandType.Text; 198 cmd.CommandText = strCmdText; 199 cmd.CommandTimeout = 600; // 10分钟 200 201 cmd.ExecuteNonQuery(); 202 } 203 } 204 catch (Exception exp) 205 { 206 throw exp; 207 } 208 finally 209 { 210 conn.Close(); 211 } 212 } 213 } 214 215 /// <summary> 216 /// 生成插入数据的sql语句 217 /// </summary> 218 /// <param name="command">SQL命令</param> 219 /// <param name="tableName">目的表名称</param> 220 /// <param name="table">目的表数据</param> 221 /// <returns></returns> 222 private string GenerateInsertSql(OracleCommand command, string tableName, DataTable table) 223 { 224 int cols = table.Columns.Count; 225 int rows = table.Rows.Count; 226 227 StringBuilder names = new StringBuilder(); 228 StringBuilder values = new StringBuilder(); 229 230 for (int i = 0; i < cols; i++) 231 { 232 DataColumn column = table.Columns[i]; 233 OracleParameter param = new OracleParameter(column.ColumnName, this.GetOracleDbType(column.DataType)); 234 //OracleParameter param = new OracleParameter(column.ColumnName, OracleDbType.Varchar2); 235 236 string[] data = new string[rows]; 237 for (int j = 0; j < rows; j++) 238 { 239 data[j] = table.Rows[j][column.ColumnName].ToString().TrimEnd(); 240 } 241 242 param.Direction = ParameterDirection.Input; 243 param.Value = data; 244 command.Parameters.Add(param); 245 246 if (names.Length > 0) 247 { 248 names.Append(","); 249 values.Append(","); 250 } 251 names.AppendFormat("{0}", column.ColumnName); 252 values.AppendFormat("{0}{1}", ":", column.ColumnName); 253 } 254 return string.Format("INSERT INTO {0}({1}) VALUES ({2})", tableName, names, values); 255 } 256 257 /// <summary> 258 /// 生成更新数据的sql语句 259 /// </summary> 260 /// <param name="command"></param> 261 /// <param name="tableName"></param> 262 /// <param name="keyColumns"></param> 263 /// <param name="table"></param> 264 /// <returns></returns> 265 private string GenerateUpdateSql(OracleCommand command, string tableName, string[] keyColumns, DataTable table) 266 { 267 int cols = table.Columns.Count; 268 int rows = table.Rows.Count; 269 270 StringBuilder sets = new StringBuilder(); 271 StringBuilder wheres = new StringBuilder(); 272 273 for (int i = 0; i < cols; i++) 274 { 275 DataColumn column = table.Columns[i]; 276 277 // 是否为条件列 278 bool isCond = false; 279 foreach (string cod in keyColumns) 280 { 281 isCond = cod.Equals(column.ColumnName); 282 if (isCond) 283 { 284 break; 285 } 286 } 287 288 289 string[] data = new string[rows]; 290 for (int j = 0; j < rows; j++) 291 { 292 data[j] = table.Rows[j][column.ColumnName].ToString().TrimEnd(); 293 } 294 295 // 设定参数 296 OracleParameter param; 297 OracleDbType dbType = OracleDbType.Varchar2; 298 299 dbType = this.GetOracleDbType(column.DataType); 300 param = new OracleParameter(column.ColumnName, dbType); 301 param.Direction = ParameterDirection.Input; 302 param.Value = data; 303 command.Parameters.Add(param); 304 305 // 条件列 306 if (isCond) 307 { 308 if (wheres.Length > 0) 309 { 310 wheres.Append(" and "); 311 } 312 313 wheres.AppendFormat("{0} = :{0}", column.ColumnName); 314 } 315 else 316 { 317 if (sets.Length > 0) 318 { 319 sets.Append(","); 320 } 321 sets.AppendFormat("{0} = :{0}", column.ColumnName); 322 } 323 } 324 return string.Format("update {0} set {1} where {2}", tableName, sets, wheres); 325 } 326 327 /// <summary> 328 /// 根据数据类型获取OracleDbType 329 /// </summary> 330 /// <param name="value"></param> 331 /// <returns></returns> 332 private OracleDbType GetOracleDbType(object value) 333 { 334 //OracleDbType dataType = OracleDbType.Object; 335 OracleDbType dataType = OracleDbType.Varchar2; 336 337 if (value is string) 338 { 339 dataType = OracleDbType.Varchar2; 340 } 341 else if (value is DateTime) 342 { 343 dataType = OracleDbType.TimeStamp; 344 } 345 else if (value is int || value is short) 346 { 347 dataType = OracleDbType.Int32; 348 } 349 else if (value is long) 350 { 351 dataType = OracleDbType.Int64; 352 } 353 else if (value is decimal || value is double) 354 { 355 dataType = OracleDbType.Decimal; 356 } 357 else if (value is Guid) 358 { 359 dataType = OracleDbType.Varchar2; 360 } 361 else if (value is bool || value is Boolean) 362 { 363 dataType = OracleDbType.Byte; 364 } 365 else if (value is byte[]) 366 { 367 dataType = OracleDbType.Blob; 368 } 369 else if (value is char) 370 { 371 dataType = OracleDbType.Char; 372 } 373 374 return dataType; 375 } 376 377 /// <summary> 378 /// 执行SQL 379 /// </summary> 380 /// <param name="strSql"></param> 381 public void ExecuteSql(string strSql) 382 { 383 using (OracleConnection conn = new OracleConnection(strWMSConn)) 384 { 385 try 386 { 387 conn.Open(); 388 389 using (OracleCommand cmd = conn.CreateCommand()) 390 { 391 cmd.BindByName = true; 392 cmd.CommandType = CommandType.Text; 393 cmd.CommandText = strSql; 394 cmd.CommandTimeout = 600; 395 396 cmd.ExecuteNonQuery(); 397 } 398 } 399 catch (Exception exp) 400 { 401 throw exp; 402 } 403 finally 404 { 405 conn.Close(); 406 } 407 } 408 } 409 } View Code

 

调用：

 ODP_Inserter batchInsert = new ODP_Inserter();

batchInsert.BatchInsert("table_name", ConvertTable);//table_name为数据库表名称，ConvertTable为要写入的DataTable

使用的时候，注意DataTable的数据类型

 

ODP方式，大批量数据写入ORACLE数据库

标签：