详细解说NParsing框架实现原理 —— 1）控制器组件（ObHelper）

ObHelper —— O(object) B(Database) 软件对象模型到数据库关系模型的映射控制器

控制器组件是本框架的核心组件，是对数据库所有操作的实现。实现控制器组件必须解决以下几个问题：

1、解决不同数据库平台的支持。
2、解决同一项目多个数据库连接的支持。
3、解决数据库连接和数据库操作类库的配置。
4、解决SQL语句如何动态生成。

 

一、如何解决不同数据库平台的支持？

我使用一个公用的数据库操作类（DotNet.Frameworks.NParsing.DbUtilities.DbHelper）统一调用（里面使用DbConnection, DbCommand，DbDataAdapter，DbParameter组件），
实现ExecuteNonQuery，ExecuteDataset，ExecuteReader，ExecuteScalar

 

代码如下：

代码

  1  using  System;
  2  using  System.Collections.Generic;
  3  using  System.Data;
  4  using  System.Data.Common;
  5  using  DotNet.Frameworks.NParsing.Interface;
  6 
  7  namespace  DotNet.Frameworks.NParsing.DbUtilities
  8  {
  9       public   class  DbHelper : IDisposable
 10      {
 11           private   readonly  DbConnection _DbConn;
 12           private   readonly  IDbHelper _IDbHelper;
 13 
 14           public  DbHelper(IDbHelper iDbHelper)
 15          {
 16              _IDbHelper  =  iDbHelper;
 17              _DbConn  =  iDbHelper.DbConnection();
 18              _DbConn.Open();
 19          }
 20 
 21           #region  IDisposable Members
 22 
 23           public   void  Dispose()
 24          {
 25               if  (_DbConn  !=   null )
 26              {
 27                   if  (_DbConn.State  ==  ConnectionState.Open)
 28                  {
 29                      _DbConn.Close();
 30                  }
 31                  _DbConn.Dispose();
 32              }
 33          }
 34 
 35           #endregion
 36 
 37           #region  AttachParameters
 38 
 39           private   static   void  AttachParameters(DbCommand command, IEnumerable < DbParameter >  commandParameters)
 40          {
 41               if  (command  ==   null )  throw   new  ArgumentNullException( " command " );
 42               if  (commandParameters  !=   null )
 43              {
 44                   foreach  (DbParameter p  in  commandParameters)
 45                  {
 46                       if  (p  !=   null )
 47                      {
 48                           if  ((p.Direction  ==  ParameterDirection.InputOutput  ||  p.Direction  ==  ParameterDirection.Input)  &&
 49                              (p.Value  ==   null ))
 50                          {
 51                              p.Value  =  DBNull.Value;
 52                          }
 53                          command.Parameters.Add(p);
 54                      }
 55                  }
 56              }
 57          }
 58 
 59           #endregion
 60 
 61           #region  Exists
 62 
 63           public   bool  Exists( string  commandText)
 64          {
 65               return  Exists(commandText,  new  DbParameter[ 0 ]);
 66          }
 67 
 68           public   bool  Exists( string  commandText,  params  DbParameter[] commandParameters)
 69          {
 70               using  (DbCommand dbCom  =  _IDbHelper.DbCommand())
 71              {
 72                   try
 73                  {
 74                      dbCom.Connection  =  _DbConn;
 75                      dbCom.CommandType  =  CommandType.Text;
 76                      dbCom.CommandText  =  commandText;
 77                      dbCom.CommandTimeout  =   240 ;
 78                      AttachParameters(dbCom, commandParameters);
 79                       object  obj  =  dbCom.ExecuteScalar();
 80 
 81                       int  cmdresult;
 82                       if  ((Equals(obj,  null ))  ||  (Equals(obj, DBNull.Value)))
 83                      {
 84                          cmdresult  =   0 ;
 85                      }
 86                       else
 87                      {
 88                          cmdresult  =   int .Parse(obj.ToString());
 89                      }
 90                       if  (cmdresult  ==   0 )
 91                      {
 92                           return   false ;
 93                      }
 94                       else
 95                      {
 96                           return   true ;
 97                      }
 98                  }
 99                   finally
100                  {
101                      dbCom.Parameters.Clear();
102                  }
103              }
104          }
105 
106           public   static   bool  Exists(IDbHelper iDbHelper, DbTransaction transaction,  string  commandText,  params  DbParameter[] commandParameters)
107          {
108               using  (DbCommand dbCom  =  iDbHelper.DbCommand())
109              {
110                   try
111                  {
112                      dbCom.Connection  =  transaction.Connection;
113                      dbCom.Transaction  =  transaction;
114                      dbCom.CommandType  =  CommandType.Text;
115                      dbCom.CommandText  =  commandText;
116                      dbCom.CommandTimeout  =   240 ;
117                      AttachParameters(dbCom, commandParameters);
118                       object  obj  =  dbCom.ExecuteScalar();
119 
120                       int  cmdresult;
121                       if  ((Equals(obj,  null ))  ||  (Equals(obj, DBNull.Value)))
122                      {
123                          cmdresult  =   0 ;
124                      }
125                       else
126                      {
127                          cmdresult  =   int .Parse(obj.ToString());
128                      }
129                       if  (cmdresult  ==   0 )
130                      {
131                           return   false ;
132                      }
133                       else
134                      {
135                           return   true ;
136                      }
137                  }
138                   finally
139                  {
140                      dbCom.Parameters.Clear();
141                  }
142              }
143          }
144 
145           #endregion
146 
147           #region  ExecuteNonQuery
148 
149           public   int  ExecuteNonQuery( string  commandText)
150          {
151               return  ExecuteNonQuery(CommandType.Text, commandText,  new  DbParameter[ 0 ]);
152          }
153 
154           public   int  ExecuteNonQuery(CommandType commandType,  string  commandText)
155          {
156               return  ExecuteNonQuery(commandType, commandText,  new  DbParameter[ 0 ]);
157          }
158 
159           public   int  ExecuteNonQuery( string  commandText,  params  DbParameter[] commandParameters)
160          {
161               using  (DbCommand dbCom  =  _IDbHelper.DbCommand())
162              {
163                   int  iRet;
164                   try
165                  {
166                      dbCom.Connection  =  _DbConn;
167                      dbCom.CommandType  =  CommandType.Text;
168                      dbCom.CommandText  =  commandText;
169                      dbCom.CommandTimeout  =   240 ;
170                      AttachParameters(dbCom, commandParameters);
171                      iRet  =  dbCom.ExecuteNonQuery();
172                  }
173                   finally
174                  {
175                      dbCom.Parameters.Clear();
176                  }
177                   return  iRet;
178              }
179          }
180 
181           public   int  ExecuteNonQuery(CommandType commandType,  string  commandText,  params  DbParameter[] commandParameters)
182          {
183               using  (DbCommand dbCom  =  _IDbHelper.DbCommand())
184              {
185                   int  iRet;
186                   try
187                  {
188                      dbCom.Connection  =  _DbConn;
189                      dbCom.CommandType  =  commandType;
190                      dbCom.CommandText  =  commandText;
191                      dbCom.CommandTimeout  =   240 ;
192                      AttachParameters(dbCom, commandParameters);
193                      iRet  =  dbCom.ExecuteNonQuery();
194                  }
195                   finally
196                  {
197                      dbCom.Parameters.Clear();
198                  }
199                   return  iRet;
200              }
201          }
202 
203           public   int  ExecuteNonQuery(IDbHelper iDbHelper, DbTransaction transaction,  string  commandText)
204          {
205               return  ExecuteNonQuery(iDbHelper, transaction, CommandType.Text, commandText,  new  DbParameter[ 0 ]);
206          }
207 
208           public   int  ExecuteNonQuery(IDbHelper iDbHelper, DbTransaction transaction, CommandType commandType,  string  commandText)
209          {
210               return  ExecuteNonQuery(iDbHelper, transaction, commandType, commandText,  new  DbParameter[ 0 ]);
211          }
212 
213           public   static   int  ExecuteNonQuery(IDbHelper iDbHelper, DbTransaction transaction,  string  commandText,  params  DbParameter[] commandParameters)
214          {
215               using  (DbCommand dbCom  =  iDbHelper.DbCommand())
216              {
217                   int  iRet;
218                   try
219                  {
220                      dbCom.Connection  =  transaction.Connection;
221                      dbCom.Transaction  =  transaction;
222                      dbCom.CommandType  =  CommandType.Text;
223                      dbCom.CommandText  =  commandText;
224                      dbCom.CommandTimeout  =   240 ;
225                      AttachParameters(dbCom, commandParameters);
226                      iRet  =  dbCom.ExecuteNonQuery();
227                  }
228                   finally
229                  {
230                      dbCom.Parameters.Clear();
231                  }
232                   return  iRet;
233              }
234          }
235 
236           public   int  ExecuteNonQuery(IDbHelper iDbHelper, DbTransaction transaction, CommandType commandType,  string  commandText,
237                                      params  DbParameter[] commandParameters)
238          {
239               using  (DbCommand dbCom  =  _IDbHelper.DbCommand())
240              {
241                   int  iRet;
242                   try
243                  {
244                      dbCom.Connection  =  transaction.Connection;
245                      dbCom.Transaction  =  transaction;
246                      dbCom.CommandType  =  commandType;
247                      dbCom.CommandText  =  commandText;
248                      dbCom.CommandTimeout  =   240 ;
249                      AttachParameters(dbCom, commandParameters);
250                      iRet  =  dbCom.ExecuteNonQuery();
251                  }
252                   finally
253                  {
254                      dbCom.Parameters.Clear();
255                  }
256                   return  iRet;
257              }
258          }
259 
260           #endregion
261 
262           #region  ExecuteDataset
263 
264           public  DataSet ExecuteDataset( string  commandText)
265          {
266               return  ExecuteDataset(CommandType.Text, commandText,  new  DbParameter[ 0 ]);
267          }
268 
269           public  DataSet ExecuteDataset(CommandType commandType,  string  commandText)
270          {
271               return  ExecuteDataset(commandType, commandText,  new  DbParameter[ 0 ]);
272          }
273 
274           public  DataSet ExecuteDataset(CommandType commandType,  string  commandText,  params  DbParameter[] commandParameters)
275          {
276               using  (DbDataAdapter da  =  _IDbHelper.DbDataAdapter())
277              {
278                   try
279                  {
280                      da.SelectCommand  =  _IDbHelper.DbCommand();
281                      da.SelectCommand.Connection  =  _DbConn;
282                      da.SelectCommand.CommandType  =  commandType;
283                      da.SelectCommand.CommandText  =  commandText;
284                      da.SelectCommand.CommandTimeout  =   240 ;
285 
286                      AttachParameters(da.SelectCommand, commandParameters);
287                      var dataSet  =   new  DataSet();
288                      da.Fill(dataSet);
289                       return  dataSet;
290                  }
291                   finally
292                  {
293                      da.SelectCommand.Parameters.Clear();
294                  }
295              }
296          }
297 
298           public  DataSet ExecuteDataset(IDbHelper iDbHelper, DbTransaction transaction,  string  commandText)
299          {
300               return  ExecuteDataset(iDbHelper, transaction, CommandType.Text, commandText,  new  DbParameter[ 0 ]);
301          }
302 
303           public  DataSet ExecuteDataset(IDbHelper iDbHelper, DbTransaction transaction, CommandType commandType,  string  commandText)
304          {
305               return  ExecuteDataset(iDbHelper, transaction, commandType, commandText,  new  DbParameter[ 0 ]);
306          }
307 
308           public  DataSet ExecuteDataset(IDbHelper iDbHelper, DbTransaction transaction, CommandType commandType,  string  commandText,  params  DbParameter[] commandParameters)
309          {
310               using  (DbDataAdapter da  =  _IDbHelper.DbDataAdapter())
311              {
312                   try
313                  {
314                      da.SelectCommand  =  _IDbHelper.DbCommand();
315                      da.SelectCommand.Connection  =  _DbConn;
316                      da.SelectCommand.CommandType  =  commandType;
317                      da.SelectCommand.CommandText  =  commandText;
318                      da.SelectCommand.CommandTimeout  =   240 ;
319 
320                      AttachParameters(da.SelectCommand, commandParameters);
321                      var dataSet  =   new  DataSet();
322                      da.Fill(dataSet);
323                       return  dataSet;
324                  }
325                   finally
326                  {
327                      da.SelectCommand.Parameters.Clear();
328                  }
329              }
330          }
331 
332           #endregion
333 
334           #region  ExecuteReader
335 
336           public  DbDataReader ExecuteReader( string  commandText)
337          {
338               return  ExecuteReader(CommandType.Text, commandText,  new  DbParameter[ 0 ]);
339          }
340 
341           public  DbDataReader ExecuteReader(CommandType commandType,  string  commandText)
342          {
343               return  ExecuteReader(commandType, commandText,  new  DbParameter[ 0 ]);
344          }
345 
346           public  DbDataReader ExecuteReader( string  commandText,  params  DbParameter[] commandParameters)
347          {
348               using  (DbCommand dbCom  =  _IDbHelper.DbCommand())
349              {
350                   try
351                  {
352                      dbCom.Connection  =  _DbConn;
353                      dbCom.CommandType  =  CommandType.Text;
354                      dbCom.CommandText  =  commandText;
355                      dbCom.CommandTimeout  =   240 ;
356                      AttachParameters(dbCom, commandParameters);
357                       return  dbCom.ExecuteReader(CommandBehavior.CloseConnection);
358                  }
359                   finally
360                  {
361                      dbCom.Parameters.Clear();
362                  }
363              }
364          }
365 
366           public  DbDataReader ExecuteReader(CommandType commandType,  string  commandText,
367                                             params  DbParameter[] commandParameters)
368          {
369               using  (DbCommand dbCom  =  _IDbHelper.DbCommand())
370              {
371                   try
372                  {
373                      dbCom.Connection  =  _DbConn;
374                      dbCom.CommandType  =  commandType;
375                      dbCom.CommandText  =  commandText;
376                      dbCom.CommandTimeout  =   240 ;
377                      AttachParameters(dbCom, commandParameters);
378                       return  dbCom.ExecuteReader(CommandBehavior.CloseConnection);
379                  }
380                   finally
381                  {
382                      dbCom.Parameters.Clear();
383                  }
384              }
385          }
386 
387           public   static  DbDataReader ExecuteReader(IDbHelper iDbHelper, DbTransaction transaction,  string  commandText)
388          {
389               return  ExecuteReader(iDbHelper, transaction, CommandType.Text, commandText,  new  DbParameter[ 0 ]);
390          }
391 
392           public  DbDataReader ExecuteReader(IDbHelper iDbHelper, DbTransaction transaction, CommandType commandType,
393                                             string  commandText)
394          {
395               return  ExecuteReader(iDbHelper, transaction, commandType, commandText,  new  DbParameter[ 0 ]);
396          }
397 
398           public   static  DbDataReader ExecuteReader(IDbHelper iDbHelper, DbTransaction transaction,  string  commandText,
399                                             params  DbParameter[] commandParameters)
400          {
401               using  (DbCommand dbCom  =  iDbHelper.DbCommand())
402              {
403                   try
404                  {
405                      dbCom.Connection  =  transaction.Connection;
406                      dbCom.Transaction  =  transaction;
407                      dbCom.CommandType  =  CommandType.Text;
408                      dbCom.CommandText  =  commandText;
409                      dbCom.CommandTimeout  =   240 ;
410                      AttachParameters(dbCom, commandParameters);
411                       return  dbCom.ExecuteReader();
412                  }
413                   finally
414                  {
415                      dbCom.Parameters.Clear();
416                  }
417              }
418          }
419 
420           public   static  DbDataReader ExecuteReader(IDbHelper iDbHelper, DbTransaction transaction, CommandType commandType,
421                                             string  commandText,
422                                             params  DbParameter[] commandParameters)
423          {
424               using  (DbCommand dbCom  =  iDbHelper.DbCommand())
425              {
426                   try
427                  {
428                      dbCom.Connection  =  transaction.Connection;
429                      dbCom.Transaction  =  transaction;
430                      dbCom.CommandType  =  commandType;
431                      dbCom.CommandText  =  commandText;
432                      dbCom.CommandTimeout  =   240 ;
433                      AttachParameters(dbCom, commandParameters);
434                       return  dbCom.ExecuteReader();
435                  }
436                   finally
437                  {
438                      dbCom.Parameters.Clear();
439                  }
440              }
441          }
442 
443           #endregion
444 
445           #region  ExecuteScalar
446 
447           public   object  ExecuteScalar( string  commandText)
448          {
449               return  ExecuteScalar(CommandType.Text, commandText,  new  DbParameter[ 0 ]);
450          }
451 
452           public   object  ExecuteScalar(CommandType commandType,  string  commandText)
453          {
454               return  ExecuteScalar(commandType, commandText,  new  DbParameter[ 0 ]);
455          }
456 
457           public   object  ExecuteScalar( string  commandText,  params  DbParameter[] commandParameters)
458          {
459               using  (DbCommand dbCom  =  _IDbHelper.DbCommand())
460              {
461                   try
462                  {
463                      dbCom.Connection  =  _DbConn;
464                      dbCom.CommandType  =  CommandType.Text;
465                      dbCom.CommandText  =  commandText;
466                      dbCom.CommandTimeout  =   240 ;
467                      AttachParameters(dbCom, commandParameters);
468                       return  dbCom.ExecuteScalar();
469                  }
470                   finally
471                  {
472                      dbCom.Parameters.Clear();
473                  }
474              }
475          }
476 
477           public   object  ExecuteScalar(CommandType commandType,  string  commandText,  params  DbParameter[] commandParameters)
478          {
479               using  (DbCommand dbCom  =  _IDbHelper.DbCommand())
480              {
481                   try
482                  {
483                      dbCom.Connection  =  _DbConn;
484                      dbCom.CommandType  =  commandType;
485                      dbCom.CommandText  =  commandText;
486                      dbCom.CommandTimeout  =   240 ;
487                      AttachParameters(dbCom, commandParameters);
488                       return  dbCom.ExecuteScalar();
489                  }
490                   finally
491                  {
492                      dbCom.Parameters.Clear();
493                  }
494              }
495          }
496 
497           public   static   object  ExecuteScalar(IDbHelper iDbHelper, DbTransaction transaction,  string  commandText)
498          {
499               return  ExecuteScalar(iDbHelper, transaction, CommandType.Text, commandText,  new  DbParameter[ 0 ]);
500          }
501 
502           public   object  ExecuteScalar(IDbHelper iDbHelper, DbTransaction transaction, CommandType commandType,  string  commandText)
503          {
504               return  ExecuteScalar(iDbHelper, transaction, commandType, commandText,  new  DbParameter[ 0 ]);
505          }
506 
507           public   static   object  ExecuteScalar(IDbHelper iDbHelper, DbTransaction transaction,  string  commandText,
508                                       params  DbParameter[] commandParameters)
509          {
510               using  (DbCommand dbCom  =  iDbHelper.DbCommand())
511              {
512                  dbCom.Connection  =  transaction.Connection;
513                  dbCom.Transaction  =  transaction;
514                  dbCom.CommandType  =  CommandType.Text;
515                  dbCom.CommandText  =  commandText;
516                  dbCom.CommandTimeout  =   240 ;
517                  AttachParameters(dbCom, commandParameters);
518                   return  dbCom.ExecuteScalar();
519              }
520          }
521 
522           public   static   object  ExecuteScalar(IDbHelper iDbHelper, DbTransaction transaction, CommandType commandType,  string  commandText,
523                                       params  DbParameter[] commandParameters)
524          {
525               using  (DbCommand dbCom  =  iDbHelper.DbCommand())
526              {
527                  dbCom.Connection  =  transaction.Connection;
528                  dbCom.Transaction  =  transaction;
529                  dbCom.CommandType  =  commandType;
530                  dbCom.CommandText  =  commandText;
531                  dbCom.CommandTimeout  =   240 ;
532                  AttachParameters(dbCom, commandParameters);
533                   return  dbCom.ExecuteScalar();
534              }
535          }
536 
537           #endregion
538      }
539  }

 

