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

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

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;

namespace Dongke.IBOSS.PRD.Basics.DataAccess
{
    public class OracleDBTransaction : IDBTransaction
    {
        ///// <summary>
        ///// SQL执行超时设置 30分钟（30*60=1800s）
        ///// </summary>
        //public const int CommandTimeout = 1800;
        /// <summary>
        /// SQL执行超时设置 0分钟（不限制）
        /// </summary>
        public const int CommandTimeout = 0;

        /// <summary>
        /// 数据库OracleConnection对象
        /// </summary>
        private OracleConnection m_DbConnection;

        /// <summary>
        /// 数据库OracleTransaction对象
        /// </summary>
        private OracleTransaction m_OracleTransaction;

        /// <summary>
        /// 数据库连接状态
        /// </summary>
        private ConnectionState m_ConnState;

        /// <summary>
        /// 数据库连接串
        /// </summary>
        private string m_connStr;

        /// <summary>
        /// 事务是否自动提交
        /// </summary>
        public bool m_AutoCommit = false;
		/// <summary>
		/// 不忽略大小写，效率高
		/// </summary>
		private bool m_IgnoreCase = false;
		private bool m_IsCommandTimeout = true;

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

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

        }

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

            get
            {
                return m_connStr;
            }

        }

        /// <summary>
        /// 所属工程：数据访问层
        /// 数据库连接状态。
        /// 该属性为读写
        /// </summary>
        public ConnectionState ConnState
        {
            get
            {
                return m_ConnState;
            }
        }

        /// <summary>
        /// 所属工程：数据访问层
        /// 事务是否自动提交
        /// 该属性为读写
        /// </summary>
        public bool AutoCommit
        {
            get
            {
                return m_AutoCommit;
            }
            set
            {
                m_AutoCommit = value;
            }
        }

        /// <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;
			}
		}

        public System.Data.ConnectionState Connect()
        {
            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_OracleTransaction = m_DbConnection.BeginTransaction(IsolationLevel.ReadCommitted);

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

            return m_ConnState;

        }

        public bool CreateTransaction()
        {
            bool blSucess = false;
            try
            {
                if (m_ConnState != ConnectionState.Open)
                    Connect();

                m_OracleTransaction = m_DbConnection.BeginTransaction(IsolationLevel.ReadCommitted);

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

            return blSucess;
        }

        public System.Data.ConnectionState Disconnect()
        {
            try
            {
                if (m_ConnState == ConnectionState.Open)
                    m_DbConnection.Close();

                m_ConnState = m_DbConnection.State;

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

            return m_ConnState;
        }

        public void Commit()
        {
            try
            {
                m_OracleTransaction.Commit();
            }
            catch (Exception ex)
            {
                string strError = ex.Message + ex.Source;
                throw new Exception(strError, ex);
            }
        }

        public void Rollback()
        {
            try
            {
                m_OracleTransaction.Rollback();
            }
            catch (Exception ex)
            {
                string strError = ex.Message + ex.Source;
                throw new Exception(strError, ex);
            }
        }

        public void RollbackTo(string p_strcheckPoint)
        {
            //try
            //{
            //    m_OracleTransaction.Rollback();
            //}
            //catch (Exception ex)
            //{
            //    string strError = ex.Message + ex.Source;
            //    throw new Exception(strError, ex);
            //}
            m_OracleTransaction.Rollback(p_strcheckPoint);
        }

        public void SavePoint(string p_strcheckPoint)
        {
            //throw new Exception("不支持此方法！");
            m_OracleTransaction.Save(p_strcheckPoint);
        }

        public int ExecuteNonQuery(string p_strCommand, IDataParameter[] p_Parameter)
        {
            return ExecuteNonQuery(p_strCommand, false, p_Parameter);
        }

        public int ExecuteNonQuery(string p_strCommand, bool p_procedure, IDataParameter[] p_Parameter)
        {
            int blReturn = 0;

            if (m_ConnState != ConnectionState.Open)
                Connect();

            try
            {
				using (OracleCommand myCommand = new OracleCommand(p_strCommand, m_DbConnection))
				{
					if (p_Parameter != null)
					{
						myCommand.Parameters.AddRange(p_Parameter);
						myCommand.BindByName = true;
					}
					if (m_IsCommandTimeout)
					{
						myCommand.CommandTimeout = OracleDBTransaction.CommandTimeout;
					}
					//2009-05-24增加
					myCommand.Transaction = m_OracleTransaction;
					if (p_procedure)
					{
						myCommand.CommandType = CommandType.StoredProcedure;
					}
					else
					{
						myCommand.CommandType = CommandType.Text;
					}

					blReturn = myCommand.ExecuteNonQuery();

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

            return blReturn;
        }

        public int UpdateBlob(string p_strTable, string p_strColumn, byte[] p_blobData, string p_strWhere)
        {
            int intReturn = 0;
            string strCommand = "UPDATE " + p_strTable + " SET " + p_strColumn + " =:blobValue where " + p_strWhere;

			//if (m_DbConnection.State != ConnectionState.Open)
			//	Connect();
			if (m_ConnState != ConnectionState.Open)
				Connect();
            try
            {

				using (OracleCommand myCommand = new OracleCommand(strCommand, m_DbConnection))
				{
					//2009-05-24增加
					myCommand.Transaction = m_OracleTransaction;
					OracleParameter oraParam = new OracleParameter("blobValue", OracleDbType.Blob);

					oraParam.Direction = ParameterDirection.InputOutput;
					oraParam.Value = p_blobData;

					myCommand.Parameters.Add(oraParam);

					intReturn = myCommand.ExecuteNonQuery();

					if (m_AutoCommit)
						Commit();

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

            return intReturn;
        }

        /// <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.State != ConnectionState.Open)
			//	Connect();
			if (m_ConnState != ConnectionState.Open)
				Connect();

            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 = OracleDBTransaction.CommandTimeout;
					}
					dataAdapter.SelectCommand.Transaction = m_OracleTransaction;
					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.State != ConnectionState.Open)
			//	Connect();
			if (m_ConnState != ConnectionState.Open)
				Connect();

            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 = OracleDBTransaction.CommandTimeout;
					}
					//2009-05-24增加
					dataAdapter.SelectCommand.Transaction = m_OracleTransaction;
					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.State != ConnectionState.Open)
			//	Connect();
			if (m_ConnState != ConnectionState.Open)
				Connect();
            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 = OracleDBTransaction.CommandTimeout;
					}
					//2009-05-24增加
					dataAdapter.SelectCommand.Transaction = m_OracleTransaction;
					//填充数据集(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_ConnState != ConnectionState.Open)
                Connect();

            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 = OracleDBTransaction.CommandTimeout;
					}
					//2009-05-24增加
					oraCmd.Transaction = m_OracleTransaction;
					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_ConnState != ConnectionState.Open)
                Connect();

            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 = OracleDBTransaction.CommandTimeout;
					}
					//2009-05-24增加
					oraCmd.Transaction = m_OracleTransaction;
					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.State != ConnectionState.Open)
			//	Connect();
			if (m_ConnState != ConnectionState.Open)
				Connect();

            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 = OracleDBTransaction.CommandTimeout;
					}
					//2009-05-24增加
					oraCommand.Transaction = m_OracleTransaction;
					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.State != ConnectionState.Open)
			//	Connect();
			if (m_ConnState != ConnectionState.Open)
				Connect();

            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 = OracleDBTransaction.CommandTimeout;
					}
					dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
					dataAdapter.SelectCommand.Transaction = m_OracleTransaction;
					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;
		}
    }
}