环境:SQLServer 2016,ASP.NET Core 2.1,Entity Framework Core 2.1.1
在【SQL Server 复制 - 搭建一个读写分离的数据库环境】的时候,已经介绍了怎么利用SQLServer复制搭建一个读写分离的的数据库环境。
搭建好的环境是这样的:一个主库负责增删改操作,三个从库负责查询业务。
本文主要介绍读写分离在代码里面怎么实现。
(百度)
就是在appsettings.json配置数据库链接的时候,把读写的链接串分开配置,在业务层做业务操作的时候,根据读写不同的业务切换不同的链接。
"ConnectionStrings": {
"WriteConnection": "Data Source=.;Initial Catalog=DB_Test;User ID=sa;Password=123456;",
"ReadConnectionList": [
"Data Source=.;Initial Catalog=DB_Test_Copy1;User ID=sa;Password=123456;",
"Data Source=.;Initial Catalog=DB_Test_Copy2;User ID=sa;Password=123456;",
"Data Source=.;Initial Catalog=DB_Test_Copy3;User ID=sa;Password=123456;"
]
}
下面来看具体的是做的。
代码框架大概如下,就是个三层这样子
需要添加 Microsoft.EntityFrameworkCore.SqlServer
一、封装数据访问层
1、定义一个数据库操作类型的枚举,用来控制拿那个数据库链接去实例化DbContext。
namespace Model
{
public enum DbActionType
{
Write,
Read
}
}
2、定义一个数据库链接串的类,用来存appsettings.json配置数据库链接。
using System.Collections.Generic;
namespace BaseClassLibrary
{
public class DBConnectionOption
{
public string WriteConnection { get; set; }
public List<string> ReadConnectionList { get; set; }
}
}
3、创建业务类。
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace Model
{
[Table("T_User")]
public partial class User
{
public int Id { get; set; }
[StringLength(50)]
public string Account { get; set; }
[StringLength(100)]
public string FullName { get; set; }
public Byte? Sex { get; set; }
public Byte? Age { get; set; }
[StringLength(255)]
public string Mail { get; set; }
public DateTime? CreateDate { get; set; }
}
}
4、自定义数据库上下文对象,要做的很简单 设置数据库链接。
using Microsoft.EntityFrameworkCore;
namespace Model
{
public partial class CustomDBContext : DbContext
{
private string ConnectionString { get; }
public CustomDBContext(string connectionString)
{
this.ConnectionString = connectionString;
}
public virtual DbSet<User> User { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(this.ConnectionString);
}
}
}
5、定义创建DbContext的工厂类(包括接口和实现类)。
接口:定义创建数据库上下文实例的方法
namespace Model
{
public interface IDbContextFactory
{
CustomDBContext CreateContext(DbActionType dbActionType);
}
}
实现:Ioc通过构造函数,把配置文件的数据库链接传进来,CreateContext()方法根据传进来的数据库操作类型拿不同的数据库链接创建数据库上下文实例。
using BaseClassLibrary;
using Microsoft.Extensions.Options;
using System;
namespace Model
{
public class DbContextFactory : IDbContextFactory
{
private DBConnectionOption dbConnectionOption = null;
public DbContextFactory(IOptionsMonitor<DBConnectionOption> options)
{
dbConnectionOption = options.CurrentValue;
}
public CustomDBContext CreateContext(DbActionType dbActionType)
{
string connectionString = string.Empty;
switch (dbActionType)
{
case DbActionType.Write:
connectionString = this.dbConnectionOption.WriteConnection;
break;
case DbActionType.Read:
int num = new Random().Next(0, this.dbConnectionOption.ReadConnectionList.Count);
connectionString = this.dbConnectionOption.ReadConnectionList[num];
break;
default:
break;
}
return new CustomDBContext(connectionString);
}
}
}
二、业务逻辑层
1、业务模块接口定义
using Model;
using System.Collections.Generic;
namespace IService
{
public interface IUserService
{
User Get(int id);
List<User> GetUsers();
User Inser(User user);
}
}
2、实现业务逻辑。根据读写业务分别传入数据库操作类型(DbActionType),创建数据库上下文实例,执行业务操作。
using IService;
using Model;
using System.Collections.Generic;
using System.Linq;
namespace Service
{
public class UserService : IUserService
{
IDbContextFactory DbContextFactory { get; }
public UserService(IDbContextFactory dbContextFactory)
{
this.DbContextFactory = dbContextFactory;
}
public User Get(int id)
{
var dbContext = DbContextFactory.CreateContext(DbActionType.Read);
return dbContext.Set<User>().Find(id);
}
public List<User> GetUsers()
{
var dbContext = DbContextFactory.CreateContext(DbActionType.Read);
return dbContext.Set<User>().OrderBy(u => u.CreateDate).ToList();
}
public User Inser(User user)
{
var dbContext = DbContextFactory.CreateContext(DbActionType.Write);
User u = dbContext.Set<User>().Add(user).Entity;
dbContext.SaveChanges();
return u;
}
}
}
三、应用程序配置和添加服务
1、配置文件添加数据库链接串ConnectionStrings。
{
"Logging": {
"LogLevel": {
"Default": "Warning"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"WriteConnection": "Data Source=.;Initial Catalog=DB_Test;User ID=sa;Password=19910719;",
"ReadConnectionList": [
"Data Source=.;Initial Catalog=DB_Test_Copy1;User ID=sa;Password=19910719;",
"Data Source=.;Initial Catalog=DB_Test_Copy2;User ID=sa;Password=19910719;",
"Data Source=.;Initial Catalog=DB_Test_Copy3;User ID=sa;Password=19910719;"
]
}
}
2、在Startup.cs的ConfigureServices()下面添加服务
using BaseClassLibrary;
using IService;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Model;
using Service;
namespace WebUI
{
public class Startup
{
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.AddTransient<IUserService, UserService>();
//数据库工厂类注册
services.AddTransient<IDbContextFactory, DbContextFactory>();
//数据库链接
services.Configure<DBConnectionOption>(Configuration.GetSection("ConnectionStrings"));
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
app.UseMvc(routes =>
{
routes.MapRoute(
name: "default",
template: "{controller=Home}/{action=Index}");
});
}
}
}
3、控制器调用服务
using IService;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Model;
using System;
namespace WebUI.Controllers
{
public class HomeController : Controller
{
private IConfiguration Configuration;
private IUserService UserService;
public HomeController(IConfiguration configuration, IUserService userService)
{
Configuration = configuration;
UserService = userService;
}
public IActionResult Index()
{
User user = UserService.Get(2);
return View();
}
[HttpGet]
public JsonResult All()
{
var list = UserService.GetUsers();
return Json(list);
}
[HttpGet]
public JsonResult Insert()
{
User model = new User();
model.Account = "TEST" + DateTime.Now.ToString("yyyyMMddhhmmss");
model.FullName = "测试" + DateTime.Now.ToString("hhmmss");
model.Age = 25;
model.Sex = 1;
model.Mail = "test@163.com";
model.CreateDate = DateTime.Now;
User user = UserService.Inser(model);
return Json(user);
}
}
}
数据库读写分离完成。。。
读写分离的demo在这里,有兴趣的朋友可以下载来玩玩