using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using System.Configuration;
using System.Data.Common;
namespace etong.DAL
{
    public interface IOracleDb
    {
        //string GetDbType();
        /// <summary>
        /// 开始 Transaction
        /// </summary>
        void BeginTransaction();
        /// <summary>
        /// 回滚 
        /// </summary>
        void RollbackTransaction();
        /// <summary>
        /// 提交 Transaction
        /// </summary>
        void CommitTransaction();
        /// <summary>
        /// 结束 Transaction
        /// </summary>
        void EndTransaction();
        /// <summary>
        /// 打开连接 
        /// </summary>
        void CreateConnection();
        /// <summary>
        /// 对连接执行 Transact-SQL 语句并返回受影响的行数。
        /// </summary>
        /// <param name="psSQL">Insert,Update,Delete等语句</param>
        /// <returns>对连接执行 Transact-SQL 语句并返回受影响的行数。</returns>
        int ExeNonQuery(string psSQL);
        /// <summary>
        /// 获得DataSet
        /// </summary>
        /// <param name="psSQL"></param>
        /// <returns></returns>
        DataSet GetDataSet(string psSQL);
        /// <summary>
        /// 返回 DataTable 对象
        /// </summary>
        /// <param name="psSQL"></param>
        /// <returns></returns>
        DataTable GetDataTable(string psSQL);
        /// <summary>
        /// 从数据库中读取信息
        /// </summary>
        /// <param name="psDataFielde">字段名称数组</param>
        /// <param name="psSQL">查询语句</param>
        /// <returns>对应字段的值数组</returns>
        string[] ReadData(string[] psDataFielde, string psSQL);
        /// <summary>
        /// 主要用于Min,Max,Count，Sum，Avg等函数,返回数值，错误返回-1
        /// </summary>
        /// <param name="psSQL">查询语句</param>
        /// <returns>数值</returns>
        int ExeScalar(string psSQL);
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="psProcedureName">存储过程名</param>
        /// <param name="psParameters">参数列表</param>
        int ExecuteNonQuery(string psProcedureName, OracleParameter[] psParameters);
        /// <summary>
        /// 执行存储过程并返回数据集
        /// </summary>
        /// <param name="psProcedureName">存储过程名</param>
        /// <param name="psParameters">参数列表</param>
        /// <returns></returns>
        DataSet GetProcDataSet(string psProcedureName, OracleParameter[] psParameters);
        /// <summary>
        /// 关闭连接
        /// </summary>
        void CloseConnection();
        /// <summary>
        /// 获取最大字段值
        /// </summary>
        /// <param name="FieldName">字段名</param>
        /// <param name="TableName">表名</param>
        /// <returns></returns>
        int GetMaxID(string FieldName, string TableName);
        /// <summary>
        /// 执行存储过程返回int字符
        /// </summary>
        /// <param name="psProcedureName">存储过程名</param>
        /// <param name="psParameters">参数列表</param>
        /// <returns></returns>
        string GetProcInt(string psProcedureName, OracleParameter[] psParameters);
    }

    public class ORACLEDB : IOracleDb
    {

        #region property

        private OracleTransaction m_Transaction;

        private OracleCommand m_Command;
        public OracleCommand DBCommand
        {
            get { return m_Command; }
        }
        private OracleConnection m_Connection;
        public OracleConnection DBConnection
        {
            get { return m_Connection; }
        }

        private string m_ConnectString;
        public string DBConnectString
        {
            get { return m_ConnectString; }
        }


