简单工厂模式封装数据库

通常项目需要支持多种类型的数据库，比如MySql, Oracle11g, SqlServer. 在代码中封装数据的的时候，一般使用工厂模式， 根据配置文件中的DBType创建数据库。

首先，数据库配置文件中需要配置以下内容：

  <DBType>MySql55</DBType>
  <DBHost>localhost</DBHost>
  <DBName>testdb</sDBName>
  <DBUser>root</PacsDBUser>
  <DBPassword>test2012</DBPassword>

其次，根据配置文件内容，创建数据库连接串，不同数据库的connectionString 有所不同, 所以需要用各自的方法建立各自的字符串：

 public static string CreateConnectionString(DBType dbType, string dbHost, string dbName, string dbUser, string dbPwd)
        {
            switch (dbType)
            {
                case DBType.SqlServer2008:
                    return CreateSQLServerConnectionString(dbHost, dbName, dbUser, dbPwd);

                case DBType.MySql55:
                    return CreateMySqlConnectionString(dbHost, dbName, dbUser, dbPwd);

                case DBType.Oracle11:
                    return CreateOracleConnectionString(dbHost, dbName, dbUser, dbPwd);
            }

最后， 创建工厂类， 提供封装数据库的方法。

public static class DatabaseFactory
    {
        public static IDatabase CreateDatabase(DBType dbType, string connStr = null)
        {
            switch (dbType)
            {
                case DBType.SqlServer2008:
                    return CreateDatabase(typeof (SqlServerDatabase), connStr);
                case DBType.MySql55:
                    return CreateDatabase(typeof (MySqlDatabase), connStr);
                case DBType.Oracle11:
                    return CreateDatabase(typeof (OracleDatabase), connStr);
            }

            Logger.ErrorWithFormat("Invalid DBType {0}! Failed to create database.", dbType.ToString());
            return null;
        }

        public static IDatabase CreateDatabase(Type dbType, string connStr = null)
        {
            System.Reflection.ConstructorInfo constructor = dbType.GetConstructor(Type.EmptyTypes);
            if (constructor == null)
            {
                Logger.ErrorWithFormat("Failed to create database, invalid database type {0}", dbType.ToString());
                return null;
            }

            try
            {
                var database = constructor.Invoke(null) as IDatabase;
                if (database != null && !string.IsNullOrEmpty(connStr))
                    database.ConnectionString = connStr;
                return database;
            }
            catch (Exception ex)
            {
                Logger.ErrorWithFormat("Failed to create database {0}. {1}", dbType.ToString(), ex.Message);
                throw;
            }
        }
    }

其中， IDatabase接口定义了操作数据库的一些方法：

 public interface IDatabase
  {
    IDbConnection Connection { get; }

    string ConnectionString { get; set; }

    bool Open();

    void Close();

    void Dispose();

    void BeginTrans();

    void CommitTrans();

    void RollbackTrans();

    int ExcuteSql(string strSql, bool enableLog = true);

    int ExcuteSql(string strSql, string[] strParams, object[] objValues, bool enableLog = true);

    object ExcuteScalarSql(string strSql, bool enableLog = true);

    object ExcuteScalarSql(string strSql, string[] strParams, object[] strValues, bool enableLog = true);

    DataSet ExcuteSqlForDataSet(string queryString, bool enableLog = true);
  }

SqlServerDatabase，MySqlDatabase，OracleDatabase三个class分别实现IDatabase，以SqlServerDatabase为例：

 public class SqlServerDatabase : IDatabase
    {
        #region Private fields
        private SqlConnection _conn;
        private SqlTransaction _trans;
        private bool _isInTransaction; 
        private string _connectionString;
        #endregion

        #region Public function
        public SqlServerDatabase()
        {
        }

        public SqlServerDatabase(string connStr)
        {
            _connectionString = connStr;

            _conn = new SqlConnection(_connectionString);
            //Logger.InfoWithFormat(" >> Create database connection with {0}", _connectionString);
        }

        private string GetConnectionState()
        {
            if (_conn == null)
            {
                Logger.Error("SQL Server connection was not created.");
                return null;
            }
                
            return _conn.State.ToString().ToUpper();
        }

        private bool IsConnectionOpened()
        {
            var state = GetConnectionState();
            return (state != null && state == "OPEN");
        }
        #endregion

        #region IDatabase interface
        public IDbConnection Connection
        {
            get { return _conn; }
        }

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

        public bool Open()
        {
            if (_conn == null)
            {
                if (!string.IsNullOrEmpty(_connectionString))
                {
                    _conn = new SqlConnection(_connectionString);
                    //Logger.InfoWithFormat(" >> Create database connection with {0}", _connectionString);
                }
                else
                {
                    Logger.Error("Invalid connection string when open database.");
                    return false;
                }
            }

            if (!IsConnectionOpened())
            {
                var result = false;
                try
                {
                    _conn.Open();
                    result = true;

                    //Logger.Info(" >> Opened database connection.");
                }
                catch (Exception ex)
                {
                    Logger.ErrorWithFormat("Failed to connect to database with connection string : {0}. {1}", _connectionString, ex.Message);
                }

                return result;
            }

            return true;
        }

        public void Close()
        {
            if (_conn == null)
                return;

            if (IsConnectionOpened())
            {
                _conn.Close();
                //Logger.Info(" >> Closed database connection.");
            }
        }

        public void Dispose()
        {
            if (_conn == null)
                return;

            Close();

            _conn.Dispose();
            _conn = null;
        }

        public void BeginTrans()
        {
            if (_conn != null)
            {
                if (_trans != null)
                {
                    Logger.Error("Transition already began! Please commit it before begin a new one.");
                    return;
                }

                _trans = _conn.BeginTransaction();
                _isInTransaction = true;
            }
        }

        public void CommitTrans()
        {
            if (_trans != null)
            {
                _trans.Commit();
                _isInTransaction = false;
            }
        }

        public void RollbackTrans()
        {
            if (_trans != null)
            {
                _trans.Rollback();
                _isInTransaction = false;
            }
        }

        public int ExcuteSql(string strSql, bool enableLog = true)
        {
            return ExcuteSql(strSql, null, null, enableLog);
        }

        public int ExcuteSql(string strSql, string[] strParams, object[] strValues, bool enableLog = true)
        {
            if (enableLog)
                Logger.DebugWithFormat(" >> Excuting SQL: {0}", strSql);

            if ((strParams != null) && (strParams.Length != strValues.Length))
                throw new ArgumentException("SQL查询参数和值个数不匹配!");

            var cmd = new SqlCommand { Connection = _conn, CommandText = strSql };

            if (_isInTransaction)
                cmd.Transaction = _trans;

            if (strParams != null)
            {
                for (int i = 0; i < strParams.Length; i++)
                    cmd.Parameters.AddWithValue(strParams[i], strValues[i]);
            }

            return cmd.ExecuteNonQuery();
        }


        public object ExcuteScalarSql(string strSql, bool enableLog = true)
        {
            return ExcuteScalarSql(strSql, null, null, enableLog);
        }

        public object ExcuteScalarSql(string strSql, string[] strParams, object[] strValues, bool enableLog = true)
        {
            if (enableLog)
                Logger.DebugWithFormat(" >> Excuting SQL: {0}", strSql);

            if ((strParams != null) && (strParams.Length != strValues.Length))
                throw new ArgumentException("SQL查询参数和值个数不匹配!");

            var cmd = new SqlCommand { Connection = _conn, CommandText = strSql };

            if (_isInTransaction)
                cmd.Transaction = _trans;

            if (strParams != null)
            {
                for (int i = 0; i < strParams.Length; i++)
                    cmd.Parameters.AddWithValue(strParams[i], strValues[i]);
            }

            object obj = null;
            try
            {
                obj = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                Logger.Error(ex.Message);
            }
            return obj;
        }

        public DataSet ExcuteSqlForDataSet(string queryString, bool enableLog = true)
        {
            if (enableLog)
                Logger.DebugWithFormat(" >> Excuting SQL: {0}", queryString);

            try
            {
                var cmd = new SqlCommand { Connection = _conn, CommandText = queryString };
                if (_isInTransaction)
                    cmd.Transaction = _trans;
                var ds = new DataSet();
                var ad = new SqlDataAdapter { SelectCommand = cmd };
                ad.Fill(ds);

                return ds;
            }
            catch (Exception ex)
            {
                Logger.ErrorWithFormat("ExcuteSqlForDataSet failed with SQL: {0}, {1}", queryString, ex.Message);
            }

            return null;
        }

        #endregion
    }

 

 

转载于:https://www.cnblogs.com/moon-sunshine/p/3173683.html