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

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

using System;
using System.Collections;
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>
    /// SQL数据库不带有事物的操作类
    /// </summary>
    public class MSSQLServerDBConnection : IDBConnection
    {
        private static ArrayList m_Pool = new ArrayList();
        private SqlConnection m_SqlConnection;
        private ConnectionState m_ConState = ConnectionState.Closed;
        private string m_Constr;

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

        public MSSQLServerDBConnection(string m_strConn)
        {
            try
            {
                m_Constr = 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;
            }
        }

        public ConnectionState ConnState
        {
            get
            {
                return m_ConState;
            }
        }

        public IDbConnection DbConnection
        {
            get
            {
                return m_SqlConnection;
            }
            set
            {
                m_SqlConnection = (value as SqlConnection);
            }
        }

        public string ConnStr
        {
            get { return m_Constr; }
        }

        public ConnectionState Open()
        {
            try
            {
                m_SqlConnection = new SqlConnection(m_Constr);
                m_SqlConnection.Open();
                m_ConState = m_SqlConnection.State;
				//m_Pool.Add(m_SqlConnection);
            }
            catch (Exception ex)
            {
                m_ConState = ConnectionState.Closed;
                string strError = ex.Message + ex.Source;
                throw new Exception(strError, ex);
            }
            return m_ConState;
        }

        public ConnectionState Close()
        {
            try
            {
                if (m_SqlConnection.State == ConnectionState.Open)
                {
                    m_SqlConnection.Close();
                    m_ConState = m_SqlConnection.State;
                }

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

        public DataSet GetSqlResultToDs(string p_strCommand, IDataParameter[] p_Parameter)
        {
            DataSet myDs = new DataSet();
            if (m_SqlConnection.State != ConnectionState.Open)
                Open();
            try
            {
                SqlDataAdapter dataAdapter = new SqlDataAdapter(p_strCommand, m_SqlConnection);
                if (p_Parameter != null)
                {
                    dataAdapter.SelectCommand.Parameters.AddRange(p_Parameter);
                }
                dataAdapter.SelectCommand.CommandTimeout = 120; //2012-2-21 张国印 增加 用于查询超时
                dataAdapter.Fill(myDs);
            }
            catch (Exception ex)
            {
                ex.Source = ex.Source + " 对应的SQL语句为：" + p_strCommand;
                string strError = ex.Message + ex.Source;
                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)
                Open();

            try
            {

                SqlDataAdapter dataAdapter = new SqlDataAdapter(p_strCommand, m_SqlConnection);
                if (p_Parameter != null)
                {
                    dataAdapter.SelectCommand.Parameters.AddRange(p_Parameter);
                }
                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)
                Open();
            try
            {
                //dataAdapte实例化
                SqlDataAdapter dataAdapter = new SqlDataAdapter(p_strCommand, m_SqlConnection);
                if (p_Parameter != null)
                {
                    dataAdapter.SelectCommand.Parameters.AddRange(p_Parameter);
                }
                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_ConState != ConnectionState.Open)
                Open();
            try
            {
                SqlCommand oraCmd = new SqlCommand(p_strCommand, m_SqlConnection);
                if (p_Parameter != null)
                {
                    oraCmd.Parameters.AddRange(p_Parameter);
                }
                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_ConState != ConnectionState.Open)
                Open();
            try
            {
                SqlCommand oraCmd = new SqlCommand(p_strCommand, m_SqlConnection);
                if (p_Parameter != null)
                {
                    oraCmd.Parameters.AddRange(p_Parameter);
                }
                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)
                Open();

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

                SqlCommand sqlCommand = new SqlCommand(p_strCommand, m_SqlConnection);
                if (p_Parameter != null)
                {
                    sqlCommand.Parameters.AddRange(p_Parameter);
                }
                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)
                Open();
            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.CommandTimeout = 120; //2012-2-21 张国印 增加 用于查询超时
                dataAdapter.Fill(myDs);
            }
            catch (Exception ex)
            {
                ex.Source = ex.Source + " 对应的SQL语句为：" + p_strCommand;
                string strError = ex.Message + ex.Source;
                throw new Exception(strError, ex);
            }

            return myDs;
        }
        #endregion
    }
}