        public Boolean IsInTransaction
        {
            get
            {
                if (m_Transaction == null)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        #endregion

        public ORACLEDB()
        {
        }
        #region 公用方法

        public int GetMaxID(string FieldName, string TableName)
        {
            string strsql = "select max(" + FieldName + ")+1 from " + TableName;
            object obj = GetSingle(strsql);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }
        #endregion

        #region Public method

        //public string GetDbType()
        //{
        //    return HprGlobal.DB_ORACLE;
        //}

        public void BeginTransaction()
        {
            if (!this.IsInTransaction)
            {
                // 开始一个新的Ｔｒａｎｓａｃｔｉｏｎ
                m_Transaction = this.DBConnection.BeginTransaction();
            }
            else
            {
                // 已经在Ｔｒａｎｓａｃｔｉｏｎ中，　不需要做任何动作。
            }

            m_Command.Transaction = m_Transaction;//2008-8-4
        }

        public void RollbackTransaction()
        {
            if (this.IsInTransaction)
            {
                m_Transaction.Rollback();
            }
            else
            {

            }
        }

        public void CommitTransaction()
        {
            if (this.IsInTransaction)
            {
                m_Transaction.Commit();
            }
            else
            {

            }

        }

        public void EndTransaction()
        {
            if (this.IsInTransaction)
            {
                m_Transaction = null;
            }
            else
            {

            }
        }

        private void Init()
        {
            //1. 判断数据库类型，　然后指定使用何种Ｃｏｎｎｅｃｔｉｏｎ
            m_Connection = new OracleConnection();
            m_Connection.ConnectionString = m_ConnectString;
            //2. 建立Command 对象
            m_Command = m_Connection.CreateCommand();

        }

        public ORACLEDB(string psDBConnectString)
        {
            if (string.IsNullOrEmpty(psDBConnectString))
            {
                m_ConnectString = System.Configuration.ConfigurationManager.ConnectionStrings["OracleConnString"].ConnectionString;
            }
            else
            {
                m_ConnectString = psDBConnectString;

            }
            Init();
        }
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public object GetSingle(string SQLString)
        {
            m_ConnectString = System.Configuration.ConfigurationManager.ConnectionStrings["OracleConnString"].ConnectionString;
            using (OracleConnection connection = new OracleConnection(m_ConnectString))
            {
                using (OracleCommand cmd = new OracleCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.OracleClient.OracleException e)
                    {
                        connection.Close();
                        throw new Exception(e.Message);
                    }
                }
            }
        }
        public void CreateConnection()
        {
            if (this.m_Connection.State != ConnectionState.Open)
                this.m_Connection.Open();
        }

        public string[] ReadData(string[] psDataField, string psSQL)
        {
            string[] sValue = new string[psDataField.Length];
            try
            {
                CreateConnection();
                m_Command.CommandText = psSQL;
                OracleDataReader objDataReader = m_Command.ExecuteReader();
                while (objDataReader.Read())
                {
                    for (int i = 0; i < psDataField.Length; i++)
                        sValue[i] = objDataReader[psDataField[i]].ToString();
                }

                objDataReader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (!this.IsInTransaction) CloseConnection();
            }
            return sValue;
        }

        public int ExeScalar(string psSQL)
        {
            CreateConnection();
            int iValue = -1;
            try
            {
                m_Command.CommandText = psSQL;
                m_Command.CommandType = CommandType.Text;
                int iNum = Convert.ToInt32(m_Command.ExecuteScalar());
                iValue = iNum;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (!this.IsInTransaction) CloseConnection();
            }
            return iValue;
        }

        public int ExeNonQuery(string psSQL)
        {
            int iRowCount = 0;
            try
            {
                this.CreateConnection();
                m_Command.CommandText = psSQL;
                if (this.IsInTransaction)
                {
                    m_Command.Transaction = m_Transaction;
                    iRowCount = m_Command.ExecuteNonQuery();
                }
                else
                {
                    iRowCount = m_Command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                iRowCount = -1;
                throw ex;
            }
            finally
            {
                m_Command = new OracleCommand();
                m_Command = m_Connection.CreateCommand();
                if (!this.IsInTransaction) CloseConnection();
            }
            return iRowCount;
        }

        public DataSet GetDataSet(string psSQL)
        {
            DataSet objDataSet = new DataSet();
            try
            {
                CreateConnection();
                m_Command.CommandText = psSQL;
                m_Command.CommandType = CommandType.Text;
                OracleDataAdapter objDataAdapter = new OracleDataAdapter(m_Command);
                objDataAdapter.Fill(objDataSet);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (!this.IsInTransaction) CloseConnection();
            }
            return objDataSet;
        }

        public DataTable GetDataTable(string psSQL)
        {
            DataSet objDataSet = GetDataSet(psSQL);
            return objDataSet.Tables[0];
        }

        public int ExecuteNonQuery(string psProcedureName, OracleParameter[] psParameters)
        {
            int result = -1;
            try
            {


                this.CreateConnection();
                m_Command.CommandText = psProcedureName;//声明存储过程名
                m_Command.CommandType = CommandType.StoredProcedure;
                foreach (OracleParameter objParam in psParameters)
                {
                    m_Command.Parameters.Add(objParam);
                }

                if (this.IsInTransaction)
                {
                    m_Command.Transaction = this.m_Transaction;
                    result = m_Command.ExecuteNonQuery();//执行存储过程
                }
                else
                {
                    result = m_Command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                result = -1;
                throw ex;
            }
            finally
            {
                m_Command = new OracleCommand();
                m_Command = m_Connection.CreateCommand();
                if (!this.IsInTransaction) CloseConnection();
            }
            return result;

        }

        public DataSet GetProcDataSet(string psProcedureName, OracleParameter[] psParameters)
        {
            DataSet objDataSet = new DataSet();
            try
            {
                int paramLength = psParameters.Length;

                OracleParameter _oParamCursor = new OracleParameter();
                _oParamCursor.ParameterName = "v_cursor";
                _oParamCursor.OracleType = OracleType.Cursor;
                _oParamCursor.Direction = ParameterDirection.Output;

                this.CreateConnection();
                m_Command.CommandText = psProcedureName;//声明存储过程名
                m_Command.CommandType = CommandType.StoredProcedure;
                foreach (OracleParameter objParam in psParameters)
                {
                    m_Command.Parameters.Add(objParam);
                }
                m_Command.Parameters.Add(_oParamCursor);

                OracleDataAdapter objDataAdapter = new OracleDataAdapter(m_Command);

                objDataAdapter.Fill(objDataSet);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                m_Command = new OracleCommand();
                m_Command = m_Connection.CreateCommand();
                if (!this.IsInTransaction) CloseConnection();
            }

            return objDataSet;
        }

        public DataSet GetProcDataSetTwo(string psProcedureName, OracleParameter[] psParameters)
        {
            DataSet objDataSet = new DataSet();
            try
            {
                int paramLength = psParameters.Length;

                OracleParameter _oParamCursor = new OracleParameter();
                _oParamCursor.ParameterName = "v_cursor";
                _oParamCursor.OracleType = OracleType.Cursor;
                _oParamCursor.Direction = ParameterDirection.Output;

                OracleParameter _oParamCursorAll = new OracleParameter();
                _oParamCursorAll.ParameterName = "v_cursorall";
                _oParamCursorAll.OracleType = OracleType.Cursor;
                _oParamCursorAll.Direction = ParameterDirection.Output;

                this.CreateConnection();
                m_Command.CommandText = psProcedureName;//声明存储过程名
                m_Command.CommandType = CommandType.StoredProcedure;
                foreach (OracleParameter objParam in psParameters)
                {
                    m_Command.Parameters.Add(objParam);
                }
                m_Command.Parameters.Add(_oParamCursor);
                m_Command.Parameters.Add(_oParamCursorAll);

                OracleDataAdapter objDataAdapter = new OracleDataAdapter(m_Command);

                objDataAdapter.Fill(objDataSet);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                m_Command = new OracleCommand();
                m_Command = m_Connection.CreateCommand();
                if (!this.IsInTransaction) CloseConnection();
            }

            return objDataSet;
        }

        public string GetProcInt(string psProcedureName, OracleParameter[] psParameters)
        {
            string objResult = "";
            try
            {
                int paramLength = psParameters.Length;

                OracleParameter _oParamCursor = new OracleParameter();
                _oParamCursor.ParameterName = "v_number";
                _oParamCursor.OracleType = OracleType.NVarChar;
                _oParamCursor.Size = 100;
                _oParamCursor.Direction = ParameterDirection.Output;

                this.CreateConnection();
                m_Command.CommandText = psProcedureName;//声明存储过程名
                m_Command.CommandType = CommandType.StoredProcedure;
                foreach (OracleParameter objParam in psParameters)
                {
                    m_Command.Parameters.Add(objParam);
                }
                m_Command.Parameters.Add(_oParamCursor);
                m_Command.ExecuteNonQuery();

                objResult = _oParamCursor.Value.ToString();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                m_Command = new OracleCommand();
                m_Command = m_Connection.CreateCommand();
                if (!this.IsInTransaction) CloseConnection();
            }

            return objResult;
        }

        public OracleParameter MakeInParam(string ParamName, OracleType DbType, int Size, object Value)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
        }

        public OracleParameter MakeOutParam(string ParamName, OracleType DbType, int Size)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
        }

        public OracleParameter MakeParam(string ParamName, OracleType DbType, int Size, ParameterDirection Direction, object Value)
        {
            OracleParameter parameter = new OracleParameter(ParamName, Value);
            parameter.Direction = Direction;
            if ((Direction != ParameterDirection.Output) || (Value != null))
            {
                parameter.Value = Value;
            }
            return parameter;
        }


        public void CloseConnection()
        {
            if (this.IsInTransaction)
            {
                this.m_Transaction = null;
            }

            if (this.m_Connection.State == ConnectionState.Open)
            {
                this.m_Connection.Close();
            }
        }


        #endregion
    }




}