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

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

一丶主要关系图

 

二丶自定义配置文件节点

　　通过这种方式可以自定义节点，而不需要系统提供的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 

 1     public class DBAccessElement : ConfigurationElement
 2     {
 3         [ConfigurationProperty("Namespace", IsKey = true, IsRequired = true)]
 4         public string Namespace
 5         {
 6             get
 7             {
 8                 return (string)base["Namespace"];
 9             }
10             set
11             {
12                 base["Namespace"] = value;
13             }
14         }
15 
16         [ConfigurationProperty("FullName", DefaultValue = "", IsKey = false, IsRequired = false)]
17         public string FullName
18         {
19             get
20             {
21                 return (string)base["FullName"];
22             }
23             set
24             {
25                 base["FullName"] = value;
26             }
27         }
28     }

　　3.定义ElementCollection

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

View Code 
 1     public class DBConnectionElement : ConfigurationElement
 2     {
 3         [ConfigurationProperty("ConnectionString", IsKey = false, IsRequired = false)]
 4         public string ConnectionString
 5         {
 6             get
 7             {
 8                 return (string)base["ConnectionString"];
 9             }
10             set
11             {
12                 base["ConnectionString"] = value;
13             }
14         }
15     }
16 
17     [ConfigurationCollection(typeof(DBConnectionElement))]
18     public class DBConnectionCollection : ConfigurationElementCollection  //注意继承关系
19     {
20         protected override ConfigurationElement CreateNewElement()  //重载基类中的方法
21         {
22             return new DBConnectionElement();
23         }
24 
25         protected override object GetElementKey(ConfigurationElement element)
26         {
27             return ((DBConnectionElement)element).ConnectionString;
28         }
29 
30         public DBConnectionElement this[int index]
31         {
32             get
33             {
34                 return (DBConnectionElement)BaseGet(index);
35             }
36         }
37     }

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

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

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

 

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

 1     public sealed class DBBrokerManager
 2     {
 3         private static DBAccessBroker _accessBroke = null;
 4         private static object _obj = new object();
 5 
 6         private DBBrokerManager() { }
 7 
 8         static DBBrokerManager() { }
 9 
10         public static DBAccessBroker CurrentBroker
11         {
12             get
13             {
14                 if (_accessBroke == null)
15                 {
16                     lock (_obj)
17                     {
18                         if (_accessBroke == null)
19                         {
20                             _accessBroke = new DBAccessBroker(GenerateDBAccessFromConfiguration(), GenerateDBConnectionsFromConfiguration());
21                             return _accessBroke;
22                         }
23                     }
24                 }
25                 return _accessBroke;
26             }
27         }
28 
29         private static IDBAccess GenerateDBAccessFromConfiguration()  //通过反射获取实例对象
30         {
31             try
32             {
33                 DBAccessBrokerSection section = (DBAccessBrokerSection)ConfigurationManager.GetSection("DBAccessBroker");
34                 IDBAccess dbAccess = (IDBAccess)Assembly.Load(section.DBAccess.Namespace).CreateInstance(section.DBAccess.FullName);
35                 return dbAccess;
36             }
37             catch (ConfigurationException configurationException)
38             {
39                 LoggingManager.WriteLog(LogLevel.Exception, configurationException.Message, configurationException.InnerException);
40                 return null;
41             }
42             catch (Exception exception)
43             {
44                 LoggingManager.WriteLog(LogLevel.Exception, exception.Message, exception.InnerException);
45                 return null;
46             }
47         }
48 
49         private static IList<DBConnectionInfo> GenerateDBConnectionsFromConfiguration()  //通过反射获取具体的连接字符串信息，这要根据项目有所变化了
50         {
51             try
52             {
53                 List<DBConnectionInfo> connectionInfoList = new List<DBConnectionInfo>();
54                 DBAccessBrokerSection section = (DBAccessBrokerSection)ConfigurationManager.GetSection("DBAccessBroker");
55                 foreach (DBConnectionElement element in section.DBConnections)
56                 {
57                     DBConnectionInfo connectionInfo = (DBConnectionInfo)Assembly.Load(section.DBAccess.Namespace).CreateInstance(section.DBAccess.Namespace + ".DBConnectionInfo");
58                     foreach (PropertyInfo peopertyInfo in connectionInfo.GetType().GetProperties())
59                     {
60                         peopertyInfo.SetValue(connectionInfo, element.GetType().GetProperty(peopertyInfo.Name).GetValue(element, null), null);
61                     }
62                     connectionInfoList.Add(connectionInfo);
63                 }
64                 return connectionInfoList;
65             }
66             catch (ConfigurationException configurationException)
67             {
68                 LoggingManager.WriteLog(LogLevel.Exception, configurationException.Message, configurationException.InnerException);
69                 return null;
70             }
71             catch (Exception exception)
72             {
73                 LoggingManager.WriteLog(LogLevel.Exception, exception.Message, exception.InnerException);
74                 return null;
75             }
76         }
77     }

