Change a database connection string from code

The connection string to a database should never be hard coded in your application. There is (almost) always a difference between the development and production database server. Besides that IT management should have an easy way to change the configuration. Part of this has been automated by the wizards of Visual Studio. When you add a dataset to a web site the wizard will create a web.config entry for the connection string. That's nice but, as I described in a another post, when you add database code directly to your web site your site will be (sooner or later) toast. My way is to isolate all database code in a class library. But after building the datasets in there it has become harder to change the connection string later on. You have become the sorcerers apprentice. (For those unknown with that story: In there the pupil of a great wizard starts playing around on his own. In the end he's almost killed by a bewitched broom, the wizard himself comes back just in time to save him. The story has been beautiful visualized in Disney's Fantasia where Mickey Mouse is the sorcerers apprentice).

There is a way out. You can change the connection string of any table adapter at runtime by setting it's Connection.Connectionstring to the desired value. Scott describes how to add this functionality to the code of a dataset component. Which is nice but you have to write this code for every table adapter and you have to set the connection after every instantiation of the table adapter. Which is tedious and a possible source of bugs.

What the wizards do is store the value of the connection string in a setting of the app. There is a settings page under the properties of a project

In here settings are stored in name-value pairs. The name is the name of the database; for sqlServer it's the DB name for FireBird (as in the screenshot) the name is the full name of the database file.

In a web application these settings are stored in the web.config, in a class libray in the app.config file.

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

    <configSections>

    </configSections>

    <connectionStrings>

        <add name="DataLibrary.Properties.Settings.C__Documents_and_Settings_Peter_PETERSGEKKO_My_Documents_My_Custoners_DirActivity_DacPro_DACPRO_FDBConnectionString"

            connectionString="port number=3050;charset=ISO8859_1;dialect=3;server type=0;database=&quot;C:\Documents and Settings\Peter.PETERSGEKKO\My Documents\My Custoners\DirActivity\DacPro\DACPRO.FDB&quot;;data source=Farlowella;user id=gebruiker;password=project"

            providerName="FirebirdSql.Data.FirebirdClient" />

    </connectionStrings>

</configuration>

What could work is editing this app.config file. But the downside is that you have to maintain besides the web.config an app.config for every class library which works with a database. Which is prone to error.

To good thing is that you can override the values of these settings from code. When the library is loaded the settings are available in Settings, a property of  the namespace of the library. In this scenario I have a class library which wraps up access to a Firebird database. I add one public method.

public static void SetConnectionString(string connString)

{

    const string FBconnectionTEmplate = "port number=3050;charset=ISO8859_1;dialect=3;server type=0;database=\"{0}";

 

    const string SettingsName = "C__Documents_and_Settings_Peter_PETERSGEKKO_My_Documents_My_Custoners_DirActivity_DacPro_DACPRO_FDBConnectionString";

 

    Settings.Default[SettingsName] = string.Format(FBconnectionTEmplate, connString);

}

The method has a template for a Firebird connection string. It expects the filename of the Firebird database to use. The setting has a horrible name, that's the fault of the Firebird designer support. When you use sqlserver it will be a simple database name. The name is the indexer to the Settings.Default array. To this I assign the composed connectionstring.

Now I make one call to this method, passing it a value read form the web.config

string dbConnString = System.Configuration.ConfigurationManager.AppSettings["dbConnection"];

ConnectionStringSetter.SetConnectionString(dbConnString);

After that all table adapters will use the right connection. I have my database wrapped up in a separate layer and still have all configuration in the web.config.

