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

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

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;

namespace Dongke.IBOSS.PRD.Basics.DataAccess
{
    /// <summary>
    /// 数据库带有事物的操作类
    /// </summary>
    public class MSSQLServerDBTransaction : IDBTransaction
    {
        private SqlConnection m_SqlConnection;
        private SqlTransaction m_SqlTransaction;
        private ConnectionState m_ConnState;
        private string m_connStr;

        /// <summary>
        /// 事务是否自动提交
        /// </summary>
        public bool m_AutoCommit = false;

        /// <summary>
        /// 忽略大小写
        /// </summary>
        public bool IgnoreCase
        {
            get;
            set;
        }
		/// <summary>
		/// SQL执行限定超时
		/// </summary>
		public bool IsCommandTimeout
		{
			get;
			set;
		}

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

        #region IDBTransaction 成员
        public IDbConnection DbConnection
        {
            get
            {
                return m_SqlConnection;
            }
            set
            {
                m_SqlConnection = (value as SqlConnection);
            }
        }

        public bool AutoCommit
        {
            get
            {
                return m_AutoCommit;
            }
            set
            {
                m_AutoCommit = value;
            }
        }

        public ConnectionState ConnState
        {
            get
            {
                return m_ConnState;
            }
            set
            {
                m_ConnState = value;
            }
        }

        public string ConnStr
        {
            get { return m_connStr; }
        }

