Dubonnet

A .NET Core v2.1 ORM that runs on Dapper.

下载 NuGet:

Thanks to

• https://github.com/getson/Dapper.Nona
• https://github.com/henkmollema/Dommel
• https://github.com/sqlkata/querybuilder

Example

1. 安装 .NET Core v2.1 版本

2. 在命令行下以 webapi 为模板创建新项目

dotnet new webapi -n MyDubon -lang C#
cd MyDubon
#dotnet run #访问 https://localhost:5001/api/values/ 查看效果

并安装相关的依赖库

dotnet add package MySqlConnector
dotnet add package Dapper
dotnet add package Dubonnet
dotnet add package Dubonnet.QueryBuilder

3. 创建 MySQL 数据库，修改配置文件 appsettings.json 增加数据库连接

{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "server=127.0.0.1;user id=dba;password=pass;port=3306;database=db_mobile;SslMode=none"
  },
  "AllowedHosts": "*"
}

4. 增加 Models 文件夹和两个文件 AppDbContext.cs 和 Mobile.cs

#复制 Dubonnet/src/Example/Models/ 下的这两个文件复制过来，
#并将 namespace 从 Dubonnet.Example 改为 MyDubon

修改 Startup.cs 中 ConfigureServices() 增加依赖注入

// 其他代码 ...

namespace MyDubon
{
    public class Startup
    {
    	// 其他代码 ...
    	
		public void ConfigureServices(IServiceCollection services)
		{
			var dsnMySql = Configuration.GetConnectionString("DefaultConnection");
			services.AddSingleton(new AppDbContext(dsnMySql));
			services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
		}
		
		// 其他代码 ...
	}
}

5. 对 Controllers/ValuesController.cs 作如下修改

// 其他代码 ...

namespace MyDubon.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ValuesController : ControllerBase
    {
		private readonly AppDbContext db;
		
		public ValuesController(AppDbContext db)
		{
		    this.db = db;
		}
		
		// GET api/values
		[HttpGet]
		public ActionResult<IEnumerable<string>> Get()
		{
		    var queryArea = db.Areas.Where("areacode", "0755");
		    var area = queryArea.First<Area>();
		    return new string[] { area.province, area.city };
		    //return new string[] { "value1", "value2" };
		}
		
		// 其他代码 ...
	}
}

---

API

Retrieving entities by id

var product = db.Products.Get(1);

Retrieving all entities in a table

var products = db.Products.All().ToList();

Selecting some entities

var products = db.Products.WhereIn("id", new int[]{3,4,5}).All();
// 和下面的查询等价：
var same_products = db.Products.WhereRaw("id IN (?,?,?)",3,4,5).All();

Inserting entities

var product = new Product { Name = "Awesome bike", InStock = 4 };
int id = db.Conn.Insert(product);

var products = new List<Product>
{
    new Product { Name = "Awesome bike", InStock = 4 },
    new Product { Name = "Awesome bike 2", InStock = 5 }
};

int count = db.Conn.Insert(products);

Updating entities

var product = db.Products.Get(1);
product.LastUpdate = DateTime.Now;
db.Conn.Update(product);

var products = db.Products.WhereIn("id", new int[]{1,2}).All().ToList();
products[0].LastUpdate = DateTime.Now;
products[1].LastUpdate = DateTime.Now;
db.Conn.Update(products);

Removing entities

var product = db.Products.Get(1);
db.Conn.Delete(product);

var products = db.Products.WhereIn("id", new int[]{1,2}).All();
db.Conn.Delete(products);

---

Extensibility

ITableNameResolver

// 根据 Model 类名对应表名，比如 Area 的表名为 t_areas
public class CustomTableNameResolver : ITableNameResolver
{
    public string Resolve(Type type)
    {
        var lower = type.Name.ToLower();
        return $"t_{lower}s";
    }
}

Register the custom implementation:

db.NameResolver = new CustomTableNameResolver();

IColumnNameResolver

// 根据 Model 成员名找出表中字段名称，比如 AreaCode 的段名为 area_code
public class CustomColumnNameResolver : IColumnNameResolver
{
    public string Resolve(DubonProperty info)
    {
        var re = new Regex(@"([A-Z])([A-Z][a-z])|([a-z0-9])([A-Z])");
        return re.Replace(info.Name, @"$1$3_$2$4").ToLower();
    }
}