CODEWITHSUNDEEP
c#entity-framework
J.P Masangcay
J.P Masangcay

Reputation: 769

Can I use one connection string to use multiple databases with the same table structure in Entity Framework?

I have a web api application and it has its own database. The web api app distributes data to its branches' database. The branch databases are all same in table structure but each database has its own connection string in the web.config

<add name="Branch" connectionString="metadata=res://*/Models.HOGC.csdl|res://*/Models.HOGC.ssdl|res://*/Models.HOGC.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(local);initial catalog=GC_BranchName;user id=sa;password=Qwer0987;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
<add name="Branch_1" connectionString="metadata=res://*/Models.HOGC.csdl|res://*/Models.HOGC.ssdl|res://*/Models.HOGC.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(local);initial catalog=GC_BranchName_1;user id=sa;password=Qwer0987;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

I am using Entity framework Database First and I route my data using dbcontext

public HOGCEntities(string connString)
        : base("name=" + connString)

I have a list of branches in my main database table. It contains the name of the connection string and the name of the database/catalog (Branch_1 & GC_BranchName_1).

I want to know is, can I use just one connection string instead of manually or programmatically creating another connection string with the same attributes? Like looping through my branch table to route my data?

Upvotes: 0

Views: 1259

Answers (1)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112259

"How can I send postcards to several people living at different places by using the same address?"

Well, you can't.

The connection string is the "address" of a database and if you have several databases you need a specific connection string for each one of them.

However, if the databases have the same structure, you can use the same entity model and the same business classes for all of them.

Configuration settings

You can use the same connection string setting in your web.config, if you provide a template connection string with a placeholder for the catalog name:

<add name="Branch"
   connectionString=
       "...provider=...;data source=(local);initial catalog={0};..." providerName="..." />

where "{0}" is the placeholder.

public HOGCEntities(string catalog)
    : base(String.Format(ConfigurationManager.ConnectionStrings["Branch"].ConnectionString,
                         catalog)
          )

You will also need a reference to the assembly System.Configuration.dll and a using System.Configuration;.

Some databases allow you to create database links. By using them, you could link satellite databases to a master database. But this leads to the problem of accessing the different tables with the same name. I don't know how the same business class could be mapped to the different tables in turn.

Upvotes: 3

Related Questions