　　单例请参考：别再让面试官问你单例（暨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 过渡类

View Code 
 1     public class DBAccessBroker : IDBAccess
 2     {
 3         private IDBAccess _dbAccess;
 4         private IList<DBConnectionInfo> _connectionInfoList;
 5         private DBConnectionInfo _localConnection;
 6         private DBConnectionInfo _serverConnection;
 7 
 8         public DBAccessBroker(IDBAccess dbAccess, IList<DBConnectionInfo> connectionInfoList)
 9         {
10             _dbAccess = dbAccess;
11             _connectionInfoList = connectionInfoList;
12             _serverConnection = (from connectionInfo in connectionInfoList where connectionInfo.IsServer == true select connectionInfo).ToList().FirstOrDefault();
13             _localConnection = (from connectionInfo in connectionInfoList where connectionInfo.IsServer == false select connectionInfo).ToList().FirstOrDefault();
14             if (_localConnection != null)
15                 SetConnectionInfo(_localConnection);
16         }
17 
18         #region IDBAccess Members
19 
20         public void SetConnectionInfo(DBConnectionInfo connectionInfo)
21         {
22             BaseUtils.Common common = new BaseUtils.Common();
23             if (string.IsNullOrEmpty(connectionInfo.ConfigFile))
24                 throw new Exception("XML configuration path is empty, please provides correct XML path. Please contact administrator for the errors.");
25             if (string.IsNullOrEmpty(connectionInfo.SystemName))
26                 throw new Exception("System Name is empty, please provides correct system name that configured in XML configuration file. Please contact administrator for the errors.");
27             connectionInfo.ConnectionString = common.GetConnString(connectionInfo.ConfigFile, connectionInfo.SystemName, connectionInfo.IsConnStrEncrypted, connectionInfo.ConnStringNode);
28             if (_dbAccess != null)
29                 _dbAccess.SetConnectionInfo(connectionInfo);
30         }
31 
32         public int ExecuteNonQuery(CommandType commandType, string commandText, params  SqlParameter[] parameters)
33         {
34             return _dbAccess.ExecuteNonQuery(commandType, commandText, null);
35         }
36 
37         public int ExecuteNonQueryWithTrans(CommandType commandType, string commandText, params  SqlParameter[] parameters)
38         {
39             return _dbAccess.ExecuteNonQueryWithTrans(commandType, commandText, parameters);
40         }
41 
42         public DbDataReader ExecuteReader(CommandType commandType, string commandText, params  SqlParameter[] parameters)
43         {
44             return _dbAccess.ExecuteReader(commandType, commandText, parameters);
45         }
46 
47         public object ExecuteScalar(CommandType commandType, string commandText, params  SqlParameter[] parameters)
48         {
49             return _dbAccess.ExecuteScalar(commandType, commandText, parameters);
50         }
51 
52         public DataTable ExecuteDataTable(CommandType commandType, string commandText, string tableName)
53         {
54             return _dbAccess.ExecuteDataTable(commandType, commandText, tableName);
55         }
56 
57         public DataTable ExecuteDataTable(CommandType commandType, string commandText, string tableName, params  SqlParameter[] parameters)
58         {
59             return _dbAccess.ExecuteDataTable(commandType, commandText, tableName, parameters);
60         }
61 
62         #endregion
63     }

  　2.2 底层调用类

