Modify ConnectionString in App.Config

Question

Okay so I've found various different forum posts on this, but none giving a viable solution with a code example in C#... so here goes...

I've created a DataSet using the dataset designer, and this has used the connection string "dataConnectionString" from the app.config.

the problem is that this database is a SqlCe database that will hold userdata.  therefore it will be copied from the install directory to a location they specify (allowing them to easily back it up if needed etc.).

This presents a problem in that all the code I've wrote for adding things to the database using the DataSet created tableadaptors etc. is going into the wrong database!

My first thought was to change the dataConnectionString in code, but it appears that ConnectionString settings have to be ApplicationScope and therefore readonly at runtime...

I then thought I would change all the DataSet Designer parts to use a new String Setting i created that can be user-scoped, but that doesn't appear to be possible as it's requiring a ConnectionString...

Can anyone give me a nudge in the right direction?  Not asking for a complete how-to, I'm willing to do the research, I'm just getting nowhere right now!

thanks in advance...

Martin

Answer 1

You have at least a couple of choices. You can create a connection string yourself; you do not have to use the connection string from the configuration. An alternative might be to modify the location of the data before the database is opened, but I am not sure of how to do that. In other words, you could essentially override what is specified in the connection string. It would probably work better if you learn about how to create the connection string yourself.

---

Sam Hobbs; see my SimpleSamples.Info

Answer 2

Thanks for the reply....

The problem I have is that I can't create the Connection String myself...

the dataset Designer requires it, therefore I have to create it at design time.   when it's created at design time, it can't be changed at runtime...

What's bugging me is that I'm sure that this is something that has a very simple answer, but I just can't see it!

Answer 3

hey martin,


can you please elaborate on following,

the problem is that this database is a SqlCe database that will hold userdata.  therefore it will be copied from the install directory to a location they specify (allowing them to easily back it up if needed etc.).

This presents a problem in that all the code I've wrote for adding things to the database using the DataSet created tableadaptors etc. is going into the wrong database!


Do you mean that a copy of your database is made at some location specified by the user while the data is stored in copy of the database at another location?

I couldnt follow it ...

---

student,UH

Answer 4

It can be changed at runtime.  The following code snippet assumes the name of the connection string is stored in the variable "_connectionStringName".  In this example, I'm setting the connection string in the application configuration file to match the value that is entered into the textBox1 TextBox at runtime.  Note that I'm fetching a "Configuration" object first, then changing the value of the connection string, then saving the Configuration file, using the ConfigurationSaveMode.Modified enum value, forcing the save by passing in the "true" flag as the second argument to the Save method. 

The key here, however, is the "RefreshSection" method, which refreshes the entire section from the application configuration file.  The next time the specific connection string that has been changed is pulled from the configuration file, the new value will be retrieved.

Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
config.ConnectionStrings.ConnectionStrings[_connectionStringName].ConnectionString = textBox1.Text;
config.Save(ConfigurationSaveMode.Modified, true);
ConfigurationManager.RefreshSection("connectionStrings");

---

David Morton - http://blog.davemorton.net/ - @davidmmorton - ForumsBrowser, a WPF MSDN Forums Client

Answer 5

Another example:

//Add config like this:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
   
  </connectionStrings>
</configuration>


 Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            config.ConnectionStrings.ConnectionStrings.Add(new ConnectionStringSettings(
                                                          "MyConnectionString",
                                                          String.Format("DataSource={0};InitialCatalog={1};IntegratedSecurity={2}",
                                                                         "testing", "testing2", "Testing6")));
            config.Save(ConfigurationSaveMode.Modified, true);
            ConfigurationManager.RefreshSection("connectionStrings");
            MessageBox.Show(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString)

---

John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

Answer 6

Also see Using Compact Edition instead of non-Compact; I think it will help. Also read Accessing Data (Visual Studio). There is a lot there to read but I have read a lot of it and most everything I know I know from reading the documentation. There are many samples in the documentation. After reading the documentation, you will see much more.

---

Sam Hobbs; see my SimpleSamples.Info

Answer 7

There are problems with the proposed solutions.  It assumes that your program will actually be able to change the .config file.  That's unlikely in a properly secured Windows machine, programs do not have write access to folders like c:\program files.  It requires an administrator account with UAC disabled or bypassed to modify the file.

Since you are using the Compact edition, the real solution is to store the database in a directory that's accessible to your program.  Find it with Environment.GetFolderPath(), passing something like Environment.SpecialFolder.LocalApplicationData for example.  The database could be put there by an installer.  But I personally prefer to have it done by the app itself.  Keep the initial database in the same folder as the .exe.  At runtime, check if the file exists in the GetFolderPath().  If it doesn't, copy the initial database there.

Advantages are:
- the ConnectionString doesn't need to be in the .config file anymore, just build it yourself
- the application is resilient, it automatically recovers from gross data loss
- you don't need an installer, xcopy deployment works.

