学习之路十六：自定义数据库通用类 → 模仿+改进

这段时间参考了公司封装的数据库类库，感觉挺新奇了，从中学到了一些新的知识，有配置文件，有通过反射获得实例名，单例模式等等，这篇算是一个总结把，分享一些我的感受~~

一丶主要关系图

 

二丶自定义配置文件节点

　　通过这种方式可以自定义节点，而不需要系统提供的key和value来存储数据库连接字符串！

　　而且自定义节点算是比较简单的，主要弄懂一些继承关系就OK了...

　　Note：在定义节点的时候要注意一些主从关系，也就是Section，Element，ElementCollection之间的关系！

　　一个Section下面可能有很多的Element，一个Element下面可能有很多的Element集合，如：

  <appSettings> <!--Element-->
    <add key="" value=""/> <!--ElementCollection-->
    <add key="" value=""/> <!--可以存在多个add-->
    <add key="" value=""/>
  </appSettings>

　　在写代码之前先要熟悉ConfigurationSection，ConfigurationElement，ConfigurationElementCollection，下面三个基类对应这上面说的三者关系，理解他们写代码就容易多了，代码如下：

　　1.首先定义Section　　

    public class DBAccessBrokerSection : ConfigurationSection
    {
        [ConfigurationProperty("DBAccess")]  //定义Section下面有多少个Element，可以是单个Element，也可以是ElementCollection
        public DBAccessElement DBAccess
        {
            get
            {
                return (DBAccessElement)base["DBAccess"];
            }
        }

        [ConfigurationProperty("DBConnections")]  //注意这个特性的用法，它对应这配置文件中的具体写法
        public DBConnectionCollection DBConnections
        {
            get
            {
                return (DBConnectionCollection)base["DBConnections"];
            }
        }
    }

　　2.定义单个Element 

    public class DBAccessElement : ConfigurationElement
    {
        [ConfigurationProperty("Namespace", IsKey = true, IsRequired = true)]
        public string Namespace
        {
            get
            {
                return (string)base["Namespace"];
            }
            set
            {
                base["Namespace"] = value;
            }
        }

        [ConfigurationProperty("FullName", DefaultValue = "", IsKey = false, IsRequired = false)]
        public string FullName
        {
            get
            {
                return (string)base["FullName"];
            }
            set
            {
                base["FullName"] = value;
            }
        }
    }

　　3.定义ElementCollection

　　Note：主要要把ElementCollection和单个Element关联起来，因为ElementCollection也是由单个Element组合起来，所以这点很重要！

    public class DBConnectionElement : ConfigurationElement
    {
        [ConfigurationProperty("ConnectionString", IsKey = false, IsRequired = false)]
        public string ConnectionString
        {
            get
            {
                return (string)base["ConnectionString"];
            }
            set
            {
                base["ConnectionString"] = value;
            }
        }
    }

    [ConfigurationCollection(typeof(DBConnectionElement))]
    public class DBConnectionCollection : ConfigurationElementCollection  //注意继承关系
    {
        protected override ConfigurationElement CreateNewElement()  //重载基类中的方法
        {
            return new DBConnectionElement();
        }

        protected override object GetElementKey(ConfigurationElement element)
        {
            return ((DBConnectionElement)element).ConnectionString;
        }

        public DBConnectionElement this[int index]
        {
            get
            {
                return (DBConnectionElement)BaseGet(index);
            }
        }
    }

  　　4.最终在配置文件中的形式如下

  <DBAccessBroker>  //Section
    <DBAccess FullName="类的完全限定名" Namespace ="填写命名空间"/>  //Element
    <DBConnections>  //ElementCollection
      <add ConnectionString=""/> <!--这是伪代码-->
      <add ConnectionString=""/> //Elemetnt,这边属性不只有ConnectionString一个，还可以定义很多，这边只是写的伪代码
      <add ConnectionString=""/>
    </DBConnections>
  </DBAccessBroker>