View Code 
  1     public class SQLDBAccess : IDBAccess
  2     {
  3         private string _connectionString = string.Empty;
  4 
  5         #region IDBAccess Members
  6 
  7         public void SetConnectionInfo(DBConnectionInfo connectionInfo)
  8         {
  9             if (string.IsNullOrEmpty(connectionInfo.ConnectionString))
 10                 LoggingManager.WriteLog(LogLevel.Exception, "The connection string is null or empty.", "");
 11             else
 12                 _connectionString = connectionInfo.ConnectionString;
 13         }
 14 
 15         public int ExecuteNonQuery(CommandType commandType, string commandText, params SqlParameter[] parameters)
 16         {
 17             try
 18             {
 19                 using (SqlConnection connection = new SqlConnection(_connectionString))
 20                 {
 21                     connection.Open();
 22                     SqlCommand command = connection.CreateCommand();
 23                     command.CommandType = commandType;
 24                     command.CommandText = commandText;
 25                     if (parameters != null && parameters.Length > 0)
 26                         command.Parameters.AddRange(parameters);
 27                     return command.ExecuteNonQuery();
 28                 }
 29             }
 30             catch (SqlException sqlException)
 31             {
 32                 LoggingManager.WriteLog(LogLevel.Exception, sqlException.Message, sqlException.InnerException);
 33                 return 0;
 34             }
 35             catch (Exception exception)
 36             {
 37                 LoggingManager.WriteLog(LogLevel.Exception, exception.Message, exception.InnerException);
 38                 return 0;
 39             }
 40         }
 41 
 42         public int ExecuteNonQueryWithTrans(CommandType commandType, string commandText, params SqlParameter[] parameters)
 43         {
 44             try
 45             {
 46                 using (SqlConnection connection = new SqlConnection(_connectionString))
 47                 {
 48                     connection.Open();
 49                     SqlTransaction transaction = connection.BeginTransaction("Transaction");
 50                     try
 51                     {
 52                         SqlCommand command = connection.CreateCommand();
 53                         command.CommandType = commandType;
 54                         command.CommandText = commandText;
 55                         command.Transaction = transaction;
 56                         if (parameters != null && parameters.Length > 0)
 57                             command.Parameters.AddRange(parameters);
 58                         int result = command.ExecuteNonQuery();
 59                         transaction.Commit();
 60                         return result;
 61                     }
 62                     catch (Exception executeException)
 63                     {
 64                         transaction.Rollback();
 65                         connection.Close();
 66                         LoggingManager.WriteLog(LogLevel.Exception, executeException.Message, executeException.InnerException);
 67                         return 0;
 68                     }
 69                 }
 70             }
 71             catch (SqlException sqlException)
 72             {
 73                 LoggingManager.WriteLog(LogLevel.Exception, sqlException.Message, sqlException.InnerException);
 74                 return 0;
 75             }
 76             catch (Exception exception)
 77             {
 78                 LoggingManager.WriteLog(LogLevel.Exception, exception.Message, exception.InnerException);
 79                 return 0;
 80             }
 81         }
 82 
 83         public DbDataReader ExecuteReader(CommandType commandType, string commandText, params SqlParameter[] parameters)
 84         {
 85             try
 86             {
 87                 using (SqlConnection connection = new SqlConnection(_connectionString))
 88                 {
 89                     connection.Open();
 90                     SqlCommand command = connection.CreateCommand();
 91                     command.CommandType = commandType;
 92                     command.CommandText = commandText;
 93                     if (parameters != null && parameters.Length > 0)
 94                         command.Parameters.AddRange(parameters);
 95                     return command.ExecuteReader(CommandBehavior.CloseConnection);
 96                 }
 97             }
 98             catch (SqlException sqlException)
 99             {
100                 LoggingManager.WriteLog(LogLevel.Exception, sqlException.Message, sqlException.InnerException.Message);
101                 return null;
102             }
103             catch (Exception exception)
104             {
105                 LoggingManager.WriteLog(LogLevel.Exception, exception.Message, exception.InnerException.Message);
106                 return null;
107             }
108         }
109 
110         public object ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] parameters)
111         {
112             try
113             {
114                 using (SqlConnection connection = new SqlConnection(_connectionString))
115                 {
116                     connection.Open();
117                     SqlCommand command = connection.CreateCommand();
118                     command.CommandType = commandType;
119                     command.CommandText = commandText;
120                     if (parameters != null && parameters.Length > 0)
121                         command.Parameters.AddRange(parameters);
122                     return command.ExecuteScalar();
123                 }
124             }
125             catch (SqlException sqlException)
126             {
127                 LoggingManager.WriteLog(LogLevel.Exception, sqlException.Message, sqlException.InnerException);
128                 return null;
129             }
130             catch (Exception exception)
131             {
132                 LoggingManager.WriteLog(LogLevel.Exception, exception.Message, exception.InnerException);
133                 return null;
134             }
135         }
136 
137         public DataTable ExecuteDataTable(CommandType commandType, string commandText, string tableName)
138         {
139             return ExecuteDataTable(commandType, commandText, tableName, null);
140         }
141 
142         public DataTable ExecuteDataTable(CommandType commandType, string commandText, string tableName, params SqlParameter[] parameters)
143         {
144             try
145             {
146                 using (SqlConnection connection = new SqlConnection(_connectionString))
147                 {
148                     connection.Open();
149                     DataTable table = new DataTable(tableName);
150                     SqlCommand command = connection.CreateCommand();
151                     command.CommandType = commandType;
152                     command.CommandText = commandText;
153                     if (parameters != null && parameters.Length > 0)
154                         command.Parameters.AddRange(parameters);
155                     SqlDataAdapter adapter = new SqlDataAdapter(command);
156                     adapter.Fill(table);
157                     return table;
158                 }
159             }
160             catch (SqlException sqlException)
161             {
162                 LoggingManager.WriteLog(LogLevel.Exception, sqlException.Message, sqlException.InnerException);
163                 return null;
164             }
165             catch (Exception exception)
166             {
167                 LoggingManager.WriteLog(LogLevel.Exception, exception.Message, exception.InnerException);
168                 return null;
169             }
170         }
171 
172         #endregion
173     }

 

五丶最后总结

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

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

posted @ 2012-08-19 17:42  TimYang  阅读(1904)  评论(4)  编辑  收藏  举报