How to change connectionstrings that are readonly in app.config

Ian

The only way I've found of doing this is via the .Connection.ConnectionString property of each TableAdapter.  

The Wizard embeds the ConnectionString as an Application Setting.  Although it can then be altered, I have only been able to do this at design time.  I have not been able to find any way of getting the Wizard to save it as a User Setting, or to retain amendments I made to the designer code to try to change it to a User Setting.  It's all done in the background - with Designer code and Private properties and methods - and fiddling with those either causes crashes or doesn't persist through a Build.  

But there is a "loophole".  Each TableAdapter's .Connection property is declared, in the DataSet.Designer code file (in the DataSetTableAdapters Namespace), as Friend, and with a Set as well as a Get.  So a sub on these lines for each TableAdapter (or one sub with a number of lines covering each of them)

    Private Sub changeConnection()
        myTableAdapter.Connection.ConnectionString = "<myUserConnectionString>"
    End Sub

called before any use is made of them allows the default, Application Setting value/s to be overwritten.  As the only access I can find is via the TableAdapter it means that the change (probably the same change) has to be made for each.  But I think this sort of "workaround" is essentially the line that you'll have to take.  Where and how you store and retrieve the value/s that you use for <myUserConnectionString> is up to you.

I'll be interested to hear if anyone else has found a more "pukka" approach.

Roger

Thanks Roger

I have been using this approach up til now but I recently wasted time looking at why an app worked fine in development and not in live envionment only to find I'd forgotten to include the redirect on a newly added table.

I do not understand why Microsoft allows us to change so many parameters and system attributes in VB.NET but decided we're not to be trusted changing connnectionstrings!!

Ian

Hi All

I have found a workaround to this but I'm still looking for a better solution (i.e changing the app.config files)

The solution I have used involves changing the settings.desogner.vb code which is created by the dataset design tool. The only reason my.settings.<connection string> are read only is that the designer only creates a "Get" property for them, its a 2 line change to add the "Set" property (since all we're doing is storing a string value there!). I then set the value to what I want in the settings.vb.settingsloaded function.

Ok it's not perfect as the settings.designer.vb gets re-written every time the designer is changed but you'll know if that's happened as the connectionstring will become readonly again and your code won't compile.

Obviously you'll have to do this for each connectionstring if you have more than one and don't forget to remove the "readonly" value from the property command

Ian

i know this is in C# but this is what i use to save changes to the app.config during runtime:

what you can do is enable your application to read from a static variable which contains your connection string and allow you application to modify this variable during runtime and before your application exits save the value of the connection string back into the app.config for use next time round.

using System;
using System.Xml;  
using System.Configuration;
using System.Collections;
using System.Reflection;  
using System.Diagnostics ;

namespace BDS.Core.Config
{

      public enum ConfigFileType
      {
            WebConfig ,
            AppConfig
      }

      public class AppConfig : System.Configuration.AppSettingsReader
      {
            public string  docName = String.Empty;
            private  XmlNode node=null;

            private int _configType;
            public   int ConfigType
            {
                  get
                  {
                        return _configType;
                  }
                  set
                  {
                        _configType=value;
                  }
            }

            public bool SetValue(string key, string value)
            {
                  XmlDocument cfgDoc = new XmlDocument();
                  LoadConfigDoc(cfgDoc);
                  // retrieve the appSettings node
                  node =  cfgDoc.SelectSingleNode("//appSettings");
   
                  if( node == null )
                  {
                        throw new System.InvalidOperationException( "appSettings section not found");
                  }
   
                  try
                  {
                        // XPath select setting "add" element that contains this key    
                        XmlElement addElem= (XmlElement)node.SelectSingleNode("//add[@key='" +key +"']") ;
                        if (addElem!=null)
                        {
                              addElem.SetAttribute("value",value);    
                        }
                              // not found, so we need to add the element, key and value
                        else
                        {
                              XmlElement entry = cfgDoc.CreateElement("add");
                              entry.SetAttribute("key",key);
                              entry.SetAttribute("value",value);
                              node.AppendChild(entry);    
                        }
                        //save it
                        SaveConfigDoc(cfgDoc,docName);
                        return true;
                  }
                  catch
                  {
                        return false;
                  }
            }
 
            private void SaveConfigDoc(XmlDocument cfgDoc,string cfgDocPath)
            {
                  try
                  {    
                        XmlTextWriter writer = new XmlTextWriter( cfgDocPath , null );
                        writer.Formatting = Formatting.Indented;    
                        cfgDoc.WriteTo( writer );    
                        writer.Flush();
                        writer.Close();  
                        return;
                  }
                  catch
                  {
                        throw;
                  }
            }
 
            public bool RemoveElement ( string elementKey)
            {
                  try
                  {
                        XmlDocument cfgDoc = new XmlDocument();
                        LoadConfigDoc(cfgDoc);
                        // retrieve the appSettings node
                        node =  cfgDoc.SelectSingleNode("//appSettings");  
                        if( node == null )
                        {
                              throw new System.InvalidOperationException( "appSettings section not found");
                        }  
                        // XPath select setting "add" element that contains this key to remove  
                        node.RemoveChild( node.SelectSingleNode("//add[@key='" +elementKey +"']") );
   
                        SaveConfigDoc(cfgDoc,docName);
                        return true;
                  }
                  catch
                  {
                        return false;
                  }
            }


            private XmlDocument LoadConfigDoc( XmlDocument cfgDoc )
            {
                  // load the config file
                  if(  Convert.ToInt32(ConfigType)==Convert.ToInt32(ConfigFileType.AppConfig))
                  {
   
                        docName= ((Assembly.GetEntryAssembly()).GetName()).Name;
                        docName +=   ".exe.config";
                  }
                  else
                  {
                        docName=System.Web.HttpContext.Current.Server.MapPath("web.config");
                  }
                  cfgDoc.Load( docName );
                  return cfgDoc;
            }

      }
}

// this is how you use it

AppConfig ac = new AppConfig();
ac.ConfigType = (int)ConfigFileType.AppConfig;
appConfigDataSet = ac.GetAppSettings();
// set the value of the ConnString key
ac.SetValue("ConnString","this is my connection string");

REgards,
Ready1

Hi

Thank you for post. I've run it through a c# to vb.net convertor but it appears to be missing the .getsettings function which is described in your "how to use" instructions.

Ian

Many thanks - that works a treat. Hopefully it'll help out all the other web searchers looking for the answer to this problem

Ready1

My thanks too.

Roger

no probs mate glad to be of some assistance

Regards,
Ready1