我建立了一个数据库操作类接口（DotNet.Frameworks.NParsing.Interface.IDbHelper），只需实现对DbConnection，DbCommand，DbDataAdapter的进行构造。

 

接口代码如下：

代码

 1  using  System.Data.Common;
 2 
 3  namespace  DotNet.Frameworks.NParsing.Interface
 4  {
 5       public   interface  IDbHelper
 6      {
 7          DbConnection DbConnection();
 8          DbCommand DbCommand();
 9          DbDataAdapter DbDataAdapter();
10      }
11  }

 

每个数据库操作类只要对DotNet.Frameworks.NParsing.Interface.IDbHelper接口实现，比如SQL Server的类：

代码

 1  using  System.Data.Common;
 2  using  System.Data.SqlClient;
 3  using  DotNet.Frameworks.NParsing.Interface;
 4 
 5  namespace  DotNet.Frameworks.NParsing.SQLServer
 6  {
 7       public   class  DbHelper : IDbHelper
 8      {
 9           #region  IDbHelper Members
10 
11           private   readonly   string  _ConnectionString;
12 
13           public  DbHelper( string  connectionString)
14          {
15              _ConnectionString  =  connectionString;
16          }
17 
18           public  DbConnection DbConnection()
19          {
20               return   new  SqlConnection(_ConnectionString);
21          }
22 
23           public  DbCommand DbCommand()
24          {
25               return   new  SqlCommand();
26          }
27 
28           public  DbDataAdapter DbDataAdapter()
29          {
30               return   new  SqlDataAdapter();
31          }
32 
33           #endregion
34      }
35  }

 

