【原创】打造基于Dapper的数据访问层
- 前言
闲来无事，花几天功夫将之前项目里用到的一个数据访问层整理了出来。实现单个实体的增删改查，可执行存储过程，可输出返回参数，查询结果集可根据实际情况返回DataTable、DataSet和强类型，同时支持不同类型数据库。目前成熟的ORM框架多不胜数，虽然有种重复造轮子的感觉，但相信朋友们和我一样，享受造轮子的过程并把它当成一种乐趣，对吧。
- 调用示例
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);
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");
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);
4、自定义参数查询
1 //自定义参数查询 2 d = new DynamicParameters(); 3 d.Add("CompanyName", "美之源科技有限公司"); 4 query = rptBase.Query<Bas_Company>("selectByName", null, d);
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");
6、自定义实体查询
1 //查询自定义实体 2 var query1 = rptBase.Query<ThinEntity>(typeof(Bas_Company).FullName, "thinEntity", "And CompanyID <> 'FT' ");
7、DataTable 查询
1 DataTable table = null; 2 table = rptBase.QueryDataTable<Bas_Company>(); 3 table = rptBase.QueryDataTable<Bas_Company>(x => true);
8、DataSet 查询
1 DataSet data = null; 2 data = rptBase.QueryDataSet<Bas_Company>("Select",x => true);
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);
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);
还有其它的重载这里就不一一列举，源代码的单元测试里有很详细的说明。
- 详细解析
先来看看项目架构截图：
03.Src：第三方开源组件源码，有的目前没有用到，先收藏着；
04.Infrastructure：整个项目公用类库，包含一些Helper和公用类；
05.DataAccess：数据访问核心类库，实现Lambda表达式解析、Dapper封装等；
06.Model：实体层，实体使用CodeSmith生成，包含实体和脚本映射两个部分；
09.Presentation：展示层，目前只有一个单元测试项目。
Dapper，一个基于IDbConnection扩展的完全开源的轻、小、快的ORM框架（项目源码 https://github.com/SamSaffron/dapper-dot-net ）。在本框架中，需要解决的主要问题有如下几点：
- 如何生成Dapper查询所需要的TSQL脚本和参数
- 如何将Lambda表达式解析成查询条件
- 如何将Dapper返回的IDataReader转化成DataTable和DataSet
- 如何解决多数据库的问题
--------------------------------------- 华丽丽的分割线 --------------------------------------------
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节点，可以简单理解为数据库命令（下称命令），比较关键的是Text和Parameters子节点。这些命令有的带有参数有的则没有，没带参数的会有一个 ## 占位符。没带参数的命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/ 路径下面。
技术交流Q群: 816425449
