OracleHelper类

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

using Oracle.DataAccess.Client;

namespace SZMobile.Portal.DAL
{

    /// <summary>
    /// Accesses oracle database helper class.
    /// </summary>
    public class OracleHelper
    {

        /// <summary>
        /// Executes oracle command.
        /// </summary>
        /// <param name="oracle">oracle command.</param>
        /// <returns>The row count which has been affected.</returns>
        public static int ExecuteNonQuery(string commandText)
        {
            if (string.IsNullOrEmpty(commandText))
            {
                return -1;
            }
            return ExecuteNonQuery(commandText, null);
        }

        /// <summary>
        /// Executes oracle command.
        /// </summary>
        /// <param name="oracle">oracle command.</param>
        /// <param name="parameters">The OracleParameter object related to oracleCommand object.</param>
        /// <returns>The row count which has been affected.</returns>
        public static int ExecuteNonQuery(string commandText, OracleParameter[] parameters)
        {
            CommandType commandType = CommandType.Text;
            return ExecuteNonQuery(commandType, commandText, parameters);
        }

        /// <summary>
        /// Execute a database query which does not include a select
        /// </summary>       
        /// <param name="commandType">Command type either stored procedure or oracle</param>
        /// <param name="commandText">Acutall oracle Command</param>
        /// <param name="commandParameters">Parameters to bind to the command</param>
        /// <returns>The row count which has been affected</returns>
        public static int ExecuteNonQuery(CommandType commandType,string commandText, OracleParameter[] commandParameters)
        {
            int i = -3;
            if (string.IsNullOrEmpty(commandText) && (commandParameters == null))
            {
                return i;
            }
            try
            {  
                //Create a connection
                using (OracleConnection connection = ConnectionManager.OpenConnection())
                {
                    using (OracleCommand command = new OracleCommand())
                    {
                        //Prepare the command
                        PrepareCommand(command, connection,commandType, commandText, commandParameters);

                        //Execute the command
                        i = command.ExecuteNonQuery();
                        command.Parameters.Clear();
                        return i;
                    }
                }
            }
            catch (OracleException ex)
            {
                throw ex;
            }
            finally
            {
                ConnectionManager.CloseConnection();           
            }
        }

        /// <summary>
        /// Execute a select query that will return a result set
        /// </summary>       
        /// <param name="commandType">the CommandType (stored procedure, text)</param>
        /// <param name="commandText">the stored procedure name or PL/oracle command</param>
        /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
        /// <returns>retrurn a oracleDataReader if successful </returns>
        public static OracleDataReader ExecuteReader(CommandType commandType, string commandText, OracleParameter[] commandParameters)
        {
            try
            {
                //Create a connection
                using (OracleConnection connection = ConnectionManager.OpenConnection())
                {
                    using (OracleCommand command = new OracleCommand())
                    {
                        //Prepare the command
                        PrepareCommand(command, connection, commandType, commandText, commandParameters);

                        //Execute the reader
                        using (OracleDataReader oracleDataReader = command.ExecuteReader())
                        {
                            command.Parameters.Clear();
                            return oracleDataReader;
                        }
                    }
                }
            }
            catch (OracleException ex)
            {
                throw ex;
            }
            finally
            {
                ConnectionManager.CloseConnection();
            }
        }

        /// <summary>
        /// Executes oracle command and returns DataSet object.
        /// </summary>
        /// <param name="oracle">oracle command.</param>
        /// <returns>The result stored in DataSet object.</returns>
        public static DataSet ExecuteDataSet(string commandText)
        {
            if (string.IsNullOrEmpty(commandText))
            {
                return null;
            }
            return ExecuteDataSet(commandText, null);
        }

        /// <summary>
        /// Executes oracle command and returns dataset object.
        /// </summary>
        /// <param name="oracle">oracle command.</param>
        /// <param name="parameters">oracleParameters related to oraclecommand object.</param>
        /// <returns>The result stored in DataSet object.</returns>
        public static DataSet ExecuteDataSet(string commandText, params OracleParameter[] parameters)
        {
            if (string.IsNullOrEmpty(commandText) && (parameters == null))
            {
                return null;
            }
            return ExecuteDataSet(CommandType.Text, commandText, parameters);
        }

