代码之家 › 专栏 › 技术社区 › Aleksander Chelpski

如何根据公司id选择数据库?

asp.net-web-api asp.net-core entity-framework asp.net c#

Aleksander Chelpski · 技术社区 · 1 年前

我有两个完全相同的数据库(在功能中我可以拥有更多)。第一个数据库有X公司的数据,第二个数据库有Y公司的数据。有什么解决方案吗?我如何根据公司id选择要使用哪个数据库? 因为 现在我必须创建与数据库一样多的DBContext类,尽管每个数据库都是相同的 。还有别的办法吗?

// the first DbContext for company X:
    public class MyDatabase1 : DbContext, IMyDatabase
    {
            public MyDatabase1 (DbContextOptions<MyDatabase1> options)
                : base(options)
            {
            }
    
        public DbSet<Post> Posts { get; set; } = default!;
    }
    
// the second DbContext for company Y:
    public class MyDatabase2 : DbContext, IMyDatabase
    {
            public MyDatabase2 (DbContextOptions<MyDatabase2> options)
                : base(options)
            {
            }
    
        public DbSet<Post> Posts { get; set; } = default!;
    }
    
    public interface IMyDatabase
    {
        DbSet<Post> Posts { get; set; }
    }
    
    public class Post
    {
        public int PostId { get; set; }
        public string Title { get; set; }
    }

然后我必须在Program.cs中注册它:

builder.Services.AddDbContext<MyDatabase1>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("MyDatabase1")));

builder.Services.AddDbContext<MyDatabase2>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("MyDatabase2")));

我是这样使用它的:

public class DatabaseStrategy
{
    private readonly MyDatabase1 _context1;
    private readonly MyDatabase2 _context2;

    public DatabaseStrategy(MyDatabase1 context1, MyDatabase2 context2)
    {
        _context1 = context1;
        _context2 = context2;
    }

    public IMyDatabase GetDatabase(int companyId)
    {
        if (companyId == 1)
        { 
            return _context1; 
        }
        else if (companyId == 2)
        {
            return _context2;
        }
        else
        {
            throw new Exception();
        }
    }
}

builder.Services.AddScoped<DatabaseStrategy>();

[ApiController]
[Route("[controller]")]
public class PostsController : ControllerBase
{
    private readonly DatabaseStrategy _databaseStrategy;

    public PostsController(DatabaseStrategy databaseStrategy)
    {
        _databaseStrategy = databaseStrategy;                
    }

    public ActionResult<List<Post>> Get()
    {
        var companyId = 1;
        var databaseStrategy = _databaseStrategy.GetDatabase(companyId);

        var result = databaseStrategy.Posts.ToList();
        return result;
    }
}

1 回复 | 直到 1 年前

SELA 1 年前

您需要创建单个 DbContext.cs 类如下:

public class MyDatabase : DbContext, IMyDatabase
{
    public MyDatabase(DbContextOptions<MyDatabase> options)
        : base(options)
    {
    }

    public DbSet<Post> Posts { get; set; } = default!;
}

在appsettings.json中指定两个连接字符串,如下所示:

{
    "ConnectionStrings": {
        "Company1": "Server=server1;Database=company1_db;User Id=user;Password=password;",
        "Company2": "Server=server2;Database=company2_db;User Id=user;Password=password;"
    }
}

之后,创建DBContextFactory.cs类,该类依赖于公司id,如下所示:

public class DbContextFactory
{
    private readonly IConfiguration _configuration;
    private readonly IServiceProvider _serviceProvider;

    public DbContextFactory(IConfiguration configuration, IServiceProvider serviceProvider)
    {
        _configuration = configuration;
        _serviceProvider = serviceProvider;
    }

    public MyDatabase CreateDbContext(int companyId)
    {
        var optionsBuilder = new DbContextOptionsBuilder<MyDatabase>();
        var connectionString = GetConnectionString(companyId);
        optionsBuilder.UseSqlServer(connectionString);
        return new MyDatabase(optionsBuilder.Options);
    }

    private string GetConnectionString(int companyId)
    {
        return companyId switch
        {
            1 => _configuration.GetConnectionString("Company1"),
            2 => _configuration.GetConnectionString("Company2"),
            _ => throw new Exception("Invalid company ID")
        };
    }
}

别忘了注册 DbContextFactory.cs 在你的程序中上课。cs如下

builder.Services.AddSingleton<DbContextFactory>();

现在您可以修改 DatabaseStrategy.cs 类以动态获取 DbContext 通过如下方式传递公司ID:

public class DatabaseStrategy
{
    private readonly DbContextFactory _dbContextFactory;

    public DatabaseStrategy(DbContextFactory dbContextFactory)
    {
        _dbContextFactory = dbContextFactory;
    }

    public IMyDatabase GetDatabase(int companyId)
    {
        return _dbContextFactory.CreateDbContext(companyId);
    }
}

最后,在控制器级别公开要传递的端点 List<Posts> 通过控制器

[ApiController]
[Route("[controller]")]
public class PostsController : ControllerBase
{
    private readonly DatabaseStrategy _databaseStrategy;

    public PostsController(DatabaseStrategy databaseStrategy)
    {
        _databaseStrategy = databaseStrategy;                
    }

    [HttpGet]
    public ActionResult<List<Post>> Get(int companyId)
    {
        var database = _databaseStrategy.GetDatabase(companyId);
        var result = database.Posts.ToList();
        return result;
    }
}

注意:暴露你的 数据库策略.cs 直接在您的控制器中。因此,最好在 数据库策略.cs 以及您的API控制器。