Dapper-Infrastructure

文件结构说明

特点

实体映射

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BH.Domain.Entity.Base;
using DapperInfrastructure.Extensions.Attr;
namespace BH.Domain.Entity.Category
{
 /// <summary>
 /// 应用
 /// </summary>
 [Table("ApplicationMTR")]
 public class ApplicationMtr : EntityWidthStringType 
 {
 /// <summary>
 /// 应用名称
 /// </summary> 
 [Column("Name")]
 public string Name { get; set; }
 /// <summary>
 /// 所属分类应用
 /// </summary>
 [Column("CategoryId")]
 public int CategoryId { get; set; }
 /// <summary>
 /// 创建时间
 /// </summary>
 [Column("CreateTime")]
 public DateTime CreateTime { get; set; }
 }
}

使用事例

初始化

DbFactory.Init<ApplicationMtr>();

新增实体

 
using (var db = NewDB)
{
	var repository = db.GetRepository<ApplicationMtr>();
	repository.Create(new ApplicationMtr()
	{
		Name = "Test1"
	});
} 

新增实体 - 事务

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

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

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

查询

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

查询

 
using (var db = NewDB)
{ 
 var sql = new Sql("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 ");
 var repository = NewDB.GetRepository<CategoryApplicationMtr>();
 var result = repository.GetList<CategoryApplicationMtr>(sql);
 Assert.IsTrue(!result.Any());
} 

分页查询

int pageIndex = 1;
int pageSize = 10;
using (var db = NewDB)
{ 
 // 方式1
	var sql = 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");
	// 多表分页查询
	var result2 = 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)

int pageIndex = 1;
int pageSize = 10;
using (var db = NewDB)
{
 var sql = 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 ");
 
 // 多表分页查询
 var result2 = db.SqlQuery.PageList<MyDtoClass>(
 pageIndex,
 pageSize,
 sql);
 
 Console.WriteLine(result2.TotalCount);
 Assert.IsTrue(result2.TotalCount > 0);
}

SQL Execute

using (var db = NewDB)
{
 var sql = 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 (var db = NewDB)
{
 var procSql = new ProcSql("TestProc1");
 var result = 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 (var db = NewDB)
{
 var procSql = new ProcSql("TestProc");
 procSql.AddParm("Value", "Hello Value");
 procSql.AddParm("OutValue", "Hello OutValue", true);
 var result = 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 (var db = NewDB)
{
 var procSql = new ProcSql("TestProc2");
 procSql.AddParm("Value", "Hello Value");
 procSql.AddParm("OutValue", "Hello OutValue", true);
 var result = db.GetSqlRun.ExecuteProcList<string>(procSql);
 Console.WriteLine(string.Format("Result = {0}", result));
 foreach (var item in result)
 {
 Console.WriteLine(item);
 }
 Console.WriteLine(String.Format("OutValue = {0}", procSql.GetOutValue<string>("OutValue")));
}
 

参考资料