none
Connection Strings - App.Config vs. Settings.Settings RRS feed

  • Question

  •  

    So here's some background:  There is/was the possibility that we would end up using two tracking systems for calls from various customers.  We also have another database for our customer ePortal, which ties in some information from our current main tracking database.  To ease access to all of these I've been working on a library to pull everything together and present it for purposes of managing/creating calls through this interface.  This library pulls connection strings from app.config, with the idea that the connections will be defined by whatever application is using it at the time, so that we can ease testing and easily handle database moves or changing networks. 

     

    I'm retrofitting a windows service to make use of this library, but some of the data it will need to access will come directly from the databases since it is application specific. 

     

    The problem:  When creating the datasets for the application specific data, it creates it's own connection strings in app.config with some automatically qualified path like: AppName.Properties.Settings.ConnectionStringName rather than just ConnectionStringName.  I can't seem to get the data sets to pick up the normal connection strings I entered... which leaves me having to maintain two sets of connection strings to the same databases...

     

    How do I fix this?

    Thursday, August 9, 2007 6:29 PM

All replies

  • Hi,

    I am also having the same kinda problem.

    I have app.config file where i have already mentioned connection string.

    In my project i need to connect two database alternately.

    for that each time i have to stop the application and i have to go to the app.config file and have to change the connection string and then will have to run it again.

     

    What i want is i want to have 2 textboxes on the application ui and i will put each time connection strings userid and password and application should take this for reseting the app.config file to connect the desired database.

     

    Is it possible? then please suggest me how i can accomplish this.

     

    Thanks in advance

    Rupesh Kumar Tiwari

    Monday, August 20, 2007 7:43 AM
  • In your app.config you can create as many connections as you want using appSettings tag
    see code below.

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
    <appSettings>
    <!--SQL Server 2005-->
        <add key="AppName.ConnectionStringName" value="data source=NAME\SQLEXPRESS;initial catalog=NAME;integrated security=SSPI;persist security info=False;packet size=4096"/>
       
    I am trying to figure out value for MS Access DB This Connection does not work.

    <add key="AppName.ConnectionStringName1" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\DataSource\Maintenance.mdb;Persist Security Info=True"/>
          
      </appSettings>
    </configuration>

    In your code can you use this to read the values.

    Imports System.Configuration

    Private m_appsrConfigReader As AppSettingsReader = New AppSettingsReader

    Private sub GetDataBaseType(bytDatasourceType as byte)
               

                Select Case bytDataSourceType
                    Case g_enumDataSourceType.SQLServer
     m_sqlConnection.ConnectionString = m_appsrConfigReader.GetValue("AppName.ConnectionStringName", "".GetType()).ToString()
                    Case g_enumDataSourceType.MSAccess
    m_sqlConnection.ConnectionString = m_appsrConfigReader.GetValue("AppName.ConnectionStringName1", "".GetType()).ToString()

                End Select

                End Select
    End sub
    I declared enumerator enumdataSourceType to distinguish between two connections and call

        Public Enum g_enumDataSourceType As Byte
            SQLServer = 1
            MSAccess = 2
        End Enum

    I think this help.

    Friday, August 24, 2007 1:46 PM
  • In your app.config you can create as many connections as you want using appSettings tag
    see code below.

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
    <appSettings>
    <!--SQL Server 2005-->
        <add key="AppName.ConnStringName1" value="data source=NAME\SQLEXPRESS;initial catalog=NAME;integrated security=SSPI;persist security info=False;packet size=4096"/>
       
    I am trying to figure out value for MS Access DB This Connection does not work.

    <add key="
    AppName.ConnStringName2" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\DataSource\Maintenance.mdb;Persist Security Info=True"/>
          
      </appSettings>
    </configuration>

    In your code you can use this to read the values.

    Imports System.Configuration

    Private m_appsrConfigReader As AppSettingsReader = New AppSettingsReader

    Private sub GetDataBaseType(bytDatasourceType as byte)
               

                Select Case bytDataSourceType
                    Case g_enumDataSourceType.SQLServer
     m_sqlConnection.ConnectionString = m_appsrConfigReader.GetValue("AppName.ConnStringName1", "".GetType()).ToString()
                    Case g_enumDataSourceType.MSAccess
    m_sqlConnection.ConnectionString = m_appsrConfigReader.GetValue("AppName.ConnStringName2", "".GetType()).ToString()

                End Select

               
    End sub
    I declared enumerator enumdataSourceType to distinguish between two connections and call

        Public Enum g_enumDataSourceType As Byte
            SQLServer = 1
            MSAccess = 2
        End Enum

    I think this help.

    Friday, August 24, 2007 1:49 PM
  • Actually to read the connection strings you can just do this: ConfigurationManager.ConnectionStrings["StringName"].ToString();

    Now, that said I think he may be having a problem with the DataSets, which don't seem to like dynamically changing their connection strings.  I've moved away from using them due to that...
    Friday, August 24, 2007 2:54 PM
  • This really ought to be a FAQ.

     

    What the DataSet is doing is not an impenetrable mystery, it just takes a systematic search to figure it out.  (And it's so worth figuring this out if you're seriously considering discontinuing the use of the DataSet, because the DataSet is incredibly useful.)

     

    Do this:  Create a new project.  Add a DataSet to it.  Right-click on the DataSet and add a TableAdapter.  You'll be asked to create a connection string; connect to AdventureWorks or Northwind or pubs or whatever test database you have lying around.

     

    When it asks you if you want to save the connection string to the application configuration file, check Yes, and give it a name.  I'm calling mine MyTest, because I'm very original.  Finish building the TableAdapter with the SQL statement of your choice.

     

    Okay, now let's take a look at what has been created:

     

    If you look in Solution Explorer under your project, under Properties you'll find Settings.Settings.  Double-click on this, and you'll see that your test connection string shows up in the grid.

     

    You'll also see that an app.config file has been added to your project, containing something like this:

     

    Code Snippet

    <connectionStrings>

    <add name="WindowsApplication1.Properties.Settings.MyTest" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksLT_Data.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"

    providerName="System.Data.SqlClient" />

    </< FONT><connectionStrings>

     

    There's a reason it's been given that ridiculous name, and we will uncover that reason as we proceed.

     

    Now look at the TableAdapter's InitConnection method (in the Designer code for the DataSet).  You'll see something like this:

     

    Code Snippet

    private void InitConnection() {

       this._connection = new System.Data.SqlClient.SqlConnection();

       this._connection.ConnectionString = global::WindowsApplication1.Properties.Settings.Default.MyTest;

    }

     

    Finally, look in the Designer code for your Settings.  You'll find something like this:

     

    Code Snippet

    [global::System.Configuration.ApplicationScopedSettingAttribute()]

    [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]

    [global::System.Configuration.SpecialSettingAttribute(global::System.Configuration.SpecialSetting.ConnectionString)]

    [global::System.Configuration.DefaultSettingValueAttribute("Data Source=.\\SQLEXPRESS;AttachDbFilename=\"C:\\Program Files\\Microsoft SQL Server\\" +

    "MSSQL.1\\MSSQL\\Data\\AdventureWorksLT_Data.mdf\";Integrated Security=True;Connect T" +

    "imeout=30;User Instance=True")]

    public string MyTest {

    get {

    return ((string)(this["MyTest"]));

    }

    }

     

     

    So, here's what's happening. 

     

    Whenever this table adapter's connection first gets used (look back in the code, you'll see that it implements lazy evaluation), it's getting its connection string from the MyTest property of WindowsApplication1.Properties.Settings. 

     

    When you create a setting in the Settings designer, what you're really doing is telling the designer to generate a property in the static Settings class.  The Settings class derives from ApplicationSettingsBase, and ApplicationSettingsBase is magic.  (Actually, ApplicationSettingsBase is an example of what you can use reflection for if you really, really understand it.)

     

    Settings gets connection strings from the application configuration file.  How does it know to do this?  Through reflection.  The ApplicationSettingsBase constructor looks at the attributes on all of the derived class's properties.  The attributes on the MyTest property tell ApplicationSettingsBase that the property is an application-scope setting (so it will look to the application configuration to get its value), that it's a connection string (so it will look in the connection strings section of the config file), and that if there's no config file, what default value it should use (which is why your program will still work even if you delete its configuration file.).  But how does it know which connection string?  Well, it uses the fully namespace-qualified name of the property that all those attributes are decorating, which, in our example, is WindowsApplication1.Properties.Settings.MyTest.

     

    And that's how it is that the elaborately-named setting in app.config gets into your table adapter.

     

    With all this in mind, let's look at the ways you can change the connection string that a table adapter is using.

     

    The obvious way is by changing the value in the application configuration file.  That's what this whole rigamarole is in support of, after all.  And this is the right answer in the great majority of cases.  But you wouldn't be reading this post if that worked for you.

     

    Another way is to create two connection strings in your Settings, and select the appropriate one in the table adapter's properties.  This definitely has its uses - for instance, if you need to use a different connection at design time than the one you're using at debug time.  (It happens.)  But you can't change this property at runtime, because the property isn't used at runtime - it's used when you save the DataSet in the designer and the designer generates that code in InitConnection().

     

    A third way is to set the connection explicitly in code for each table adapter.  You may have looked at a table adapter under Intellisense hoping to see a Connection property, and despairing because it's not there.  That's because it's an internal property. 

     

    But you can change that:  under the Connection property of the table adapter, change the Modifier from Internal to Public.  Now you can set the connection explicitly whenever you create a table adapter:

     

    Code Snippet

    MyDataSetTableAdapters.SalesOrderHeaderTableAdapter a =

       new WindowsApplication1.MyDataSetTableAdapters.SalesOrderHeaderTableAdapter();

    a.Connection = new SqlConnection(connectionString);

     

    There you go:  you can now set the connection string at runtime.  Problem solved.

     

    Except you have to do it every time you create an instance of the table adapter.  And you have to do it for every table adapter.  And if you forget to do it, the table adapter will go back to using the value from the application configuration file, or the default value in the Settings class if that's not present.  That's a lot of failure modes. 

     

    But it's not hard to eliminate those failure modes.  As the old maxim goes, any software problem can be solved with another layer of indirection, and this one's pretty sweet:

     

    Code Snippet

    public static class Adapters

    {

       public static MyDataSet.SalesOrderHeaderTableAdapter SalesOrderHeader;

       // other adapters go here

     

       public static void Init()

       {

          string cs = ConfigurationManager.ConnectionStrings[name].ToString();

     

          SalesOrderHeader = new MyDataSet.SalesOrderHeaderTableAdapter();

          SalesOrderHeader.Connection = new SqlConnection(cs);

          // initializing other adapters goes here

       }

    }

     

    I think this is the best solution of those I'm listing here.  It creates a very simple idiom for you to use in your code.  For instance, all the ADO.NET sample code you've ever seen fills a DataTable with ugly code like this:

     

    Code Snippet

    MyDataSet.SalesOrderHeaderTableAdapter a =

       new MyDataSet.SalesOrderHeaderTableAdapter();

    a.Fill(this.SalesOrderHeader);

     

    But here's what you do once you've created the static class:

     

    Code Snippet

    Adapters.SalesOrderHeader.Fill(this.SalesOrderHeader);

     

    I can't tell you how many headaches this eliminates.

     

    If you don't want to go back through your code and retrofit this fine solution to your problem, you can just change the value that the Settings class is giving to the generated table adapter code.  Not directly, though.  The connection string property in Settings is read-only.  If you do this:

     

    Code Snippet

    WindowsApplication1.Properties.Settings.Default.MyTest = myNewString;

     

    you'll get a run-time error. 

     

    But that property accessor is just a wrapper around an array element.  And that array (the Item proprerty of ApplicationManagerBase) is public.  So you can do this:

     

    Code Snippet

    WindowsApplication1.Properties.Settings.Default["MyTest"] = myNewString;

     

    That will change the connection string for every table adapter in your DataSet that's getting their connection string from the MyTest setting - assuming, of course, that you haven't created and used the table adapter before you make this change.

     

    One thing you have to watch out for here is ApplicationSettingsBase.  It has a Reload() method that re-reads the settings.  If something calls that method, you're in trouble.  (This method is what makes it possible to update a running ASP.NET application's configuration without restarting it.)  Fortunately, in a regular Windows application, that's not going to happen unless you do it yourself. 

     

    I'm sure there are even more ways of skinning this cat, but this is more than enough for one night.  I hope this helps.

    • Proposed as answer by vijayk Friday, July 9, 2010 11:42 AM
    Sunday, August 26, 2007 8:31 AM
  • Telos, this may or may not help, but here is some related info: http://rajmsdn.wordpress.com/

    Wednesday, December 9, 2009 9:30 PM
  • In your app.config you can create as many connections as you want using appSettings tag
    see code below.







    I am trying to figure out value for MS Access DB This Connection does not work.






    In your code can you use this to read the values.

    Imports System.Configuration

    Private m_appsrConfigReader As AppSettingsReader = New AppSettingsReader

    Private sub GetDataBaseType(bytDatasourceType as byte)


    Select Case bytDataSourceType
    Case g_enumDataSourceType.SQLServer
    m_sqlConnection.ConnectionString = m_appsrConfigReader.GetValue("AppName.ConnectionStringName", "".GetType()).ToString()
    Case g_enumDataSourceType.MSAccess
    m_sqlConnection.ConnectionString = m_appsrConfigReader.GetValue("AppName.ConnectionStringName1", "".GetType()).ToString()

    End Select

    End Select
    End sub
    I declared enumerator enumdataSourceType to distinguish between two connections and call

    Public Enum g_enumDataSourceType As Byte
    SQLServer = 1
    MSAccess = 2
    End Enum

    I think this help.


    Now I have a more clear idea about this.
    Tuesday, January 25, 2011 10:38 PM
  • Hi All,

    Is their any way i can change the tag to be chosen based on some condition. I am using xml file as a data source to my tests.I have say two tags in the xml file,my xml somehow luks like below:

      Data.xml file luks like below:

    <GraphicalTopUp>
          <!--MOVISTAR Spain Prepaid-->
          <TOP_TC_449S>
            <Carrier>MOVISTAR Spain Prepaid</Carrier>
            <Amount>5 €</Amount>
            <PhoneNo>649818912</PhoneNo>
          </TOP_TC_449S>

    <!--VODAFONE Spain Prepaid-->

          <TOP_TC_449>
            <Carrier>VODAFONE Spain Prepaid</Carrier>
            <Amount>15 €</Amount>
            <PhoneNo>610654940</PhoneNo>
          </TOP_TC_449>

    </GraphicalTopUp>

    One testmethod is binded with this data file as below:

    [DeploymentItem("FxOnline_TopUp\\DataSheet\\Data.xml"), DataSource("Microsoft.VisualStudio.TestTools.DataSource.XML", "|DataDirectory|\\Data.xml", "TOP_TC_449", DataAccessMethod.Sequential), TestMethod]
     public void TOP_TC_449()
    {

    //Now inside my test method their are two coditions,based on which the dataset of particular tag shold be picked.

    if(codition 1)

    {

    we can use dataset under tag TOP_TC_449S to pass as a testcontext 

    if (condition 2)

    {

    we can use dataset under tag TOP_TC_449 to pass as a testcontext 

    }

    }

    Please help 

    Tuesday, June 19, 2012 6:50 AM