数据库 分布式 redis FreeSql使用

目的:

1.方库分表 2.主从分离

3.分布式事务

过程：

官网：指南 | FreeSql 官方文档

1.Startup.cs 添加配置（本地数据库MySql）

ConfigureServices：

Func fsql = r =>

{

IFreeSql fsql = new FreeSql.FreeSqlBuilder()

.UseConnectionString(FreeSql.DataType.MySql, @"Data Source=localhost;Database=testfreesql;User ID=root;Password=123456;pooling=true;port=3306;sslmode=none;CharSet=utf8;")

.UseMonitorCommand(cmd => Console.WriteLine($"Sql：{cmd.CommandText}"))//监听SQL语句

// .UseSlave(@"Data Source = localhost; Database = testfreesql1; User ID = root; Password = 123456; pooling = true; port = 3306; sslmode = none; CharSet = utf8; ", @"Data Source=localhost;Database=testfreesql2;User ID=root;Password=123456;pooling=true;port=3306;sslmode=none;CharSet=utf8;")

.UseAutoSyncStructure(true) //自动同步实体结构到数据库，FreeSql不会扫描程序集，只有CRUD时才会生成表。

.Build();

return fsql;

};

services.AddSingleton(fsql);

Configure：

//在项目启动时，从容器中获取IFreeSql实例，并执行一些操作：同步表，种子数据,FluentAPI等

using (IServiceScope serviceScope = app.ApplicationServices.CreateScope())

{

var fsql = serviceScope.ServiceProvider.GetRequiredService();

fsql.CodeFirst.SyncStructure(typeof(Blog), typeof(User), typeof(AsTableLog));//Topic 为要同步的实体类

}

2.引入组件

 3.创建类（自动生成表）

 4.接口调用

using Db.Entities;

using FreeSql;

using Microsoft.AspNetCore.Http;

using Microsoft.AspNetCore.Mvc;

using Microsoft.Extensions.Caching.Memory;

using Microsoft.Extensions.Logging;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Threading.Tasks;

namespace TestFreeSql.Controllers

{

[Route("api/[controller]/[action]")]

[ApiController]

public class FreeSqlController : ControllerBase

{

//官方文档：https://freesql.net/guide/getting-started.html

//demo作者微信（qq）：842725037

private readonly ILogger _logger;

private readonly IFreeSql _freeSql;

private readonly DbContext _dbContext;

private IMemoryCache _cache;

public FreeSqlController(ILogger logger, IFreeSql freeSql, IMemoryCache cache)

{

_logger = logger;

_freeSql = freeSql;

_dbContext = freeSql.CreateDbContext();

_cache = cache;

}

///

///

[HttpGet]

public async Task TestFreeSqlBase()

{

//插入单一数据

var blog = new Blog() { Rating = 1, Url = DateTime.Now.ToString() };

var save =await _freeSql.Insert(blog).ExecuteAffrowsAsync();

//查询

var select1 =await _freeSql.Select().Where(x => x.BlogId == 1).ToListAsync();

var select2 = _freeSql.Select().ToList();

//分页查询

var select4 = _freeSql.Select()

.Where(a => a.BlogId > 1);

var sql = select4.ToSql();

var total = await select4.CountAsync();

var list = await select4.Page(1, 20).ToListAsync();

//修改

var select3 =await _freeSql.Select().FirstAsync();

select3.Url = DateTime.Now.ToString();

var save2 = await _freeSql.InsertOrUpdate().SetSource(select3).ExecuteAffrowsAsync();

//删除

var delete =await _freeSql.Delete().Where(x => x.Url == blog.Url).ExecuteAffrowsAsync();

return "123";

}

///

///

[HttpGet]

public async Task TestFreeSqlDbContext1()

{

//工作单元

var ctx = _dbContext;

var blog = new Blog() { Rating = 1, Url = DateTime.Now.ToString() };

ctx.Set().Add(blog);

var user = new User() { Name = DateTime.Now.ToString(), Age=1 };

ctx.Set().Add(user);

var save=await ctx.SaveChangesAsync();

return "123";

}

///

///

[HttpGet]

public async Task TestFreeSqlDbContext2()

{

//工作单元

var ctx = _freeSql.CreateDbContext();

var blog = new Blog() { Rating = 1, Url = DateTime.Now.ToString() };

ctx.Set().Add(blog);

var user = new User() { Name = DateTime.Now.ToString(), Age = 1 };

ctx.Set().Add(user);

var save = await ctx.SaveChangesAsync();

return "123";

}

///

///

[HttpGet]

public async Task TestFreeSqlReadWrite()

{

//文档：https://freesql.net/guide/read-write-splitting.html

var select2 = _freeSql.Select().Where(x=>x.BlogId>0).ToList();//读取从库

//插入单一数据

var blog = new Blog() { Rating = 1, Url = DateTime.Now.ToString() };

var saveSql =await _freeSql.Insert(blog).ExecuteAffrowsAsync();//写入主库

var select3 = _freeSql.Select().ToList();//读取从库

var select4 = _freeSql.Select().Master().ToList();//读取主库

return "123";

}

///

///

[HttpGet]

public async Task TestFreeSqlSeparate()

{

//文档：https://github.com/dotnetcore/FreeSql/discussions/1066

//插入单一数据

var asTableLog = new AsTableLog() { msg=DateTime.Now.ToString(), createtime=DateTime.Now};

var saveSql =await _freeSql.Insert(asTableLog).ExecuteAffrowsAsync();

//插入单一数据

var asTableLog2 = new AsTableLog() { msg = DateTime.Now.ToString(), createtime = DateTime.Now.AddDays(10) };

var saveSql2 =await _freeSql.Insert(asTableLog2).ExecuteAffrowsAsync();

//查询

var select = _freeSql.Select();

//.Where(a => a.createtime.Between(DateTime.Parse("2022-3-1"), DateTime.Parse("2022-5-1")));

var sql = select.ToSql();

var list = select.ToList();

return "123";

}

}

}

5.项目地址：

https://github.com/zhihuixitong/TestFreeSql

总结：

1.使用上感觉一般

2.和EF相比唯一有用的分表使用，分库不支持

3.分布式事务看着复杂没有使用

4.读写分离还好，就是要自己实现数据同步

建议：

1.分库分表完善（分库支持）

2.读写分离完善（自动通过配置自己同步数据）

3.分布式事务使用简单化

4.其他基本功能少花点精力优化，重点优化其他组件没有的功能

推荐文章

 您阅读本篇文章共花了： 

大家都在找：

数据库：数据库读写分离

分布式：分布式数据库

redis：redis菜鸟教程