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                     finally104                     {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 =http://www.mamicode.com/= null))130                     {131                         parameter.Value =http://www.mamicode.com/ 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                     try150                     {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                         else158                         {159                             return obj;160                         }161                     }162                     catch (OracleException ex)163                     {164                         throw new Exception(ex.Message);165                     }166                     finally167                     {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             else186             {187                 cmdresult = int.Parse(obj.ToString());188             }189             if (cmdresult == 0)190             {191                 return false;192             }193             else194             {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 set268         /// </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             try279             {280                 //Prepare the command to execute281                 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);                282                 OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);283                 cmd.Parameters.Clear();284                 return rdr;285             }286             catch287             {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 SqlTransaction321         ///    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 execution336             OracleCommand cmd = new OracleCommand();337 338             PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);339 340             // Execute the command & return    the    results341             object retval = cmd.ExecuteScalar();342 343             // Detach the SqlParameters    from the command object, so    they can be    used again344             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 cached373         /// </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 cache383         /// </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 cache394             OracleParameter[] clonedParms = new OracleParameter[cachedParms.Length];395 396             // return a copy of the parameters397             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 database404         /// </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 required415             if (conn.State != ConnectionState.Open)416                 conn.Open();417 418             //Set up the command419             cmd.Connection = conn;420             cmd.CommandText = cmdText;421             cmd.CommandType = cmdType;422 423             //Bind it to the transaction if it exists424             if (trans != null)425                 cmd.Transaction = trans;426 427             // Bind the parameters passed in428             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 Oracle437         /// </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             else445                 return "N";446         }447 448         /// <summary>449         /// Converter to use boolean data type with Oracle450         /// </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             else458                 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                 try474                 {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                 finally529                 {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                 try551                 {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                 finally568                 {569                     if (conn.State != ConnectionState.Closed)570                         {571                             conn.Close();572                         }573                 }574             }575         }       576     }577 }