Dapper-Infrastructure

Dapper 扩展库 - (Dapper Extensions Library)

文件结构说明

• /Code/... 程序文件
• /DB/Init_DB.sql MYSQL初始化脚本

特点

• 集成PetaPoco的SQL Linq语法糖.
• 支持多表关联分页查询.
• CRUD 封装/简化调用方式.

实体映射

• Table表名映射使用 - DapperInfrastructure.Extensions.Attr.TableAttribute
• Column字段映射使用 - DapperInfrastructure.Extensions.Attr.ColumnAttribute

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;usingBH.Domain.Entity.Base;usingDapperInfrastructure.Extensions.Attr;namespaceBH.Domain.Entity.Category{/// <summary>/// 应用/// </summary>[Table("ApplicationMTR")]publicclassApplicationMtr:EntityWidthStringType{/// <summary>/// 应用名称/// </summary> [Column("Name")]publicstringName{get;set;}/// <summary>/// 所属分类应用/// </summary>[Column("CategoryId")]publicintCategoryId{get;set;}/// <summary>/// 创建时间/// </summary>[Column("CreateTime")]publicDateTimeCreateTime{get;set;}}}

使用事例

初始化

DbFactory.Init<ApplicationMtr>();

新增实体

using(vardb=NewDB){varrepository=db.GetRepository<ApplicationMtr>();repository.Create(newApplicationMtr(){Name="Test1"});}

新增实体 - 事务

using(vardb=NewDB){// 开始事务db.BeginTransaction();varrepository=db.GetRepository<ApplicationMtr>();repository.Create(newApplicationMtr(){Name="Game"});repository.Create(newApplicationMtr(){Name="Work"});repository.Create(newApplicationMtr(){Name="Book"});// 事务提交db.Commit();}

新增 - 事务 (多数据库实例切换)

using(vardb=NewDB){// 开始事务db.BeginTransaction();varrepository=db.GetRepository<ApplicationMtr>();repository.Create(newApplicationMtr(){Name="Game"});// 切换数据库实例db.ChangeDatabase(DBName.DB1.ToString());repository.Create(newApplicationMtr(){Name="Work"});// 切换数据库实例db.ChangeDatabase(DBName.DB2.ToString());repository.Create(newApplicationMtr(){Name="Book"});// 事务提交db.Commit();}

查询

using(vardb=NewDB){// 方式1varresult=db.SqlQuery.GetList<ApplicationMtr>("select * from ApplicationMTR where Name = @0 ","Game");Console.WriteLine(result.Count());Assert.IsTrue(result.Count()>0);// 方式2result=db.SqlQuery.GetList<ApplicationMtr>(Sql.Builder.Append("select * from ApplicationMTR ").Where(" Name = @0 ","Game"));Console.WriteLine(result.Count());Assert.IsTrue(result.Count()>0);// 方式3result=db.GetSqlQuery.GetList<ApplicationMtr>(Sql.Builder.SelectAll().From<ApplicationMtr>().Where(" Name = @0 ","Game"));Console.WriteLine(result.Count());Assert.IsTrue(result.Count()>0);}

查询

using(vardb=NewDB){varsql=newSql("SELECT * FROM CategoryApplicationMTR ");sql.Where(" Name = @0","A1");sql.Where(" Name = @0 ","A2");sql.Where(" Name = @0 ","A3");sql.WhereIfIn(" Name ",new[]{"A4","A5","A6"});sql.OrderBy(" Name desc ");varrepository=NewDB.GetRepository<CategoryApplicationMtr>();varresult=repository.GetList<CategoryApplicationMtr>(sql);Assert.IsTrue(!result.Any());}

分页查询

intpageIndex=1;intpageSize=10;using(vardb=NewDB){// 方式1varsql=Sql.Builder.Append(" select a.Id,"+"a.`Name` as ApplicationMtr_Name,"+"a.CategoryId as ApplicationMtr_CategoryId,"+"a.CreateTime as ApplicationMtr_CreateTime,"+"c.`Name` as CategoryApplicationMtr_Name "+" from ApplicationMTR as a"+" left join CategoryApplicationMTR as c on a.CategoryId = c.Id");sql.Where(" a.Name = @0 ","Game");// 多表分页查询varresult2=db.SqlQuery.PageList<MyDtoClass>(pageIndex,pageSize,sql);Console.WriteLine(result2.TotalCount);Assert.IsTrue(result2.TotalCount>0);// 方式2 sql=Sql.Builder.Select("a.id,a.`Name` as ApplicationMtr_Name,"+"a.CategoryId as ApplicationMtr_CategoryId,"+"a.CreateTime as ApplicationMtr_CreateTime,"+"c.`Name` as CategoryApplicationMtr_Name ").From<ApplicationMtr>("a").LeftJoin<CategoryApplicationMtr>("c").On("a.CategoryId = c.Id").Where(" a.Name = @0 ","Game");result2=db.GetSqlQuery.PageList<MyDtoClass>(pageIndex,pageSize,sql);Console.WriteLine(result2.TotalCount);Assert.IsTrue(result2.TotalCount>0);}

分页查询 (Select Distinct)

intpageIndex=1;intpageSize=10;using(vardb=NewDB){varsql=Sql.Builder.Append(" select distinct a.Id,"+" a.`Name` as ApplicationMtr_Name, "+"a.CategoryId as ApplicationMtr_CategoryId,"+" a.CreateTime as ApplicationMtr_CreateTime, "+"c.`Name` as CategoryApplicationMtr_Name "+" from ApplicationMTR as a"+" left join CategoryApplicationMTR as c on a.CategoryId = c.Id");sql.Where(" a.Name = @0 ","Game");// 处理 Select Count Distinct 情况sql.SetCountField(" distinct a.Id ");// 多表分页查询varresult2=db.SqlQuery.PageList<MyDtoClass>(pageIndex,pageSize,sql);Console.WriteLine(result2.TotalCount);Assert.IsTrue(result2.TotalCount>0);}

SQL Execute

using(vardb=NewDB){varsql=Sql.Builder.Append(string.Format("DELETE FROM {0} WHERE Id = @0",db.GetTableName<ApplicationMtr>()),1);db.GetSqlRun.Execute(sql);}

存储过程

/* USE [CommonDB] GO CREATE proc[dbo].[TestProc1] AS BEGIN SELECT 'Test' AS RESULT END*/using(vardb=NewDB){varprocSql=newProcSql("TestProc1");varresult=db.GetSqlRun.ExecuteProcObj<string>(procSql);Console.WriteLine(string.Format("Result = {0}",result));}/* USE [CommonDB] GO  CREATE proc[dbo].[TestProc] ( @Value nvarchar(255), @OutValue nvarchar(255) output ) AS BEGIN SET @OutValue = 'Hello World!' SELECT @Value AS RESULT END*/using(vardb=NewDB){varprocSql=newProcSql("TestProc");procSql.AddParm("Value","Hello Value");procSql.AddParm("OutValue","Hello OutValue",true);varresult=db.GetSqlRun.ExecuteProcObj<string>(procSql);Console.WriteLine(string.Format("Result = {0}",result));Console.WriteLine(String.Format("OutValue = {0}",procSql.GetOutValue<string>("OutValue")));}/* USE [CommonDB] USE [CommonDB] GO // 测试表1 CREATE TABLE [dbo].[Test]( [Name] [nchar](10) NULL ) ON [PRIMARY]  GO // 测试表2 CREATE TABLE [dbo].[Test1]( [Name] [nchar](10) NULL, [Age] [int] NULL ) ON [PRIMARY]  GO  // 测试存储过程 CREATE proc [dbo].[TestProc2] ( @Value nvarchar(255)  ,@OutValue nvarchar(255) output ) AS BEGIN  SET @OutValue = (SELECT top 1 name FROM CommonDB.dbo.Test) SELECT * FROM CommonDB.dbo.Test1 END*/using(vardb=NewDB){varprocSql=newProcSql("TestProc2");procSql.AddParm("Value","Hello Value");procSql.AddParm("OutValue","Hello OutValue",true);varresult=db.GetSqlRun.ExecuteProcList<string>(procSql);Console.WriteLine(string.Format("Result = {0}",result));foreach(variteminresult){Console.WriteLine(item);}Console.WriteLine(String.Format("OutValue = {0}",procSql.GetOutValue<string>("OutValue")));}

参考资料

• Dapper
• PetaPoco