how to change connection string at run time in c#

Question

0.00/5 (No votes)

See more:

C#

i am using microsoft visual studio and backend as mysql

Posted 30-May-12 4:59am

Add a Solution

Comments

John Bhatt 30-May-12 11:07am

Write Some Detail about your requirement? if possible Example or your tried code.

3 solutions

Solution 2

Here's what I do, which is similar to Tim's suggestion. This technique picks the connection string based on the machine. When you're ready to deploy, add a connection string to the App.Config for the target machine.

First the App.Config

<connectionstrings>
      
  <add name="marois_work_1_spares" connectionstring="Data Source=MAROIS_WORK_1\SQLSERVER2008;Initial Catalog=Spares2;Integrated Security=True">
		providerName="System.Data.SqlClient" />

  <add name="ui-pc_spares" connectionstring="Data Source=(local);Initial Catalog=Spares2;Integrated Security=True">
		providerName="System.Data.SqlClient" />

</connectionstrings>

Then, in my data access class:

private SparesDataContext getDataContext()
{

    string machineName = Environment.MachineName.Trim().ToLower();
    machineName = machineName + "_spares";
    string connString = string.Empty;

    try
    {
        ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings[machineName];
        connString = conSettings.ConnectionString;
    }
    catch (Exception ex)
    {
        string errorMessage = "Error reading the connection string from App.Config. " + Environment.NewLine + 
            "The machine name is '" + machineName.Trim() + "'";
        throw new Exception(errorMessage, ex);
    }


    SparesDataContext dc = new SparesDataContext(connString);

    try
    {
        dc.Connection.Open();
        dc.Connection.Close();
    }
    catch (Exception ex)
    {
        string errorMessage = "Unable to acccess the database." + Environment.NewLine + 
            "The connection string is '" + connString.Trim() + 
            "'. The machine name name is '" + machineName.Trim() + "'";
        throw new Exception(errorMessage, ex);
    }

    return dc;
}

Posted 30-May-12 5:45am

Kevin Marois

Updated 30-May-12 5:46am

v2

Comments

Sandeep Mewara 30-May-12 14:13pm

5!

Kevin Marois 30-May-12 14:17pm

Thanks!

hit91 31-May-12 1:05am

Thank you sir for your help
I added first code in App.config
I have created a form in that form some Textboxs like IPaddress ,userid,password,database
so i am not gatting exatly where should i write this second code in my application.

Kevin Marois 31-May-12 11:58am

Create a data access class. This class should handle all access to & from your database. In my case I'm using Linq-to-sql. The getDataContext returns an instance of the data context for my app.

hit91 1-Jun-12 2:12am

Thank u sir
but i am not aware about access class
so can you explain me something like which i said you above

Kevin Marois 1-Jun-12 12:19pm

Take a look at

http://www.simple-talk.com/dotnet/.net-framework/designing-a-data-access-layer-in-linq-to-sql/

and

http://msdn.microsoft.com/en-us/library/aa581776.aspx

Solution 3

This is a pretty broad topic. I suggest you look at:

For Linq To SQL[^]

For ADO[^]

Posted 1-Jun-12 6:18am

Kevin Marois

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tim Corey · Accepted Answer · 2012-05-30T05:06:00

That is a fairly broad question. There are a number of ways to do this. One would be to ask the user directly for the connection string (not a great idea). Another would be to save multiple connection strings in the config file and then have your application choose which one to use based upon given criteria (we use this for live versus test environment).

There are many ways to do this based upon your requirements (who makes the change, when, and based upon what criteria). A quick Google search will give you a number of different options.