辭職在家閒來無事，花幾天功夫將之前項目裡用到的一個數據訪問層整理了出來。實現單個實體的增刪改查，可執行存儲過程，可輸出返回參數，查詢結果集可根據實際情況返回DataTable、DataSet和強類型，同時支持不同類型數據庫。目前成熟的ORM框架多不勝數，再寫一個出來，並非想證明自己寫的有多好，一來認為現有成熟的ORM框架並不能靈活適用於大型ERP項目，二來有感於工作多年有必要寫下一些東西。雖然有種重復造輪子的感覺，但相信朋友們和我一樣，享受造輪子的過程並把它當成一種樂趣，對吧。
1、LinQ 語法查詢
2、分頁查詢
3、自定義腳本查詢
4、自定義參數查詢
5、帶返回值查詢
6、自定義實體查詢
7、DataTable 查詢
8、DataSet 查詢
9、增刪改
10、解析成字符串
還有其它的重載這裡就不一一列舉，源代碼的單元測試裡有很詳細的說明。
先來看看項目架構截圖：
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查詢所需要的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完美整合進來，只是簡單的進行了引用，如果朋友們有好的建議，在下誠心請教。需要源碼的朋友留個QQ郵箱我單獨發吧，園子的附件最大10M導致源碼上傳不了。
題外話，辭職回老家有一段時間了，明天上去廣州，廣州的朋友有工作介紹的幫忙推薦下，先謝過哈：）
