Change DbContext connection at run-time

Question

I have 3 DBs that I want to work with: A,B and C. each one have the same tables (for example: users, products).

I want to let the user decide (on run-time) which DB he wants to work with. So... I used EF5 and created 3 edbx files which created the following classes: ADBEntities, BDBEntities and CDBEntities.

How can I let him decide the selecteddb so I could get its users?

I mean,

var dstuff = from user in selecteddb.users
             where user.UserEmail == userEmail
             select user.UserID;

I've thought of using reflection / base class (DBEntities), but didn't get far with those ideas.

Oligoclase

i've added an alternative option for your sceanrio. hope it helps... – Palliasse

Answer 1

a bit late on this answer but I think there's a potential way to do this with a neat little extension method. As slypete (nice name :-)) says, you only need ONE class model, assuming all tables/properties are identical. This being the case, we can take advantage of the EF convention over configuration plus a few little framework calls.

Anyway, without further ado, the commented code and example usage:

the extension method class:

public static class ConnectionTools
{
    // all params are optional
    public static void ChangeDatabase(
        this DbContext source,
        string initialCatalog = "",
        string dataSource = "",
        string userId = "",
        string password = "",
        bool integratedSecuity = true,
        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
        }
    }
}

usage:

// assumes a connectionString name in .config of ADBEntities
var selectedDb = new ADBEntities();
// so only reference the changed properties
// using the object parameters by name
selectedDb.ChangeDatabase
    (
        initialCatalog: "name-of-bdb-initialcatalog",
        userId: "jackthelad",
        password: "nosecrets",
        dataSource: @".\sqlexpress" // could be ip address 100.23.45.67 etc
    );

I currently use this for exactly the purpose that you mention above and thus far, it's served me very well. Hope it helps in your instance.

Palliasse

Answer 2

Pass the appropriate connection string / connection name when creating a DbContext

http://msdn.microsoft.com/en-us/library/gg679467%28v=vs.113%29.aspx

using (var context = new MyDbContext("Server=localhost;Database=dbA;..."))
{
    return context.Users.Where(u => u.Email == "[email protected]").Single();
} 

Schapira

Answer 3

var defaultString = _myContext.Database.GetDbConnection().ConnectionString;
_myContext.Database.GetDbConnection().ConnectionString ="new connection" or _myContext.Database.GetDbConnection().ChangeDatabase()
//your query..
_myContext.Database.GetDbConnection().ConnectionString = defaultString;

Alla

Answer 4

Assuming the databases are identical, you just need to use one of the generated classes. You don't need all three (because they are identical). So let's pick one -- let's say ADBEntities.

Now, you need to allow her to choose her connection (connection string) at run-time. If you have your three connection strings stored in your app.config/web.config, you can load them up at run-time using the ConfigurationManager:

var connections = ConfigurationManager.ConnectionStrings;

Present the choices and select one of them somehow:

foreach (ConnectionStringSettings connection in connections)
    //display connection.Name

ConnectionStringSettings selected_connection = connections[1];

One of the DBContext's constructors takes a connection string, so just pass the user selected connection string to the constructor of ADBEntities:

using (var selecteddb = new ADBEntities(selected_connection.ConnectionString))
{
    var dstuff = from user in selecteddb.users
         where user.UserEmail == userEmail
         select user.UserID;
} 

Topple

Ok, the idea is clear to me (thank you!), but one question... ADBEntities doesn't have a constructor that takes a connection string (although it's derived from DBContext which does). Should I add such a constructor to ADBEntities? Or do something else...? – Oligoclase

If I add the following constructor: public AdbEntities(string nameOrConnectionString) : base("name=AdbEntities") { } I get this error: Schema specified is not valid. Errors: The relationship 'AdbModel.AccessCategoriesGroup' was not loaded because the type 'AdbModel.ProductCategory' is not available. – Oligoclase