This entry was posted in ASP.NET, Data. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Derek

    This can possibly be overwritten because the designer is generated code…

  • Rafal

    Better way:
    Locate settings.designer.vb and search for 

    public readonly property  MyConnectionString()

    change it to

    public property MyConnectionString()
    Set(ByVal value As String)                Me("MyConnectionString") = value            End Set

  • Ulrich Schmid

    In visual basic use the following command to modify a readonly(Application scoped) settings variable ApplicationConnectionString:
    My.Settings(“ApplicationConnectionString”) = NewApplicationConnectionString.
    Note that a subsequent My.Settings.Save() doesn’t write this settings to the appl.config file

  • http://codebetter.com/members/pvanooijen/default.aspx pvanooijen

    By Using nHibernate instead :)
    Which is another way of saying I don’t know ? Doesn’t EF have a connection object somewhere ? Set the connectionstring there before EF will actually open the connection.

  • Lord karnash

    and how i can change the connection string in EF .. :S
    < ?xml version="1.0" encoding="utf-8"?>




    i wanna change the Connection string .\sqlexpress to another string or IP or name to the server machine ok.. :D
    (fer_bolivia@hotmail.com)

  • Denz

    i have tried doing that instructions one by one i have got the connection string, vb does not return error code but the connection state always return “0″ zero which meant not connected or no connection was established.

    i have search vbcode website and found nothing that really worked, may i request from any one that has a code version of the actual working vbcode regarding connecting firebird database?

    Anything that may be embedded or not will be deeply appreciated pls send it to lukadun@yahoo.com or just post it here so that anyone that shares the same problem can view.

    thank you

  • voiger

    thank you! Beautiful solution!

  • New To 2.0

    Thanks very much, this approach requires very minimal effort and works very well.

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    The source of the connectionstring could be anywhere, up to you what fits best. I wouldn’t rely on the user’s setting for your scenario as that assumes every user having it’s own database. In case there are two users in the same region you have a configuration redundancy which will hit back. Sooner or later.

    Whatever way you are going to do it: just keep all setting together in one (custom) xml file.

  • KKH

    Someone asked about the scope of your setting previously, saying that “Shouldn’t the scope of your setting be “user” instead of “application”? Otherwise if one user changes the setting, wouldn’t that affect all users of the application?” This is a problem that I encountered. Depending on the user’s region, he/she needs to access the region’s database. (All the region’s databases have the same table structure.) Any idea on how to do it? Thanks for your hlep.

    KKH.

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    To keep the app properly layered I would keep all knowledge about the connectionstrings in the assemblies which actualy use the db connection.

  • Ram

    Hi,

    It is a great Article and i tried to implement this solution in my project. But i have lost in between. i have a webservice project which refers three assemblies (actual service layers) which has DAL.cs and table adapter. Each assemblies has its owen app.config file. Each table adaper has its own designer.cs and property called “connectionstring” to initiate the connection.

    In this situation, Where should i suppose to call your SetConnectionString() method. i tried to set the connstring in of my assemblies…but other assemblies still looking/taking values from app.config? please can you elaborate it? It will be very much helpful to resolve my issues.

    ~Ram

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    That’s a bad tutorial becauses it builds a monolothic site. As said in the post and described here in detail at http://codebetter.com/blogs/peter.van.ooijen/archive/2006/04/25/143308.aspx

    The tutorial does not work when your DAL is in a separate assembly (class library) Which was exactly the reason for this solution

  • Warren

    Here is a walk through on how to do it.. http://asp.net/learn/data-access/tutorial-72-vb.aspx

  • George

    I’ve had the same problemm with getting my Data Access Layer to read it’s ConnectionString from the web.config file. I tried different solutions, an even got afew of them to work. But this is, by far, the most elegant in it’s simplicity and logic. Thank you for posting it.

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    - Ah, I see your first point. It has to do with the VB My.Settings object. Which is just a helper which wraps several things which can also be accesed directly. Apparantly this connectionstring is exposed as a readonly property. Both things point at the same connectionstring.

    - Sorry about not clearly formulating my second answer. You shoud open the connection as late as possible. Before calling the open method the connectionstring has to be set. Your approach sounds fine to me.

  • Peter

    Peter,

    My 1st point is that if I simply use the My.Settings.NorthwindConnectString = the new connection string or My.Settings.Default.NorthwindConnectString = the new connection string, I will get a readonly error. But using the My.Settings.Default(NorthwindConnectString) = the new connectoin string is ok. It seems to me that it works because the compiler does not yield error since it did not evaluate My.Settings.Default(NorthwindConnectString) to My.Settings.Default.NorthwindConnectString. My interpretation may not be correct since I’m new to VB and .net but I assume that My.Settings.Default(NorthwindConnectString) is like an array element and it actually points to My.Settings.Default.NorthwindConnectString.

    What is the reason to set the connection string as late as possible? My thinking is that if I set the connection string as early as possible, I don’t need to worry that any future codings (firing sql) will be coded before the correct connection string is assigned.

    Peter

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    The server cache can be a little slow, I deleted the second copy of your question.

    - I miss your first point. The connectionstring is in the settings array, it’ snice to be able to use a name as indexer, allthough that is prone to spelling errors.
    Which will not show up till runtime.
    - The correct connection string has to be set before the connection is opened. Which is, in .net code, allways as late as possible. Just before your start firing sql at the db. No sooner

  • Peter

    Thanks. Your solution works. My 1st question is whether this is sort of a backdoor way to get around the readonly issue using the indexer. Since the compiler cannot evaluate the property, it just provides a warning. My 2nd question is where I should change the connection string. I want to do it right after My.Settings are populated with value so the new connection string will be used by entire application. I’m new to .net and don’t know the execution sequence of .net application and don’t know how to trace/profile to see every event/method executed.

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    The connectionstring, conatianing the db-name, should be set before opening the connection, that is calling it’s open() method.

  • sudhakar@3kal.com

    hello peter,

    we have the same problem, we have created a class library for my DAL. we have the scenario like each client has one database. Now I have to pass the DBname each time client (User) request for data from database. where exactly I have to place my code to take the database name before it hit the database.

    we are calling DAL methods from web application.

    is your solution suitable for us ?
    suggest me the best solution ?

    thank you.

  • Carlos

    Peter

    I had the same problem, wow the solution you found is very good, now i have one connectionstring in the settings.settings binding but i really use whatever i need from my app.config, in my case QA connections, and production connection, thank you.

    Carlos

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    Ron, it depends. In case all users are sharing the same db the scope is right. In case each user has his/her own db you are right :)

  • Ron

    Peter,
    Shouldn’t the scope of your setting be “user” instead of “application”? Otherwise if one user changes the setting, wouldn’t that affect all users of the application?

    Thanks,
    Ron

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    Don’t take the classname Connectionstringsetter literally. Fill in the class to which you added the method.

    Affter adding setting to the project the Settings object should be available in the namespace of your project. Check you namespaces.

  • HSBF LEWE

    I tried your code to amend app.config, and I’v got two errors:

    Error 1 The name ‘ConnectionStringSetter’ does not exist in the current context C:\Documents and Settings\My18\My Documents\Visual Studio 2005\Projects\WindowsApplication23\WindowsApplication23\Form1.cs 45 13 WindowsApplication23

    and

    Error 2 The name ‘Settings’ does not exist in the current context C:\Documents and Settings\Arabital18\My Documents\Visual Studio 2005\Projects\WindowsApplication23\WindowsApplication23\Form1.cs 54 13 WindowsApplication23

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen
  • http://jshipleyATmayfranDOTcom Jim Shipley

    Would you be able to post a VB.net version of this code?

    Thanks!
    Jim

  • Roh Collins

    Many Thanks.

    Didn’t quite click initially that you had to use the indexer directly (should have looked at your code more closely the first time), I originally assumed that because the connection string was saved as an ApplicationScoped setting and that only a getter was autogenerated in the Settings class, that I couldn’t set this value from code. Duh! Got there in the end though.
    Cheers.

  • http://codebetter.com/blogs/peter.van.ooijen/default.aspx pvanooijen

    Peter,

    you have to make the call only once. Canidates for the place are
    - You initilization routine. In case you have one
    - Application_start in global.asax
    - A static constructor in a class refrenced before you’re going to hit the db
    - A constructor or overriden endinit event in the dataset see http://codebetter.com/blogs/peter.van.ooijen/archive/2006/04/25/143308.aspx
    That will be hit more than once, but you’re always sure it is hit.

    Pick your choice

  • Peter Mounce

    “Now I make one call to this method, passing it a value read form the web.config”

    Where do you make the call to the method? I’m in a similar position – wanting to only maintain one CS, in web.config, but my DataSets live in a lower-tier class library. That class library doesn’t have anything but DataSets – no classes. So where does one make the call? Application_Start?