IMEX.mes.wcf/DK.Basics/DataAccess/OracleDBConnection.cs

/*******************************************************************************
 * Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential
 * 类的信息：
 *      1.程序名称：OracleDBConnection.cs
 *      2.功能描述：Oracle数据库不带有事物的操作类
 * 编辑履历：
 *      作者            日期                版本            修改内容
 *      张国印          2014/09/01          1.00            新建
 *******************************************************************************/

using System;
using System.Collections;
using System.Data;
using System.IO;

using Oracle.ManagedDataAccess.Client;

namespace Dongke.IBOSS.PRD.Basics.DataAccess
{
    /// <summary>
    /// Oracle数据库不带有事物的操作类
    /// </summary>
    public class OracleDBConnection : IDBConnection
	{
        ///// <summary>
        ///// SQL执行超时设置 30分钟（30*60=1800s）
        ///// </summary>
        //public const int CommandTimeout = 1800;
        /// <summary>
        /// SQL执行超时设置 0分钟（不限制）
        /// </summary>
        public const int CommandTimeout = 0;

        private static ArrayList m_Pool = new ArrayList();
        private ConnectionState m_ConnState = ConnectionState.Closed;//数据库连接状态
        private OracleConnection m_DbConnection; //数据连接
		private string m_connStr = "";	//数据库连接串
		/// <summary>
		/// 不忽略大小写，效率高
		/// </summary>
		private bool m_IgnoreCase = false;
		private bool m_IsCommandTimeout = true;

        /// <summary>
        /// 说明：构造函数初始化
        /// </summary>
        public OracleDBConnection(string m_strConn)
        {
            try
            {

                m_connStr = m_strConn;
            }
            catch (Exception ex)
            {
                string strError = ex.Message + ex.Source;
                throw new Exception(strError, ex);
            }

        }

        #region IDBConnection 成员
        public int PoolSize
        {
            get
            {
                return m_Pool.Count;
            }
        }

        /// <summary>
        /// 所属工程：数据访问层
        /// 数据库连接串
        /// 该属性只读
        /// </summary>
        public string ConnStr
        {

            get
            {
                return m_connStr;
            }

        }

        /// <summary>
        /// 连接对象
        /// </summary>
        public IDbConnection DbConnection
        {
            get
            {
                return m_DbConnection;
            }
            set
            {
                m_DbConnection = (value as OracleConnection);
            }
        }

        /// <summary>
        /// 忽略大小写
        /// </summary>
        public bool IgnoreCase
        {
            get
            {
                return m_IgnoreCase;
            }
            set
            {
                m_IgnoreCase = value;
            }
        }

        /// <summary>
		/// 执行限定超时
        /// </summary>
		public bool IsCommandTimeout
        {
            get
            {
				return m_IsCommandTimeout;
            }
            set
            {
				m_IsCommandTimeout = value;
            }
        }

        /// <summary>
        /// 所属工程：数据访问层
        /// 数据库连接状态
        /// 该属性可读写
        /// </summary>
        public ConnectionState ConnState
        {

            get
            {
                return m_ConnState;
            }
        }

        /// <summary>
        /// 说明： 打开创建数据库连接
        /// </summary>
        /// <returns>ConnectionState类型</returns>
        public ConnectionState Open()
        {
            try
            {
                // 初始化连接
                m_DbConnection = new OracleConnection(m_connStr);

                m_DbConnection.Open();

				if (this.IgnoreCase)
				{
					//using (OracleCommand oraCmd = new OracleCommand("ALTER SESSION SET NLS_SORT=BINARY_CI", m_DbConnection))
					//{
					//	oraCmd.ExecuteNonQuery();
					//}
					//using (OracleCommand oraCmd = new OracleCommand("ALTER SESSION SET NLS_COMP=LINGUISTIC", m_DbConnection))
					//{
					//	oraCmd.ExecuteNonQuery();
					//}
					using (OracleCommand oraCmd = m_DbConnection.CreateCommand())
					{
						oraCmd.CommandText = "ALTER SESSION SET NLS_SORT=BINARY_CI";
						oraCmd.ExecuteNonQuery();
						oraCmd.CommandText = "ALTER SESSION SET NLS_COMP=LINGUISTIC";
						oraCmd.ExecuteNonQuery();
					}
				}
				else
				{
					using (OracleCommand oraCmd = m_DbConnection.CreateCommand())
					{
						oraCmd.CommandText = "ALTER SESSION SET NLS_SORT=BINARY";
						oraCmd.ExecuteNonQuery();
						oraCmd.CommandText = "ALTER SESSION SET NLS_COMP=BINARY";
						oraCmd.ExecuteNonQuery();
					}
				}

                m_ConnState = m_DbConnection.State;

				//m_Pool.Add(m_DbConnection);

                //return m_ConnState;

            }
            catch (Exception ex)
            {
                m_ConnState = ConnectionState.Closed;
                string strError = ex.Message + ex.Source;
                throw new Exception(strError, ex);
            }

            return m_ConnState;

        }

