ObHelper —— O(object) B(Database) 软件对象模型到数据库关系模型的映射控制器
控制器组件是本框架的核心组件,是对数据库所有操作的实现。实现控制器组件必须解决以下几个问题:
1、解决不同数据库平台的支持。
2、解决同一项目多个数据库连接的支持。
3、解决数据库连接和数据库操作类库的配置。
4、解决SQL语句如何动态生成。
一、如何解决不同数据库平台的支持?
我使用一个公用的数据库操作类(DotNet.Frameworks.NParsing.DbUtilities.DbHelper)统一调用(里面使用DbConnection, DbCommand,DbDataAdapter,DbParameter组件),
实现ExecuteNonQuery,ExecuteDataset,ExecuteReader,ExecuteScalar
代码如下:
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的进行构造。
接口代码如下:
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的类:
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、创建指定数据库连接字符串、数据库操作类库名称的数据库操作接口。(方便数据库连接字符串加密存储)
代码如下:
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中配置。
配置代码:
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语句成生器接口如下:
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的代码:
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的代码:
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的代码:
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)实现原理解说完了。要想知道其它组件实现原理,请看下回分解。