　　参考：创建自定义配置节点（web.config和app.config都适用） 

 

 三丶通过单例实现只有一个实例，通过反射动态获取实例名

    public sealed class DBBrokerManager
    {
        private static DBAccessBroker _accessBroke = null;
        private static object _obj = new object();

        private DBBrokerManager() { }

        static DBBrokerManager() { }

        public static DBAccessBroker CurrentBroker
        {
            get
            {
                if (_accessBroke == null)
                {
                    lock (_obj)
                    {
                        if (_accessBroke == null)
                        {
                            _accessBroke = new DBAccessBroker(GenerateDBAccessFromConfiguration(), GenerateDBConnectionsFromConfiguration());
                            return _accessBroke;
                        }
                    }
                }
                return _accessBroke;
            }
        }

        private static IDBAccess GenerateDBAccessFromConfiguration()  //通过反射获取实例对象
        {
            try
            {
                DBAccessBrokerSection section = (DBAccessBrokerSection)ConfigurationManager.GetSection("DBAccessBroker");
                IDBAccess dbAccess = (IDBAccess)Assembly.Load(section.DBAccess.Namespace).CreateInstance(section.DBAccess.FullName);
                return dbAccess;
            }
            catch (ConfigurationException configurationException)
            {
                LoggingManager.WriteLog(LogLevel.Exception, configurationException.Message, configurationException.InnerException);
                return null;
            }
            catch (Exception exception)
            {
                LoggingManager.WriteLog(LogLevel.Exception, exception.Message, exception.InnerException);
                return null;
            }
        }

        private static IList<DBConnectionInfo> GenerateDBConnectionsFromConfiguration()  //通过反射获取具体的连接字符串信息，这要根据项目有所变化了
        {
            try
            {
                List<DBConnectionInfo> connectionInfoList = new List<DBConnectionInfo>();
                DBAccessBrokerSection section = (DBAccessBrokerSection)ConfigurationManager.GetSection("DBAccessBroker");
                foreach (DBConnectionElement element in section.DBConnections)
                {
                    DBConnectionInfo connectionInfo = (DBConnectionInfo)Assembly.Load(section.DBAccess.Namespace).CreateInstance(section.DBAccess.Namespace + ".DBConnectionInfo");
                    foreach (PropertyInfo peopertyInfo in connectionInfo.GetType().GetProperties())
                    {
                        peopertyInfo.SetValue(connectionInfo, element.GetType().GetProperty(peopertyInfo.Name).GetValue(element, null), null);
                    }
                    connectionInfoList.Add(connectionInfo);
                }
                return connectionInfoList;
            }
            catch (ConfigurationException configurationException)
            {
                LoggingManager.WriteLog(LogLevel.Exception, configurationException.Message, configurationException.InnerException);
                return null;
            }
            catch (Exception exception)
            {
                LoggingManager.WriteLog(LogLevel.Exception, exception.Message, exception.InnerException);
                return null;
            }
        }
    }

　　单例请参考：别再让面试官问你单例（暨6种实现方式让你堵住面试官的嘴）  

 

四丶定义实现数据库操作的接口，用两个类来实现它，一个做中间过渡，一个是底层调用

　　Note：(其实在很多项目中这个中间过渡是不需要的，但这个要根据具体的项目来变化)。

　　1.接口定义

    public interface IDBAccess
    {
        void SetConnectionInfo(DBConnectionInfo connectionInfo);

        int ExecuteNonQuery(CommandType commandType, string commandText, params SqlParameter[] parameters);

        int ExecuteNonQueryWithTrans(CommandType commandType, string commandText, params SqlParameter[] parameters);

        DbDataReader ExecuteReader(CommandType commandType, string commandText, params SqlParameter[] parameters);

        object ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] parameters);

        DataTable ExecuteDataTable(CommandType commandType, string commandText, string tableName);

        DataTable ExecuteDataTable(CommandType commandType, string commandText, string tableName, params SqlParameter[] parameters);
    }