        /// <summary>
        /// 说明：断开数据库连接
        /// </summary>
        /// <returns>ConnectionState类型</returns>
        /// 
        public ConnectionState Close()
        {
            try
            {
				if (m_DbConnection != null)
				{
					if (m_ConnState == ConnectionState.Open)
						m_DbConnection.Close();

					m_ConnState = m_DbConnection.State;

					//if (m_Pool.Contains(m_DbConnection))
					//{
					//	m_Pool.Remove(m_DbConnection);
					//}
				}
            }
            catch (Exception ex)
            {
                m_ConnState = ConnectionState.Closed;
                string strError = ex.Message + ex.Source;
                throw new Exception(strError, ex);
            }

            return m_ConnState;

        }

        /// <summary>
        /// 说明：获取语句的返回结果集
        /// </summary>
        /// <param name="p_strCommand">数据操作语句</param>
        /// <returns>
        /// DataSet对象
        /// </returns>
        public DataSet GetSqlResultToDs(string p_strCommand, IDataParameter[] p_Parameter)
        {

            DataSet myDs = new DataSet();

			if (m_DbConnection == null || m_DbConnection.State != ConnectionState.Open)
				Open();

            try
            {
				using (OracleDataAdapter dataAdapter = new OracleDataAdapter(p_strCommand, m_DbConnection))
				{
					if (p_Parameter != null)
					{
						dataAdapter.SelectCommand.Parameters.AddRange(p_Parameter);
						dataAdapter.SelectCommand.BindByName = true;
					}
					if (m_IsCommandTimeout)
					{
						dataAdapter.SelectCommand.CommandTimeout = OracleDBConnection.CommandTimeout;
					}
					//dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
					dataAdapter.Fill(myDs);
				}
            }
            catch (Exception ex)
            {
                string strError = ex.Message + ex.Source + " 对应的SQL语句为：" + CovnertToSql(p_strCommand, p_Parameter);
                throw new Exception(strError, ex);
            }

            return myDs;

        }

        /// <summary>
        /// 说明：获取语句的返回结果集
        /// </summary>
        /// <param name="p_strCommand">数据操作语句</param>
        /// <param name="p_strName">返回结果集的名称</param>
        /// <returns>
        /// DataSet对象
        /// </returns>
        public DataSet GetSqlResultToDs(string p_strCommand, string p_strName, IDataParameter[] p_Parameter)
        {

            DataSet myDs = new DataSet();

			if (m_DbConnection == null || m_DbConnection.State != ConnectionState.Open)
				Open();

            try
            {
				using (OracleDataAdapter dataAdapter = new OracleDataAdapter(p_strCommand, m_DbConnection))
				{
					if (p_Parameter != null)
					{
						dataAdapter.SelectCommand.Parameters.AddRange(p_Parameter);
						dataAdapter.SelectCommand.BindByName = true;
					}
					if (m_IsCommandTimeout)
					{
						dataAdapter.SelectCommand.CommandTimeout = OracleDBConnection.CommandTimeout;
					}
					//dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
					dataAdapter.Fill(myDs, p_strName);
				}

            }
            catch (Exception ex)
            {
                string strError = ex.Message + ex.Source + " 对应的SQL语句为：" + CovnertToSql(p_strCommand, p_Parameter);
                throw new Exception(strError, ex);
            }

            return myDs;

        }

        /// <summary>
        /// 说明：获取语句的返回结果集
        /// </summary>
        /// <param name="p_strCommand">为数据操作语句</param>
        /// <returns>
        ///	DataTable对象
        /// </returns>
        public DataTable GetSqlResultToDt(string p_strCommand, IDataParameter[] p_Parameter)
        {
            //初始化结果集(DT)
            DataTable myDt = new DataTable();

            if (m_DbConnection == null || m_DbConnection.State != ConnectionState.Open)
                Open();
            try
            {
                //dataAdapte实例化
				using (OracleDataAdapter dataAdapter = new OracleDataAdapter(p_strCommand, m_DbConnection))
				{
					if (p_Parameter != null)
					{
						dataAdapter.SelectCommand.Parameters.AddRange(p_Parameter);
						dataAdapter.SelectCommand.BindByName = true;
					}
					if (m_IsCommandTimeout)
					{
						dataAdapter.SelectCommand.CommandTimeout = OracleDBConnection.CommandTimeout;
					}
					//dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
					//填充数据集(DT)
					dataAdapter.Fill(myDt);
				}

            }
            catch (Exception ex)
            {
                string strError = ex.Message + ex.Source + " 对应的SQL语句为：" + CovnertToSql(p_strCommand, p_Parameter);
                throw new Exception(strError, ex);
            }

            //返回数据集
            return myDt;

        }

