辞职在家闲来无事,花几天功夫将之前项目里用到的一个数据访问层整理了出来。实现单个实体的增删改查,可执行存储过程,可输出返回参数,查询结果集可根据实际情况返回DataTable、DataSet和强类型,同时支持不同类型数据库。目前成熟的ORM框架多不胜数,再写一个出来,并非想证明自己写的有多好,一来认为现有成熟的ORM框架并不能灵活适用于大型ERP项目,二来有感于工作多年有必要写下一些东西。虽然有种重复造轮子的感觉,但相信朋友们和我一样,享受造轮子的过程并把它当成一种乐趣,对吧。
1、LinQ 语法查询
1 //LinQ 语法查询2 query = rptBase.Query<Bas_Company>();3 query = rptBase.Query<Bas_Company>(x => true);4 query = rptBase.Query<Bas_Company>(x => true && (x.CompanyID ?? null) != null && new[] { "1", "2" }.Contains(x.CompanyID) &&5 x.CompanyID.Substring(2, 5).TrimEnd() == "OK" && x.AllowUsed);
View Code
2、分页查询
1 //分页查询2 query = rptBase.Query<Bas_Company>(new PageInfo(3, 20));3 query = rptBase.Query<Bas_Company>(new PageInfo(3, 20), x => x.CompanyID == "FT");
View Code
3、自定义脚本查询
1 d = new DynamicParameters();2 d.Add("CompanyName", "美之源科技有限公司", DbType.String, null, 20);3 query = rptBase.Query<Bas_Company>("Select * From Bas_Company WHERE CompanyName = @CompanyName", d);
View Code
4、自定义参数查询
1 //自定义参数查询2 d = new DynamicParameters();3 d.Add("CompanyName", "美之源科技有限公司");4 query = rptBase.Query<Bas_Company>("selectByName", null, d);
View Code
5、带返回值查询
1 //带返回值查询2 d = new DynamicParameters();3 d.Add("Row", null);4 table = rptBase.QueryDataTable<Bas_Company>("returnValue", x => x.CompanyID != "FT", d);5 eff = d.Get<int?>("Row");
View Code
6、自定义实体查询
1 //查询自定义实体2 var query1 = rptBase.Query<ThinEntity>(typeof(Bas_Company).FullName, "thinEntity", "And CompanyID <> 'FT' ");
View Code
7、DataTable 查询
1 DataTable table = null;2 table = rptBase.QueryDataTable<Bas_Company>();3 table = rptBase.QueryDataTable<Bas_Company>(x => true);
View Code
8、DataSet 查询
1 DataSet data = null;2 data = rptBase.QueryDataSet<Bas_Company>("Select",x => true);
View Code
9、增删改
1 //新增 2 Bas_Company company = new Bas_Company(); 3 company.CompanyID = "TH"; 4 company.CompanyCode = "TH001"; 5 rptBase.Insert(company); 6 7 //修改 8 company.CompanyCode = "TH00x"; 9 rptBase.Update(company);10 //批量修改11 rptBase.Update<Bas_Company>(x => new Bas_Company { CompanyCode = "TH003" }, x => x.CompanyID == "TH");12 13 //删除14 rptBase.Delete(company);
View Code
10、解析成字符串
1 sql = rptBase.Resolve<Bas_Company>("Select",x => true && (x.CompanyID ?? null) != null && new[] { "1", "2" }.Contains(x.CompanyID) &&2 x.CompanyID.Substring(2, 5).TrimEnd() == "OK" && x.AllowUsed);3 sqlList.Add(sql);
View Code
还有其它的重载这里就不一一列举,源代码的单元测试里有很详细的说明。
先来看看项目架构截图:
03.Src:第三方开源组件源码,有的目前没有用到,先收藏着;
04.Infrastructure:整个项目公用类库,包含一些Helper和公用类;
05.DataAccess:数据访问核心类库,实现Lambda表达式解析、Dapper封装等;
06.Model:实体层,实体使用CodeSmith生成,包含实体和脚本映射两个部分;
09.Presentation:展示层,目前只有一个单元测试项目。
Dapper,一个基于IDbConnection扩展的完全开源的轻、小、快的ORM框架(项目源码 https://github.com/SamSaffron/dapper-dot-net )。在本框架中,需要解决的主要问题有如下几点:
- 如何生成Dapper查询所需要的TSQL脚本和参数
- 如何将Lambda表达式解析成查询条件
- 如何将Dapper返回的IDataReader转化成DataTable和DataSet
- 如何解决多数据库的问题
--------------------------------------- 华丽丽的分割线 --------------------------------------------
1. 如何生成Dapper查询所需要的SQL语句和参数
基于SQL和代码分离原则,数据库中每一张表都有一个POCO实体与之对应并且用一个文件来描述,包括表名称、字段、主键和增删改查SQL及参数。因为
1 <??> 2 <EntityMapper ="http://www.w3.org/2001/ ="http://www.w3.org/2001/> 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 SELECT186 [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]=@CompanyID253 And [BankID]=@BankID254 </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] SET279 [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] = @AllowUsed293 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] SET415 [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] = @AllowUsed429 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] SET569 #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 @AllowUsed611 )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 finally12 {13 Bid.ScopeLeave(ref ptr);14 }15 return num;16 }17 }18 19 public abstract class DbDataAdapter : DataAdapter, IDbDataAdapter, IDataAdapter, ICloneable20 {21 protected virtual int Fill(DataSet dataSet, int startRecord, int maxRecords, string srcTable, IDbCommand command, CommandBehavior behavior)22 {23 try24 {25 //srcTable="Table",注意跟踪形参26 num = this.FillInternal(dataSet, null, startRecord, maxRecords, srcTable, command, behavior);27 }28 finally29 {30 Bid.ScopeLeave(ref ptr);31 }32 return num;33 }34 }35 36 public abstract class DbDataAdapter : DataAdapter, IDbDataAdapter, IDataAdapter, ICloneable37 {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 try42 {43 try44 {45 using (IDataReader reader = null)46 {47 reader = command.ExecuteReader(behavior);48 ... ...49 return this.Fill(dataset, srcTable, reader, startRecord, maxRecords);50 }51 }52 finally53 {54 QuietClose(connection, open);55 }56 }57 finally58 {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, IDataAdapter70 {71 protected virtual int Fill(DataSet dataSet, string srcTable, IDataReader dataReader, int startRecord, int maxRecords)72 {73 try74 { 75 DataReaderContainer container = DataReaderContainer.Create(dataReader, this.ReturnProviderSpecificTypes);76 num = this.FillFromReader(dataSet, null, srcTable, container, startRecord, maxRecords, null, null);77 }78 finally79 {80 Bid.ScopeLeave(ref ptr);81 }82 return num;83 }84 }
看到了没,SqlDataAdapter.Fill(DataSet)方法内部是调用了另外一个重载,形参srcTable就是一个硬编码的"Table"。
再来看看DataSet.Load的内部处理:
1 public class DataSet : MarshalByValueComponent, IListSource, I 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.AddWithKey10 };11 if (errorHandler != null)12 {13 adapter.FillError += errorHandler;14 }15 adapter.FillFromReader(tables, reader, 0, 0);16 ... ...17 }18 finally19 {20 ... ...21 }22 }23 }24 25 internal sealed class LoadAdapter : DataAdapter26 {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 : DataSet22 {23 public override void Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler handler, params DataTable[] tables)24 {25 XLoadAdapter adapter = new XLoadAdapter26 {27 FillLoadOption = loadOption,28 MissingSchemaAction = MissingSchemaAction.AddWithKey29 };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导致源码上传不了。
题外话,辞职回老家有一段时间了,明天上去广州,广州的朋友有工作介绍的帮忙推荐下,先谢过哈:)
原标题:【原创】打造基于Dapper的数据访问层
关键词: