C# .NET - Dynamic Connection String in App.Cong

Asked By Anoop S on 28-Jun-12 02:12 AM
Hi
i am creating a windows application in C#.net, How can i use Dynamic connection string? This is my App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
  <add name="DbLeakTestConn" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
     Data Source=C:\....\databse.mdb;
     Persist Security Info=False;" providerName="System.Data.OleDb" />
  </connectionStrings>
</configuration>


How can i change the connection string (Source=C:\....\database.mdb;) to something like this way this way
Source =" + App.Path + "\database.mdb

Thanks

Neha Garg replied to Anoop S on 28-Jun-12 02:44 AM

Hello Anoop,

 

 Add the below code in your web.config file:


    <configuration>

      <connectionStrings>

        <clear />

        <add name="Target" connectionString="server=YourSERVER; Database=MYDB; Integrated Security=SSPI;" />

      </connectionStrings>

    </configuration>

 

retrieve it by using
ConfigurationManager:


 ConfigurationManager
.ConnectionStrings["Target"].ConnectionString

 

  you have to give reference of the System.Configuration library.


And to change the ConnectionString use the below code:

 

 textBox1.Text = ConfigurationManager.ConnectionStrings["Target"].ConnectionString;


Neha Garg replied to Neha Garg on 28-Jun-12 02:45 AM

You have to add the below code in app.config

    <configuration>

      <connectionStrings>

        <clear />

        <add name="Target" connectionString="server=YourSERVER; Database=MYDB; Integrated Security=SSPI;" />

      </connectionStrings>

    </configuration>

dipa ahuja replied to Anoop S on 28-Jun-12 04:18 AM
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
  <add key="name" value="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"/>
  </appSettings>
</configuration>
 
 
string s = ConfigurationSettings.AppSettings["name"].ToString();
      
 
Anoop S replied to Neha Garg on 28-Jun-12 04:30 AM
Hi
i am installing setup file in client computers ( windows application, ms access database), i am including database with that setup file and that database also installed that time to client machine, so path may be different in different system, so i want to open its from application start up path,  in VB i use like this way
Data Source=" & App.Path & "\database.mdb;Persist Security Info=False"'
i need some thing like that way in c#
Thanks
Anoop S replied to dipa ahuja on 28-Jun-12 04:30 AM
Hi
i am installing setup file in client computers ( windows application, ms access database), i am including database with that setup file and that database also installed that time to client machine, so path may be different in different system, so i want to open its from application start up path,  in VB i use like this way
Data Source=" & App.Path & "\database.mdb;Persist Security Info=False"'
i need some thing like that way in c#
Thanks
dipa ahuja replied to Anoop S on 28-Jun-12 04:32 AM
Never give the absolute path like C:\project\db1.mdb. This will create problem everytime you move your project from one place to another.

The path which i suggested you will work at all the places. because its Relative path. You have to give relative path for the database every time you dev. projects. 
Vikram Singh Saini replied to Anoop S on 28-Jun-12 04:45 AM
Hi,

For your problem requirement, I would recommend to use following solution:

using System.Configuration;

private void button1_Click(object sender, EventArgs e)
      {
        string newConString = "<add name=\"DbLeakTestConn\" connectionString=\"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\database.mdb; Persist Security Info=False;\" providerName=\"System.Data.OleDb\" />";

        Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
        config.ConnectionStrings.ConnectionStrings[1].ConnectionString = newConString;          
        ConfigurationManager.RefreshSection("connectionStrings");    
      }


We have used Configuration and ConfigurationManager to deal with modifying connection string and Application.StartupPath returns the path of bin/Debug folder in the application folder.
Anoop S replied to Vikram Singh Saini on 28-Jun-12 05:04 AM
I given like that way only in app.config, but thats getting many error

<add name="DbLeakTestConn" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
     Data Source="+Application.StartupPath+"\\database.mdb;
     Persist Security Info=False;" providerName="System.Data.OleDb" />
  </connectionStrings>

