using System;using System.Configuration;using System.Data;using System.Data.OracleClient;using System.Collections;using System.Data.OleDb;namespace DBUtility{    /// 
    /// A helper class used to execute queries against an Oracle database    /// 
    public abstract class OracleHelper    {        // Read the connection strings from the configuration file        public static readonly string connectionString = ConfigurationManager.AppSettings["OrdersDAL"];        //Create a hashtable for the parameter cached        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());        /// 
        /// 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 connectionString, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)        {            // Create a new Oracle command            OleDbCommand cmd = new OleDbCommand();            //Create a connection            using (OleDbConnection connection = new OleDbConnection(connectionString))            {                //Prepare the command                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);                //Execute the command                int val = cmd.ExecuteNonQuery();                cmd.Parameters.Clear();                return val;            }        }        /// 
        /// Execute an OleDbCommand (that returns no resultset) against an existing database transaction         /// using the provided parameters.        /// 
        /// 
     
              /// e.g.:          ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter(":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(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)        {            OleDbCommand cmd = new OleDbCommand();            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);            int val = cmd.ExecuteNonQuery();            cmd.Parameters.Clear();            return val;        }        /// 
        /// Execute an OleDbCommand (that returns no resultset) against an existing database connection         /// using the provided parameters.        /// 
        /// 
     
              /// e.g.:          ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter(":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(OleDbConnection connection, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)        {            OleDbCommand cmd = new OleDbCommand();            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);            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 OleDbDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)        {            //Create the command and connection            OleDbCommand cmd = new OleDbCommand();            OleDbConnection conn = new OleDbConnection(connectionString);            try            {                //Prepare the command to execute                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);                //Execute the query, stating that the connection should close when the resulting datareader has been read                OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);                cmd.Parameters.Clear();                return rdr;            }            catch            {                //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;            }        }        /// 
        /// Execute an OleDbCommand 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 OleDbParameter(":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 connectionString, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)        {            OleDbCommand cmd = new OleDbCommand();            using (OleDbConnection conn = new OleDbConnection(connectionString))            {                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);                object val = cmd.ExecuteScalar();                cmd.Parameters.Clear();                return val;            }        }        ///    
        ///    Execute    a OleDbCommand (that returns a 1x1 resultset)    against    the    specified SqlTransaction        ///    using the provided parameters.        ///    
        ///    A    valid SqlTransaction        ///    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 containing the value    in the 1x1 resultset generated by the command
             public static object ExecuteScalar(OleDbTransaction transaction, CommandType commandType, string commandText, params OleDbParameter[] commandParameters)        {            if (transaction == null)                throw new ArgumentNullException("transaction");            if (transaction != null && transaction.Connection == null)                throw new ArgumentException("The transaction was rollbacked    or commited, please    provide    an open    transaction.", "transaction");            // Create a    command    and    prepare    it for execution            OleDbCommand cmd = new OleDbCommand();            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);            // Execute the command & return    the    results            object retval = cmd.ExecuteScalar();            // Detach the SqlParameters    from the command object, so    they can be    used again            cmd.Parameters.Clear();            return retval;        }        /// 
        /// Execute an OleDbCommand 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 OleDbParameter(":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(OleDbConnection connectionString, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)        {            OleDbCommand cmd = new OleDbCommand();            PrepareCommand(cmd, connectionString, null, cmdType, cmdText, commandParameters);            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 OleDbParameter[] commandParameters)        {            parmCache[cacheKey] = commandParameters;        }        /// 
        /// Fetch parameters from the cache        /// 
        /// Key to look up the parameters        /// 
             public static  OleDbParameter [] GetCachedParameters(string cacheKey)        {            OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];            if (cachedParms == null)                return null;            // If the parameters are in the cache            OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];            // return a copy of the parameters            for (int i = 0, j = cachedParms.Length; i < j; i++)                clonedParms[i] = (OleDbParameter)((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(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] commandParameters)        {            //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 (commandParameters != null)            {                foreach (OleDbParameter parm in commandParameters)                    cmd.Parameters.Add(parm);            }        }        /// 
        /// Converter to use boolean data type with Oracle        /// 
        /// Value to convert        /// 
             public static string OraBit(bool value)        {            if (value)                return "Y";            else                return "N";        }        /// 
        /// Converter to use boolean data type with Oracle        /// 
        /// Value to convert        /// 
             public static bool OraBool(string value)        {            if (value.Equals("Y"))                return true;            else                return false;        }    }}