Change DB Context connection string dynamically in ASP.Net Core

manvendra45
 
on Jun 20, 2022 06:06 AM
Sample_425960.zip
236 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
Results 1 - 5 of 9
dharmendr
 
on Jun 20, 2022 06:09 AM
manvendra45
 
on Jun 20, 2022 06:30 AM

Hello dharmendr thanks lot for quick reply , i have created code to connect DB context for master database , now on the basis of master i want create one more child db context with dynamic Connection string .

for example , i have three record in location table (Master Database Server) 

Delhi , Lucknow , Mumbai , once user select Delhi then child db context 

update connection string and get the data from delhi server .

 

 

dharmendr
 
on Jun 21, 2022 08:27 AM

Hi manvendra45,

You need to add multiple connection string for using multiple database in the appsetting.json.

{
  "ConnectionStrings": {
    "MyConnNorthwind": "Data Source=192.168.0.100\\SQL2019;Initial Catalog=Northwind;UID=sa;PWD=pass@123;",
    "MyConnAjaxSamples": "Data Source=192.168.0.100\\SQL2019;Initial Catalog=AjaxSamples;UID=sa;PWD=pass@123;"
  }
}

Then configure the connection in the Startup class ConfigureServices method.

public Startup(IConfiguration configuration)
{
    Configuration = configuration;
}

public IConfiguration Configuration { get; }

public void ConfigureServices(IServiceCollection services)
{
    services.AddMvc();
    string conStrNorthwind = this.Configuration.GetConnectionString("MyConnNorthwind");
    string conStrAjaxSamples = this.Configuration.GetConnectionString("MyConnAjaxSamples");
    services.AddDbContext<DBCtx>(options => options.UseSqlServer(conStrNorthwind))
        .AddDbContext<DBCtxCustomer>(options => options.UseSqlServer(conStrAjaxSamples));           
}

Then create DBContext for each Model.

Model

Customer

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

Employee

public class Employee
{
    public int EmployeeId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Country { get; set; }
}

DBContext

Employee

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

Customer

public class DBCtxCustomer : DbContext
{
    public DBCtxCustomer(DbContextOptions<DBCtxCustomer> options) : base(options)
    {

    }

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

Use the Context class in the controller.

public class HomeController : Controller
{
    private DBCtx Context { get; }
    private DBCtxCustomer ContextCustomer { get; }
    public HomeController(DBCtx _context, DBCtxCustomer _contextCustomer)
    {
        this.Context = _context;
        this.ContextCustomer = _contextCustomer;
    }

    public IActionResult Index()
    {
        List<SelectListItem> employees = this.Context.Employees
            .Select(x => new SelectListItem
            {
                Text = string.Format("{0} {1}", x.FirstName, x.LastName),
                Value = Convert.ToString(x.EmployeeId)
            }).ToList();

        return View(new SelectList(employees, "Value", "Text"));
    }

    [HttpPost]
    public IActionResult Index(int employeeId)
    {
        ViewData["Customer"] = this.ContextCustomer.Customers.Where(x => x.CustomerId == employeeId).FirstOrDefault();
        List<SelectListItem> employees = this.Context.Employees
            .Select(x => new SelectListItem
            {
                Text = string.Format("{0} {1}", x.FirstName, x.LastName),
                Value = Convert.ToString(x.EmployeeId)
            }).ToList();

        return View(new SelectList(employees, "Value", "Text"));
    }
}

View

@using EF_Core_MVC.Models
@model SelectList
@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <form method="post" asp-action="Index" asp-controller="Home">
        <select id="ddlEmployees" name="EmployeeId" asp-items="@Model">
            <option value="0">Please select</option>
        </select>
        <input type="submit" value="Submit" />
    </form>
    <hr />
    @if (ViewData["Customer"] != null)
    {
        var customer = ViewData["Customer"] as Customer;
        <table cellpadding="0" cellspacing="0">
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>Country</th>
            </tr>
            <tr>
                <td>@customer.CustomerId</td>
                <td>@customer.Name</td>
                <td>@customer.Country</td>
            </tr>
        </table>
    }
</body>
</html>

Screenshot

manvendra45
 
on Jun 22, 2022 12:45 AM
on Jun 22, 2022 12:55 AM

Hi Dharmendr thannks lot and i appreciate your efforts .

So as your code  you are binding Employee details from DBCtx db context and once select any employee from dropdown list customer details are binding from DBCtxCustomer Db context and that is good  .

But in my case ,each employee details has other property related  Server details ( Data source ,DB name ,user id ,passowrd) .

once we select a employee then DBCtxCustomer connention string should first update from Employee model  server property  and then   customer details should bind from specfic server . IN your case each customer details are coming from same server (DBCtxCustomer  connection string ).

DBCtx  

EMPID

EMPLOYEENAME

Server Name

Database

 User ID

Password

 

1

Rakesh

192.168.0.100

CustomerMngmt

abcd

12345

 

2

Mohan

190.168.100.200

CustomerMngmt

xyz

98765

 

 

DBCtxCustomer update  Connection string DB server 192.168.0.100

then bind customer 

Customer ID

Customer name

Location

State

1

Rakesh

Delhi

DELHI

 

 

DBCtxCustomer update  Connection string DB server 190.168.100.200 then bind customer 

Customer ID

Customer name

Location

State

1

Mohan

Mumbai

Maharastra  

 

 

 

 

 

 

 

dharmendr
 
on Jun 22, 2022 01:07 AM

Please answer the following questions.

1. Do you have same Database and Table structure in all the 3 state servers?

2. Do you want to keep Model and DBContext Same and just the connection string will change based on employee location?

 

Please confirm

Results 1 - 5 of 9