defining two different databases in startup.cs/appsettings.json file that resides on different servers

Question

I have two separate databases- Database1 and database2. Database1 is on a seperate server and databse2 is on a different server. I need to insert the data from database1 to Datbase2 in my C# coding. This is how my startup file looks like :

  public void ConfigureServices(IServiceCollection services)
        {

            services.AddDbContext<AckPackage.Data.AckContext>(options =>
                options.UseSqlServer(
                    Configuration.GetConnectionString(DefaultConnection)));
        }

this is the connection string of the Database1 in appsettings.json file:

{

  "ConnectionStrings": {

    "DefaultConnection": "Server=TestServer;TrustServerCertificate=True;Initial Catalog=Database1;uid=test;pwd=test"
  }}

How can I define the connection string for database2 in startup.cs file or access the database2 in the controller in order to insert the data from one database to another one. this is how my controller constructor looks like

private readonly IEmployeeService _employeeService;
        public readonly IConfiguration _configuration;
        public readonly ILogger _logger;
        private readonly IHttpContextAccessor _contextAccessor;
        private readonly IWebHostEnvironment _environment;
        public EmployeeController(IEmployeeService employeeService,
          IConfiguration configuration, ILogger<EmployeeController> logger, IHttpContextAccessor contextAccessor, IWebHostEnvironment environment)
        {
            _employeeService = employeeService;
            _configuration = configuration;
            _logger = logger;
            _contextAccessor = contextAccessor;
            _environment = environment;
        }

I just need to insert the data from database1 tableA to Database2 tableB. Any help will be appreciated.

Are you want to just move your records between these two, or its a part of your business logic ? — RezaNoei, Apr 06 '23 at 22:23

score 0 · Accepted Answer · answered Apr 06 '23 at 22:51

Its a kind of problem that makes a lots of side effects.

First, the thing you should know is that you cant register to instance of the same DbContext with a different ConnectionString.

Read this to find the reason.

So at least we need to copy and paste your DbContext and renaming it to deceive ASP.NET DI system.

After all you will have multiple DbContext with a shared schema. try to read this article to learn about Migrations and other stuffs when you have multiple DbContext.

Try it. The bad news is that you are forced to Update your migrations twice for these two.

There is another way of doing this. Avoid Dependency Injection:

Do as this article says. Its not a good thing because you ignore DI and you should manualy build DbConext wherever you needed. But it works.

There is a little bit things on doing migrations that you should take care of them on the Startup.cs with the same process of creating of it.

defining two different databases in startup.cs/appsettings.json file that resides on different servers

1 Answers1