        /// <summary>
        /// 说明：获取语句的单个字符串返回结果
        /// </summary>
        /// <param name="p_strCommand">为数据操作语句</param>
        /// <returns>
        ///     string:单个查询结果
        /// </returns>
        public string GetSqlResultToStr(string p_strCommand, IDataParameter[] p_Parameter)
        {
            string strResult = "";
            object retValue = null;

			if (m_DbConnection == null || m_DbConnection.State != ConnectionState.Open)
				Open();

            try
            {

				using (OracleCommand oraCmd = new OracleCommand(p_strCommand, m_DbConnection))
				{
					if (p_Parameter != null)
					{
						oraCmd.Parameters.AddRange(p_Parameter);
						oraCmd.BindByName = true;
					}

					if (m_IsCommandTimeout)
					{
						oraCmd.CommandTimeout = OracleDBConnection.CommandTimeout;
					}
					retValue = oraCmd.ExecuteScalar();

					if (retValue != null)
						strResult = retValue.ToString();
					else
						strResult = String.Empty;
				}
            }
            catch (Exception ex)
            {
                string strError = ex.Message + ex.Source + " 对应的SQL语句为：" + CovnertToSql(p_strCommand, p_Parameter);
                throw new Exception(strError, ex);
            }

            return strResult;

        }


        /// <summary>
        /// 说明：获取语句的单个字符串返回结果
        /// </summary>
        /// <param name="p_strCommand">为数据操作语句</param>
        /// <returns>
        ///     object:单个查询结果
        /// </returns>
        public object GetSqlResultToObj(string p_strCommand, IDataParameter[] p_Parameter)
        {
            object retValue = null;

			if (m_DbConnection == null || m_DbConnection.State != ConnectionState.Open)
				Open();

            try
            {

				using (OracleCommand oraCmd = new OracleCommand(p_strCommand, m_DbConnection))
				{
					if (p_Parameter != null)
					{
						oraCmd.Parameters.AddRange(p_Parameter);
						oraCmd.BindByName = true;
					}
					oraCmd.CommandTimeout = OracleDBTransaction.CommandTimeout;
					retValue = oraCmd.ExecuteScalar();
				}
            }
            catch (Exception ex)
            {
                string strError = ex.Message + ex.Source + " 对应的SQL语句为：" + CovnertToSql(p_strCommand, p_Parameter);
                throw new Exception(strError, ex);
            }

            return retValue;

        }

