Dynamically update connection string on user input

Question

I have 10 databases with identical schema (appx. 20 tables). I am using MVC5 and Entity Framework 6. I am required to change between these databases based on user input. I.e. By selecting a year database associated with this year needs to be selected.

I have succeed at this task by manipulating web.config file using following code:

        public static bool setDBConnectionString(string path)
    {
        try
        {
            string dataSource = @"metadata=res://*/Models.Micropay.Micropay.csdl|res://*/Models.Micropay.Micropay.ssdl|res://*/Models.Micropay.Micropay.msl;provider=VfpEntityFrameworkProvider2;provider connection string='data source=" + path + "'";

            var configuration = WebConfigurationManager.OpenWebConfiguration("~");
            var section = (ConnectionStringsSection)configuration.GetSection("connectionStrings");
            section.ConnectionStrings["connectionStringEntity"].ConnectionString = dataSource;
            configuration.Save(ConfigurationSaveMode.Modified);
            ConfigurationManager.RefreshSection("connectionStrings");
        }
        catch
        {
            return false;
        }
        return true;
    }

However, this is not optimal solution based on scale of the project and number of users.

I've tried rebuilding connection string using following code:

            Debug.WriteLine(this.Database.Connection.ConnectionString);
        var entityCnxStringBuilder = new EntityConnectionStringBuilder
            (System.Configuration.ConfigurationManager
             .ConnectionStrings["connectionStringEntity"].ConnectionString);

        var sqlCnxStringBuilder = new SqlConnectionStringBuilder
            (entityCnxStringBuilder.ProviderConnectionString);

        sqlCnxStringBuilder.DataSource = @"C:\location";

        this.Database.Connection.ConnectionString
            = sqlCnxStringBuilder.ConnectionString;


        this.Database.Connection.Open();

but I was unable to get around following error message:

"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

Any help would be much appreciated.

Do not change the web.config. Simply store the selected connection string in the user's Session or a cookie or some other persistent storage, then pass the connection string to your context. — mason
– mason, Commented May 22, 2015 at 16:05

Parv Sharma · Accepted Answer · 2015-05-22 16:02:49Z

3

why dont you use the other overload of the DbContext class. This overload's signature is DbContext(string). So you can manually pass the connection string into the class object. Also if you are not ok with the passing connection string around in the code. add all the string to the Web.Config and pass their name along.

So after that your code will be like.

string userInput // actul user input
var connS = SomeLogicClass.GetConnString(userInput);
using(var db = new YourDbContext:DbContext){
  //do your operations
}

answered May 22, 2015 at 16:02

Parv Sharma

12.7k5 gold badges52 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamically update connection string on user input

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related