Oracle数据库操作类
时间：2022-03-13 23:17
1 using System;
2 using System.Configuration;
3 using System.Data;
4 using System.Data.OracleClient;
5 using System.Collections;
6 using System.Collections.Generic;
7
8 namespace EIM.DBUtility
9 {
10
11 /// <summary>
12 /// A helper class used to execute queries against an Oracle database
13 /// </summary>
14 public abstract class OracleHelper
15 {
16
17 // Read the connection strings from the configuration file
18 public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.AppSettings["OraConnString1"];
19 public static readonly string ConnectionStringInventoryDistributedTransaction = ConfigurationManager.AppSettings["OraConnString2"];
20 public static readonly string ConnectionStringOrderDistributedTransaction = ConfigurationManager.AppSettings["OraConnString3"];
21 public static readonly string ConnectionStringProfile = ConfigurationManager.AppSettings["OraProfileConnString"];
22 public static readonly string ConnectionStringMembership = ConfigurationManager.AppSettings["OraMembershipConnString"];
23
24 //Create a hashtable for the parameter cached
25 private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
26
27 /// <summary>
28 /// Execute a database query which does not include a select
29 /// </summary>
30 /// <param name="connString">Connection string to database</param>
31 /// <param name="cmdType">Command type either stored procedure or SQL</param>
32 /// <param name="cmdText">Acutall SQL Command</param>
33 /// <param name="commandParameters">Parameters to bind to the command</param>
34 /// <returns></returns>
35 public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
36 {
37 // Create a new Oracle command
38 OracleCommand cmd = new OracleCommand();
39
40 //Create a connection
41 using (OracleConnection connection = new OracleConnection(connectionString))
42 {
43
44 //Prepare the command
45 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
46
47 //Execute the command
48 int val = cmd.ExecuteNonQuery();
49 connection.Close();
50 cmd.Parameters.Clear();
51 return val;
52 }
53 }
54 /// <summary>
55 /// 执行查询语句，返回DataSet
56 /// </summary>
57 /// <param name="SQLString">查询语句</param>
58 /// <returns>DataSet</returns>
59 public static DataSet Query(string connectionString, string SQLString)
60 {
61 using (OracleConnection connection = new OracleConnection(connectionString))
62 {
63 DataSet ds = new DataSet();
64 try
65 {
66 connection.Open();
67 OracleDataAdapter command = new OracleDataAdapter(SQLString, connection);
68 command.Fill(ds, "ds");
69 }
70 catch (OracleException ex)
71 {
72 throw new Exception(ex.Message);
73 }
74 finally
75 {
76 if (connection.State != ConnectionState.Closed)
77 {
78 connection.Close();
79 }
80 }
81 return ds;
82 }
83 }
84
85 public static DataSet Query(string connectionString, string SQLString, params OracleParameter[] cmdParms)
86 {
87 using (OracleConnection connection = new OracleConnection(connectionString))
88 {
89 OracleCommand cmd = new OracleCommand();
90 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
91 using (OracleDataAdapter da = new OracleDataAdapter(cmd))
92 {
93 DataSet ds = new DataSet();
94 try
95 {
96 da.Fill(ds, "ds");
97 cmd.Parameters.Clear();
98 }
99 catch (System.Data.OracleClient.OracleException ex)
100 {
101 throw new Exception(ex.Message);
102 }
103 finally
104 {
105 if (connection.State != ConnectionState.Closed)
106 {
107 connection.Close();
108 }
109 }
110 return ds;
111 }
112 }
113 }
114
115 private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string cmdText, OracleParameter[] cmdParms)
116 {
117 if (conn.State != ConnectionState.Open)
118 conn.Open();
119 cmd.Connection = conn;
120 cmd.CommandText = cmdText;
121 if (trans != null)
122 cmd.Transaction = trans;
123 cmd.CommandType = CommandType.Text;//cmdType;
124 if (cmdParms != null)
125 {
126 foreach (OracleParameter parameter in cmdParms)
127 {
128 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
129 (parameter.Value == null))
130 {
131 parameter.Value = DBNull.Value;
132 }
133 cmd.Parameters.Add(parameter);
134 }
135 }
136 }
137
138 /// <summary>
139 /// 执行一条计算查询结果语句，返回查询结果（object）。
140 /// </summary>
141 /// <param name="SQLString">计算查询结果语句</param>
142 /// <returns>查询结果（object）</returns>
143 public static object GetSingle(string connectionString, string SQLString)
144 {
145 using (OracleConnection connection = new OracleConnection(connectionString))
146 {
147 using (OracleCommand cmd = new OracleCommand(SQLString, connection))
148 {
149 try
150 {
151 connection.Open();
152 object obj = cmd.ExecuteScalar();
153 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
154 {
155 return null;
156 }
157 else
158 {
159 return obj;
160 }
161 }
162 catch (OracleException ex)
163 {
164 throw new Exception(ex.Message);
165 }
166 finally
167 {
168 if (connection.State != ConnectionState.Closed)
169 {
170 connection.Close();
171 }
172 }
173 }
174 }
175 }
176
177 public static bool Exists(string connectionString,string strOracle)
178 {
179 object obj = OracleHelper.GetSingle(connectionString,strOracle);
180 int cmdresult;
181 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
182 {
183 cmdresult = 0;
184 }
185 else
186 {
187 cmdresult = int.Parse(obj.ToString());
188 }
189 if (cmdresult == 0)
190 {
191 return false;
192 }
193 else
194 {
195 return true;
196 }
197 }
198
199 /// <summary>
200 /// Execute an OracleCommand (that returns no resultset) against an existing database transaction
201 /// using the provided parameters.
202 /// </summary>
203 /// <remarks>
204 /// e.g.:
205 /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
206 /// </remarks>
207 /// <param name="trans">an existing database transaction</param>
208 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
209 /// <param name="commandText">the stored procedure name or PL/SQL command</param>
210 /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
211 /// <returns>an int representing the number of rows affected by the command</returns>
212 public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
213 {
214 OracleCommand cmd = new OracleCommand();
215 PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
216 int val = cmd.ExecuteNonQuery();
217 cmd.Parameters.Clear();
218 return val;
219 }
220
221 /// <summary>
222 /// Execute an OracleCommand (that returns no resultset) against an existing database connection
223 /// using the provided parameters.
224 /// </summary>
225 /// <remarks>
226 /// e.g.:
227 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
228 /// </remarks>
229 /// <param name="conn">an existing database connection</param>
230 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
231 /// <param name="commandText">the stored procedure name or PL/SQL command</param>
232 /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
233 /// <returns>an int representing the number of rows affected by the command</returns>
234 public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
235 {
236
237 OracleCommand cmd = new OracleCommand();
238
239 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
240 int val = cmd.ExecuteNonQuery();
241 cmd.Parameters.Clear();
242 return val;
243 }
244 /// <summary>
245 /// Execute an OracleCommand (that returns no resultset) against an existing database connection
246 /// using the provided parameters.
247 /// </summary>
248 /// <remarks>
249 /// e.g.:
250 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
251 /// </remarks>
252 /// <param name="conn">an existing database connection</param>
253 /// <param name="commandText">the stored procedure name or PL/SQL command</param>
254 /// <returns>an int representing the number of rows affected by the command</returns>
255 public static int ExecuteNonQuery(string connectionString, string cmdText)
256 {
257
258 OracleCommand cmd = new OracleCommand();
259 OracleConnection connection = new OracleConnection(connectionString);
260 PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, null);
261 int val = cmd.ExecuteNonQuery();
262 cmd.Parameters.Clear();
263 return val;
264 }
265
266 /// <summary>
267 /// Execute a select query that will return a result set
268 /// </summary>
269 /// <param name="connString">Connection string</param>
270 //// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
271 /// <param name="commandText">the stored procedure name or PL/SQL command</param>
272 /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
273 /// <returns></returns>
274 public static OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
275 {
276 OracleCommand cmd = new OracleCommand();
277 OracleConnection conn = new OracleConnection(connectionString);
278 try
279 {
280 //Prepare the command to execute
281 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
282 OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
283 cmd.Parameters.Clear();
284 return rdr;
285 }
286 catch
287 {
288 conn.Close();
289 throw;
290 }
291 }
292
293 /// <summary>
294 /// Execute an OracleCommand that returns the first column of the first record against the database specified in the connection string
295 /// using the provided parameters.
296 /// </summary>
297 /// <remarks>
298 /// e.g.:
299 /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
300 /// </remarks>
301 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
302 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
303 /// <param name="commandText">the stored procedure name or PL/SQL command</param>
304 /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
305 /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
306 public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
307 {
308 OracleCommand cmd = new OracleCommand();
309
310 using (OracleConnection conn = new OracleConnection(connectionString))
311 {
312 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
313 object val = cmd.ExecuteScalar();
314 cmd.Parameters.Clear();
315 return val;
316 }
317 }
318
319 /// <summary>
320 /// Execute a OracleCommand (that returns a 1x1 resultset) against the specified SqlTransaction
321 /// using the provided parameters.
322 /// </summary>
323 /// <param name="transaction">A valid SqlTransaction</param>
324 /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
325 /// <param name="commandText">The stored procedure name or PL/SQL command</param>
326 /// <param name="commandParameters">An array of OracleParamters used to execute the command</param>
327 /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
328 public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
329 {
330 if (transaction == null)
331 throw new ArgumentNullException("transaction");
332 if (transaction != null && transaction.Connection == null)
333 throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
334
335 // Create a command and prepare it for execution
336 OracleCommand cmd = new OracleCommand();
337
338 PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
339
340 // Execute the command & return the results
341 object retval = cmd.ExecuteScalar();
342
343 // Detach the SqlParameters from the command object, so they can be used again
344 cmd.Parameters.Clear();
345 return retval;
346 }
347
348 /// <summary>
349 /// Execute an OracleCommand that returns the first column of the first record against an existing database connection
350 /// using the provided parameters.
351 /// </summary>
352 /// <remarks>
353 /// e.g.:
354 /// Object obj = ExecuteScalar(conn, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
355 /// </remarks>
356 /// <param name="conn">an existing database connection</param>
357 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
358 /// <param name="commandText">the stored procedure name or PL/SQL command</param>
359 /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
360 /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
361 public static object ExecuteScalar(OracleConnection connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
362 {
363 OracleCommand cmd = new OracleCommand();
364
365 PrepareCommand(cmd, connectionString, null, cmdType, cmdText, commandParameters);
366 object val = cmd.ExecuteScalar();
367 cmd.Parameters.Clear();
368 return val;
369 }
370
371 /// <summary>
372 /// Add a set of parameters to the cached
373 /// </summary>
374 /// <param name="cacheKey">Key value to look up the parameters</param>
375 /// <param name="commandParameters">Actual parameters to cached</param>
376 public static void CacheParameters(string cacheKey, params OracleParameter[] commandParameters)
377 {
378 parmCache[cacheKey] = commandParameters;
379 }
380
381 /// <summary>
382 /// Fetch parameters from the cache
383 /// </summary>
384 /// <param name="cacheKey">Key to look up the parameters</param>
385 /// <returns></returns>
386 public static OracleParameter[] GetCachedParameters(string cacheKey)
387 {
388 OracleParameter[] cachedParms = (OracleParameter[])parmCache[cacheKey];
389
390 if (cachedParms == null)
391 return null;
392
393 // If the parameters are in the cache
394 OracleParameter[] clonedParms = new OracleParameter[cachedParms.Length];
395
396 // return a copy of the parameters
397 for (int i = 0, j = cachedParms.Length; i < j; i++)
398 clonedParms[i] = (OracleParameter)((ICloneable)cachedParms[i]).Clone();
399
400 return clonedParms;
401 }
402 /// <summary>
403 /// Internal function to prepare a command for execution by the database
404 /// </summary>
405 /// <param name="cmd">Existing command object</param>
406 /// <param name="conn">Database connection object</param>
407 /// <param name="trans">Optional transaction object</param>
408 /// <param name="cmdType">Command type, e.g. stored procedure</param>
409 /// <param name="cmdText">Command test</param>
410 /// <param name="commandParameters">Parameters for the command</param>
411 private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
412 {
413
414 //Open the connection if required
415 if (conn.State != ConnectionState.Open)
416 conn.Open();
417
418 //Set up the command
419 cmd.Connection = conn;
420 cmd.CommandText = cmdText;
421 cmd.CommandType = cmdType;
422
423 //Bind it to the transaction if it exists
424 if (trans != null)
425 cmd.Transaction = trans;
426
427 // Bind the parameters passed in
428 if (commandParameters != null)
429 {
430 foreach (OracleParameter parm in commandParameters)
431 cmd.Parameters.Add(parm);
432 }
433 }
434
435 /// <summary>
436 /// Converter to use boolean data type with Oracle
437 /// </summary>
438 /// <param name="value">Value to convert</param>
439 /// <returns></returns>
440 public static string OraBit(bool value)
441 {
442 if (value)
443 return "Y";
444 else
445 return "N";
446 }
447
448 /// <summary>
449 /// Converter to use boolean data type with Oracle
450 /// </summary>
451 /// <param name="value">Value to convert</param>
452 /// <returns></returns>
453 public static bool OraBool(string value)
454 {
455 if (value.Equals("Y"))
456 return true;
457 else
458 return false;
459 }
460 /// <summary>
461 /// 执行多条SQL语句，实现数据库事务。
462 /// </summary>
463 /// <param name="SQLStringList">多条SQL语句</param>
464 public static bool ExecuteSqlTran(string conStr, List<CommandInfo> cmdList)
465 {
466 using (OracleConnection conn = new OracleConnection(conStr))
467 {
468 conn.Open();
469 OracleCommand cmd = new OracleCommand();
470 cmd.Connection = conn;
471 OracleTransaction tx = conn.BeginTransaction();
472 cmd.Transaction = tx;
473 try
474 {
475 foreach (CommandInfo c in cmdList)
476 {
477 if (!String.IsNullOrEmpty(c.CommandText))
478 {
479 PrepareCommand(cmd, conn, tx, CommandType.Text, c.CommandText, (OracleParameter[])c.Parameters);
480 if (c.EffentNextType == EffentNextType.WhenHaveContine || c.EffentNextType == EffentNextType.WhenNoHaveContine)
481 {
482 if (c.CommandText.ToLower().IndexOf("count(") == -1)
483 {
484 tx.Rollback();
485 throw new Exception("Oracle:违背要求" + c.CommandText + "必须符合select count(..的格式");
486 //return false;
487 }
488
489 object obj = cmd.ExecuteScalar();
490 bool isHave = false;
491 if (obj == null && obj == DBNull.Value)
492 {
493 isHave = false;
494 }
495 isHave = Convert.ToInt32(obj) > 0;
496
497 if (c.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
498 {
499 tx.Rollback();
500 throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须大于0");
501 //return false;
502 }
503 if (c.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
504 {
505 tx.Rollback();
506 throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须等于0");
507 //eturn false;
508 }
509 continue;
510 }
511 int res = cmd.ExecuteNonQuery();
512 if (c.EffentNextType == EffentNextType.ExcuteEffectRows && res == 0)
513 {
514 tx.Rollback();
515 throw new Exception("Oracle:违背要求" + c.CommandText + "必须有影像行");
516 // return false;
517 }
518 }
519 }
520 tx.Commit();
521 return true;
522 }
523 catch (System.Data.OracleClient.OracleException E)
524 {
525 tx.Rollback();
526 throw E;
527 }
528 finally
529 {
530 if (conn.State != ConnectionState.Closed)
531 {
532 conn.Close();
533 }
534 }
535 }
536 }
537 /// <summary>
538 /// 执行多条SQL语句，实现数据库事务。
539 /// </summary>
540 /// <param name="SQLStringList">多条SQL语句</param>
541 public static void ExecuteSqlTran(string conStr,List<String> SQLStringList)
542 {
543 using (OracleConnection conn = new OracleConnection(conStr))
544 {
545 conn.Open();
546 OracleCommand cmd = new OracleCommand();
547 cmd.Connection = conn;
548 OracleTransaction tx = conn.BeginTransaction();
549 cmd.Transaction = tx;
550 try
551 {
552 foreach (string sql in SQLStringList)
553 {
554 if (!String.IsNullOrEmpty(sql))
555 {
556 cmd.CommandText = sql;
557 cmd.ExecuteNonQuery();
558 }
559 }
560 tx.Commit();
561 }
562 catch (System.Data.OracleClient.OracleException E)
563 {
564 tx.Rollback();
565 throw new Exception(E.Message);
566 }
567 finally
568 {
569 if (conn.State != ConnectionState.Closed)
570 {
571 conn.Close();
572 }
573 }
574 }
575 }
576 }
577 }
View Code
