Change DB Context connection string dynamically in ASP.Net Core

manvendra45
 
on Jun 20, 2022 06:06 AM
Sample_425960.zip
6133 Views

Hello All,

How to Create DB context connection string dynamically in ASP.Net Core.

I am new in asp core entity framework, my requirement is i have two type of database one is master and another is multiple TRAN database and each database details we keep in table (location table) in master (some other name db name) Database, so i have created one fixed DB context with connection string in appsetting. 

Once bind the location table from first DB context i want to bind DB another DB context with new connection string. So that TRAN Model will intact only dynamically created DB connection string.

Please help me here or share any sample code that would be great 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
manvendra45
 
on Jun 29, 2022 06:43 AM

Hi Dharmender,

Below code works for me 

I have created parameterized DBcontext with Dbconnectionstring Parameter, in this way we can pass connection string before binding Customer Data.

DBcontext  class  for customer 

public class CustomerDbContext: DbContext
{
    public CustomerDbContext(string databaseConnection)
      : base()
    {
        ConnectionString = databaseConnection.ToString();
    }

    public string ConnectionString { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseLazyLoadingProxies(true).UseSqlServer(ConnectionString);
    }
    public DbSet<Customer> Customer { get; set; }

get Method in Repository class 

private CustomerDbContext _contextFactory;
public List<Customer> GetCustomer(string databaseName)
{
    _contextFactory = new CustomerDbContext(databaseName); //first set the connection string 
    return _contextFactory.Customer.Select(p => this.mapper.Map<CustomerResponse>(p)).ToList(); //Bind data from provided server
}

 

dharmendr
 
on Jul 02, 2022 04:24 AM
on Jul 02, 2022 04:25 AM

Hi manvendra45,

Create DBContext with parameterized connection string.

So you can pass connection string before retrieving the data from DBContext.

Check the example.

Model

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string Country { get; set; }
}

DBContext

using Microsoft.EntityFrameworkCore;

public class CustomerDbContext : DbContext
{
    public CustomerDbContext(string databaseConnection) 
        : base()
    {           
        ConnectionString = databaseConnection;
    }

    public string ConnectionString { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(ConnectionString);
    }

    public DbSet<Customer> Customers { get; set; }
}

Namespaces

using System.Collections.Generic;
using Microsoft.Extensions.Configuration;

Controller

public class HomeController : Controller
{
    private IConfiguration Configuration;

    public HomeController(IConfiguration _configuration)
    {
        Configuration = _configuration;
    }

    public IActionResult Index()
    {
        return View();
    }

    [HttpPost]
    public IActionResult Index(string server)
    {
        string constr = this.Configuration.GetConnectionString(string.Format("MyConn{0}", server));
        return View(this.GetCustomer(constr));
    }

    public List<Customer> GetCustomer(string connectionString)
    {
        CustomerDbContext _contextFactory = new CustomerDbContext(connectionString);
        return _contextFactory.Customers.ToList();
    }
}

View

@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers
@model IEnumerable<Dynamic_Connection_Core_MVC.Models.Customer>

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <form method="post" asp-controller="Home" asp-action="Index">
        <select name="server">
            <option value="">Select</option>
            <option value="Mumbai">Mumbai</option>
            <option value="Delhi">Delhi</option>
        </select>
        <input type="submit" value="Get Data" />
    </form>
    @if (Model != null)
    {
        <hr />
        <table id="tblFiles" cellpadding="0" cellspacing="0">
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>Country</th>
            </tr>
            @foreach (var customer in Model)
            {
                <tr>
                    <td>@customer.CustomerId</td>
                    <td>@customer.Name</td>
                    <td>@customer.Country</td>
                </tr>
            }
        </table>
    }
</body>
</html>

Screenshot