当我们构造控制器组件对象时，它会根据你传入的配置，构造相应数据库的操作类对象。
在构造公用数据库操作类（DotNet.Frameworks.NParsing.DbUtilities.DbHelper）时，必须传入已构造完成的数据库的操作类对象，这样，我们就可以使用公用数据库操作类对象操作相应数据库了。

 

二、如何解决同一项目多个数据库连接的支持？

我使用了三种构造控制器组件（ObHelper）的构造方法。

1、创建默认数据库操作接口，默认取NParsing数据库连接配置结点信息。
2、创建指定数据库连接配置结点名称的数据库操作接口。
3、创建指定数据库连接字符串、数据库操作类库名称的数据库操作接口。（方便数据库连接字符串加密存储）

 

代码如下：

代码

 1  using  System;
 2  using  System.Configuration;
 3  using  System.Reflection;
 4  using  DotNet.Frameworks.NParsing.Interface;
 5 
 6  namespace  DotNet.Frameworks.NParsing.Factory
 7  {
 8       public   static   class  ObHelper
 9      {
10           private   const   string  ASSEMBLY_STRING  =   " DotNet.Frameworks.NParsing.DbUtilities " ;
11           private   const   string  CLASS_NAME  =  ASSEMBLY_STRING  +   " .ObHelper`1 " ;
12 
13           ///   <summary>
14           ///  创建数据库操作接口，默认NParsing数据库连接
15           ///   </summary>
16           ///   <typeparam name="M"> 对象模型 </typeparam>
17           ///   <returns></returns>
18           public   static  IObHelper < M >  Create < M > ()
19          {
20              var connectionStringSettings  =  ConfigurationManager.ConnectionStrings[ " NParsing " ];
21               #if  (DEBUG)
22               if  (connectionStringSettings  ==   null )
23              {
24                   throw   new  Exception( string .Format( " 数据库连接配置节点NParsing未找到 " ));
25              }
26               #endif
27              Type t  =   typeof (M);
28              var className  =  CLASS_NAME  +   " [[ "   +  t.FullName  +   " , "   +  t.Assembly.FullName  +   " ]] " ;
29              t  =  Assembly.Load(ASSEMBLY_STRING).GetType(className);
30              var parameters  =   new []
31                                   {
32                                       connectionStringSettings.ConnectionString,
33                                       connectionStringSettings.ProviderName
34                                   };
35               return  (IObHelper < M > )Activator.CreateInstance(t, parameters);
36          }
37 
38           ///   <summary>
39           ///  创建数据库操作接口，指定connectionStringName数据库连接配置结点名称
40           ///   </summary>
41           ///   <typeparam name="M"> 对象模型 </typeparam>
42           ///   <param name="connectionStringName"> 数据库连接配置结点名称 </param>
43           ///   <returns></returns>
44           public   static  IObHelper < M >  Create < M > ( string  connectionStringName)
45          {
46              var connectionStringSettings  =  ConfigurationManager.ConnectionStrings[connectionStringName];
47               #if  (DEBUG)
48               if  (connectionStringSettings  ==   null )
49              {
50                   throw   new  Exception( string .Format( " 数据库连接配置节点{0}未找到 " , connectionStringName));
51              }
52               #endif
53              Type t  =   typeof  (M);
54              var className  =  CLASS_NAME  +   " [[ "   +  t.FullName  +   " , "   +  t.Assembly.FullName  +   " ]] " ;
55              t  =  Assembly.Load(ASSEMBLY_STRING).GetType(className);
56              var parameters  =   new []
57                                   {
58                                       connectionStringSettings.ConnectionString,
59                                       connectionStringSettings.ProviderName
60                                   };
61               return  (IObHelper < M > ) Activator.CreateInstance(t, parameters);
62          }
63 
64           ///   <summary>
65           ///  创建数据库操作接口，传入数据库连接字符串和数据库操作类库名称，方便数据库连接字符串加密存储
66           ///   </summary>
67           ///   <typeparam name="M"> 对象模型 </typeparam>
68           ///   <param name="connectionString"> 数据库连接字符串 </param>
69           ///   <param name="providerName"> 数据库操作类库名称 </param>
70           ///   <returns></returns>
71           public   static  IObHelper < M >  Create < M > ( string  connectionString,  string  providerName)
72          {
73              Type t  =   typeof (M);
74              var className  =  CLASS_NAME  +   " [[ "   +  t.FullName  +   " , "   +  t.Assembly.FullName  +   " ]] " ;
75              t  =  Assembly.Load(ASSEMBLY_STRING).GetType(className);
76              var parameters  =   new []
77                                   {
78                                       connectionString,
79                                       providerName
80                                   };
81               return  (IObHelper < M > )Activator.CreateInstance(t, parameters);
82          }
83      }
84  }

 

三、如何解决数据库连接和数据库操作类库的配置？

一开始时，为了看起来更加专业，我为它单独写了一个配置文件，用来存放数据库连接和数据库操作类库名称。但后来我渐渐明白，如果每个人写的东西都有自己的配置文件，没有统一的标准，会让使用者很头痛。所以咬咬牙，改，改成在app.config或web.config中配置。

 

配置代码：

代码

1     < connectionStrings >
2       <!-- 默认连接 -->
3       < add  name ="NParsing"  connectionString ="连接串"  providerName ="DotNet.Frameworks.NParsing.SQLServer" />
4       <!-- 自己定义连接 -->
5       < add  name ="NParsing1"  connectionString ="连接串"  providerName ="DotNet.Frameworks.NParsing.Access" />
6       < add  name ="NParsing2"  connectionString ="连接串"  providerName ="DotNet.Frameworks.NParsing.MySQL" />
7       < add  name ="NParsing3"  connectionString ="连接串"  providerName ="DotNet.Frameworks.NParsing.Oracle" />
8     </ connectionStrings >

 

四、如何解决SQL语句如何动态生成？

动态生成SQL语句的首要条件是，先要建立对象模型。有了对象模型，就可以通过将对象模型或对象模型实例反射，得到对象模型名和属性名。以对象模型名作为表名，以对象模型属性作为表的列名。通过组合形成SQL语句。

