bizoledb.cs

.Net控件

using System;
using System.Data;
using System.Data.OleDb;

namespace BlestBiz.Data
{

    public enum BizDbType { ODBC = 0, ACCESS97 = 1, ACCESS2000 = 2, ACCESS2003 = 3, ACCESS2007 = 4 };		


	/// <summary>
	/// 有关数据库的操作如打开、关闭、事务、操作、查询、绑定。如不特别指出，构造函数默认OleDb数据库连接
	/// </summary>

	public class BizDba: IDisposable
    {

        #region 释放资源
        private bool _disposed = false;

        public bool IsDisposed
        {
            get { return _disposed; }
            set { _disposed = value; }
        }

        ~BizDba()
        {
            InternalDispose(false);
        }

        public void Dispose()
        {
            InternalDispose(true);
        }

        protected void InternalDispose(bool disposing)
        {
            if(disposing)
            {
                GC.SuppressFinalize(this);
            }
        }
        #endregion

        
        //***私有成员变量***
		private string			mConnectionString = "";					//数据库连接字符串
		private bool			mTransSuccess = false;					//事务处理是否成功

		/*
		*******************************************************************************
		*定义最常用的对象，以支持跨数据库、独立于OleDb的类库
		*******************************************************************************
		*/

		//连接
		private OleDbConnection		mBizDbCn	= null;	//= new OleDbConnection();	//OleDb数据库连接

		//命令
		private OleDbCommand		mBizDbCmd	= null;	//= new OleDbCommand();		//定义Command对象

		//适配器
		private OleDbDataAdapter	mBizDbDA	= null;	//= new OleDbDataAdapter();	//定义DataAdapter对象

		//参数
		private OleDbParameter		mBizDbPar	= null;	//= new OleDbParameter();	//定义Parameter对象

		//事务
		private OleDbTransaction	mBizDbTr	= null;								//定义Transaction对象，以支持事务处理


		/// <summary>
		/// 构造函数默认OleDbConnection数据库连接
		/// </summary>
		public BizDba()
		{
		}

		public string ConnectionString
		{
			get { return mConnectionString; }
			set { mConnectionString = value; }
		}

        public OleDbDataAdapter DbAdapter
        {
            get { return mBizDbDA; }
            set { mBizDbDA = value; }
        }

        public OleDbCommand DbCmd
        {
            get { return mBizDbCmd; }
            set { mBizDbCmd = value; }
        }

        public OleDbParameter DbPar
        {
            get { return mBizDbPar; }
            set { mBizDbPar = value; }
        }





		//-------------------------------------------------------------------------------------------------------------------------
		/// <summary>
		/// 根据联接串，打开并返回 SqlConnection/OleDbConnection 数据库联接
		/// </summary>
		/// <Input>
		///		<param name="strConnectionString">数据库联接字符串。如果不含有"PROVIDER"或者含有"SQLOLEDB"将返回SqlConnection的object，否则返回OleDbConnection的object</param>
		/// </Input>   
		/// <Other>无</Other>
		/// <returns>返回 object类型的 OleDbConnection 数据库联接</returns>

        private OleDbConnection GetDbConnection(string connectionString)
		{
			OleDbConnection bizDbCn;

            try
            {
                bizDbCn = new OleDbConnection(connectionString);
                bizDbCn.Open();
                return bizDbCn;
            }
            catch (Exception ex)
            {
                throw new Exception("错误：连接数据库失败！" + "\n" + ex.Message);
            }
		}
		