error like
Character '+', hexadecimal value 0x2b is illegal in an XML name. etc...etc..
Vikram Singh Saini replied to Anoop S on 28-Jun-12 05:11 AM
Hi,

As you told, I given like that way only in app.config, but thats getting many error

That would give error. Because the code I provided to you is for running in .cs file. And it would modify connection string automatically within App.config file from .cs file.

Try it from .cs file and then let me know the action result.

Anoop S replied to Vikram Singh Saini on 28-Jun-12 05:21 AM
i will check and tell
Anoop S replied to Vikram Singh Saini on 28-Jun-12 06:01 AM
Hi
its showing error  "Format of the initialization string does not conform to specification starting at index 0."
Thanks
Vikram Singh Saini replied to Anoop S on 28-Jun-12 11:11 PM
its showing error  "Format of the initialization string does not conform to specification starting at index 0."

In which code line are you facing this problem?
Anoop S replied to Vikram Singh Saini on 29-Jun-12 12:48 AM
its showing after calling calling connection

OleDbConnection myConn = new OleDbConnection();
myConn.ConnectionString = ConfigurationManager.ConnectionStrings["DbLeakTestConn"].ConnectionString ; //This line shows error
Thanks
Vikram Singh Saini replied to Anoop S on 29-Jun-12 03:29 AM
I'm extremely sorry for providing you wrong way of modifying connection string from .cs file. And I think that is the reason of number of errors you are facing. Sorry once again.

Rectify all this way. Don't forget to add line using System.Configuration;

And you would need to add reference to System.Configuration.dll for making this to work.

private void button1_Click(object sender, EventArgs e)
    {
      Configuration config = ConfigurationManager.OpenExeConfiguration("ApplicationName.exe"); //1//
    ConnectionStringsSection connectionStringsSection = (ConnectionStringsSection)config.GetSection("connectionStrings"); //2//
      connectionStringsSection.ConnectionStrings["DbLeakTestConn"].ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\database.mdb;Persist Security Info=False;"; //3//
      config.Save(ConfigurationSaveMode.Modified); //4//
      ConfigurationManager.RefreshSection("connectionStrings"); //5//
    }

 
Explanation of // Points//:

//1// We have opened the respective configuration file of the application. Please take care of passing name same as application name.

For e.g.
if your application name is Calculator, you should pass it as "Calculator.exe"

Some noteworthy points: There are three .config files when using application. One is app.config (the one that's been added to application by developer), second ApplicationName.vshost.exe (the one used by application during debugging)
and third one is ApplicationName.exe (this one is also created during application and your application use it in real scenario after installation).

The last two configuration files can be found in the bin/Debug folder of your application folder.

So whenever the configuration files are modified,the changes would be reflected in these two configuration files. You can check out the changes.

//2//
We have retrieved the connection string section from the configuration file.

//3// Modified the connection string with new customized value.

//4// Save the configuration of connection strings in configuration file if changes are made.

//5// Finally refreshing the connection string section for making changes reflected or visible in the configuration file.

Hope it helps. Let us know back the action result.
Anoop S replied to Vikram Singh Saini on 29-Jun-12 04:30 AM
Hi thanks for the detailed explanation, Actually i solved it by changing data source path in app.config like this way

Data Source=..\Database\DbLeakTest.mdb;

One more doubt, if i provide like this way and i make setup file and install in any other system, will it cause any problem?

 
Vikram Singh Saini replied to Anoop S on 29-Jun-12 04:56 AM
I'm not sure about this. But I think that the app.config would try to search the .mdb file from the Application folder --> Database folder --> and then the .mdb file.

There is possibility of generating problems. Because when your application would be install, there would be two extra folders named as C:/Program Files/ApplicationFolder/ and then comes our Database folder. Now the code would try to look for same file in ApplicationFolder.

So all can create confusion. Further if user installs the program deep in some folders, then there would be probability of mistakes. My recommendation still would be to use Application.StartupPath. Because in any condition it will never went wrong.


Anoop S replied to Vikram Singh Saini on 29-Jun-12 05:24 AM
Ok, thanks  for the information, i will try both case and replay the feedback