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?

you seriously anticipate switching DB servers while the application is actually live, or not? If it's part of a controlled change you just re-publish the web.config with the new values. If you need to use more than one database, and the databases are known in advance, you can store multiple connection strings in web.config and just access the necessary one (by its name) from your code. If OTOH you need to dynamically switch to another DB whose connection parameters are not known in advance, then you'll need to build the connectionstring dynamically in your code using String.Format or similar — ADyson, Mar 05 '18 at 11:28
Look into providing a connection string when instantiating your DbContext. Check `DbContext Constructor(String)` here: https://msdn.microsoft.com/en-us/library/system.data.entity.dbcontext.dbcontext(v=vs.113).aspx#M:System.Data.Entity.DbContext. — Miro J., Mar 05 '18 at 14:58

Jovino Rodríguez · Accepted Answer · 2018-03-05T12:11:59.027

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();

edited Mar 05 '18 at 12:11

answered Mar 05 '18 at 12:05

Jovino Rodríguez

84
5

in this code, I can't replace parameters via model to web.config.I must set another connection string by `"Data Source=...".` I want replace data source with `model.datasource`. – Lili.brh Mar 06 '18 at 07:30
If you want to change connection strings in the web.config, the only way (afaik) is replacing it, there are not properties to access the connection string members. You can use a placeholder string "DataSource={0}" and format it with String.Format, but you will need to assign it. That is using the web.config way, the another solution allows you to replace parameters individually. – Jovino Rodríguez Mar 07 '18 at 15:21
yes I must replace properties.Can you Explain more with sample? – Lili.brh Mar 10 '18 at 19:56
Can you help me for replacing parameters? – Lili.brh Mar 23 '18 at 20:25
Didn't see your answer, sorry. Replacing parameters in a string is a new topic not strictly related to your initial question. You should open a new question, that is how stackoverflow remains such an useful tool for developers, one question, one answer. – Jovino Rodríguez Mar 25 '18 at 15:16

Changing connection string at web.config in MVC

1 Answers1