		/// <summary>
		/// 用connectionString打开并返回数据库连接
		/// </summary>
		/// <returns></returns>
        public OleDbConnection Connect()
		{
            try
            {
                if (mBizDbCn != null)
                {
                    if (mBizDbCn.State != ConnectionState.Open)
                        mBizDbCn.Open();
                    return mBizDbCn;
                }
                else
                {
                    if (mConnectionString != "")
                    {
                        DisconnectDB();
                        return GetDbConnection(mConnectionString);
                    }
                    else
                        return null;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("错误：连接数据库失败！" + "\n" + ex.Message);
            }
        }

        public OleDbConnection ConnectOleDb(string connectionString)
		{
			OleDbConnection bizDbCn = null;

			if (connectionString.Length <= 0) throw new Exception("错误：连接数据库失败！" + "\n" + "Connection String Not Specified");
			DisconnectDB();
			mConnectionString = connectionString;

			try
			{
				bizDbCn = new OleDbConnection(connectionString);
				bizDbCn.Open();
				mBizDbCn = bizDbCn;
				return bizDbCn;
			}
			catch(Exception ex)
			{
				bizDbCn.Dispose();
				bizDbCn = null;
                throw new Exception("错误：连接数据库失败！" + "\n" + ex.Message);
			}
		}

		/// <summary>
		/// 打开并返回 OleDbConnection 联接
		/// </summary>
		/// <returns>返回 OleDbConnection 联接</returns>
        /// 

        public OleDbConnection ConnectOleDb(BizDbType dataBaseType, string dataSourceOrAccessPath)
        {
            string connectionString = "";

            switch (dataBaseType)
            {
                case BizDbType.ODBC:
                    connectionString = "DSN=" + dataSourceOrAccessPath;
                    break;

                case BizDbType.ACCESS97:
                    connectionString = "Provider=Microsoft.Jet.OLEDB.3.51" +
                                       ";Data Source=" + dataSourceOrAccessPath;
                    break;

                case BizDbType.ACCESS2000:
                case BizDbType.ACCESS2003:
                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0" +
                                       ";Data Source=" + dataSourceOrAccessPath;
                    break;

                case BizDbType.ACCESS2007:
                    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0" +
                                       ";Data Source=" + dataSourceOrAccessPath;
                    break;
            }

            try
            {
                ConnectOleDb(connectionString);
                return mBizDbCn;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public OleDbConnection ConnectOleDb(BizDbType dataBaseType, string dataSourceOrAccessPath, string password)
        {
            string connectionString = "";

            switch (dataBaseType)
            {
                case BizDbType.ODBC:
                    connectionString = "DSN=" + dataSourceOrAccessPath +
                                       ";PWD=" + password;
                    break;

                case BizDbType.ACCESS97:
                    connectionString = "Provider=Microsoft.Jet.OLEDB.3.51;User Id=Admin" +
                                       ";Jet OLEDB:Database Password=" + password +
                                       ";Data Source=" + dataSourceOrAccessPath +
                                       ";Persist Security Info=false";
                    break;

                case BizDbType.ACCESS2000:
                case BizDbType.ACCESS2003:
                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin" +
                                       ";Jet OLEDB:Database Password=" + password +
                                       ";Data Source=" + dataSourceOrAccessPath +
                                       ";Persist Security Info=false";
                    break;

                case BizDbType.ACCESS2007:
                    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;User Id=Admin" +
                                       ";Jet OLEDB:Database Password=" + password +
                                       ";Data Source=" + dataSourceOrAccessPath +
                                       ";Persist Security Info=false";
                    break;
            }

            try
            {
                ConnectOleDb(connectionString);
                return mBizDbCn;
            }
            catch (Exception ex)
            {
                throw new Exception("错误：连接数据库失败！" + "\n" + ex.Message);
            }
        }

		public OleDbConnection ConnectOleDb(BizDbType dataBaseType, string dataSourceOrAccessPath, string userID, string password)
		{
			string connectionString = "";

			switch (dataBaseType)
			{
                case BizDbType.ODBC:
                    connectionString = "DSN=" + dataSourceOrAccessPath + 
                                       ";UID=" + userID + 
                                       ";PWD=" + password;
                    break;
                
                case BizDbType.ACCESS97:
                    connectionString = "Provider=Microsoft.Jet.OLEDB.3.51" + 
                                       ";Password=" + password + 
                                       ";User ID=" + userID +
                                       ";Data Source=" + dataSourceOrAccessPath;
                    break;
                
                case BizDbType.ACCESS2000:
                case BizDbType.ACCESS2003:
                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0" + 
						               ";Password=" + password + 
						               ";User ID=" + userID + 
						               ";Data Source=" + dataSourceOrAccessPath;
					break;
                
                case BizDbType.ACCESS2007:
                    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0" +
                                       ";Password=" + password +
                                       ";User ID=" + userID +
                                       ";Data Source=" + dataSourceOrAccessPath;
                    break;
            }

			try
			{
				ConnectOleDb(connectionString);
				return mBizDbCn;
			}
			catch(Exception ex)
			{
                throw new Exception("错误：连接数据库失败！" + "\n" + ex.Message);
			}
		}


		/// <summary>
		/// 关闭数据库连接
		/// </summary>
		public void DisconnectDB()
		{
			if (mBizDbCn != null)
			{
				if (mBizDbCn.State == ConnectionState.Open) mBizDbCn.Close();
				mBizDbCn.Dispose();
				mBizDbCn = null;
			}
		}

		/// <summary>
		/// 开始事务
		/// </summary>
		public void BeginTrans()
		{
			mTransSuccess = true;	//默认执行成功，一旦ExeSql中有执行不成功SQL，m_blnTransSuccess = false

			if (mBizDbCn != null)
				mBizDbTr = mBizDbCn.BeginTransaction();
		}

		/// <summary>
		/// 提交事务
		/// </summary>
		public void Commit()
		{
			try
			{
				if (mBizDbTr != null)
				{
					mBizDbTr.Commit();
					mBizDbTr = null;
				}
			}
			catch (Exception ex)
			{
                throw new Exception("错误：操作数据库失败！" + "\n" + ex.Message);
			}
		}
		
		/// <summary>
		/// 事务回滚
		/// </summary>
		public void Rollback()
		{
			try
			{
				if (mBizDbTr != null)
				{
					mBizDbTr.Rollback();
					mBizDbTr = null;
				}
			}
			catch(Exception ex)
			{
                throw new Exception("错误：操作数据库失败！" + "\n" + ex.Message);
			}
		}


		/// <summary>
		/// 根据连接数据库类型隐示获取或数据库连接
		/// </summary>
        public OleDbConnection DbCn
		{
			get { return mBizDbCn; }
		}

        public OleDbDataAdapter NewDbAdapter()
        {
                return new OleDbDataAdapter();
        }

        public OleDbCommand NewDbCmd()
        {
            return new OleDbCommand("", mBizDbCn, mBizDbTr);
        }

        public OleDbParameter NewDbPar()
        {
            return new OleDbParameter();
        }


		/// <summary>
		/// 获取事务执行是否成功状态
		/// </summary>
		public bool IsSuccessTrans
		{
			get
			{
				return mTransSuccess;
			}
		}


		/// <param name="p_CommandText">指定的操作型SQL语句(DELETE/INSERT/UPDATE)或操作型存储过程</param>
		/// <param name="p_OleDbConnection">OleDbConnection连接对象</param>
		public int ExeSql(string commandText)
		{
			int intEffect;

			try
			{
				intEffect = ExecuteSql(commandText);
				return intEffect;
			}
			catch (Exception ex)
			{
				mTransSuccess = false;
                throw new Exception("错误：操作数据库失败！" + "\n" + ex.Message);
			}
		}

		public int ExeSql(string commandText, string autoIdFieldName)
		{
			int intEffect;

			try