我们知道每个数据库平台的SQL语句都可能出现不同的语法。我们不能用一变应万变，所以写不出通用的、共用的SQL语句成生器。只能使用同一个接口，用不同的实现。

我的SQL语句成生器接口如下：

代码

 1  using  System.Collections.Generic;
 2  using  System.Data.Common;
 3 
 4  namespace  DotNet.Frameworks.NParsing.Interface
 5  {
 6       public   interface  ISqlBuilder
 7      {
 8           string  Insert( object  model,  ref  IList < DbParameter >  dbParameters);
 9 
10           string  Delete(IObParameter iObParameter,  ref  IList < DbParameter >  dbParameters);
11 
12           string  Update( object  model, IObParameter iObParameter,  ref  IList < DbParameter >  dbParameters);
13 
14           string  CountSelect();
15           string  CountSelect(IObParameter iObParameter,  ref  IList < DbParameter >  dbParameters);
16 
17           string  MaxSelect( string  columnName);
18           string  MaxSelect( string  columnName, IObParameter iObParameter,  ref  IList < DbParameter >  dbParameters);
19 
20           string  MinSelect( string  columnName);
21           string  MinSelect( string  columnName, IObParameter iObParameter,  ref  IList < DbParameter >  dbParameters);
22 
23           string  Select();
24           string  Select(IObSort iObSort);
25           string  Select(IObParameter iObParameter,  ref  IList < DbParameter >  dbParameters);
26           string  Select(IObParameter iObParameter, IObSort iObSort,  ref  IList < DbParameter >  dbParameters);
27 
28           string  Select( int  pageSize,  int  pageIndex, IObSort iObSort);
29 
30           string  Select( int  pageSize,  int  pageIndex, IObParameter iObParameter, IObSort iObSort,  ref  IList < DbParameter >  dbParameters);
31      }
32  }

 