        /// <summary>
        /// Executes oracle command and return DataSet object.
        /// </summary>
        /// <param name="commandText">oracleCommand text.</param>
        /// <param name="commandType">oracleCommand type.</param>
        /// <param name="parameters">oracleParameters related to oraclecommand object.</param>
        /// <returns>DataSet object.</returns>
        public static DataSet ExecuteDataSet(CommandType commandType, string commandText, params OracleParameter[] parameters)
        {
            try
            {
                using (OracleConnection connection = ConnectionManager.OpenConnection())
                {
                    using (OracleCommand command = new OracleCommand())
                    {

                        DataSet dataSet = new DataSet();
                        PrepareCommand(command, connection, commandType, commandText, parameters);
                        using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                        {
                            adapter.Fill(dataSet);
                            command.Parameters.Clear();
                            return dataSet;
                        }
                    }
                }
            }
            catch (OracleException ex)
            {
                throw ex;
            }
            finally
            {
                ConnectionManager.CloseConnection();
            }
        }

        /// <summary>
        /// Prepare Input type parameter for oracle command.
        /// </summary>
        /// <param name="paraName">The parameter's name.</param>
        /// <param name="dbType">The parameter's data type.</param>
        /// <param name="size">The parameter's size.</param>
        /// <param name="value">The parameter's default value.</param>
        /// <returns>Returns the Input type parameter.</returns>
        public static OracleParameter MakeParameter(string paraName, OracleDbType dbType, int size, object value)
        {
            OracleParameter parameter;
            if (size > 0)
            {
                parameter = new OracleParameter(paraName, dbType, size);
            }
            else
            {
                parameter = new OracleParameter(paraName, dbType);
            }

            if (null != value)
            {
                parameter.Value = value;
            }
            return parameter;
        }

        /// <summary>
        /// Prepares works before execution the command.
        /// </summary>
        /// <param name="command">The oracleCommand object.</param>
        /// <param name="connection">The oracleConnection object.</param>
        /// <param name="transaction">The oracleTransaction </param>
        /// <param name="commandType">The CommandType object.</param>
        /// <param name="commandText">The command text.</param>
        /// <param name="commandParameters">The command parameters related to the command.</param>
        public static void PrepareCommand(OracleCommand command, OracleConnection connection,CommandType commandType, string commandText, OracleParameter[] commandParameters)
        {
            if (command == null)
            {
                throw new ArgumentNullException();
            }

            if ((commandText == null) || (commandText.Length == 0))
            {
                throw new ArgumentNullException();
            }

            if (null != connection)
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
            }
            else
            {
                throw new ArgumentNullException();
            }
            command.Connection = connection;
            command.CommandText = commandText;           
            command.CommandType = commandType;
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
        }

        /// <summary>
        /// Attaches oracle parameters object to oraclecommand object.
        /// </summary>
        /// <param name="command">oracle Command Object.</param>
        /// <param name="commandParameters">oracle parameters relates to the oracleCommand object.</param>
        public static void AttachParameters(OracleCommand command, OracleParameter[] commandParameters)
        {
            if (null == command)
            {
                throw new ArgumentNullException();
            }
            if (null != commandParameters)
            {
                foreach (OracleParameter parameter in commandParameters)
                {
                    if (null != parameter)
                    {
                        if (parameter.Value == null)
                        {
                            parameter.Value = DBNull.Value;
                        }
                        command.Parameters.Add(parameter);
                    }
                }
            }
        }

        /// <summary>
        /// Executes the procedure.
        /// </summary>
        /// <param name="name">The procedure name.</param>
        /// <param name="parameters">The parameters needs in the procedure.</param>
        /// <returns>Returns the lines affected.</returns>
        public static int RunProcedure(string name, OracleParameter[] parameters)
        {
            return ExecuteNonQuery(CommandType.StoredProcedure, name, parameters);
        }                   
       
       

    }
}