        /// <summary>
        /// 说明： 获取语句的单个byte[]类型返回结果
        /// </summary>
        /// <param name="p_strCommand">为数据操作语句</param>
        /// <returns>
        /// byte[]对象
        /// </returns>
        public byte[] GetSqlResultToBt(string p_strCommand, IDataParameter[] p_Parameter)
        {
            int bufferSize = 100;
            byte[] bytData = new byte[bufferSize];
            byte[] bytResult = null;
            int retval;
            int startIndex = 0;

			if (m_DbConnection == null || m_DbConnection.State != ConnectionState.Open)
				Open();

            try
            {

				using (OracleCommand oraCommand = new OracleCommand(p_strCommand, m_DbConnection))
				{
					if (p_Parameter != null)
					{
						oraCommand.Parameters.AddRange(p_Parameter);
						oraCommand.BindByName = true;
					}
					if (m_IsCommandTimeout)
					{
						oraCommand.CommandTimeout = OracleDBConnection.CommandTimeout;
					}
					using (OracleDataReader myReader = oraCommand.ExecuteReader(CommandBehavior.SequentialAccess))
					{
						//创建内存流，用于将byte[]数组写入到流中
						using (MemoryStream ms = new MemoryStream())
						{
							while (myReader.Read())
							{

								//读取blob列数据到bytData中
								retval = Convert.ToInt32(myReader.GetBytes(0, startIndex, bytData, 0, bufferSize));

								//将bytData写入到流中
								ms.Write(bytData, 0, retval);

								while (retval == bufferSize)
								{
									// 重新设置读取bytData数据后缓冲读取位置的开始索引
									startIndex += bufferSize;
									retval = Convert.ToInt32(myReader.GetBytes(0, startIndex, bytData, 0, bufferSize));
									ms.Write(bytData, 0, retval);
								}
							}

							bytResult = ms.ToArray();
						}
					}
					//ms.Close();
					//myReader.Close();
				}
            }
            catch (Exception ex)
            {
                string strError = ex.Message + ex.Source + " 对应的SQL语句为：" + CovnertToSql(p_strCommand, p_Parameter);
                throw new Exception(strError, ex);
            }

            return bytResult;

        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="p_strCommand">存储过程名称</param>
        /// <param name="p_Parameter">IDataParameter类型 参数集合</param>
        /// <returns></returns>
        public DataSet ExecStoredProcedure(string p_strCommand, IDataParameter[] p_Parameter)
        {
            DataSet myDs = new DataSet();

			if (m_DbConnection == null || m_DbConnection.State != ConnectionState.Open)
				Open();

            try
            {

				using (OracleDataAdapter dataAdapter = new OracleDataAdapter(p_strCommand, m_DbConnection))
				{
					if (p_Parameter != null)
					{
						dataAdapter.SelectCommand.Parameters.AddRange(p_Parameter);
						dataAdapter.SelectCommand.BindByName = true;
					}
					if (m_IsCommandTimeout)
					{
						dataAdapter.SelectCommand.CommandTimeout = OracleDBConnection.CommandTimeout;
					}
					dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
					//dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
					dataAdapter.Fill(myDs);
				}
            }
            catch (Exception ex)
            {
				string strError = ex.Message + ex.Source + " 对应的SQL语句为：" + CovnertToSqlProcedure(p_strCommand, p_Parameter);
                throw new Exception(strError, ex);
            }

            return myDs;
        }
        #endregion

        /// <summary>
        /// SQL语句转换类
        /// </summary>
        /// <param name="p_strSql">SQL语句</param>
        /// <param name="p_Parameter">参数集合</param>
        /// <returns></returns>
        private string CovnertToSql(string p_strSql, IDataParameter[] p_Parameter)
        {
            if (p_Parameter == null)
                return p_strSql;
            foreach (var itemPara in p_Parameter)
            {
                string strDbType = itemPara.DbType.ToString().ToUpper();
                string paraValue = null;
                if (strDbType.StartsWith("INT") || strDbType.StartsWith("DECIMAL"))
                {
                    if (itemPara.Value == null || itemPara.Value == DBNull.Value)
                    {
                        paraValue = "NULL";
                    }
                    else
                    {
                        paraValue = itemPara.Value.ToString();
                    }
                }
                else if (strDbType.StartsWith("DATE"))
                {
                    if (itemPara.Value == null || itemPara.Value == DBNull.Value)
                    {
                        paraValue = "NULL";
                    }
                    else
                    {
                        paraValue = "to_date('" + itemPara.Value + "', 'yyyy-mm-dd hh24:mi:ss')";
                    }
                }
                else
                {
                    if (itemPara.Value == null || itemPara.Value == DBNull.Value)
                    {
                        paraValue = "NULL";
                    }
                    else
                    {
                        paraValue = "'" + itemPara.Value + "'";
                    }
                }
                p_strSql = p_strSql.Replace(itemPara.ParameterName, paraValue);
            }
            return p_strSql;
        }
		private string CovnertToSqlProcedure(string p_strSql, IDataParameter[] p_Parameter)
        {
            if (p_Parameter == null)
                return p_strSql;
            p_strSql += "(";
            foreach (var itemPara in p_Parameter)
            {
                if (itemPara.Direction == ParameterDirection.Output ||
                    itemPara.Direction == ParameterDirection.InputOutput)
                {
                    p_strSql += itemPara.ParameterName + " => :" + itemPara.ParameterName + ",";
                    continue;
                }
                string strDbType = itemPara.DbType.ToString().ToUpper();
                string paraValue = null;
                if (strDbType.StartsWith("INT") || strDbType.StartsWith("DECIMAL"))
                {
                    if (itemPara.Value == null || itemPara.Value == DBNull.Value)
                    {
                        paraValue = "NULL";
                    }
                    else
                    {
                        paraValue = itemPara.Value.ToString();
                    }
                }
                else if (strDbType.StartsWith("DATE"))
                {
                    if (itemPara.Value == null || itemPara.Value == DBNull.Value)
                    {
                        paraValue = "NULL";
                    }
                    else
                    {
                        paraValue = "to_date('" + itemPara.Value + "', 'yyyy-mm-dd hh24:mi:ss')";
                    }
                }
                else
                {
                    if (itemPara.Value == null || itemPara.Value == DBNull.Value)
                    {
                        paraValue = "NULL";
                    }
                    else
                    {
                        paraValue = "'" + itemPara.Value + "'";
                    }
                }
                p_strSql += itemPara.ParameterName + " => " + paraValue + ",";
            }
            p_strSql += ");";
            return p_strSql;
		}
    }
}