开发环境:Visual studio 项目模板:Asp.Net Core Web Api 框架:.Net5
1.创建项目
2.安装ShardingCore+MySql
使用Nuget管理包工具安装即可,
ShardingCore版本:6.8.0.9 (ShardingCore)
MySql版本:5.0.0(Pomelo.EntityFrameworkCore.MySql)
3.添加实体类
订单状态枚举类
public enum OrderStatusEnum
{
NoPay=1,
Paying=2,
Payed=3,
PayFail=4
}
订单实体类
public class Order
{
public string Id { get; set; }
public string Payer { get; set; }
public long Money { get; set; }
public string Area { get; set; }
public OrderStatusEnum OrderStatus { get; set; }
public DateTime CreationTime { get; set; }
}
系统用户实体类
public class SysUser
{
public string Id { get; set; }
public string Name { get; set; }
public string Area { get; set; }
}
4.添加数据库上下文
using Microsoft.EntityFrameworkCore;
using ShardingCore.Sharding;
namespace ShardingCoreDatabase
{
public class MyDbContext:AbstractShardingDbContext
{
public MyDbContext(DbContextOptions<MyDbContext> options):base(options)
{
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Order>(entity=>
{
entity.HasKey(o => o.Id);
entity.Property(o => o.Id).IsRequired().IsUnicode(false).HasMaxLength(50);
entity.Property(o => o.Payer).IsRequired().IsUnicode(false).HasMaxLength(50);
entity.Property(o => o.Area).IsRequired().IsUnicode(false).HasMaxLength(50);
entity.Property(o => o.OrderStatus).HasConversion<int>();
entity.ToTable(nameof(Order));
});
modelBuilder.Entity<SysUser>(entity =>
{
entity.HasKey(o => o.Id);
entity.Property(o => o.Id).IsRequired().IsUnicode(false).HasMaxLength(50);
entity.Property(o => o.Name).IsRequired().IsUnicode(false).HasMaxLength(50);
entity.Property(o => o.Area).IsRequired().IsUnicode(false).HasMaxLength(50);
entity.ToTable(nameof(SysUser));
});
}
}
}
5.添加数据库路由配置类
订单数据库路由配置类
using ShardingCore.Core.EntityMetadatas;
using ShardingCore.Core.VirtualRoutes;
using ShardingCore.Core.VirtualRoutes.DataSourceRoutes.Abstractions;
using System;
using System.Collections.Generic;
using System.Linq;
namespace ShardingCoreDatabase
{
public class OrderVirtualDataSourceRoute : AbstractShardingOperatorVirtualDataSourceRoute<Order, string>
{
private readonly List<string> _dataSource = new List<string> { "A", "B", "C" };
protected virtual string ConvertToShardingKey(object shardingKey)
{
return shardingKey?.ToString() ?? string.Empty;
}
public override bool AddDataSourceName(string dataSourceName)
{
if (_dataSource.Any(o => o == dataSourceName))
return false;
_dataSource.Add(dataSourceName);
return true;
}
public override void Configure(EntityMetadataDataSourceBuilder<Order> builder)
{
builder.ShardingProperty(o => o.Area);
}
public override List<string> GetAllDataSourceNames()
{
return _dataSource;
}
public override Func<string, bool> GetRouteToFilter(string shardingKey, ShardingOperatorEnum shardingOperator)
{
var t = ShardingKeyToDataSourceName(shardingKey);
switch (shardingOperator)
{
case ShardingOperatorEnum.Equal: return tail => tail == t;
default:
{
return tail => true;
}
}
}
public override string ShardingKeyToDataSourceName(object shardingKey)
{
return ConvertToShardingKey(shardingKey);
}
}
}
用户数据库路由配置类
using ShardingCore.Core.EntityMetadatas;
using ShardingCore.Core.VirtualRoutes;
using ShardingCore.Core.VirtualRoutes.DataSourceRoutes.Abstractions;
using System;
using System.Collections.Generic;
using System.Linq;
namespace ShardingCoreDatabase
{
public class SysUserVirtualDataSourceRoute : AbstractShardingOperatorVirtualDataSourceRoute<SysUser, string>
{
private readonly List<string> _dataSources = new List<string>()
{
"A", "B", "C"
};
protected virtual string ConvertToShardingKey(object shardingKey)
{
return shardingKey?.ToString() ?? string.Empty;
}
//我们设置区域就是数据库
public override string ShardingKeyToDataSourceName(object shardingKey)
{
return ConvertToShardingKey(shardingKey);
}
public override List<string> GetAllDataSourceNames()
{
return _dataSources;
}
public override bool AddDataSourceName(string dataSourceName)
{
if (_dataSources.Any(o => o == dataSourceName))
return false;
_dataSources.Add(dataSourceName);
return true;
}
public override Func<string, bool> GetRouteToFilter(string shardingKey, ShardingOperatorEnum shardingOperator)
{
var t = ShardingKeyToDataSourceName(shardingKey);
switch (shardingOperator)
{
case ShardingOperatorEnum.Equal: return tail => tail == t;
default:
{
return tail => true;
}
}
}
public override void Configure(EntityMetadataDataSourceBuilder<SysUser> builder)
{
builder.ShardingProperty(o => o.Area);
}
}
}
6.启动类配置分库服务
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.OpenApi.Models;
using ShardingCore;
using System;
using System.Collections.Generic;
namespace ShardingCoreDatabase
{
public class Startup
{
public static readonly ILoggerFactory efLogger = LoggerFactory.Create(builder => {
builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information).AddConsole();
});
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public IConfiguration Configuration { get; }
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddControllers();
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new OpenApiInfo { Title = "ShardingCoreDatabase", Version = "v1" });
});
//分库路由配置,数据源配置
services.AddShardingDbContext<MyDbContext>().UseRouteConfig(o =>
{
o.AddShardingDataSourceRoute<OrderVirtualDataSourceRoute>();
o.AddShardingDataSourceRoute<SysUserVirtualDataSourceRoute>();
}).UseConfig(op=>
{
op.ThrowIfQueryRouteNotMatch = false;
op.UseShardingQuery((conStr, builder) =>
{
builder.UseMySql(conStr, new MySqlServerVersion(new Version())).UseLoggerFactory(efLogger);
});
op.UseShardingTransaction((connection, builder) =>
{
builder.UseMySql(connection, new MySqlServerVersion(new Version())).UseLoggerFactory(efLogger);
});
op.AddDefaultDataSource("A", "Server=192.168.1.xx;port=3306;user=root;password=xxxxxxx;database=yzwDb_A;");
op.AddExtraDataSource(sp => {
return new Dictionary<string, string>()
{
{
"B",
"Server=192.168.1.xx;port=3306;user=root;password=xxxxxx;database=yzwDb_B;"
},
{
"C",
"Server=192.168.1.xx;port=3306;user=root;password=xxxxxx;database=yzwDb_C;"
}
};
});
}).AddShardingCore();
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
app.UseSwagger();
app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "ShardingCoreDatabase v1"));
}
using(var scope = app.ApplicationServices.CreateScope())
{
var myDbContext = scope.ServiceProvider.GetRequiredService<MyDbContext>();
myDbContext.Database.EnsureCreated();
}
app.ApplicationServices.UseAutoTryCompensateTable();
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
app.InitSeed();
}
}
}
7.数据库初始化
新增数据库初始化脚本处理类
using Microsoft.AspNetCore.Builder;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
namespace ShardingCoreDatabase
{
public static class StartupExtension
{
public static void InitSeed(this IApplicationBuilder app)
{
using (var serviceScope = app.ApplicationServices.CreateScope())
{
var myDbContext = serviceScope.ServiceProvider.GetRequiredService<MyDbContext>();
if (!myDbContext.Set<SysUser>().Any())
{
string[] areas = new string[] { "A", "B", "C" };
List<SysUser> users = new List<SysUser>(10);
for (int i = 0; i < 10; i++)
{
var user = new SysUser()
{
Id = i.ToString(),
Name = $"MyName{i}",
Area = areas[i % 3]
};
users.Add(user);
}
List<Order> orders = new List<Order>(300);
var begin = new DateTime(2021, 1, 1, 3, 3, 3);
for (int i = 0; i < 300; i++)
{
var order = new Order()
{
Id = i.ToString(),
Payer = $"{i % 10}",
Money = 100 + new Random().Next(100, 3000),
OrderStatus = (OrderStatusEnum)(i % 4 + 1),
Area = areas[i % 3],
CreationTime = begin.AddDays(i)
};
orders.Add(order);
}
myDbContext.AddRange(users);
myDbContext.AddRange(orders);
myDbContext.SaveChanges();
}
}
}
}
}
8.启动程序
至此,分库功能完成。