C# How To Update Entity Model Connection String

Question

This is my first WinForm application using the Entity Framework and I have to be able to update the connection string for the entity model I created on the fly and in my app.config file I have the following connectionString:

<add name="NCIPEntities" connectionString="metadata=res://*/NCIPModel.csdl|res://*/NCIPModel.ssdl|res://*/NCIPModel.msl;provider=System.Data.SQLite;provider connection string='data source=&quot;C:\Test\NCIP\NCIP.db3&quot;;pooling=True'" providerName="System.Data.EntityClient" />

This is what I wrote to update the string on the fly, it doesn't throw any errors when it runs but it also doesn't save the new connection string back to the app.config file.

private void UpdateEntityConnection()
    {
        StringBuilder Sb = new StringBuilder();
        Sb.Append(@"metadata=res://*/NCIPModel.csdl|res://*/NCIPModel.ssdl|res://*/NCIPModel.msl;provider=System.Data.SQLite;provider""");
        Sb.Append("connection string='data source=" + Settings.Default.Directory + "\\NCIP.db3;pooling=True'");

        Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
        config.ConnectionStrings.ConnectionStrings["NCIPEntities"].ConnectionString = Sb.ToString();

        config.Save(ConfigurationSaveMode.Minimal);
        ConfigurationManager.RefreshSection("connectionStrings");
    }

Does anyone see what I am doing wrong because I don't.

Thanks.

Answer 1

You should use EntityConnectionStringBuilder instead of trying to rewrite it. :)

Answer 2

The article here will help you but ...

From article ... emphasis mine

So here's my take on a convenient little class that allows you to either modify, add or delete any appSettings element, in either your Executable, Console or ASP.NET web application at runtime, on the fly. Bear in mind of course, that if you modify a web.config on a running ASP.NET app, the ASP.NET worker process will recycle. Users currently using your app aren't exactly guaranteed to have a fun experience when this happens...

Kindness,

Dan

Answer 3

Did you know you can do this:

var connstr = GetConnectionString();

using (NCIPEntities ctx = new NCIPEntities(connstr))
{
    ...
}

I.e. the Entity Framework doesn't have to get the connection string from the App.Config.

Anyway knowing this might change your approach/requirements a little?

Hope this helps

Alex

PS: you might want to check out my Tip 45 for more info. I have a whole series of Tips too