        public ConnectionState Connect()
        {
            try
            {
                // 初始化连接
                m_SqlConnection = new SqlConnection(m_connStr);

                m_SqlConnection.Open();

                m_ConnState = m_SqlConnection.State;

                m_SqlTransaction = m_SqlConnection.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_SqlTransaction = m_SqlConnection.BeginTransaction(IsolationLevel.ReadCommitted);

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

            return blSucess;
        }

        public ConnectionState Disconnect()
        {
            try
            {
                if (m_ConnState == ConnectionState.Open)
                    m_SqlConnection.Close();

                m_ConnState = m_SqlConnection.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_SqlTransaction.Commit();
            }
            catch (Exception ex)
            {
                string strError = ex.Message + ex.Source;
                throw new Exception(strError, ex);
            }
        }

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

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

        public void SavePoint(string p_strcheckPoint)
        {
            //throw new Exception("不支持此方法！");
            m_SqlTransaction.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
            {
                SqlCommand myCommand = new SqlCommand(p_strCommand, m_SqlConnection);
                if (p_Parameter != null)
                {
                    myCommand.Parameters.AddRange(p_Parameter);
                }
                myCommand.Transaction = m_SqlTransaction;
                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语句为：" + p_strCommand;
                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_SqlConnection.State != ConnectionState.Open)
                Connect();
            try
            {

                SqlCommand myCommand = new SqlCommand(strCommand, m_SqlConnection);
                myCommand.Transaction = m_SqlTransaction;
                SqlParameter aseParam = new SqlParameter("@blobValue", SqlDbType.Image);

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

                myCommand.Parameters.Add(aseParam);

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

        public DataSet GetSqlResultToDs(string p_strCommand, IDataParameter[] p_Parameter)
        {
            DataSet myDs = new DataSet();
            if (m_SqlConnection.State != ConnectionState.Open)
                Connect();

            try
            {
                SqlDataAdapter dataAdapter = new SqlDataAdapter(p_strCommand, m_SqlConnection);
                if (p_Parameter != null)
                {
                    dataAdapter.SelectCommand.Parameters.AddRange(p_Parameter);
                }
                dataAdapter.SelectCommand.Transaction = m_SqlTransaction;
                dataAdapter.SelectCommand.CommandTimeout = 120; //2012-2-21 张国印 增加 用于查询超时
                dataAdapter.Fill(myDs);
            }
            catch (Exception ex)
            {
                string strError = ex.Message + ex.Source + " 对应的SQL语句为：" + p_strCommand;
                throw new Exception(strError, ex);
            }

            return myDs;
        }

        public DataSet GetSqlResultToDs(string p_strCommand, string p_strName, IDataParameter[] p_Parameter)
        {
            DataSet myDs = new DataSet();
            if (m_SqlConnection.State != ConnectionState.Open)
                Connect();

            try
            {

                SqlDataAdapter dataAdapter = new SqlDataAdapter(p_strCommand, m_SqlConnection);
                if (p_Parameter != null)
                {
                    dataAdapter.SelectCommand.Parameters.AddRange(p_Parameter);
                }
                dataAdapter.SelectCommand.Transaction = m_SqlTransaction;
                dataAdapter.SelectCommand.CommandTimeout = 120; //2012-2-21 张国印 增加 用于查询超时
                dataAdapter.Fill(myDs, p_strName);

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

            return myDs;
        }

        public DataTable GetSqlResultToDt(string p_strCommand, IDataParameter[] p_Parameter)
        {
            //初始化结果集(DT)
            DataTable myDt = new DataTable();
            if (m_SqlConnection.State != ConnectionState.Open)
                Connect();
            try
            {
                //dataAdapte实例化
                SqlDataAdapter dataAdapter = new SqlDataAdapter(p_strCommand, m_SqlConnection);
                if (p_Parameter != null)
                {
                    dataAdapter.SelectCommand.Parameters.AddRange(p_Parameter);
                }
                dataAdapter.SelectCommand.Transaction = m_SqlTransaction;
                dataAdapter.SelectCommand.CommandTimeout = 120; //2012-2-21 张国印 增加 用于查询超时
                //填充数据集(DT)
                dataAdapter.Fill(myDt);

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

            //返回数据集
            return myDt;
        }

        public string GetSqlResultToStr(string p_strCommand, IDataParameter[] p_Parameter)
        {
            string strResult = "";
            object retValue = null;
            if (m_ConnState != ConnectionState.Open)
                Connect();

            try
            {

                SqlCommand oraCmd = new SqlCommand(p_strCommand, m_SqlConnection);
                if (p_Parameter != null)
                {
                    oraCmd.Parameters.AddRange(p_Parameter);
                }
                oraCmd.Transaction = m_SqlTransaction;
                oraCmd.CommandTimeout = 120; //2012-2-21 张国印 增加 用于查询超时
                retValue = oraCmd.ExecuteScalar();

                if (retValue != null)
                    strResult = retValue.ToString();
                else
                    strResult = String.Empty;

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

            return strResult;
        }

        public object GetSqlResultToObj(string p_strCommand, IDataParameter[] p_Parameter)
        {
            object retValue = null;
            if (m_ConnState != ConnectionState.Open)
                Connect();

            try
            {

                SqlCommand oraCmd = new SqlCommand(p_strCommand, m_SqlConnection);
                if (p_Parameter != null)
                {
                    oraCmd.Parameters.AddRange(p_Parameter);
                }
                oraCmd.Transaction = m_SqlTransaction;
                oraCmd.CommandTimeout = 120; //2012-2-21 张国印 增加 用于查询超时
                retValue = oraCmd.ExecuteScalar();

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

            return retValue;
        }

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

            try
            {
                //创建内存流，用于将byte[]数组写入到流中
                MemoryStream ms = new MemoryStream();

                SqlCommand sqlCommand = new SqlCommand(p_strCommand, m_SqlConnection);
                if (p_Parameter != null)
                {
                    sqlCommand.Parameters.AddRange(p_Parameter);
                }
                sqlCommand.Transaction = m_SqlTransaction;
                SqlDataReader myReader = sqlCommand.ExecuteReader(CommandBehavior.SequentialAccess);

                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语句为：" + p_strCommand;
                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_SqlConnection.State != ConnectionState.Open)
                Connect();

            try
            {
                SqlDataAdapter dataAdapter = new SqlDataAdapter(p_strCommand, m_SqlConnection);
                if (p_Parameter != null)
                {
                    dataAdapter.SelectCommand.Parameters.AddRange(p_Parameter);
                }
                dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                dataAdapter.SelectCommand.Transaction = m_SqlTransaction;
                dataAdapter.SelectCommand.CommandTimeout = 120; //2012-2-21 张国印 增加 用于查询超时
                dataAdapter.Fill(myDs);
            }
            catch (Exception ex)
            {
                string strError = ex.Message + ex.Source + " 对应的SQL语句为：" + p_strCommand;
                throw new Exception(strError, ex);
            }

            return myDs;
        }

        #endregion
    }
}