appsettings.json
{ "LogicDelete": false, //是否开启逻辑删除,默认为物理删除 "DatabaseType": "SqlServer", //默认数据库类型,定义详见EFCore.Sharding.DatabaseType "ConnectionName": "BaseDb", //默认连接字符串名 "ConnectionStrings": { //SQLServer "BaseDb": "Data Source=.;Initial Catalog=Colder.Admin.AntdVue;Integrated Security=True;Pooling=true;" //PostgreSQL //"BaseDb": "SERVER=localhost;PORT=5432;DATABASE=Colder.Admin.AntdVue;USER ID=postgres;PASSWORD=postgres" //MySQl //"BaseDb": "server=127.0.0.1;user id=root;password=root;persistsecurityinfo=True;database=Colder.Admin.AntdVue;SslMode=none" //Oracle //"BaseDb": "Data Source=127.0.0.1/ORCL;User ID=COLDER.ADMIN.ANTDVUE;Password=123456;Connect Timeout=3" } }普通的使用方式
ShardingConfig.Init(config => { config.AddAbsDb(DatabaseType.SQLite) .AddPhysicDb(ReadWriteType.Read | ReadWriteType.Write, "DataSource=db.db") .AddPhysicDbGroup() .SetHashModShardingRule<Base_UnitTest>(nameof(Base_UnitTest.Id), 3); }); using(IShardingDbAccessor _db = DbFactory.GetShardingDbAccessor()) { Base_UnitTest _newData = new Base_UnitTest { Id = Guid.NewGuid().ToString(), UserId = "Admin", UserName = "超级管理员", Age = 22 }; List<Base_UnitTest> _insertList = new List<Base_UnitTest> { new Base_UnitTest { Id = Guid.NewGuid().ToString(), UserId = "Admin1", UserName = "超级管理员1", Age = 22 }, new Base_UnitTest { Id = Guid.NewGuid().ToString(), UserId = "Admin2", UserName = "超级管理员2", Age = 22 } }; //添加单条数据 _db.Insert(_newData); //添加多条数据 _db.Insert(_insertList); //清空表 _db.DeleteAll<Base_UnitTest>(); //删除单条数据 _db.Delete(_newData); //删除多条数据 _db.Delete(_insertList); //删除指定数据 _db.Delete<Base_UnitTest>(x => x.UserId == "Admin2"); //更新单条数据 _db.Update(_newData); //更新多条数据 _db.Update(_insertList); //更新单条数据指定属性 _db.UpdateAny(_newData, new List<string> { "UserName", "Age" }); //更新多条数据指定属性 _db.UpdateAny(_insertList, new List<string> { "UserName", "Age" }); //更新指定条件数据 _db.UpdateWhere<Base_UnitTest>(x => x.UserId == "Admin", x => { x.UserId = "Admin2"; }); //GetList获取表的所有数据 var list=_db.GetList<Base_UnitTest>(); //GetIQPagination获取分页后的数据 var list=_db.GetIShardingQueryable<Base_UnitTest>().GetPagination(pagination); //Max var max=_db.GetIShardingQueryable<Base_UnitTest>().Max(x => x.Age); //Min var min=_db.GetIShardingQueryable<Base_UnitTest>().Min(x => x.Age); //Average var min=_db.GetIShardingQueryable<Base_UnitTest>().Average(x => x.Age); //Count var min=_db.GetIShardingQueryable<Base_UnitTest>().Count(); //事务,使用方式与普通事务一致 bool succcess = _db.RunTransaction(() => { _db.Insert(_newData); var newData2 = _newData.DeepClone(); _db.Insert(newData2); }).Success; Assert.AreEqual(succcess, false); }按时间分表
var db = DbFactory.GetShardingDbAccessor(); while (true) { try { db.Insert(new Base_UnitTest { Id = Guid.NewGuid().ToString(), Age = 1, UserName = Guid.NewGuid().ToString(), CreateTime = DateTime.Now }); DateTime time = DateTime.Now.AddMinutes(-2); var count = db.GetIShardingQueryable<Base_UnitTest>() .Where(x => x.CreateTime >= time) .Count(); Console.WriteLine($"当前数据量:{count}"); Stopwatch watch = new Stopwatch(); var q = db.GetIQueryable<Base_UnitTest>() .Where(x => x.UserName.Contains("00001C22-8DD2-4D47-B500-407554B099AB")) .OrderByDescending(x => x.Id) .Skip(0) .Take(30); q.ToList(); q.ToSharding().ToList(); watch.Restart(); var list1 = q.ToList(); watch.Stop(); Console.WriteLine($"未分表耗时:{watch.ElapsedMilliseconds}ms");//7800ms watch.Restart(); var list2 = q.ToSharding().ToList(); watch.Stop(); Console.WriteLine($"分表后耗时:{watch.ElapsedMilliseconds}ms");//2400ms } catch (Exception ex) { Console.WriteLine(ex.Message); } Thread.Sleep(50); }备注
/// <summary> /// 单元测试表 /// </summary> [Table("Base_UnitTest")] [Index(false, nameof(CreateTime))]//建表自动创建索引 [Index(false, nameof(Age))]//建表自动创建索引 [Keys(nameof(Id), nameof(UserName))]//自动建表时会自动创建主键(多主键支持) public class Base_UnitTest { /// <summary> /// 代理主键 /// </summary> [Key, StringLength(50)] public String Id { get; set; } /// <summary> /// 创建时间 /// </summary> public DateTime CreateTime { get; set; } /// <summary> /// 用户名 /// </summary> public String UserName { get; set; } /// <summary> /// Age /// </summary> public Int32? Age { get; set; } }多数据库插入
class Program { static void Main(string[] args) { Host.CreateDefaultBuilder(args) .ConfigureLogging(config => { }) .ConfigureServices((host, services) => { services.AddHostedService<DbTest>(); services.UseEFCoreSharding(config => { //单表 config.UseDatabase(Config.CONSTRING1, DatabaseType.SqlServer); //使用多个数据库 config.UseDatabase<IMyDbAccessor>(Config.CONSTRING1, DatabaseType.SqlServer); DateTime startTime = DateTime.Now.AddMinutes(-5); DateTime endTime = DateTime.MaxValue; //分表 config.AddAbsDb(DatabaseType.SqlServer)//添加抽象数据库 .AddPhysicDbGroup()//添加物理数据库组 .AddPhysicDb(ReadWriteType.Read | ReadWriteType.Write, Config.CONSTRING1)//添加物理数据库1 .SetDateShardingRule<Base_UnitTest>(nameof(Base_UnitTest.CreateTime))//设置分表规则 .AutoExpandByDate<Base_UnitTest>(//设置为按时间自动分表 ExpandByDateMode.PerMinute, (startTime, endTime, ShardingConfig.DefaultDbGourpName) ); }); }) .Build() .Run(); } } public interface IMyDbAccessor : IDbAccessor { } class DbTest : BackgroundService { readonly IServiceProvider _serviceProvider; readonly ILogger _logger; public DbTest(IServiceProvider serviceProvider, ILogger<DbTest> logger) { _serviceProvider = serviceProvider; _logger = logger; } protected override async Task ExecuteAsync(CancellationToken stoppingToken) { Task.Factory.StartNew(async () => { while (true) { try { using (var scop = _serviceProvider.CreateScope()) { //单表 var db = scop.ServiceProvider.GetService<IMyDbAccessor>(); List<Base_UnitTest> insertList = new List<Base_UnitTest>(); for (int i = 0; i < 100; i++) { insertList.Add(new Base_UnitTest { Id = Guid.NewGuid().ToString(), Age = i, CreateTime = DateTime.Now, UserName = Guid.NewGuid().ToString() }); } var single = new Base_UnitTest { Id = Guid.NewGuid().ToString(), Age = 100, CreateTime = DateTime.Now, UserName = Guid.NewGuid().ToString() }; await db.InsertAsync(single); await db.InsertAsync(insertList); int count = await db.GetIQueryable<Base_UnitTest>().CountAsync(); _logger.LogInformation("单表插入数据成功 当前数据量:{Count}", count); //分表 var shardingDb = scop.ServiceProvider.GetService<IShardingDbAccessor>(); await shardingDb.InsertAsync(single); await shardingDb.InsertAsync(insertList); count = await shardingDb.GetIShardingQueryable<Base_UnitTest>().CountAsync(); _logger.LogInformation("分表插入数据成功 当前数据量:{Count}", count); } } catch (Exception ex) { _logger.LogError(ex, ""); } await Task.Delay(2000); } }, TaskCreationOptions.LongRunning); await Task.CompletedTask; } }webapi的使用
[ApiController] [Route("[controller]")] public class TestController : ControllerBase { readonly IShardingDbAccessor _shardingDbAccessor; public TestController(IShardingDbAccessor shardingDbAccessor) { _shardingDbAccessor = shardingDbAccessor; } [HttpGet] public async Task<string> Get() { List<Base_UnitTest> insertList = new List<Base_UnitTest>(); for (int i = 0; i < 100; i++) { insertList.Add(new Base_UnitTest { Id = Guid.NewGuid().ToString(), Age = i, CreateTime = DateTime.Now, UserName = Guid.NewGuid().ToString() }); } await _shardingDbAccessor.InsertAsync(insertList); return "成功"; } }备注：非分表用IDbAccessor，分表用IShardingDbAccessor