---

Hans Passant.

Answer 8

hi all,

Thanks for the responses, and appologies for not getting on earlier.

Firstly, nobugz, thanks for the reply but I don't think you've quite understood the underlying problem.  It's to do with the dataset designer only allowing the user a ConnectionString located in the app.config, so building it myself is something I've done in the past, but can't if I want to use the dataset designer.  As I understand it, I couldn't use things like Environment.SpecialFolder etc. inside of the app.config??? If I've misunderstood your solution, then i'm more than willing to re-mark it an answer...

Secondly, Vayuu, you are correct... the database that I ship out is more of a template.  The application creates a copy of the database for use by the user (they would normally just dump it in My Documents), this will then store all their user information (e.g. customers, orders, etc.)... I've built in the ability for the application to do a kind "self update" if I need to make changes to their database.

finally, David and John... I can't seem to get either of those solution working???  I have a form I've setup for maintaining settings, so I added those 4 lines... but IntelliSense doesn't seem to recognise ConfigurationManager, or Configuration for that matter... Can you tell me what I'm doing wrong? I'm not completely new to programming, but I am still learning...

Thanks for all the responses, glad to see there are people around who know what they're doing!

Martin

Answer 9

... IntelliSense doesn't seem to recognise ConfigurationManager, or Configuration for that matter... 

1. Right click on the References folder in your project. 

2. Select the .NET Tab, and browse to select "System.configuration". (Why "configuration" is lower case, I'll never know). 

3. Ensure you have a line saying "using System.Configuration;" at the top of your code file. 

---

David Morton - http://blog.davemorton.net/ - @davidmmorton - ForumsBrowser, a WPF MSDN Forums Client

Answer 10

The answer to that David is: 

Quoting from a blog I can't recall where I originally found it, but I was smart enough to copy it down. 

"When you try to use ConfigurationSettings.AppSettings in the .NET Framework 2.0, you receive a message stating that AppSettings is obsolete, use ConfigurationManager.AppSettings instead. I did finally figure it out. ConfigurationManager does not exist in the default System.Configuration namespace. When you use System.Configuration, it loads the System.Configuration.dll assembly. If you manually set a reference to System.configuration.dll, you can easily find the needed ConfigurationManager.

I did a little searching after figuring this out and a few people are irritated that Microsoft chose this route. However, I think they would have been irate if backwards compatibility would have been broken. My hats off to the .NET Framework developers. There was obviously something lacking in System.Configuration and I think System.configuration is a great start.

As a side note, you can continue using ConfigurationSettings.AppSettings and your app will compile and all will be right with the world but resistance is futile. I strongly encourage using the ConfigurationManager. There are so many new features that will make your life easy, it is worth the effort to learn."

---

John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

Answer 11

Thanks david...

I actually realised that this morning at work... I must have been on something last night...

Thou shalt not ask questions on 3 hours sleep......

Answer 12

hi 

JohnGrove

i have tested your code. it will changed only runtime but it does not save permanently. i have open App.Config files but not showing my connection string

Answer 13

update value sustain till programe is running .its values gone after application is closed.

its better to used xml file

Answer 14

Hi

This is not works for me, below is my code 

Dim myconfig As Configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)

            myconfig.ConnectionStrings.ConnectionStrings("ConnString").ConnectionString = "server=system14;database=erp;uid=sa;pwd=sa;"

            myconfig.Save(ConfigurationSaveMode.Modified, True)

            ConfigurationManager.RefreshSection("ConnectionStrings")

            Dim strcon As String = ConfigurationManager.ConnectionStrings("ConnString").ConnectionString

here strcon returns old entry not the new

Can you explain the reason

Thanks

Ramakrishnankt

Answer 15

hi dear sir

after compile , your code has error in 2nd Line , Something like this :

An unhandled exception of type 'System.NullReferenceException' occurred in MyProjectName.exe

Additional information: Object reference not set to an instance of an object.

what should we do now ???

Mean this line :

config.ConnectionStrings.ConnectionStrings[_connectionStringName].ConnectionString = textBox1.Text;

i did change _connectionStringName to mine also

thanks before

farzad khajeh

Answer 16

Hi

Attached here is my code which set the conneciton string to a new value, But IT IS NOT WORKING

            Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            config.ConnectionStrings.ConnectionStrings.Remove("sedonaConnection");
            config.ConnectionStrings.ConnectionStrings.Add(new ConnectionStringSettings("sedonaConnection", myConnectionString));
            config.Save(ConfigurationSaveMode.Modified, true);
            ConfigurationManager.RefreshSection("connectionStrings");
            Console.WriteLine(ConfigurationManager.ConnectionStrings["sedonaConnection"].ConnectionString);

I do not understand: it is exactly like your sample.

Please advise.  Thanks