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. Both databases resides on seperate servers. Any help will be appreciated.

Answer 1

Hi @Anjali Agarwal

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

It is similar with the database1. To use multiple Database and DbContext in the asp.net core application. In the appsettings.json file, you can add two connection strings (change the connection string to yours):

  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=database1;Trusted_Connection=True;MultipleActiveResultSets=true",
    "SecondDbConnection": "Server=(localdb)\\mssqllocaldb;Database=database2;Trusted_Connection=True;MultipleActiveResultSets=true"
  },

Then, in the startup.cs/program.cs file, you can configure the DbContext like this (change the ApplicationDbContext and SecondDBContext to your DbContext):

services.AddDbContext<ApplicationDbContext>(options =>  options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
services.AddDbContext<SecondDBContext>(options => options.UseSqlServer(Configuration.GetConnectionString("SecondDbConection")));

The DbContext as below:

    public class SecondDBContext:DbContext
    {
        public DbSet<Employee> Employees { get; set; }
        public SecondDBContext(DbContextOptions<SecondDBContext> options)
            : base(options)
        {
        }
    }

Then, in the controller, you can inject the DbContext as below:

    public class HomeController : Controller
    {
        private readonly ILogger<HomeController> _logger; 
        private readonly IPasswordHasher<ApplicationUser> _passwordHasher;

        private readonly ApplicationDbContext _dbContext; //first dbcontext
        private readonly SecondDBContext _secondDbContext; //second dbcontext

        public HomeController(ILogger<HomeController> logger,
            ApplicationDbContext applicationDbContext,
            SecondDBContext secondDBContext)
        {
            _logger = logger;
            _dbContext=applicationDbContext;
            _secondDbContext=secondDBContext; 
            _passwordHasher=passwordHasher;
        }

        public IActionResult Index()
        {
            //query data from the first DbContext,
            //since the primary key is auto insert, when insert the data into the new database, there is no need to set its value.
            var employees = (_dbContext.Employees.Select(v => new Employee()
            {
                Name = v.Name,
                Address = v.Address,
                Date = v.Date,
                Surname = v.Surname,
                Gender = v.Gender
            })).ToList();
            //insert the data into the second Database.
            _secondDbContext.Employees.AddRange(employees);
            _secondDbContext.SaveChanges();


            var vm = new EmployeeViewModel();


            return View();
        }

Note: Since there has multiple DbContext, when enable migration, you have to assign the DbContext using the -context option. For example:

add-migration addseconddb -context SecondDBContext
update-database -context SecondDBContext

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Best regards,
Dillion