locked
Programmatically setting a ConnectionString at runtime RRS feed

  • Question

  • User523891944 posted

    Hi again, forum.

    Here's the deal - I want to have identical files on my dev machine and the live webserver, right down to the web.config. This means my application must know connection strings for "both" environments (dev environment being any box with sqlexpress and my database installed).

    In this post I mentioned a custom SqlDataSource control that allows me to do this with my "normal" data source by supplying both connection strings to the datasource control, and letting it decide which one to use based on whether Request.IsLocal is true or not. This approach seems to be working well for this simple data-driven website so far.

    So, whats the problem?

    I'm using the managed roles and permissions ASP.NET 2 provides, and I'm running into the same problem again. I've merged the permissions database into my schema using aspnet_regsql.exe. This means my application already has both (local and live) connection strings it needs to connect. But, it seems that a connection string named "localSqlServer" must exist for the permissions stuff to know what to connect to, or, alternatively, even more extensive editing of the web.config file.

    OK, so, I figure I can use the same approach as with the SqlDataSource control, and set the value of the localSqlServer ConnectionString at runtime, right? So, I pull out Global.asax and...

        void Application_Start(object sender, EventArgs e){
    string localCon = ConfigurationManager.ConnectionStrings["Dev"].ConnectionString;
    string liveCon = ConfigurationManager.ConnectionStrings["Live"].ConnectionString;
    ConnectionStringSettings css = new ConnectionStringSettings();

    css.Name = "localSqlServer";
    if (Request.IsLocal){ css.ConnectionString = localCon; }
    else{ css.ConnectionString = liveCon; }

    ConfigurationManager.ConnectionStrings.Remove("localSqlServer");
    ConfigurationManager.ConnectionStrings.Add(css);
    }

      Error message. Hmm, it's harder than I thought; I guess Application_Start fires before the Request exists, somehow. So, just to test it out, I tried replacing "Request.IsLocal" with "true".

    Now I get a much more sinister error message: System.Configuration.ConfigurationErrorsException: The configuration is read only.

    Yikes. OK, that's where I'm at. Maybe I'm going about this the wrong way, but as I said my goal is to have identical files on my dev machine and the live webserver, including the web.config -- to keep a long story from growing any longer, does anyone know of a way I can accomplish this using ASP.NET's built in security management? I wouldn't be adverse to extending some security provider if the changes wouldn't have to be too drastic, if thats what it takes, but I'm hoping someone can point me in the right direction before I set off to do that.

    Well, thanks in advance to anyone who can help.

    Thursday, December 7, 2006 11:31 PM

All replies

  • User523891944 posted
    oops, I meant this post (the one where I mention a custom SqlDataSource control).
    Friday, December 8, 2006 12:09 AM
  • User523891944 posted

    Hi again -

    Why am I not getting any responses? 

    Am I posting in the wrong section of the forum, or the wrong forum entirely? Am I coming off sounding rude or impolite? Does my breath stink?

     I see a lot of people getting help on these forums, but I'm not having any luck at all. I would really appreciate a response to this, even if it's something along the lines of "no you can't do that" or "you're going about it all wrong" or even "gee, I have no idea..."


     

    Thursday, December 21, 2006 10:04 PM
  • User-1211370683 posted

    I looked on the code though Reflector, and it looks like the only work around is to get an object ConfigurationManager.ConnectionStrings, and with a help of reflection set the value of private variable "bReadOnly" to "true".

    I haven't tried it out, but I'm sure it will solve your problem.
     

    Monday, March 31, 2008 5:31 AM
  • User-1254308814 posted

    Why wouldn't you just set the configSource="con.config" on the connection strings element in the web.config and have separate 'con.config' files containing the local and server connection strings respectively on both the server and client accordingly?

    On the dev
    
     <connectionStrings>
            <add name="ConnectionName" connectionString="<dev connection>..." providerName="System.Data.SqlClient" />
        </connectionStrings>
    
    On the server
     <connectionStrings>
            <add name="ConnectionName" connectionString="<server connection>..." providerName="System.Data.SqlClient" />
        </connectionStrings>
    


     

     

    Saturday, July 11, 2009 4:26 AM
  • User1966326256 posted

    I guess, because then he would have to keep to different web.config files and cannot simply synchronize/update the server project in one go. At least, that's my reason. ;-)

    Friday, January 24, 2014 7:21 AM