Changing connection string at web.config in MVC

Question

I set a MySQL connection string with MVC database first as below:

<add name="wf_workflowEntities" connectionString="metadata=res://*/Models.MySql.Model1.csdl|res://*/Models.MySql.Model1.ssdl|res://*/Models.MySql.Model1.msl;provider=MySql.Data.MySqlClient;provider connection string=&quot;server=192.168.1.8;user id=test;password=abc.1234;CHARSET=utf8;database=wf_workflow&quot;" providerName="System.Data.EntityClient" /> 

I want to replace some parameters of connection string if it was needed.for instance, for switching servers the IP of server must be set again(etc username,password).

I use this model for sending customized parameters:

public partial class BPMEngine
{
    public string DBServer { get; set; }//ip server
    public string DBName { get; set; }//database name
    public string DBUserName { get; set; }//username
    public string DBPass { get; set; }//password
}

and this is view:

@Html.TextBoxFor(model => model.DBServer, new { @class = "form-control1", placeholder = "ip server" })
@Html.TextBoxFor(model => model.DBName, new { @class = "form-control1", placeholder = "database name" })
@Html.TextBoxFor(model => model.DBUserName, new { @class = "form-control1", placeholder = "username" })
@Html.TextBoxFor(model => model.DBPass, new { @class = "form-control1", placeholder = "password" })
<button type="submit" class="btn btn-block btn-success"  id="transfer">save</button>

But I don't know how can I change connection string's parameters at web.config via controller(or action result).

In fact, I don't know, what should I do in controller for this problem?

Answer 1

You can change connection at runtime using the method supplied here.

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

I think this is the better way to do what you need, dealing with programatic web.config modifications can be cumbersome, but, if you prefer going web.config modification route, you can use this another one.

var configuration = WebConfigurationManager.OpenWebConfiguration("~");
var section = 
  (ConnectionStringsSection)configuration.GetSection("connectionStrings");
  section.ConnectionStrings["MyConnectionString"].ConnectionString = "Data Source=...";
configuration.Save();