星空网 > 软件开发 > ASP.net

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

  • 前言

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

  • 调用示例

  1、LinQ 语法查询

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

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

View Code

  5、带返回值查询

【原创】打造基于Dapper的数据访问层【原创】打造基于Dapper的数据访问层
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、自定义实体查询

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

View Code

  7、DataTable 查询

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

View Code

  8、DataSet 查询

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

View Code

  9、增删改

【原创】打造基于Dapper的数据访问层【原创】打造基于Dapper的数据访问层
 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、解析成字符串

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

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实体与之对应并且用一个文件来描述,包括表名称、字段、主键和增删改查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的数据访问层

关键词:

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流