.Net 8.0之SQL Server读写分离的配置

1.背景

在实际工作中，会有读写分离的场景。对于该类型场景，我们应该怎么处理了。最先考虑的肯定是配置不同的数据库连接，查询数据的就走查询用的数据库A，增删改数据的就走修改用的数据库B。专业点描述就是先将数据库配置发布订阅模式，实现了1主多从的模式，主数据库一般负责更新数据，从数据库会同步主数据库的数据过来。上面的A就是从数据库服务器，B就是主数据库服务器。

本文介绍在.Net 8.0下，结合EFCore在项目中如何配置Sql Server读写分离。解决思路是在DBContext中去修改数据库连接，在具体使用DBContext查询数据或者新增数据时，指定具体的数据库配置去查询数据。

2.操作

2.1 准备一个web api项目

这是我之前做的demo，我会以它为例子去讲解。

2.2 新增配置

打开配置文件appsettings.json，加入下列配置

"ConnectionStrings": {
  "WriteConnection": "Data Source=127.0.0.1;Initial Catalog=AdvancedCustomerDB_Init;Persist Security Info=True;User ID=sa;Password=*;Encrypt=False;TrustServerCertificate=true;",
  "ReadConnectionList": [
    "Data Source=127.0.0.1;Initial Catalog=AdvancedCustomerDB_Init_1;Persist Security Info=True;User ID=sa;Password=*;Encrypt=False;TrustServerCertificate=true;",
    "Data Source=127.0.0.1;Initial Catalog=AdvancedCustomerDB_Init_2;Persist Security Info=True;User ID=sa;Password=*;Encrypt=False;TrustServerCertificate=true;"
  ]
}

2.3 编写代码

关键类DbContextFactory，专门生成DBContext

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Options;
using SimpleWebApi.Migration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SimpleWebApi.Business.Service.Interface
{
    public class DbContextFactory : IDbContextFactory
    {
        private DBConnectionOption _DBConnection = null;

        private DbContext _DbContext = null;

        public DbContextFactory(IOptionsMonitor<DBConnectionOption>
            optionsMonitor, DbContext dbContext)
        {
            _DBConnection = optionsMonitor.CurrentValue;
            _DbContext = dbContext;
        }

        public DbContext GetDbContext(WriteAndReadEnum writeAndRead)
        {
            switch (writeAndRead)
            {
                case WriteAndReadEnum.Write:
                    ToWrite();
                    break;
                case WriteAndReadEnum.Read:
                    ToRead();
                    break;
                default:
                    break;
            }

            return _DbContext;
        }

        private void ToWrite()
        {
            string conn = _DBConnection.WriteConnection; //主库连接
            this._DbContext = _DbContext.SetConnectionString(conn);
        }


        private void ToRead() 
        {
            string conn = string.Empty;

            int Count = _DBConnection.ReadConnectionList.Count;
            int index=new Random().Next(0, Count);
            conn = _DBConnection.ReadConnectionList[index];

           
            this._DbContext=_DbContext.SetConnectionString(conn);

        }

    }
}

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SimpleWebApi.Business.Service.Interface
{
    public interface IDbContextFactory
    {

        public DbContext GetDbContext(WriteAndReadEnum writeAndRead);

    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SimpleWebApi.Business.Service.Interface
{
    public enum WriteAndReadEnum
    {
        Write, 
        Read 

    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SimpleWebApi.Business.Service.Interface
{
    /// <summary>
    /// 读写数据库的数据库连接
    /// </summary>
    public class DBConnectionOption
    {
        public string WriteConnection { get; set; }   

        public List<string> ReadConnectionList { get; set; }    
    }
}

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SimpleWebApi.Migration
{
    public static class DbContextExtension
    {
        public static DbContext SetConnectionString(this DbContext dbContext,string conn)
        {
            if (dbContext is AdvancedCustomerDbContext)
            {
                AdvancedCustomerDbContext context = (AdvancedCustomerDbContext)dbContext;
                return context.SetConnectionString(conn);
            }
            else
            {
                throw new Exception();
            }


        }

    }
}

2.4 改造原有代码

将BaseService文件，注入IDbContextFactory对象，并在每个方法中，加入数据操作的类型，比如是查询还是更新数据。

对于子类CommodityService等，里面的每个方法中，加入数据操作的类型，比如是查询还是更新数据。相关接口变动了参数的，也需要修改。此处不做具体介绍。

找到AdvancedCustomerDbContext。按照下图修改代码

打开Program.cs，加入下图代码

   #region EFCore支持读写分离
   builder.Services.AddTransient<IDbContextFactory, DbContextFactory>();
   builder.Services.Configure<DBConnectionOption>(builder.Configuration.GetSection("ConnectionStrings"));
  
   #endregion

ApiController代码如下：

using AutoMapper;
using Microsoft.AspNetCore.Mvc;
using SimpleWebApi.Business.Service.Interface;
using SimpleWebApi.Migration.Models;
using System.Linq.Expressions;

namespace SimpleWebApi.Controllers
{
    [ApiController]
    [Route("api/[controller]/[action]")]
    public class ApiController : ControllerBase
    {
        private readonly ILogger<ApiController> _logger;

        private ICommodityService _comService;
        private ICompanyInfoService _companyService;

        private IMapper _mapper;

        public ApiController(ILogger<ApiController> logger, ICommodityService comService, ICompanyInfoService companyService, IMapper mapper)
        {
            _logger = logger;
            _comService = comService;
            _companyService = companyService;
            _mapper = mapper;
        }

        [HttpGet]
        public IEnumerable<CommodityDTO> GetCommodity(int Id,int type=1)
        {
            WriteAndReadEnum typeDB = (WriteAndReadEnum)type;

            Expression<Func<Commodity, bool>> funcWhere = null;
            funcWhere = a => a.Id == Id;
            var commodityList = _comService.Query(funcWhere,typeDB);
            List<CommodityDTO> list = new List<CommodityDTO>();
            _mapper.Map<IQueryable<Commodity>, List<CommodityDTO>>(commodityList, list);

            return list;

        }

        [HttpGet]
        public CompanyInfoDTO GetCompanyInfo(int companyId)
        {
            var company = _companyService.GetCompany(companyId);
            CompanyInfoDTO dto = new CompanyInfoDTO();
            _mapper.Map<CompanyInfo, CompanyInfoDTO>(company, dto);

            return dto;

        }

        [HttpPost]
        public bool AddCommodity(CommodityDTO companyDto)
        {
            Commodity company = new Commodity();
            _mapper.Map<CommodityDTO, Commodity>(companyDto,company);
            var flag = _comService.AddCommodity(company);
            return flag;

        }
    }
}