www.pudn.com > AlumniWeb.rar > OracleHelper.cs

using System; 
using System.Configuration; 
using System.Data; 
using System.Data.OracleClient; 
using System.Collections; 
 
namespace DBHelper 
{ 
	/// 
 
	/// Summary description for OracleHelper. 
	/// 
 
	public class OracleHelper 
	{ 
		/// 
 
		/// Execute a database query which does not include a select 
		/// 
 
		/// Connection string to database 
		/// Command type either stored procedure or SQL 
		/// Acutall SQL Command 
		/// Parameters to bind to the command 
		///  
		public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)  
		{ 
			 
			// Create a new Oracle command 
			OracleCommand cmd = new OracleCommand(); 
 
			//Create a connection 
			using (OracleConnection conn = new OracleConnection(connString))  
			{ 
				 
				//Prepare the command 
				PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 
				 
				//Execute the command 
				int val = cmd.ExecuteNonQuery(); 
				cmd.Parameters.Clear(); 
				return val; 
			} 
		} 
 
		/// 
 
		/// Execute an OracleCommand (that returns no resultset) against an existing database transaction  
		/// using the provided parameters. 
		/// 
 
		///  
		/// e.g.:   
		///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24)); 
		///  
		/// an existing database transaction 
		/// the CommandType (stored procedure, text, etc.) 
		/// the stored procedure name or PL/SQL command 
		/// an array of OracleParamters used to execute the command 
		/// an int representing the number of rows affected by the command 
		public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)  
		{ 
			OracleCommand cmd = new OracleCommand(); 
			PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms); 
			int val = cmd.ExecuteNonQuery(); 
			cmd.Parameters.Clear(); 
			return val; 
		} 
 
		/// 
 
		/// Execute an OracleCommand (that returns no resultset) against an existing database connection  
		/// using the provided parameters. 
		/// 
 
		///  
		/// e.g.:   
		///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24)); 
		///  
		/// an existing database connection 
		/// the CommandType (stored procedure, text, etc.) 
		/// the stored procedure name or PL/SQL command 
		/// an array of OracleParamters used to execute the command 
		/// an int representing the number of rows affected by the command 
		public static int ExecuteNonQuery(OracleConnection conn, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)  
		{ 
 
			OracleCommand cmd = new OracleCommand(); 
 
			PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 
			int val = cmd.ExecuteNonQuery(); 
			cmd.Parameters.Clear(); 
			return val; 
		} 
 
		/// 
 
		/// Execute a select query that will return a result set 
		/// 
 
		/// Connection string 
		//// the CommandType (stored procedure, text, etc.) 
		/// the stored procedure name or PL/SQL command 
		/// an array of OracleParamters used to execute the command 
		///  
		public static OracleDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)  
		{ 
			 
			//Create the command and connection 
			OracleCommand cmd = new OracleCommand(); 
			OracleConnection conn = new OracleConnection(connString); 
 
			try  
			{ 
				//Prepare the command to execute 
				PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 
				 
				//Execute the query, stating that the connection should close when the resulting datareader has been read 
				OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); 
				cmd.Parameters.Clear(); 
				return rdr; 
			 
			} 
			catch (Exception e)  
			{ 
 
				//If an error occurs close the connection as the reader will not be used and we expect it to close the connection 
				conn.Close(); 
				throw e; 
			} 
		} 
		 
		/// 
 
		/// Execute an OracleCommand that returns the first column of the first record against the database specified in the connection string  
		/// using the provided parameters. 
		/// 
 
		///  
		/// e.g.:   
		///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24)); 
		///  
		/// a valid connection string for a SqlConnection 
		/// the CommandType (stored procedure, text, etc.) 
		/// the stored procedure name or PL/SQL command 
		/// an array of OracleParamters used to execute the command 
		/// An object that should be converted to the expected type using Convert.To{Type} 
		public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)  
		{ 
			OracleCommand cmd = new OracleCommand(); 
 
			using (OracleConnection conn = new OracleConnection(connString))  
			{ 
				PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 
				object val = cmd.ExecuteScalar(); 
				cmd.Parameters.Clear(); 
				return val; 
			} 
		} 
 
		/// 
 
		/// Execute an OracleCommand that returns the first column of the first record against an existing database connection  
		/// using the provided parameters. 
		/// 
 
		///  
		/// e.g.:   
		///  Object obj = ExecuteScalar(conn, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24)); 
		///  
		/// an existing database connection 
		/// the CommandType (stored procedure, text, etc.) 
		/// the stored procedure name or PL/SQL command 
		/// an array of OracleParamters used to execute the command 
		/// An object that should be converted to the expected type using Convert.To{Type} 
		public static object ExecuteScalar(OracleConnection conn, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)  
		{ 
			 
			OracleCommand cmd = new OracleCommand(); 
 
			PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 
			object val = cmd.ExecuteScalar(); 
			cmd.Parameters.Clear(); 
			return val; 
		} 
 
		/// 
 
		/// Add a set of parameters to the cached 
		/// 
 
		/// Key value to look up the parameters 
		/// Actual parameters to cached 
		public static void CacheParameters(string cacheKey, params OracleParameter[] cmdParms)  
		{ 
			DBHelper.paramCache[cacheKey] = cmdParms; 
		} 
 
		/// 
 
		/// Fetch parameters from the cache 
		/// 
 
		/// Key to look up the parameters 
		///  
		public static OracleParameter[] GetCachedParameters(string cacheKey)  
		{ 
			OracleParameter[] cachedParms = (OracleParameter[])DBHelper.paramCache[cacheKey]; 
			 
			if (cachedParms == null) 
				return null; 
			 
			// If the parameters are in the cache 
			OracleParameter[] clonedParms = new OracleParameter[cachedParms.Length]; 
 
			// return a copy of the parameters 
			for (int i = 0, j = cachedParms.Length; i < j; i++) 
				clonedParms[i] = (OracleParameter)((ICloneable)cachedParms[i]).Clone(); 
 
			return clonedParms; 
		} 
 
		/// 
 
		/// Internal function to prepare a command for execution by the database 
		/// 
 
		/// Existing command object 
		/// Database connection object 
		/// Optional transaction object 
		/// Command type, e.g. stored procedure 
		/// Command test 
		/// Parameters for the command 
		private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] cmdParms)  
		{ 
			 
			//Open the connection if required 
			if (conn.State != ConnectionState.Open) 
				conn.Open(); 
 
			//Set up the command 
			cmd.Connection = conn; 
			cmd.CommandText = cmdText; 
			cmd.CommandType = cmdType; 
 
			//Bind it to the transaction if it exists 
			if (trans != null) 
				cmd.Transaction = trans; 
 
			// Bind the parameters passed in 
			if (cmdParms != null)  
			{ 
				foreach (OracleParameter parm in cmdParms) 
					cmd.Parameters.Add(parm); 
			} 
		} 
	} 
}