下面以生成分页SQL语句为例，说一对SQL语句成生器接口的实现。
先看SQL Server的代码：

代码

 1           ///   <summary>
 2           ///  生成分页SELECT语句
 3           ///   </summary>
 4           ///   <param name="pageSize"> 分页大小 </param>
 5           ///   <param name="pageIndex"> 第几页 </param>
 6           ///   <param name="iObParameter"> 条件参数 </param>
 7           ///   <param name="iObSort"> 排序 </param>
 8           ///   <param name="dbParameters"> 转成数据库参数 </param>
 9           ///   <returns></returns>
10           public   string  Select( int  pageSize,  int  pageIndex, IObParameter iObParameter, IObSort iObSort,  ref  IList < DbParameter >  dbParameters)
11          {
12              var dbSort  =  iObSort.List[ 0 ];
13              var sortName  =   string .Format( " {0}.{1} " , dbSort.TableName, dbSort.ColumnName);
14               string  columns  =   string .Empty;
15               string  innerJoin  =  InnerJoin(mType,  ref  columns);
16               string  strWhereAnd  =   "  WHERE  " ;
17               string  strWhere  =   string .Empty;
18               if  (iObParameter  !=   null )
19              {
20                  strWhere  =  iObParameter.ToString( ref  dbParameters);
21                   if  (strWhere.Length  >   0 )
22                  {
23                      strWhere  =   " WHERE ( "   +  strWhere  +   " ) " ;
24                      strWhereAnd  =   "  AND  " ;
25                  }
26              }
27               string  sql;
28               string  sql1  =   string .Format( " SELECT TOP {4} {0} FROM {1} {2} {3} " , columns, NParsingUtil.GetTableName(mType.Name), innerJoin, strWhere, pageSize);
29               string  sql2  =   string .Format( " SELECT TOP {4} {0} FROM {1} {2} {3} " , sortName, NParsingUtil.GetTableName(mType.Name), innerJoin, strWhere, pageSize  *  (pageIndex  -   1 ));
30               if  (pageIndex  ==   1 )
31              {
32                  sql  =  sql1  +   "  ORDER BY  "   +  sortName  +  (dbSort.IsAsc  ?   ""  :  "  DESC " );
33              }
34               else
35              {
36                   if  (dbSort.IsAsc)
37                  {
38                      sql  =  sql1  +  strWhereAnd  +  sortName  +   " >(SELECT MAX( "   +  sortName  +   " ) FROM ( "   +  sql2  +
39                             "  ORDER BY  "   +  sortName  +   " )  "   +  NParsingUtil.GetTableName(mType.Name)  +   " ) ORDER BY  "   +  sortName;
40                  }
41                   else
42                  {
43                      sql  =  sql1  +  strWhereAnd  +  sortName  +   " <(SELECT MIN( "   +  sortName  +   " ) FROM ( "   +  sql2  +
44                             "  ORDER BY  "   +  sortName  +   "  DESC)  "   +  NParsingUtil.GetTableName(mType.Name)  +   " ) ORDER BY  "   +  sortName  +   "  DESC " ;
45                  }
46              }
47               return  sql;
48          }

 

