【原创】打造基于Dapper的数据访问层

  • 前言

  闲来无事,花几天功夫将之前项目里用到的一个数据访问层整理了出来。实现单个实体的增删改查,可执行存储过程,可输出返回参数,查询结果集可根据实际情况返回DataTable、DataSet和强类型,同时支持不同类型数据库。目前成熟的ORM框架多不胜数,再写一个出来,并非想证明自己写的有多好,一来认为现有成熟的ORM框架并不能灵活适用于大型ERP项目,二来有感于工作多年有必要写下一些东西。虽然有种重复造轮子的感觉,但相信朋友们和我一样,享受造轮子的过程并把它当成一种乐趣,对吧。

  • 调用示例

  1、LinQ 语法查询

1     //LinQ 语法查询
2     query = rptBase.Query<Bas_Company>();
3     query = rptBase.Query<Bas_Company>(x => true);
4     query = rptBase.Query<Bas_Company>(x => true && (x.CompanyID ?? null) != null && new[] { "1", "2" }.Contains(x.CompanyID) &&
5         x.CompanyID.Substring(2, 5).TrimEnd() == "OK" && x.AllowUsed);
View Code

  2、分页查询

1     //分页查询
2     query = rptBase.Query<Bas_Company>(new PageInfo(3, 20));
3     query = rptBase.Query<Bas_Company>(new PageInfo(3, 20), x => x.CompanyID == "FT");
View Code

  3、自定义脚本查询

1     d = new DynamicParameters();
2     d.Add("CompanyName", "美之源科技有限公司", DbType.String, null, 20);
3     query = rptBase.Query<Bas_Company>("Select * From Bas_Company WHERE CompanyName = @CompanyName", d);
View Code

  4、自定义参数查询

1     //自定义参数查询
2     d = new DynamicParameters();
3     d.Add("CompanyName", "美之源科技有限公司");
4     query = rptBase.Query<Bas_Company>("selectByName", null, d);
View Code

  5、带返回值查询

1     //带返回值查询
2     d = new DynamicParameters();
3     d.Add("Row", null);
4     table = rptBase.QueryDataTable<Bas_Company>("returnValue", x => x.CompanyID != "FT", d);
5     eff = d.Get<int?>("Row");
View Code

  6、自定义实体查询

1     //查询自定义实体
2     var query1 = rptBase.Query<ThinEntity>(typeof(Bas_Company).FullName, "thinEntity", "And CompanyID <> 'FT' ");
View Code

  7、DataTable 查询

1     DataTable table = null;
2     table = rptBase.QueryDataTable<Bas_Company>();
3     table = rptBase.QueryDataTable<Bas_Company>(x => true);
View Code

  8、DataSet 查询

1     DataSet data = null;
2     data = rptBase.QueryDataSet<Bas_Company>("Select",x => true);
View Code

  9、增删改

 1     //新增
 2     Bas_Company company = new Bas_Company();
 3     company.CompanyID = "TH";
 4     company.CompanyCode = "TH001";
 5     rptBase.Insert(company);
 6 
 7     //修改
 8     company.CompanyCode = "TH00x";
 9     rptBase.Update(company);
10     //批量修改
11     rptBase.Update<Bas_Company>(x => new Bas_Company { CompanyCode = "TH003" }, x => x.CompanyID == "TH");
12 
13     //删除
14     rptBase.Delete(company);
View Code

  10、解析成字符串

1     sql = rptBase.Resolve<Bas_Company>("Select",x => true && (x.CompanyID ?? null) != null && new[] { "1", "2" }.Contains(x.CompanyID) &&
2         x.CompanyID.Substring(2, 5).TrimEnd() == "OK" && x.AllowUsed);
3     sqlList.Add(sql);
View Code

  还有其它的重载这里就不一一列举,源代码的单元测试里有很详细的说明。

  • 详细解析

  先来看看项目架构截图:

03.Src:第三方开源组件源码,有的目前没有用到,先收藏着;
04.Infrastructure:整个项目公用类库,包含一些Helper和公用类;
05.DataAccess:数据访问核心类库,实现Lambda表达式解析、Dapper封装等;
06.Model:实体层,实体使用CodeSmith生成,包含实体和脚本映射两个部分;
09.Presentation:展示层,目前只有一个单元测试项目。

  Dapper,一个基于IDbConnection扩展的完全开源的轻、小、快的ORM框架(项目源码 https://github.com/SamSaffron/dapper-dot-net )。在本框架中,需要解决的主要问题有如下几点:

  1. 如何生成Dapper查询所需要的TSQL脚本和参数
  2. 如何将Lambda表达式解析成查询条件
  3. 如何将Dapper返回的IDataReader转化成DataTable和DataSet
  4. 如何解决多数据库的问题

--------------------------------------- 华丽丽的分割线 --------------------------------------------

  1. 如何生成Dapper查询所需要的SQL语句和参数

  基于SQL和代码分离原则,数据库中每一张表都有一个POCO实体与之对应并且用一个Xml文件来描述,包括表名称、字段、主键和增删改查SQL及参数。因为Xml文件的结构都是一样的,我这里用CodeSmith Studio来自动生成。CodeSmith的语法跟Asp.Net的语法类似,这里  http://blog.csdn.net/mapdigit/article/category/1264541 有比较全面的学习资源,本文不做过多着墨。另外若有自定义SQL需求,则需要把自定义Xml文件放到另外目录,以免被CodeSmith覆盖。Xml文件结构如下:

  1 <?xml version="1.0" encoding="utf-8" ?>
  2 <EntityMapper  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  3     <TableType>
  4         <TableName>Bas_Bank</TableName>
  5         <TypeFullName>XFramework.Model.Bas_Bank</TypeFullName>
  6     </TableType>
  7     <Properties>
  8         <Property>
  9             <Name>CompanyID</Name>
 10             <DbType>AnsiString</DbType>
 11             <NativeType>varchar</NativeType>
 12             <Precision>0</Precision>
 13             <Scale>0</Scale>
 14             <Size>10</Size>
 15         </Property>
 16         <Property>
 17             <Name>BankID</Name>
 18             <DbType>AnsiString</DbType>
 19             <NativeType>varchar</NativeType>
 20             <Precision>0</Precision>
 21             <Scale>0</Scale>
 22             <Size>20</Size>
 23         </Property>
 24         <Property>
 25             <Name>BankCode</Name>
 26             <DbType>String</DbType>
 27             <NativeType>nvarchar</NativeType>
 28             <Precision>0</Precision>
 29             <Scale>0</Scale>
 30             <Size>20</Size>
 31         </Property>
 32         <Property>
 33             <Name>BankName</Name>
 34             <DbType>String</DbType>
 35             <NativeType>nvarchar</NativeType>
 36             <Precision>0</Precision>
 37             <Scale>0</Scale>
 38             <Size>40</Size>
 39         </Property>
 40         <Property>
 41             <Name>SWIFT</Name>
 42             <DbType>String</DbType>
 43             <NativeType>nvarchar</NativeType>
 44             <Precision>0</Precision>
 45             <Scale>0</Scale>
 46             <Size>20</Size>
 47         </Property>
 48         <Property>
 49             <Name>AreaID</Name>
 50             <DbType>AnsiString</DbType>
 51             <NativeType>varchar</NativeType>
 52             <Precision>0</Precision>
 53             <Scale>0</Scale>
 54             <Size>19</Size>
 55         </Property>
 56         <Property>
 57             <Name>Address</Name>
 58             <DbType>String</DbType>
 59             <NativeType>nvarchar</NativeType>
 60             <Precision>0</Precision>
 61             <Scale>0</Scale>
 62             <Size>100</Size>
 63         </Property>
 64         <Property>
 65             <Name>Phone</Name>
 66             <DbType>AnsiString</DbType>
 67             <NativeType>varchar</NativeType>
 68             <Precision>0</Precision>
 69             <Scale>0</Scale>
 70             <Size>60</Size>
 71         </Property>
 72         <Property>
 73             <Name>ParentID</Name>
 74             <DbType>AnsiString</DbType>
 75             <NativeType>varchar</NativeType>
 76             <Precision>0</Precision>
 77             <Scale>0</Scale>
 78             <Size>20</Size>
 79         </Property>
 80         <Property>
 81             <Name>Level</Name>
 82             <DbType>Int32</DbType>
 83             <NativeType>int</NativeType>
 84             <Precision>10</Precision>
 85             <Scale>0</Scale>
 86             <Size>4</Size>
 87         </Property>
 88         <Property>
 89             <Name>IsDetail</Name>
 90             <DbType>Boolean</DbType>
 91             <NativeType>bit</NativeType>
 92             <Precision>1</Precision>
 93             <Scale>0</Scale>
 94             <Size>1</Size>
 95         </Property>
 96         <Property>
 97             <Name>FullName</Name>
 98             <DbType>String</DbType>
 99             <NativeType>nvarchar</NativeType>
100             <Precision>0</Precision>
101             <Scale>0</Scale>
102             <Size>100</Size>
103         </Property>
104         <Property>
105             <Name>FullParentID</Name>
106             <DbType>String</DbType>
107             <NativeType>nvarchar</NativeType>
108             <Precision>0</Precision>
109             <Scale>0</Scale>
110             <Size>80</Size>
111         </Property>
112         <Property>
113             <Name>ModifyDTM</Name>
114             <DbType>DateTime</DbType>
115             <NativeType>datetime</NativeType>
116             <Precision>23</Precision>
117             <Scale>3</Scale>
118             <Size>8</Size>
119         </Property>
120         <Property>
121             <Name>Remark</Name>
122             <DbType>String</DbType>
123             <NativeType>nvarchar</NativeType>
124             <Precision>0</Precision>
125             <Scale>0</Scale>
126             <Size>200</Size>
127         </Property>
128         <Property>
129             <Name>AllowUsed</Name>
130             <DbType>Boolean</DbType>
131             <NativeType>bit</NativeType>
132             <Precision>1</Precision>
133             <Scale>0</Scale>
134             <Size>1</Size>
135         </Property>
136     </Properties>
137     <Keys>
138         <Property>
139             <Name>CompanyID</Name>
140             <DbType>AnsiString</DbType>
141             <NativeType>varchar</NativeType>
142             <Precision>0</Precision>
143             <Scale>0</Scale>
144             <Size>10</Size>
145         </Property>
146         <Property>
147             <Name>BankID</Name>
148             <DbType>AnsiString</DbType>
149             <NativeType>varchar</NativeType>
150             <Precision>0</Precision>
151             <Scale>0</Scale>
152             <Size>20</Size>
153         </Property>
154     </Keys>
155     <Commands>
156         <Command>
157             <Key>Select</Key>
158             <CommandType>Text</CommandType>
159             <Text>
160              SELECT 
161             [CompanyID],
162             [BankID],
163             [BankCode],
164             [BankName],
165             [SWIFT],
166             [AreaID],
167             [Address],
168             [Phone],
169             [ParentID],
170             [Level],
171             [IsDetail],
172             [FullName],
173             [FullParentID],
174             [ModifyDTM],
175             [Remark],
176             [AllowUsed]
177             FROM [Bas_Bank]
178             WHERE 1=1 #WHERE#
179             </Text>
180         </Command>
181         <Command>
182             <Key>SelectByPaging</Key>
183             <CommandType>Text</CommandType>
184             <Text>
185             SELECT
186                 [CompanyID],
187                 [BankID],
188                 [BankCode],
189                 [BankName],
190                 [SWIFT],
191                 [AreaID],
192                 [Address],
193                 [Phone],
194                 [ParentID],
195                 [Level],
196                 [IsDetail],
197                 [FullName],
198                 [FullParentID],
199                 [ModifyDTM],
200                 [Remark],
201                 [AllowUsed],
202                 [XRecordCount],
203                 [XRowNum]
204             FROM(
205                 SELECT 
206                     [CompanyID],
207                     [BankID],
208                     [BankCode],
209                     [BankName],
210                     [SWIFT],
211                     [AreaID],
212                     [Address],
213                     [Phone],
214                     [ParentID],
215                     [Level],
216                     [IsDetail],
217                     [FullName],
218                     [FullParentID],
219                     [ModifyDTM],
220                     [Remark],
221                     [AllowUsed],
222                     Count(*) Over() as [XRecordCount],
223                     Row_Number() Over(Order By [CompanyID],[BankID],[BankCode],[BankName],[SWIFT],[AreaID],[Address],[Phone],[ParentID],[Level],[IsDetail],[FullName],[FullParentID],[ModifyDTM],[Remark],[AllowUsed]) as [XRowNum]
224                 FROM [Bas_Bank]
225                 WHERE 1=1 #WHERE#
226             ) a WHERE [XRowNum] BETWEEN #BETWEEN#
227             </Text>
228         </Command>
229         <Command>
230             <Key>SelectByKey</Key>
231             <CommandType>Text</CommandType>
232             <Text>
233              SELECT 
234             [CompanyID],
235             [BankID],
236             [BankCode],
237             [BankName],
238             [SWIFT],
239             [AreaID],
240             [Address],
241             [Phone],
242             [ParentID],
243             [Level],
244             [IsDetail],
245             [FullName],
246             [FullParentID],
247             [ModifyDTM],
248             [Remark],
249             [AllowUsed]
250             FROM [Bas_Bank]
251             WHERE 1=1         
252             And [CompanyID]=@CompanyID
253             And [BankID]=@BankID
254             </Text>
255             <Parameters>            
256                 <Parameter>
257                     <Name>CompanyID</Name>
258                     <DbType>AnsiString</DbType>
259                     <NativeType>varchar</NativeType>
260                     <Precision>0</Precision>
261                     <Scale>0</Scale>
262                     <Size>10</Size>
263                 </Parameter>
264                 <Parameter>
265                     <Name>BankID</Name>
266                     <DbType>AnsiString</DbType>
267                     <NativeType>varchar</NativeType>
268                     <Precision>0</Precision>
269                     <Scale>0</Scale>
270                     <Size>20</Size>
271                 </Parameter>
272             </Parameters>
273         </Command>
274         <Command>
275             <Key>Update</Key>
276             <CommandType>Text</CommandType>
277             <Text>
278             UPDATE [Bas_Bank] SET
279                     [BankCode] = @BankCode,
280                     [BankName] = @BankName,
281                     [SWIFT] = @SWIFT,
282                     [AreaID] = @AreaID,
283                     [Address] = @Address,
284                     [Phone] = @Phone,
285                     [ParentID] = @ParentID,
286                     [Level] = @Level,
287                     [IsDetail] = @IsDetail,
288                     [FullName] = @FullName,
289                     [FullParentID] = @FullParentID,
290                     [ModifyDTM] = @ModifyDTM,
291                     [Remark] = @Remark,
292                     [AllowUsed] = @AllowUsed
293             WHERE 1=1  #WHERE#
294             </Text>
295             <Parameters>
296                 <Parameter>
297                     <Name>BankCode</Name>
298                     <DbType>String</DbType>
299                     <NativeType>nvarchar</NativeType>
300                     <Precision>0</Precision>
301                     <Scale>0</Scale>
302                     <Size>20</Size>
303                 </Parameter>
304                 <Parameter>
305                     <Name>BankName</Name>
306                     <DbType>String</DbType>
307                     <NativeType>nvarchar</NativeType>
308                     <Precision>0</Precision>
309                     <Scale>0</Scale>
310                     <Size>40</Size>
311                 </Parameter>
312                 <Parameter>
313                     <Name>SWIFT</Name>
314                     <DbType>String</DbType>
315                     <NativeType>nvarchar</NativeType>
316                     <Precision>0</Precision>
317                     <Scale>0</Scale>
318                     <Size>20</Size>
319                 </Parameter>
320                 <Parameter>
321                     <Name>AreaID</Name>
322                     <DbType>AnsiString</DbType>
323                     <NativeType>varchar</NativeType>
324                     <Precision>0</Precision>
325                     <Scale>0</Scale>
326                     <Size>19</Size>
327                 </Parameter>
328                 <Parameter>
329                     <Name>Address</Name>
330                     <DbType>String</DbType>
331                     <NativeType>nvarchar</NativeType>
332                     <Precision>0</Precision>
333                     <Scale>0</Scale>
334                     <Size>100</Size>
335                 </Parameter>
336                 <Parameter>
337                     <Name>Phone</Name>
338                     <DbType>AnsiString</DbType>
339                     <NativeType>varchar</NativeType>
340                     <Precision>0</Precision>
341                     <Scale>0</Scale>
342                     <Size>60</Size>
343                 </Parameter>
344                 <Parameter>
345                     <Name>ParentID</Name>
346                     <DbType>AnsiString</DbType>
347                     <NativeType>varchar</NativeType>
348                     <Precision>0</Precision>
349                     <Scale>0</Scale>
350                     <Size>20</Size>
351                 </Parameter>
352                 <Parameter>
353                     <Name>Level</Name>
354                     <DbType>Int32</DbType>
355                     <NativeType>int</NativeType>
356                     <Precision>10</Precision>
357                     <Scale>0</Scale>
358                     <Size>4</Size>
359                 </Parameter>
360                 <Parameter>
361                     <Name>IsDetail</Name>
362                     <DbType>Boolean</DbType>
363                     <NativeType>bit</NativeType>
364                     <Precision>1</Precision>
365                     <Scale>0</Scale>
366                     <Size>1</Size>
367                 </Parameter>
368                 <Parameter>
369                     <Name>FullName</Name>
370                     <DbType>String</DbType>
371                     <NativeType>nvarchar</NativeType>
372                     <Precision>0</Precision>
373                     <Scale>0</Scale>
374                     <Size>100</Size>
375                 </Parameter>
376                 <Parameter>
377                     <Name>FullParentID</Name>
378                     <DbType>String</DbType>
379                     <NativeType>nvarchar</NativeType>
380                     <Precision>0</Precision>
381                     <Scale>0</Scale>
382                     <Size>80</Size>
383                 </Parameter>
384                 <Parameter>
385                     <Name>ModifyDTM</Name>
386                     <DbType>DateTime</DbType>
387                     <NativeType>datetime</NativeType>
388                     <Precision>23</Precision>
389                     <Scale>3</Scale>
390                     <Size>8</Size>
391                 </Parameter>
392                 <Parameter>
393                     <Name>Remark</Name>
394                     <DbType>String</DbType>
395                     <NativeType>nvarchar</NativeType>
396                     <Precision>0</Precision>
397                     <Scale>0</Scale>
398                     <Size>200</Size>
399                 </Parameter>
400                 <Parameter>
401                     <Name>AllowUsed</Name>
402                     <DbType>Boolean</DbType>
403                     <NativeType>bit</NativeType>
404                     <Precision>1</Precision>
405                     <Scale>0</Scale>
406                     <Size>1</Size>
407                 </Parameter>
408             </Parameters>
409         </Command>
410         <Command>
411             <Key>UpdateByKey</Key>
412             <CommandType>Text</CommandType>
413             <Text>
414             UPDATE [Bas_Bank] SET
415                     [BankCode] = @BankCode,
416                     [BankName] = @BankName,
417                     [SWIFT] = @SWIFT,
418                     [AreaID] = @AreaID,
419                     [Address] = @Address,
420                     [Phone] = @Phone,
421                     [ParentID] = @ParentID,
422                     [Level] = @Level,
423                     [IsDetail] = @IsDetail,
424                     [FullName] = @FullName,
425                     [FullParentID] = @FullParentID,
426                     [ModifyDTM] = @ModifyDTM,
427                     [Remark] = @Remark,
428                     [AllowUsed] = @AllowUsed
429             WHERE 1=1         
430             And [CompanyID]=@CompanyID 
431             And [BankID]=@BankID 
432             </Text>
433             <Parameters>
434                 <Parameter>
435                     <Name>BankCode</Name>
436                     <DbType>String</DbType>
437                     <NativeType>nvarchar</NativeType>
438                     <Precision>0</Precision>
439                     <Scale>0</Scale>
440                     <Size>20</Size>
441                 </Parameter>
442                 <Parameter>
443                     <Name>BankName</Name>
444                     <DbType>String</DbType>
445                     <NativeType>nvarchar</NativeType>
446                     <Precision>0</Precision>
447                     <Scale>0</Scale>
448                     <Size>40</Size>
449                 </Parameter>
450                 <Parameter>
451                     <Name>SWIFT</Name>
452                     <DbType>String</DbType>
453                     <NativeType>nvarchar</NativeType>
454                     <Precision>0</Precision>
455                     <Scale>0</Scale>
456                     <Size>20</Size>
457                 </Parameter>
458                 <Parameter>
459                     <Name>AreaID</Name>
460                     <DbType>AnsiString</DbType>
461                     <NativeType>varchar</NativeType>
462                     <Precision>0</Precision>
463                     <Scale>0</Scale>
464                     <Size>19</Size>
465                 </Parameter>
466                 <Parameter>
467                     <Name>Address</Name>
468                     <DbType>String</DbType>
469                     <NativeType>nvarchar</NativeType>
470                     <Precision>0</Precision>
471                     <Scale>0</Scale>
472                     <Size>100</Size>
473                 </Parameter>
474                 <Parameter>
475                     <Name>Phone</Name>
476                     <DbType>AnsiString</DbType>
477                     <NativeType>varchar</NativeType>
478                     <Precision>0</Precision>
479                     <Scale>0</Scale>
480                     <Size>60</Size>
481                 </Parameter>
482                 <Parameter>
483                     <Name>ParentID</Name>
484                     <DbType>AnsiString</DbType>
485                     <NativeType>varchar</NativeType>
486                     <Precision>0</Precision>
487                     <Scale>0</Scale>
488                     <Size>20</Size>
489                 </Parameter>
490                 <Parameter>
491                     <Name>Level</Name>
492                     <DbType>Int32</DbType>
493                     <NativeType>int</NativeType>
494                     <Precision>10</Precision>
495                     <Scale>0</Scale>
496                     <Size>4</Size>
497                 </Parameter>
498                 <Parameter>
499                     <Name>IsDetail</Name>
500                     <DbType>Boolean</DbType>
501                     <NativeType>bit</NativeType>
502                     <Precision>1</Precision>
503                     <Scale>0</Scale>
504                     <Size>1</Size>
505                 </Parameter>
506                 <Parameter>
507                     <Name>FullName</Name>
508                     <DbType>String</DbType>
509                     <NativeType>nvarchar</NativeType>
510                     <Precision>0</Precision>
511                     <Scale>0</Scale>
512                     <Size>100</Size>
513                 </Parameter>
514                 <Parameter>
515                     <Name>FullParentID</Name>
516                     <DbType>String</DbType>
517                     <NativeType>nvarchar</NativeType>
518                     <Precision>0</Precision>
519                     <Scale>0</Scale>
520                     <Size>80</Size>
521                 </Parameter>
522                 <Parameter>
523                     <Name>ModifyDTM</Name>
524                     <DbType>DateTime</DbType>
525                     <NativeType>datetime</NativeType>
526                     <Precision>23</Precision>
527                     <Scale>3</Scale>
528                     <Size>8</Size>
529                 </Parameter>
530                 <Parameter>
531                     <Name>Remark</Name>
532                     <DbType>String</DbType>
533                     <NativeType>nvarchar</NativeType>
534                     <Precision>0</Precision>
535                     <Scale>0</Scale>
536                     <Size>200</Size>
537                 </Parameter>
538                 <Parameter>
539                     <Name>AllowUsed</Name>
540                     <DbType>Boolean</DbType>
541                     <NativeType>bit</NativeType>
542                     <Precision>1</Precision>
543                     <Scale>0</Scale>
544                     <Size>1</Size>
545                 </Parameter>
546                 <Parameter>
547                     <Name>CompanyID</Name>
548                     <DbType>AnsiString</DbType>
549                     <NativeType>varchar</NativeType>
550                     <Precision>0</Precision>
551                     <Scale>0</Scale>
552                     <Size>10</Size>
553                 </Parameter>
554                 <Parameter>
555                     <Name>BankID</Name>
556                     <DbType>AnsiString</DbType>
557                     <NativeType>varchar</NativeType>
558                     <Precision>0</Precision>
559                     <Scale>0</Scale>
560                     <Size>20</Size>
561                 </Parameter>
562             </Parameters>
563         </Command>
564         <Command>
565             <Key>UpdateByExpr</Key>
566             <CommandType>Text</CommandType>
567             <Text>
568             UPDATE [Bas_Bank] SET
569             #SET#
570             WHERE 1=1  #WHERE#
571             </Text>
572         </Command>
573         <Command>
574             <Key>Insert</Key>
575             <CommandType>Text</CommandType>
576             <Text>            
577             INSERT INTO [Bas_Bank](
578                     [CompanyID],
579                     [BankID],
580                     [BankCode],
581                     [BankName],
582                     [SWIFT],
583                     [AreaID],
584                     [Address],
585                     [Phone],
586                     [ParentID],
587                     [Level],
588                     [IsDetail],
589                     [FullName],
590                     [FullParentID],
591                     [ModifyDTM],
592                     [Remark],
593                     [AllowUsed]
594             ) VALUES(
595                     @CompanyID,
596                     @BankID,
597                     @BankCode,
598                     @BankName,
599                     @SWIFT,
600                     @AreaID,
601                     @Address,
602                     @Phone,
603                     @ParentID,
604                     @Level,
605                     @IsDetail,
606                     @FullName,
607                     @FullParentID,
608                     @ModifyDTM,
609                     @Remark,
610                     @AllowUsed
611             )
612             
613             </Text>
614             <Parameters>
615                 <Parameter>
616                     <Name>CompanyID</Name>
617                     <DbType>AnsiString</DbType>
618                     <NativeType>varchar</NativeType>
619                     <Precision>0</Precision>
620                     <Scale>0</Scale>
621                     <Size>10</Size>
622                 </Parameter>
623                 <Parameter>
624                     <Name>BankID</Name>
625                     <DbType>AnsiString</DbType>
626                     <NativeType>varchar</NativeType>
627                     <Precision>0</Precision>
628                     <Scale>0</Scale>
629                     <Size>20</Size>
630                 </Parameter>
631                 <Parameter>
632                     <Name>BankCode</Name>
633                     <DbType>String</DbType>
634                     <NativeType>nvarchar</NativeType>
635                     <Precision>0</Precision>
636                     <Scale>0</Scale>
637                     <Size>20</Size>
638                 </Parameter>
639                 <Parameter>
640                     <Name>BankName</Name>
641                     <DbType>String</DbType>
642                     <NativeType>nvarchar</NativeType>
643                     <Precision>0</Precision>
644                     <Scale>0</Scale>
645                     <Size>40</Size>
646                 </Parameter>
647                 <Parameter>
648                     <Name>SWIFT</Name>
649                     <DbType>String</DbType>
650                     <NativeType>nvarchar</NativeType>
651                     <Precision>0</Precision>
652                     <Scale>0</Scale>
653                     <Size>20</Size>
654                 </Parameter>
655                 <Parameter>
656                     <Name>AreaID</Name>
657                     <DbType>AnsiString</DbType>
658                     <NativeType>varchar</NativeType>
659                     <Precision>0</Precision>
660                     <Scale>0</Scale>
661                     <Size>19</Size>
662                 </Parameter>
663                 <Parameter>
664                     <Name>Address</Name>
665                     <DbType>String</DbType>
666                     <NativeType>nvarchar</NativeType>
667                     <Precision>0</Precision>
668                     <Scale>0</Scale>
669                     <Size>100</Size>
670                 </Parameter>
671                 <Parameter>
672                     <Name>Phone</Name>
673                     <DbType>AnsiString</DbType>
674                     <NativeType>varchar</NativeType>
675                     <Precision>0</Precision>
676                     <Scale>0</Scale>
677                     <Size>60</Size>
678                 </Parameter>
679                 <Parameter>
680                     <Name>ParentID</Name>
681                     <DbType>AnsiString</DbType>
682                     <NativeType>varchar</NativeType>
683                     <Precision>0</Precision>
684                     <Scale>0</Scale>
685                     <Size>20</Size>
686                 </Parameter>
687                 <Parameter>
688                     <Name>Level</Name>
689                     <DbType>Int32</DbType>
690                     <NativeType>int</NativeType>
691                     <Precision>10</Precision>
692                     <Scale>0</Scale>
693                     <Size>4</Size>
694                 </Parameter>
695                 <Parameter>
696                     <Name>IsDetail</Name>
697                     <DbType>Boolean</DbType>
698                     <NativeType>bit</NativeType>
699                     <Precision>1</Precision>
700                     <Scale>0</Scale>
701                     <Size>1</Size>
702                 </Parameter>
703                 <Parameter>
704                     <Name>FullName</Name>
705                     <DbType>String</DbType>
706                     <NativeType>nvarchar</NativeType>
707                     <Precision>0</Precision>
708                     <Scale>0</Scale>
709                     <Size>100</Size>
710                 </Parameter>
711                 <Parameter>
712                     <Name>FullParentID</Name>
713                     <DbType>String</DbType>
714                     <NativeType>nvarchar</NativeType>
715                     <Precision>0</Precision>
716                     <Scale>0</Scale>
717                     <Size>80</Size>
718                 </Parameter>
719                 <Parameter>
720                     <Name>ModifyDTM</Name>
721                     <DbType>DateTime</DbType>
722                     <NativeType>datetime</NativeType>
723                     <Precision>23</Precision>
724                     <Scale>3</Scale>
725                     <Size>8</Size>
726                 </Parameter>
727                 <Parameter>
728                     <Name>Remark</Name>
729                     <DbType>String</DbType>
730                     <NativeType>nvarchar</NativeType>
731                     <Precision>0</Precision>
732                     <Scale>0</Scale>
733                     <Size>200</Size>
734                 </Parameter>
735                 <Parameter>
736                     <Name>AllowUsed</Name>
737                     <DbType>Boolean</DbType>
738                     <NativeType>bit</NativeType>
739                     <Precision>1</Precision>
740                     <Scale>0</Scale>
741                     <Size>1</Size>
742                 </Parameter>
743             </Parameters>
744         </Command>
745         <Command>
746             <Key>Delete</Key>
747             <CommandType>Text</CommandType>
748             <Text>
749             DELETE FROM [Bas_Bank]
750             WHERE 1=1  #WHERE#
751             </Text>
752         </Command>
753         <Command>
754             <Key>DeleteByKey</Key>
755             <CommandType>Text</CommandType>
756             <Text>
757             DELETE FROM [Bas_Bank]
758             WHERE 1=1 
759             And [CompanyID]=@CompanyID 
760             And [BankID]=@BankID 
761             </Text>
762             <Parameters>        
763                 <Parameter>
764                     <Name>CompanyID</Name>
765                     <DbType>AnsiString</DbType>
766                     <NativeType>varchar</NativeType>
767                     <Precision>0</Precision>
768                     <Scale>0</Scale>
769                     <Size>10</Size>
770                 </Parameter>
771                 <Parameter>
772                     <Name>BankID</Name>
773                     <DbType>AnsiString</DbType>
774                     <NativeType>varchar</NativeType>
775                     <Precision>0</Precision>
776                     <Scale>0</Scale>
777                     <Size>20</Size>
778                 </Parameter>
779             </Parameters>
780         </Command>
781     </Commands>
782 </EntityMapper>

  注意看Command节点,可以简单理解为数据库命令(下称命令),比较关键的是TextParameters子节点。这些命令有的带有参数有的则没有,没带参数的会有一个 ## 占位符。没带参数的命0000000令,其参数可能通过硬编码生成也有可能通过解析Lambda表达式生成,如何解析Lambda表达式会在接下来的第二点介绍。带有参数的命令,其参数名跟字段名一致,根据实体实例与字段名称就可以确定参数的值。 

 1     public Command Build<T>(string cmdName, T TEntity)
 2         where T : class
 3     {
 4         Command cmd = this.GetCommand(typeof(T), cmdName);
 5         foreach (Parameter parameter in cmd.Parameters)
 6         {
 7             //赋参数值
 8             object value = AccFacHelper.Get(TEntity, parameter.Name);
 9             parameter.Value = value;
10         }
11 
12         return cmd;
13     }

  2. 如何将Lambda表达式解析成查询条件

  很早之前大牛老赵就写过一篇博文 [扩展LINQ to SQL:使用Lambda Expression批量删除数据],基本思路是实现一个Expression<Func<T,bool>>解析器并将Lambda解析为最终需要执行的TSQL。但是老赵的实现并不完整,不能解析像 f=>true f=>!f.FieldName f=>string.Length f=>string[].Contains(s) 等表达式。我在他的基础上再增加了处理,并且把条件和参数分开来以适应Dapper的参数要求,看代码片段:

 1   case ExpressionType.Constant:
 2         //True常量解析成1==1 Flase常量解析成1==2
 3         bool value = Convert.ToBoolean(((ConstantExpression)expr).Value);
 4         leftExpr = Expression.Constant(1);
 5         rightExpr = Expression.Constant(value ? 1 : 2);
 6 
 7         break;
 8 
 9   ... ...
10 
11   string condition = b.NodeType == ExpressionType.Coalesce ? 
12         string.Format("({0}({1},{2}))", opr, left, right) : 
13         string.Format("({0} {1} {2})", left, opr, right);
14 
15   ......
16 
17   condition = string.Format(" AND {0}", _stcConditions.Pop());
18     MatchCollection matches = Regex.Matches(condition, string.Format(@"{0}(?<Name>p(?<Index>[0-9]+))", _parameterPrefix));
19     foreach (Match match in matches)
20     {
21         if (!match.Success) continue;
22 
23         string index = match.Groups["Index"].Value;
24         string parameterName = match.Groups["Name"].Value;
25         if (_parameters[parameterName] == null) _parameters.Add(parameterName, _lstArguments[Convert.ToInt32(index)]);
26     }

   3. 如何将Dapper返回的IDataReader转化成DataTable和DataSet

   IDataReader转化成DataTable相对容易,直接调用DataTable.Load(IDataReader)重载就可以,比较麻烦的是转成DataSet。DataSet.Load方法的三个重载都要传递DataTable[]形参,但在IDataReader填充DataSet之前我们是无法知道它包含有多少个数据集,也就无法确定如何给DataSet.Load传参,这似乎真的是个互相矛盾的命题。先别着急,想想之前经常用的SqlDataAdapter,它就有SqlDataAdapter.Fill(DataSet)重载。它能直接填充DataSet而不用传递DataTable[]形参,那么理论上来说DataSet.Load方法也不需要传递才对,因为实际上无论是DataSet.Load还是SqlDataAdapter.Fill,它们里面无非都是对IDataReader的层层封装而已。如此看来,只要弄清楚SqlDataAdapter.Fill(DataSet)重载,我们的问题便会迎刃而解了。

  祭出反编译利器.NET Reflector,先来看看SqlDataAdapter.Fill(DataSet)到底都干了些什么:  

 1 public abstract class DbDataAdapter : DataAdapter, IDbDataAdapter, IDataAdapter, ICloneable
 2 {
 3     public override int Fill(DataSet dataSet)
 4     {
 5         try
 6         {
 7             IDbCommand selectCommand = this._IDbDataAdapter.SelectCommand;
 8             CommandBehavior fillCommandBehavior = this.FillCommandBehavior;
 9             num = this.Fill(dataSet, 0, 0, "Table", selectCommand, fillCommandBehavior);
10         }
11         finally
12         {
13             Bid.ScopeLeave(ref ptr);
14         }
15         return num;
16     }
17 }
18 
19 public abstract class DbDataAdapter : DataAdapter, IDbDataAdapter, IDataAdapter, ICloneable
20 {
21     protected virtual int Fill(DataSet dataSet, int startRecord, int maxRecords, string srcTable, IDbCommand command, CommandBehavior behavior)
22     {
23         try
24         {
25             //srcTable="Table",注意跟踪形参
26             num = this.FillInternal(dataSet, null, startRecord, maxRecords, srcTable, command, behavior);
27         }
28         finally
29         {
30             Bid.ScopeLeave(ref ptr);
31         }
32         return num;
33     }
34 }
35 
36 public abstract class DbDataAdapter : DataAdapter, IDbDataAdapter, IDataAdapter, ICloneable
37 {
38     private int FillInternal(DataSet dataset, DataTable[] datatables, int startRecord, int maxRecords, string srcTable, IDbCommand command, CommandBehavior behavior)
39     {
40         bool flag = null == command.Connection;
41         try
42         {
43             try
44             {
45                 using (IDataReader reader = null)
46                 {
47                     reader = command.ExecuteReader(behavior);
48                     ... ...
49                     return this.Fill(dataset, srcTable, reader, startRecord, maxRecords);
50                 }
51             }
52             finally
53             {
54                 QuietClose(connection, open);
55             }
56         }
57         finally
58         {
59             if (flag)
60             {
61                 command.Transaction = null;
62                 command.Connection = null;
63             }
64         }
65         return 0;
66     }
67 }
68 
69 public class DataAdapter : Component, IDataAdapter
70 {
71     protected virtual int Fill(DataSet dataSet, string srcTable, IDataReader dataReader, int startRecord, int maxRecords)
72     {
73         try
74         {            
75             DataReaderContainer container = DataReaderContainer.Create(dataReader, this.ReturnProviderSpecificTypes);
76             num = this.FillFromReader(dataSet, null, srcTable, container, startRecord, maxRecords, null, null);
77         }
78         finally
79         {
80             Bid.ScopeLeave(ref ptr);
81         }
82         return num;
83     }
84 }

  看到了没,SqlDataAdapter.Fill(DataSet)方法内部是调用了另外一个重载,形参srcTable就是一个硬编码的"Table"。

  再来看看DataSet.Load的内部处理:

 1 public class DataSet : MarshalByValueComponent, IListSource, IXmlSerializable, ISupportInitializeNotification, ISupportInitialize, ISerializable
 2 {
 3     public virtual void Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler, params DataTable[] tables)
 4     {        
 5         try
 6         {
 7             LoadAdapter adapter = new LoadAdapter {
 8                 FillLoadOption = loadOption,
 9                 MissingSchemaAction = MissingSchemaAction.AddWithKey
10             };
11             if (errorHandler != null)
12             {
13                 adapter.FillError += errorHandler;
14             }
15             adapter.FillFromReader(tables, reader, 0, 0);
16             ... ...
17         }
18         finally
19         {
20             ... ...
21         }
22     }
23 }
24 
25 internal sealed class LoadAdapter : DataAdapter
26 {
27     internal int FillFromReader(DataTable[] dataTables, IDataReader dataReader, int startRecord, int maxRecords)
28     {
29         return this.Fill(dataTables, dataReader, startRecord, maxRecords);
30     }
31 }
32 
33  

   假如我们把LoadAdapter.FillFromReader方法修改一个,调用LoadAdapter.Fill的另外一个重载LoadAdapter.Fill(DataSet,string,IDataReader,int,int),而第二个形参只需要传"Table"而已。最终完成代码: 

 1 /// <summary>
 2 /// 数据适配器,扩展Fill方法
 3 /// .NET的DataSet.Load方法,底层调用DataAdapter.Fill(DataTable[], IDataReader, int, int)
 4 /// Dapper想要返回DataSet,需要重写Load方法,不必传入DataTable[],因为数组长度不确定
 5 /// </summary>
 6 public class XLoadAdapter : DataAdapter
 7 {
 8     public XLoadAdapter()
 9     {
10     }
11 
12     public int FillFromReader(DataSet ds, IDataReader dataReader, int startRecord, int maxRecords)
13     {
14         return this.Fill(ds, "Table", dataReader, startRecord, maxRecords);
15     }
16 }
17 
18 /// <summary>
19 /// 扩展Load方法
20 /// </summary>
21 public class XDataSet : DataSet
22 {
23     public override void Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler handler, params DataTable[] tables)
24     {
25         XLoadAdapter adapter = new XLoadAdapter
26         {
27             FillLoadOption = loadOption,
28             MissingSchemaAction = MissingSchemaAction.AddWithKey
29         };
30         if (handler != null)
31         {
32             adapter.FillError += handler;
33         }
34         adapter.FillFromReader(this, reader, 0, 0);
35         if (!reader.IsClosed && !reader.NextResult())
36         {
37             reader.Close();
38         }
39     }
40 }
41 
42 //调用
43 IDataReader reader = _session.Connection.ExecuteReader(command, dynParameters,
44     _session.Transaction, _session.DataSource.CommandTimeout, commandType);
45 DataSet ds = new XDataSet();
46 ds.Load(reader, LoadOption.OverwriteChanges, null, new DataTable[] { });

    4. 总结

   本框架在Dapper的基础上再做封装,支持Lambda表达式树查询也支持纯Sql查询,相对来说比较灵活。但限于个人水平,没有把EmitMapper完美整合进来,只是简单的进行了引用,如果朋友们有好的建议,在下诚心请教。

GitHub地址:https://github.com/TANZAME/XFramework ,在 XFramework/branch/XFramework_1/ 路径下面。

 

posted @ 2016-06-20 13:22 田乃翔 阅读(...) 评论(...) 编辑 收藏