　　2.两个类同时实现这个接口，一个做过渡，一个做底层调用

　　2.1 过渡类

    public class DBAccessBroker : IDBAccess
    {
        private IDBAccess _dbAccess;
        private IList<DBConnectionInfo> _connectionInfoList;
        private DBConnectionInfo _localConnection;
        private DBConnectionInfo _serverConnection;

        public DBAccessBroker(IDBAccess dbAccess, IList<DBConnectionInfo> connectionInfoList)
        {
            _dbAccess = dbAccess;
            _connectionInfoList = connectionInfoList;
            _serverConnection = (from connectionInfo in connectionInfoList where connectionInfo.IsServer == true select connectionInfo).ToList().FirstOrDefault();
            _localConnection = (from connectionInfo in connectionInfoList where connectionInfo.IsServer == false select connectionInfo).ToList().FirstOrDefault();
            if (_localConnection != null)
                SetConnectionInfo(_localConnection);
        }

        #region IDBAccess Members

        public void SetConnectionInfo(DBConnectionInfo connectionInfo)
        {
            BaseUtils.Common common = new BaseUtils.Common();
            if (string.IsNullOrEmpty(connectionInfo.ConfigFile))
                throw new Exception("XML configuration path is empty, please provides correct XML path. Please contact administrator for the errors.");
            if (string.IsNullOrEmpty(connectionInfo.SystemName))
                throw new Exception("System Name is empty, please provides correct system name that configured in XML configuration file. Please contact administrator for the errors.");
            connectionInfo.ConnectionString = common.GetConnString(connectionInfo.ConfigFile, connectionInfo.SystemName, connectionInfo.IsConnStrEncrypted, connectionInfo.ConnStringNode);
            if (_dbAccess != null)
                _dbAccess.SetConnectionInfo(connectionInfo);
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText, params  SqlParameter[] parameters)
        {
            return _dbAccess.ExecuteNonQuery(commandType, commandText, null);
        }

        public int ExecuteNonQueryWithTrans(CommandType commandType, string commandText, params  SqlParameter[] parameters)
        {
            return _dbAccess.ExecuteNonQueryWithTrans(commandType, commandText, parameters);
        }

        public DbDataReader ExecuteReader(CommandType commandType, string commandText, params  SqlParameter[] parameters)
        {
            return _dbAccess.ExecuteReader(commandType, commandText, parameters);
        }

        public object ExecuteScalar(CommandType commandType, string commandText, params  SqlParameter[] parameters)
        {
            return _dbAccess.ExecuteScalar(commandType, commandText, parameters);
        }

        public DataTable ExecuteDataTable(CommandType commandType, string commandText, string tableName)
        {
            return _dbAccess.ExecuteDataTable(commandType, commandText, tableName);
        }

        public DataTable ExecuteDataTable(CommandType commandType, string commandText, string tableName, params  SqlParameter[] parameters)
        {
            return _dbAccess.ExecuteDataTable(commandType, commandText, tableName, parameters);
        }

        #endregion
    }

  　2.2 底层调用类

    public class SQLDBAccess : IDBAccess
    {
        private string _connectionString = string.Empty;

        #region IDBAccess Members

        public void SetConnectionInfo(DBConnectionInfo connectionInfo)
        {
            if (string.IsNullOrEmpty(connectionInfo.ConnectionString))
                LoggingManager.WriteLog(LogLevel.Exception, "The connection string is null or empty.", "");
            else
                _connectionString = connectionInfo.ConnectionString;
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText, params SqlParameter[] parameters)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();
                    SqlCommand command = connection.CreateCommand();
                    command.CommandType = commandType;
                    command.CommandText = commandText;
                    if (parameters != null && parameters.Length > 0)
                        command.Parameters.AddRange(parameters);
                    return command.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlException)
            {
                LoggingManager.WriteLog(LogLevel.Exception, sqlException.Message, sqlException.InnerException);
                return 0;
            }
            catch (Exception exception)
            {
                LoggingManager.WriteLog(LogLevel.Exception, exception.Message, exception.InnerException);
                return 0;
            }
        }

        public int ExecuteNonQueryWithTrans(CommandType commandType, string commandText, params SqlParameter[] parameters)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();
                    SqlTransaction transaction = connection.BeginTransaction("Transaction");
                    try
                    {
                        SqlCommand command = connection.CreateCommand();
                        command.CommandType = commandType;
                        command.CommandText = commandText;
                        command.Transaction = transaction;
                        if (parameters != null && parameters.Length > 0)
                            command.Parameters.AddRange(parameters);
                        int result = command.ExecuteNonQuery();
                        transaction.Commit();
                        return result;
                    }
                    catch (Exception executeException)
                    {
                        transaction.Rollback();
                        connection.Close();
                        LoggingManager.WriteLog(LogLevel.Exception, executeException.Message, executeException.InnerException);
                        return 0;
                    }
                }
            }
            catch (SqlException sqlException)
            {
                LoggingManager.WriteLog(LogLevel.Exception, sqlException.Message, sqlException.InnerException);
                return 0;
            }
            catch (Exception exception)
            {
                LoggingManager.WriteLog(LogLevel.Exception, exception.Message, exception.InnerException);
                return 0;
            }
        }

        public DbDataReader ExecuteReader(CommandType commandType, string commandText, params SqlParameter[] parameters)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();
                    SqlCommand command = connection.CreateCommand();
                    command.CommandType = commandType;
                    command.CommandText = commandText;
                    if (parameters != null && parameters.Length > 0)
                        command.Parameters.AddRange(parameters);
                    return command.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
            catch (SqlException sqlException)
            {
                LoggingManager.WriteLog(LogLevel.Exception, sqlException.Message, sqlException.InnerException.Message);
                return null;
            }
            catch (Exception exception)
            {
                LoggingManager.WriteLog(LogLevel.Exception, exception.Message, exception.InnerException.Message);
                return null;
            }
        }

        public object ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] parameters)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();
                    SqlCommand command = connection.CreateCommand();
                    command.CommandType = commandType;
                    command.CommandText = commandText;
                    if (parameters != null && parameters.Length > 0)
                        command.Parameters.AddRange(parameters);
                    return command.ExecuteScalar();
                }
            }
            catch (SqlException sqlException)
            {
                LoggingManager.WriteLog(LogLevel.Exception, sqlException.Message, sqlException.InnerException);
                return null;
            }
            catch (Exception exception)
            {
                LoggingManager.WriteLog(LogLevel.Exception, exception.Message, exception.InnerException);
                return null;
            }
        }

        public DataTable ExecuteDataTable(CommandType commandType, string commandText, string tableName)
        {
            return ExecuteDataTable(commandType, commandText, tableName, null);
        }

        public DataTable ExecuteDataTable(CommandType commandType, string commandText, string tableName, params SqlParameter[] parameters)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();
                    DataTable table = new DataTable(tableName);
                    SqlCommand command = connection.CreateCommand();
                    command.CommandType = commandType;
                    command.CommandText = commandText;
                    if (parameters != null && parameters.Length > 0)
                        command.Parameters.AddRange(parameters);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(table);
                    return table;
                }
            }
            catch (SqlException sqlException)
            {
                LoggingManager.WriteLog(LogLevel.Exception, sqlException.Message, sqlException.InnerException);
                return null;
            }
            catch (Exception exception)
            {
                LoggingManager.WriteLog(LogLevel.Exception, exception.Message, exception.InnerException);
                return null;
            }
        }

        #endregion
    }

 

五丶最后总结

　　小菜在这里献丑了，代码里面还有需要完善的地方，如果园友们对代码有什么好的建议，请提出来我会虚心接受和请教的！ 

　　已同步至：程序猿个人文章目录索引