OracleDbOperation.cs

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

namespace AccessBlock
{
    public abstract class OracleDbOperation
    {
        protected CommandType ComdType;
        protected string connectionString = null;
        protected int commandTimeout = 0;
        protected OracleConnection connection = null;
        private static Regex regex = new Regex(@"\s* [a-zA-Z0-9._]+ \s* ( \{  .+ \} )+", RegexOptions.IgnorePatternWhitespace | RegexOptions.Compiled);

        public OracleDbOperation()
        {
            if (String.IsNullOrEmpty(connectionString))
                throw new ArgumentNullException("");

            if (connection == null)
            {
                OracleConnection conn = new OracleConnection(connectionString);
                this.connection = conn;
            }
        }

        #region Util Methods

        private void SetConnect()
        {
            if (connection.State == ConnectionState.Closed)
                connection.Open();
        }

        private void CloseConnect()
        {
            if (connection.State == ConnectionState.Open)
                connection.Open();
        }

        private CustomException ProcessException(OracleException e)
        {
            string msg = e.Message;
            if (regex.Match(e.Message).Success)
            {
                msg = e.Message;
            }
            else
            {
                msg = e.Message;
            }

            return new CustomException(msg, e);
        }

        private OracleCommand CreateCommand(string spName, IEnumerable<OracleParameter> parameters)
        {
            OracleCommand cmd = new OracleCommand(spName, connection);
            cmd.CommandType = this.ComdType;
            cmd.CommandTimeout = commandTimeout;

            if (parameters != null)
            {
                foreach (OracleParameter parameter in parameters)
                    cmd.Parameters.Add(parameter);
            }
            return cmd;
        }

        private DataTable CreateDataTable(string spName, IEnumerable<OracleParameter> parameters)
        {
            using (OracleCommand cmd = CreateCommand(spName, parameters))
            {
                using (OracleDataReader reader = cmd.ExecuteReader())
                {
                    DataTable dt = new DataTable();

                    int fieldCount = reader.FieldCount;
                    for (int i = 0; i < fieldCount; i++)
                    {
                        dt.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
                    }

                    while (reader.Read())
                    {
                        object[] values = new object[fieldCount];
                        reader.GetValues(values);
                        dt.Rows.Add(values);
                    }

                    return dt;
                }
            }
        }

        #endregion

        #region Operational Methods

        protected int ExecuteNonQuery(string spName, IEnumerable<OracleParameter> parameters)
        {
            SetConnect();
            using (OracleCommand cmd = CreateCommand(spName, parameters))
            {
                DateTime start = DateTime.Now;
                try
                {
                    return cmd.ExecuteNonQuery();
                }
                catch (OracleException e)
                {
                    throw ProcessException(e);
                }
                finally
                {
                    cmd.Parameters.Clear();
                }
            }
        }

        protected int ExecuteNonQuery(string spName)
        {
            return ExecuteNonQuery(spName, null);
        }

        protected object ExecuteScalar(string spName)
        {
            return ExecuteScalar(spName, null);
        }

        protected object ExecuteScalar(string spName, IEnumerable<OracleParameter> parameters)
        {
            SetConnect();
            using (OracleCommand cmd = CreateCommand(spName, parameters))
            {
                try
                {
                    return cmd.ExecuteScalar();
                }
                catch (OracleException e)
                {
                    throw ProcessException(e);
                }
                finally
                {
                    cmd.Parameters.Clear();
                }
            }
        }

        protected DataTable ExecuteDataTable(string spName)
        {
            return ExecuteDataTable(spName, null);
        }

        protected DataTable ExecuteDataTable(string spName, IEnumerable<OracleParameter> parameters)
        {
            SetConnect();
            DataTable dt = CreateDataTable(spName, parameters);
            return dt;
        }

        #endregion
    }

    public class CustomException
        : Exception
    {
        public CustomException() { }
        public CustomException(string message) : base(message) { }
        public CustomException(string message, Exception inner) : base(message, inner) { }
    }

    public class ProductOperation
        :OracleDbOperation
    { 
        
    }
}