Entity Framework - change connection string at runtime (explanation needed)

Question

I apologize for posting this as a question, but I'm not able to comment under the actual solution to my question yet, which was answered here. This solution also doesn't work in a same way.

I used that solution and the extension seems to work itself apart from actually changing the connection. It remains same as it is defined in web.config file. If I remove that connection string I get error saying that EF couldn't find it.

My approach is Database first (moreover, it's SQL Server 2000...) and EF version 6 (basically, the latest)

So my question is - how it supposed to work?

• Do I have to pass same connection name to the extension method as it is defined in web.config or should it be different?

My current connection string looks as follows:

<connectionStrings>
    <add name="CATALOGEntities" connectionString="metadata=~/bin/Models\InfoModel.csdl|~/bin/Models\InfoModel.ssdl|~/bin/Models\InfoModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=SERVER;initial catalog=CATALOG;integrated security=False;User Id=admin;Password=admin123;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>

ATTEMPT 1: This is what I'm passing to extension method:

ConnectionTools.ChangeDatabase(db, "ANOTHERCATALOG", "ANOTHERSERVER", "admin", "admin456", false, "ANOTHERCATALOGEntities"); 

ATTEMPT 2: Tried as suggested by VDohnal too:

db.ChangeDatabase("ANOTHERCATALOG", "ANOTHERSERVER", "admin", "admin456", false, "ANOTHERCATALOGEntities"); 

ATTEMPT 3 with:

public partial class CATALOGEntities : DbContext { 
    public CATALOGEntities(string connectionString) : base(connectionString) { } 
    public CATALOGEntities() { 
        // TODO: Complete member initialization 
        Database.SetInitializer<CATALOGEntities>(null); 
    }
}

ATTEMPT 4: Doesn't work either (assuming me having 2 connection strings defined in web.config (source)):

if (infoWhole.QueryDetails.IsCountryUK)
{
    string strConn = ConfigurationManager.ConnectionStrings["CATALOGEntities"].ConnectionString;
    db = new CATALOGEntities(strConn);
}
else
{
    string strConn = ConfigurationManager.ConnectionStrings["CATALOGEntitiesUSA"].ConnectionString;
    db = new CATALOGEntities(strConn);
}

• Also, what data source should I pass to extension method - the whole DbContext or the one defined in the controller class I'm working in, which is CATALOGEntities?

Here is the extension method that I'm using:

public static class ConnectionTools
{
    // all params are optional
    public static void ChangeDatabase(
        this CATALOGEntities source,
        string initialCatalog = "",
        string dataSource = "",
        string userId = "",
        string password = "",
        bool integratedSecuity = false,
        string configConnectionStringName = "")
    /* this would be used if the
    *  connectionString name varied from 
    *  the base EF class name */
    {
        try
        {
            // use the const name if it's not null, otherwise
            // using the convention of connection string = EF contextname
            // grab the type name and we're done
            var configNameEf = string.IsNullOrEmpty(configConnectionStringName)
                ? source.GetType().Name
                : configConnectionStringName;

            // add a reference to System.Configuration
            var entityCnxStringBuilder = new EntityConnectionStringBuilder
                (System.Configuration.ConfigurationManager
                    .ConnectionStrings[configNameEf].ConnectionString);

            // init the sqlbuilder with the full EF connectionstring cargo
            var sqlCnxStringBuilder = new SqlConnectionStringBuilder
                (entityCnxStringBuilder.ProviderConnectionString);

            // only populate parameters with values if added
            if (!string.IsNullOrEmpty(initialCatalog))
                sqlCnxStringBuilder.InitialCatalog = initialCatalog;
            if (!string.IsNullOrEmpty(dataSource))
                sqlCnxStringBuilder.DataSource = dataSource;
            if (!string.IsNullOrEmpty(userId))
                sqlCnxStringBuilder.UserID = userId;
            if (!string.IsNullOrEmpty(password))
                sqlCnxStringBuilder.Password = password;

            // set the integrated security status
            sqlCnxStringBuilder.IntegratedSecurity = integratedSecuity;

            // now flip the properties that were changed
            source.Database.Connection.ConnectionString
                = sqlCnxStringBuilder.ConnectionString;
        }
        catch (Exception ex)
        {
            // set log item if required
        }
    }
}

My DbContext:

public partial class CATALOGEntities : DbContext
{
    public CATALOGEntities()
        : base("name=CATALOGEntities")
    {
    }
}

Answer 1

Looking at the code in the answer that you link to, all it is doing is reading the connection string from the web.config file, and then uses the SqlConnectionStringBuilder class to replace the relevant parts of the connection string with the new details.

It does not write the modified connection string back to the web.config. You could think of the existing connection string as being a template.

I suspect that you'd want to pass in the context that is relevant to the controller, and the last parameter would be the name of the current connection string (unless it has the same name as your context - in which case, you can omit it).

Of course, this would all assume that the other database has the same model.

Answer 2

You must pass a connection name that already extists in .config or omit it. So call it like this:

 db.ChangeDatabase( "ANOTHERCATALOG", "ANOTHERSERVER", "admin", "admin456", false);

It does not change initial configured connection of your app, it only changes the connection during runtime for a particular existing instance of your DbContext (=CATALOGEntities). Which is not what you need, I think - you would need to call this whenever you create a new DbContext.

I suggest that you use a different approach. Create a factory that would produce instance of your DbContext based on the selected country. Use that factory whenever creating new DbContext. Another way is to change the constructor of your DbContext (=CATALOGEntities) class.

Answer 3

SOLUTION: This is what finally worked for me.

Controller class responsible for access to SQL Server:

public class FrequentlyAccessedQueries : Controller
{
    private CATALOGEntities db = FrequentlyAccessedQueries.entities();

    public static CATALOGEntities entities()
    {
        QueryDetails qdetails = new QueryDetails();
        bool uk = qdetails.IsCountryUK;
        if (uk) 
        {
            return new CATALOGEntities("name=CATALOGEntitiesUK");
        }
        else 
        {
            return new CATALOGEntities("name=CATALOGEntitiesUSA");
        }
    }
}

DbContext class:

public partial class CATALOGEntities : DbContext
{
    public CATALOGEntities(string connectionString)
        : base(connectionString)
    {
    }
}

web.config entries:

<connectionStrings>
    <add name="CATALOGEntitiesUK" connectionString="[...]" providerName="System.Data.EntityClient" />
    <add name="CATALOGEntitiesUSA" connectionString="[...]" providerName="System.Data.EntityClient" />
</connectionStrings>

Answer 4

Another way to go about changing the database connection is to dynamically change the ConfigurationManager ConnectionString.
It's a bit of a hack, but allows you to easily progress through a few different databases (Dev, Test, Prod). It does require that you have the same tables in each database to which you connect. If you need to change anything else in the connection string, hopefully this will be a good start for you.

string DataBaseName = "bab"
string applicationName = Environment.GetCommandLineArgs()[0] ;
string exePath = System.IO.Path.Combine(Environment.CurrentDirectory, applicationName);
var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
var connectionStringsSection = (ConnectionStringsSection)config.GetSection("connectionStrings");
//excuse the poor regex - I'm still figuring it out
connectionStringsSection.ConnectionStrings["Entities"].ConnectionString =
Regex.Replace(connectionStringsSection.ConnectionStrings["Entities"].ConnectionString, "initial catalog.*;(i)", "initial catalog ="+DataBaseName+";i");
config.Save();
ConfigurationManager.RefreshSection("connectionStrings");

Entities test = new Entities();
IEnumerable<int> list = from bobble in test.bobble
                                where bobble.ID < 250
                                select bobble.ID;

Answer 5

I did all of this and the source.Database.Connection.ConnectionString sets correctly but when I actually use it to read a table, it fails since it reads the one from Web.Config which has masked random creds. I am changing the password and userID during runtime like Attempt 2. Anyone got it working?

 mycontext.ChangeDatabase
            (
                userId: something,
                password: something

            );
      string b= mycontext.Table1
                        .Where(u => u.name == 'a')
                        .Select(a => a.ID)
                        .FirstOrDefault();