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

摘要:
前言闲来无事,花几天功夫将之前项目里用到的一个数据访问层整理了出来。实现单个实体的增删改查,可执行存储过程,可输出返回参数,查询结果集可根据实际情况返回DataTable、DataSet和强类型,同时支持不同类型数据库。Dapper,一个基于IDbConnection扩展的完全开源的轻、小、快的ORM框架。
  • 前言

闲来无事,花几天功夫将之前项目里用到的一个数据访问层整理了出来。实现单个实体的增删改查,可执行存储过程,可输出返回参数,查询结果集可根据实际情况返回DataTable、DataSet和强类型,同时支持不同类型数据库。目前成熟的ORM框架多不胜数,虽然有种重复造轮子的感觉,但相信朋友们和我一样,享受造轮子的过程并把它当成一种乐趣,对吧。

  • 调用示例

1、LinQ 语法查询

【原创】打造基于Dapper的数据访问层第1张【原创】打造基于Dapper的数据访问层第2张
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、分页查询

【原创】打造基于Dapper的数据访问层第1张【原创】打造基于Dapper的数据访问层第4张
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、自定义脚本查询

【原创】打造基于Dapper的数据访问层第1张【原创】打造基于Dapper的数据访问层第6张
1     d = newDynamicParameters();
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、自定义参数查询

【原创】打造基于Dapper的数据访问层第1张【原创】打造基于Dapper的数据访问层第8张
1     //自定义参数查询
2     d = newDynamicParameters();
3     d.Add("CompanyName", "美之源科技有限公司");
4     query = rptBase.Query<Bas_Company>("selectByName", null, d);
View Code

5、带返回值查询

【原创】打造基于Dapper的数据访问层第1张【原创】打造基于Dapper的数据访问层第10张
1     //带返回值查询
2     d = newDynamicParameters();
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、自定义实体查询

【原创】打造基于Dapper的数据访问层第1张【原创】打造基于Dapper的数据访问层第12张
1     //查询自定义实体
2     var query1 = rptBase.Query<ThinEntity>(typeof(Bas_Company).FullName, "thinEntity", "And CompanyID <> 'FT' ");
View Code

7、DataTable 查询

【原创】打造基于Dapper的数据访问层第1张【原创】打造基于Dapper的数据访问层第14张
1     DataTable table = null;
2     table = rptBase.QueryDataTable<Bas_Company>();
3     table = rptBase.QueryDataTable<Bas_Company>(x => true);
View Code

8、DataSet 查询

【原创】打造基于Dapper的数据访问层第1张【原创】打造基于Dapper的数据访问层第16张
1     DataSet data = null;
2     data = rptBase.QueryDataSet<Bas_Company>("Select",x => true);
View Code

9、增删改

【原创】打造基于Dapper的数据访问层第1张【原创】打造基于Dapper的数据访问层第18张
1     //新增
2     Bas_Company company = newBas_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、解析成字符串

【原创】打造基于Dapper的数据访问层第1张【原创】打造基于Dapper的数据访问层第20张
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

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

  • 详细解析

先来看看项目架构截图:

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

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>(stringcmdName, T TEntity)
2         where T : class
3 {
4         Command cmd = this.GetCommand(typeof(T), cmdName);
5         foreach (Parameter parameter incmd.Parameters)
6 {
7             //赋参数值
8             object value =AccFacHelper.Get(TEntity, parameter.Name);
9             parameter.Value =value;
10 }
11 
12         returncmd;
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   caseExpressionType.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 inmatches)
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 classDbDataAdapter : DataAdapter, IDbDataAdapter, IDataAdapter, ICloneable
2 {
3     public override intFill(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(refptr);
14 }
15         returnnum;
16 }
17 }
18 
19 public abstract classDbDataAdapter : DataAdapter, IDbDataAdapter, IDataAdapter, ICloneable
20 {
21     protected virtual int Fill(DataSet dataSet, int startRecord, int maxRecords, stringsrcTable, 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(refptr);
31 }
32         returnnum;
33 }
34 }
35 
36 public abstract classDbDataAdapter : DataAdapter, IDbDataAdapter, IDataAdapter, ICloneable
37 {
38     private int FillInternal(DataSet dataset, DataTable[] datatables, int startRecord, int maxRecords, stringsrcTable, 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 classDataAdapter : Component, IDataAdapter
70 {
71     protected virtual int Fill(DataSet dataSet, string srcTable, IDataReader dataReader, int startRecord, intmaxRecords)
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(refptr);
81 }
82         returnnum;
83 }
84 }

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

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

1 public classDataSet : MarshalByValueComponent, IListSource, IXmlSerializable, ISupportInitializeNotification, ISupportInitialize, ISerializable
2 {
3     public virtual void Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler, paramsDataTable[] tables)
4 {        
5         try
6 {
7             LoadAdapter adapter = newLoadAdapter {
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 classLoadAdapter : DataAdapter
26 {
27     internal int FillFromReader(DataTable[] dataTables, IDataReader dataReader, int startRecord, intmaxRecords)
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 classXLoadAdapter : DataAdapter
7 {
8     publicXLoadAdapter()
9 {
10 }
11 
12     public int FillFromReader(DataSet ds, IDataReader dataReader, int startRecord, intmaxRecords)
13 {
14         return this.Fill(ds, "Table", dataReader, startRecord, maxRecords);
15 }
16 }
17 
18 /// <summary>
19 ///扩展Load方法
20 /// </summary>
21 public classXDataSet : DataSet
22 {
23     public override void Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler handler, paramsDataTable[] tables)
24 {
25         XLoadAdapter adapter = newXLoadAdapter
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 = newXDataSet();
46 ds.Load(reader, LoadOption.OverwriteChanges, null, new DataTable[] { });

4.总结

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

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

技术交流Q群: 816425449

免责声明:文章转载自《【原创】打造基于Dapper的数据访问层》仅用于学习参考。如对内容有疑问,请及时联系本站处理。

上篇Bzoj 2789: [Poi2012]Letters 树状数组,逆序对牛客小白月赛5 I 区间 (interval) 【前缀和】下篇

宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=

相关文章

ToString和Convert.ToString处理null值

http://www.cnblogs.com/qinge/p/5687806.html文章来源 1.Convert.ToString能处理字符串为null的情况。 测试代码如下: 1 2 3 4 5 6 static void Main(string[] args) {   string msg = null;   Console.W...

xxl-job编写GULE(Java)运行模式,带执行参数(url)

packagecom.xxl.job.service.handler; importcom.xxl.job.core.log.XxlJobLogger; importcom.xxl.job.core.biz.model.ReturnT; importcom.xxl.job.core.handler.IJobHandler; public class q...

webos项目中EF仓储模式的代码

参考了网上EF数据处理的代码写成 在UPDATE的方法不太好,性能可能有问题。大家如果有什么好的方法可以给我留言。 暂时没有想到 接口类 using System; using System.Collections.Generic; namespace Model.EF { public interface IRepository<TEnt...

Android中的设计思想一

Android设计思想 直接上示例 ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓ xml布局 <?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/r...

java 生成二维码

最近有点时间想学点东西,想做个简单的系统,现在登录的时候使用扫描二维码获取验证码登录,于是就有了下面的一些代码 首先要导入pom依赖 <dependency> <groupId>com.google.zxing</groupId> <artifactId>co...

springmvc 整合 netty-socketio

1 maven <dependency><groupId>com.corundumstudio.socketio</groupId><artifactId>netty-socketio</artifactId><version>1.7.12</version></de...