再看My SQL的代码：

代码

 1           ///   <summary>
 2           ///  生成分页SELECT语句
 3           ///   </summary>
 4           ///   <param name="pageSize"> 分页大小 </param>
 5           ///   <param name="pageIndex"> 第几页 </param>
 6           ///   <param name="iObParameter"> 条件参数 </param>
 7           ///   <param name="iObSort"> 排序 </param>
 8           ///   <param name="dbParameters"> 转成数据库参数 </param>
 9           ///   <returns></returns>
10           public   string  Select( int  pageSize,  int  pageIndex, IObParameter iObParameter, IObSort iObSort,  ref  IList < DbParameter >  dbParameters)
11          {
12               string  columns  =   string .Empty;
13               string  innerJoin  =  InnerJoin(mType,  ref  columns);
14               string  strWhere  =   string .Empty;
15               if  (iObParameter  !=   null )
16              {
17                  strWhere  =  iObParameter.ToString( ref  dbParameters);
18                   if  (strWhere.Length  >   0 )
19                  {
20                      strWhere  =   " WHERE  "   +  strWhere;
21                  }
22              }
23               string  sql  =   string .Format( " SELECT {0} FROM {1} {2} {3} ORDER BY {4} LIMIT {5},{6} " ,
24                  columns, NParsingUtil.GetTableName(mType.Name), innerJoin, strWhere, iObSort.ToString(), (pageIndex  -   1 )  *  pageSize, pageSize);
25               return  sql;
26          }

 

再看Oracle的代码：

代码

 1           ///   <summary>
 2           ///  生成分页SELECT语句
 3           ///   </summary>
 4           ///   <param name="pageSize"> 分页大小 </param>
 5           ///   <param name="pageIndex"> 第几页 </param>
 6           ///   <param name="iObParameter"> 条件参数 </param>
 7           ///   <param name="iObSort"> 排序 </param>
 8           ///   <param name="dbParameters"> 转成数据库参数 </param>
 9           ///   <returns></returns>
10           public   string  Select( int  pageSize,  int  pageIndex, IObParameter iObParameter, IObSort iObSort,  ref  IList < DbParameter >  dbParameters)
11          {
12              var dbSort  =  iObSort.List[ 0 ];
13              var sortName  =   string .Format( " {0}.{1} " , dbSort.TableName, dbSort.ColumnName);
14               string  columns  =   string .Empty;
15               string  innerJoin  =  InnerJoin(mType,  ref  columns);
16               string  strWhere;
17               if  (iObParameter  !=   null )
18              {
19                  strWhere  =  iObParameter.ToString( ref  dbParameters);
20                   if  (strWhere.Length  >   0 )
21                  {
22                      strWhere  =   " WHERE ( "   +  strWhere  +   " ) AND " ;
23                  }
24                   else
25                  {
26                      strWhere  =   " WHERE " ;
27                  }
28              }
29               else
30              {
31                  strWhere  =   " WHERE " ;
32              }
33               string  sql;
34               string  sql1  =   string .Format( " SELECT {0} FROM {1} {2} {3} ROWNUM <= {4} " , columns, NParsingUtil.GetTableName(mType.Name), innerJoin, strWhere, pageSize);
35               string  sql2  =   string .Format( " SELECT {0} FROM {1} {2} {3} ROWNUM <= {4} " , sortName, NParsingUtil.GetTableName(mType.Name), innerJoin, strWhere, pageSize  *  (pageIndex  -   1 ));
36               if  (pageIndex  ==   1 )
37              {
38                  sql  =  sql1  +   "  ORDER BY  "   +  sortName  +  (dbSort.IsAsc  ?   ""  :  "  DESC " );
39              }
40               else
41              {
42                   if  (dbSort.IsAsc)
43                  {
44                      sql  =  sql1  +   "  AND  "   +  sortName  +   " >(SELECT MAX( "   +  sortName  +   " ) FROM ( "   +  sql2  +
45                             "  ORDER BY  "   +  sortName  +   " )  "   +  NParsingUtil.GetTableName(mType.Name)  +   " ) ORDER BY  "   +  sortName;
46                  }
47                   else
48                  {
49                      sql  =  sql1  +   "  AND  "   +  sortName  +   " <(SELECT MIN( "   +  sortName  +   " ) FROM ( "   +  sql2  +
50                             "  ORDER BY  "   +  sortName  +   "  DESC)  "   +  NParsingUtil.GetTableName(mType.Name)  +   " ) ORDER BY  "   +  sortName  +   "  DESC " ;
51                  }
52              }
53               return  sql;
54          }

 

好了，控制器组件（ObHelper）实现原理解说完了。要想知道其它组件实现原理，请看下回分解。

 

 

详细解说NParsing框架实现原理 —— 2）参数组件（ObParameter）

引言(NParsing框架功能简介、NParsing的由来)

转载于:https://www.cnblogs.com/zhidian/archive/2010/04/28/1722872.html