none
How to dynamically change connection string in generated dataset class?

    Question

  • I have a project with database classes which are generated from database objects. That is I add a dataset to the project and drag and drop a database object onto it. The problem with this is that I have to give a connectionstring which is stored in the code. This is ok while developing the application. But runtime I want to use another connectionstring, both for security reasons and because each user of the application will correspond to a database user (ms sql) and shall have a corresponding connectionstring. Is there an easy way to do this without having to restore to plain all datasets and do everything myself?

    • Moved by VMazurModerator Thursday, December 10, 2009 11:08 AM (From:ADO.NET Data Providers)
    Monday, October 17, 2005 11:08 AM

All replies

  • Of course you can.
    You  can get it at run-time from Web.config strings or you can get it from your own configuration file. Just get your connection string before opening the SqlConnection (or IDbConnection) object.

    CESAR DE LA TORRE
    [MVP - XML-WebServices]
    Software Architect

    Renacimiento 
    www.renacimiento.com
    • Proposed as answer by debbie5777 Monday, February 27, 2012 1:25 PM
    Monday, October 17, 2005 6:02 PM
  • Thanks for the answer, but I think that one of us misunderstands the other, but I am not sure whoBig Smile.

    What I have in my code is something like:

    menyDS = new MenySel();
    adapter =
    new MenySelTableAdapter();
    adapter.Fill(...);

    where MenySel is a typed dataset and MenySelTableAdapter the corresponding adapter. Both are generated when dragging a stored procedure from a database onto a dataset in the designer. If I look in the generated code for MenySelTableAdapter I see the following:

    private void InitConnection() {
       
    this._connection = new System.Data.SqlClient.SqlConnection();
       this._connection.ConnectionString 
          =   
    global::DBAccess.Properties.Settings.Default.TestConnectionString;
    }

    where Test is the name of the database. This connectionstring is made at design time. And MenySelTableAdapter does not have a public method to change it. The Settings class also only has a set method, and I don't want to edit generated files anyway.

    The solution I have tried now, is (since MenySelTableAdapter is defined using "partial") to have an extra file with a public method SetConnectionString for all my generated adapters. That way I don't need to worry if I regenerate the datasets or Visual Studio in some way mess up generated files (it has been known to happen...). So now my code looks like:

    menyDS = new MenySel();
    adapter =
    new MenySelTableAdapter();
    adapter.SetConnectionString(Session["connectionstring"]);
    adapter.Fill(...);

    (This is on a webservice, hence the Session-part.)
    I don't know if this is an optimal or even a very good solution, so I would be happy for comments.

    Thursday, October 20, 2005 8:37 AM
  • Ok, so if you are using TableAdapters, even having the fact that there is a hard-coded conn-string within it, it will be used just in case that your conn-string defined within the Web.config or App.config is not found.
    So, unless you want to change it dynamically at-runtime, normally what you do is just change the conn-string defined within the Web.config (it is first updated by TableAdapter wizard), like this:

    <connectionStrings>

    <add name="MyProject.Properties.Settings.NorthwindConnectionString"

    connectionString="Data Source=MYSERVER;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=mypwd"

    providerName="System.Data.SqlClient" />

    </connectionStrings>

    Within TableAdapter code, you can see that it is  getting the ConnString from the Web.config, doing it like that:

    public string NorthwindConnectionString {

    get {

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

    }

    }

    And just in case Web.config is  wrong and it does not find it, it will use the Hard-coded connstring which is defined using a .NET Attribute like this:

    [global::System.Configuration.DefaultSettingValueAttribute("Data Source=MYSERVER;Initial Catalog=Northwind;Persist Security Info=True;Use" +"r ID=sa;Password=mypwd")]

    1.-
    So, if you want to change it dynamically at-runtime, JUST in that case, what you are doing with "PARTIAL CLASS" would be right, OR you can just change the TableAdapter 'ConnectionModifier' property to 'public', and use its own published conn-property like "myTableAdapterObject.Connection".

    2.-
    BUT, normally, when you move your development App. to a production-stage, you just have to change the Web.config Conn-string.

    CESAR DE LA TORRE
    [MVP - XML-WebServices]
    Software Architect

    Renacimiento 
    www.renacimiento.com

    Thursday, October 20, 2005 9:44 AM
  • Thanks again, CesarSmile
    I can see that your solution is fine in most cases, but I actually want to change the connection string at runtime. The reason is that the user is interacting with the database via a webservice. When the user logs on to the webservice (gets a Session), there is a fixed connectionstring in the webservice that is used to check the users password in the database (this is the password to the application stored in a user table). But then if the user is approved, he also corresponds to an sql-user in the database, and the webservice fetches the sql-password for the user in the db and builds dynamically the connectionstring. The rest of the communication between the webservice and the database is done using this connectionstring. The reason that we want each user also to be an sql-user is that a lot of logic in the db (stored procedures, triggers etc.) is based on the knowledge of which sql-user is doing what. (The database was developed for other applications originally.)

    So in our case, as you say, I guess the solution is OK. However, I have no problem agreeing that the solution is a bit off-beatSmile
    Thursday, October 20, 2005 11:09 AM
  • I found a link on the forums:
    https://blogs.msdn.com/smartclientdata/archive/2005/07/25/443034.aspx

    Basically I had a situation where I wanted to change the connectionstring that M$ store when using the funky project data sources way of coding.

    All I did is added this sub to the settings.vb file (instructions how to view the setting code in included in that link above)

    Private Sub MySettings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded

    Me.Item("cnDatabase") = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Me.Item("UserSettingDatabaseLocation")

    End Sub
     
    Where cnDatabase is the name of the Connection you have used for your datasets, and UserSettingDatabaselocation was just a User setting that i have used, but you could set that to anything like your own XML based setting or anything.

    Good Luck!

    Wednesday, January 11, 2006 4:52 AM
  • Yesterday I googled a lot but I didn't find the solution I was lloking for.
    So this is my workaround,  it works GREAT and doesn't need an app.config at runtime:

    1) I set the settings.designer.cs build action to "none" (prev. "compile")
    2) create a fake Settings class where I can call Properties.Settings.Default.ConnectionString (get and set my connectionstring)
    3) I exclude the app.config from the project

    VS will use the connectionString in settings.settings at design-time, while run-time will be used the connection script in the "fake" class. No app.config is created.

    Feedbacks are wellcome!!

    PS: my first post ever... what a newbie ;)
    Thursday, January 19, 2006 4:22 PM
  • I find myself in almost the exact situation you describe. The solution I am currently trying to add code like the following to a partial class definition in a separate file. Since if you inspect the Connection property you can see that the connection shuffled out to many different places. This down side is that the Connection property is set twice once by the autgenerated code and then once for this custom method.

    namespace somepackage.MyDataSetTableAdapters
    {
        public partial class ClientsTableAdapter
        {
            public void SetCustomConnection(string connectionString)
            {
                System.Data.SqlClient.SqlConnection conn = new   System.Data.SqlClient.SqlConnection();
                conn.ConnectionString = connectionString;
                this.Connection = conn;
            }
        }
    }
    Wednesday, January 25, 2006 7:21 PM
  • I have the same issue.

    I've added a data source to my Windows Solution and I need to change at run-time to work online or offline. I have two connection strings in app.config and I need to switch between them.

    If you excluded the app.config from your solution then how can you switch between different DB servers that you don't know at design-time?

    I've tried the SettingsLoaded solution but is not working. On main form I can switch between online and offline work, I can change the value for connection string but somehow when I open a application form the connection string is resented to the initial value from app.config.

    Thank you,

    Wednesday, March 15, 2006 6:35 PM
  • "If you excluded the app.config from your solution then how can you switch between different DB servers that you don't know at design-time?"

    the custom tool used to edit desig-time the dataset class reads the connection strings from the app.config even if it's not included in the solution. 

    Wednesday, March 15, 2006 8:52 PM
  • I had this same problem a week or so ago and blogged about the solution I came up with:

    http://blogs.dev.bayshoresolutions.com/roger/archive/2006/04/10/3976.aspx

    Basically, I added code to the partial class which re-routed the call to the settings indexer to look for the correct name in the web.config.

    This is a really frustrating scenario though, especially if you are trying to convince someone to use a 2.0 dataset, but then you have to explain all of this to them.

    If anyone finds a better way I'd love to know!

    Thanks,
    Roger

    Wednesday, April 12, 2006 3:11 PM
  •  

    I want to use your solution for this frustration problem...

    Code you post the Fake Settings code... I am unsure how you can get or set on the Properties.Settings.Default.ConnectionString property?

    Thursday, July 13, 2006 2:21 PM
  •  gdexter wrote:

     I want to use your solution for this frustration problem...

    Code you post the Fake Settings code... I am unsure how you can get or set on the Properties.Settings.Default.ConnectionString property?

    internal sealed class Settings
    {
      private static Settings defaultInstance = new Settings();
      public static Settings Default
      {
         get { return defaultInstance; }
      }

      public string ConnectionString
      {
        
    get { return [my Property / GetMethod() returning the connection string];}
      }
    }

    Settings.Default.ConnectionString points to a method where I calculate/retrive my connection string.

     

    Thursday, July 13, 2006 3:38 PM
  • I am sorry to bother you again, I tried using the code provided modified a little bit but it still does not work.

     

    internal sealed class Settings

    {

    private static Settings defaultInstance = new Settings();

    public static Settings Default

    {

    get { return defaultInstance; }

    }

    /// <summary>

    /// Returns the datasource connection string provided by the caller

    /// </summary>

    public string McmsConnectionString

    {

    //This is a static object property set by the caller

    get { return (MCMSDataAccessComponents.CustomDataSource.ConnectionString); }

    }

    }

     

    No data is being bound to a UI control at runtime. When I do a data preview int the desigmer it works but at runitme when a pass a new connection string to the dll no data is bound.

    Am I missing an attribute?

    Any Ideas

     

     

    Thursday, July 13, 2006 6:39 PM
  • The name of the property you named McmsConnectionString should have the same name of the connection you are using design time.

    Go to the xsd designer view, select your TableAdapter and in the Properties window look at the Connection->Name property. It's something like "MyConnectionString (Settings)"
    (default is "ConnectionString (Settings)").
    The properties returning the connectionstring in the Settings class must have the same name:

    public string [MyConnectionString]

    {

    //This is a static object property set by the caller

    get { return (MCMSDataAccessComponents.CustomDataSource.ConnectionString); }

    }

     

    Friday, July 14, 2006 9:02 AM
  • Not sure if this will help, but under My Project settings I changed the scope of the connection string I was using to user so I could change the connection string during runtime

     Then it was a simple

     

    My.Settings.VisionConnectionString = ConnectionString.ConnectionString.ToString

    Seems to work for me.
    Friday, July 14, 2006 3:52 PM
  • You have to explain me how you managed to change the scope from application to user for a connectionstring. The settings.settings file won't allow you to do that. In fact variables of type 'ConnectionString' have their scoped default to Application and it is readonly.

     

     

    Wednesday, July 19, 2006 10:46 AM
  • After 5 hours of figuring out this stuff, I came up with this simple solution to update the app.config file at runtime with new string settings.  The only thing you have to know is the full name of the connection string.

    Hope this helps someone :-)


            public static void ChangeConnectionString(string strConn)
            {             
                 Configuration _config = ConfigurationManager.OpenExeConfiguration(
                            ConfigurationUserLevel.None);

                //the full name of the connection string can be found in the app.config file
                // in the "name" attribute of the connection string
                 _config.ConnectionStrings.ConnectionStrings[FULL NAME OF CONNECTION STRING HERE].ConnectionString = strConn;

                //Save to file
                _config.Save(ConfigurationSaveMode.Modified);
                
                //force changes to take effect so that we can start using
                //this new connection string immediately
                 ConfigurationManager.RefreshSection(_config.ConnectionStrings.SectionInformation.Name);
                Properties.Settings.Default.Reload();
            }
    • Proposed as answer by Dominic 75 Sunday, October 15, 2017 1:13 PM
    Thursday, July 20, 2006 12:47 PM
  • To solve this problem I changed the the ConnectionModifier in the TableAdapter's properties  to public. By default this is set to Assembly. This then exposed the connection object of the adapter where you can specify the connectionstring property.

    Note That when you change one of the adapter's ConnectionModifier property all adapters in the Dataset will be changed.

    Examples are always nice... I used this method with SQL Reporting Services 2005

    DsRptPaySlips.PaySummaryDataTable DtPaySummary = new DsRptPaySlips.PaySummaryDataTable();

    DsRptPaySlipsTableAdapters.PaySummaryTableAdapter AdptPaySummary = new DsRptPaySlipsTableAdapters.PaySummaryTableAdapter();

    AdptPaySummary.Connection.ConnectionString = StrDataseConnection;

    AdptPaySummary.Fill(

    DtPaySummary,

    Convert.ToDateTime(StrPaySummaryDateTime),

    new Guid(m_StrEmployeeUniqueID)

    );

    AdptPaySummary = null;

    ReportViewerX.LocalReport.DataSources.Add(new ReportDataSource(

    "DsRptPaySlips_PaySummary",

    DtPaySummary)

    );

    Ta

    Allan

    http://www.peopleroster.com

     

    Thursday, July 27, 2006 7:37 AM
  •  Noyabronok wrote:
    After 5 hours of figuring out this stuff, I came up with this simple solution to update the app.config file at runtime with new string settings.  The only thing you have to know is the full name of the connection string.

    Hope this helps someone :-)

    Thanks a lot!

    The main trick is to use RefreshSection. I was also very close to this solution - but was lack of this tiny secret :)

    I need to change connection strings in run-time because I'm use M$ Data Access App.Block, where the creation of database (and that's the thing you always start with) looks like DatabaseFactory.CreateDatabase(string argument), where this argument - is not the connection string itself (unfortunately!), but the name of the setting key in the "connectionStrings" section of the config-file:(( Well, my application starts with a login dialog, where user selects SQL Server, database, login method and (if not using integrated security) username/password. Constructing connection string from this dialog's fields is easy, but after that I need to store this connection string into the config-file - otherwise that CreateDatabase method will fail !

     

    Tuesday, November 21, 2006 1:25 PM
  • work great ~~ thx very much

     

    and the other easy method is :

    create different dns odbc  at the control pane

    then , it can easily to switch between each other

    Wednesday, December 6, 2006 10:20 AM
  • I had a very very bad experience with typed datasets 2.0, its PITA. There are a loyt of magic goin on, and sometimes I spent hours trying to figure out how things happened. I had a lot of frustrations using this and sometimes it makes me feel stupid and Noob to data access.

    This InitConnection method is very frustrating and I must admin this is one of my biggest mistake in my current project, the use pure typed datasets. Now am cleaning up the mess created with typeddatsets and I willonly use this for reporting and grid databinding and nothing more.

    Tuesday, December 12, 2006 12:57 PM
  • Could someone post an example of this in VB?

    Thanks.

    Wednesday, January 3, 2007 8:44 PM
  • Hello i dont know if is to late for an answer, and sory for my por english, normaly I speak french.

    So there is my solutions. Is to override the methode who go get de property in the datasetclass, in the dataset.cs.

     

    Let's see an example.

     

    namespace NameSpace.Properties
    {
      partial class Settings
      {  
        public override object this[string propertyName]
        {
          get
          {
            if (propertyName == "MyConnectionString")
            {
              return ConfigurationManager.ConnectionStrings["WebConfigConnectionString"].ConnectionString;
            }
            else
            {
              return base[propertyName];
            }
          }
          set
          {
            base[propertyName] = value;
          }
        }

      }
    }

     

     

    Give me your feedback of this answer.

    Friday, March 9, 2007 11:44 PM
  • I've seen a lot of posts about this issue. Here is the way I handle it. This allows the connection string to be set at runtime if required.

     

    Changing the generated code won't work unless you want to change it again every time you use the designer.

     

    I create a factory class to create instances of my table adapters. Here is some sample code.

     

    Code Snippet

    namespace Myapp.Data.MyappDataSetTableAdapters
    {
        internal class AdapterFactory
        {
            private string _connectionString = "";

            public string ConnectionString
            {
                get { return _connectionString; }
                set { _connectionString = value; }
            }

            internal MytableTableAdapter GetMytableTableAdapter(string connectionString)
            {
                MytableTableAdapter result = new MytableTableAdapter();
                result.Connection.ConnectionString = connectionString;
                return result;
            }

            internal MytableTableAdapter GetMytableTableAdapter(System.Data.OleDb.OleDbConnection connection)
            {
                MytableTableAdapter result = new MytableTableAdapter();
                result.Connection = connection;
                return result;
            }

            internal MytableTableAdapter GetMytableTableAdapter()
            {
                return GetMytableTableAdapter(_connectionString);
            }
    }

     

    In the factory you need to create methods to return each type of table adapter.

     

    When the factory creates a new instance of a table adapter that instance will initially get the default connection string but the string will never be used. It is immediately overwritten by the connection string you assign to the adapter factory. The connection string isn't used until the first time you try to access the database using the table adapter.

     

    You can use this with code like

     

    Code Snippet

     Myapp.Data.MyappDataSetTableAdapters.AdapterFactory adapterFactory =

        new Myapp.Data.MyappDataSetTableAdapters.AdapterFactory();
     adapterFactory.ConnectionString = myConnectionString;
     Myapp.Data.MyappDataSetTableAdapters.MytableTableAdapter myTableAdapter = adapterFactory.GetMytableTableAdapter();

     

    Send feedback if this isn't what you're looking for.

     

     

    Tuesday, April 24, 2007 12:40 AM
  • The best way i finally find is this one

     

    using System.Configuration;

    namespace Nms.Inventory.Properties {

    // This class allows you to handle specific events on the settings class:

    // The SettingChanging event is raised before a setting's value is changed.

    // The PropertyChanged event is raised after a setting's value is changed.

    // The SettingsLoaded event is raised after the setting values are loaded.

    // The SettingsSaving event is raised before the setting values are saved.

    internal sealed partial class Settings {

    public Settings() {

    // // To add event handlers for saving and changing settings, uncomment the lines below:

    //

    // this.SettingChanging += this.SettingChangingEventHandler;

    //

    // this.SettingsSaving += this.SettingsSavingEventHandler;

    //

    this["SiteConnectionString"] = ConfigurationManager.ConnectionStrings["SiteConnectionString"].ConnectionString; //(Configuration manager came from my web.config)

    // List all the connection string you whant to override.

    }

    private void SettingChangingEventHandler(object sender, System.Configuration.SettingChangingEventArgs e) {

    // Add code to handle the SettingChangingEvent event here.

    }

    private void SettingsSavingEventHandler(object sender, System.ComponentModel.CancelEventArgs e) {

    // Add code to handle the SettingsSaving event here.

    }

    }

    }

    Tuesday, April 24, 2007 8:46 PM
  • This is the solution I've sucessfully used in this scenario:

    Problem:
    We have a 3-tier application developed using the new features of VS 2005. Connection string is stored as a setting in the DAL (Data Access Layer).
    We want to change the connection in run-time from upper layers (for example, in GUI layer, user wants to change the access database file used)

    Solution: The Data Access Layer will expose that setting to upper layers. For that, we need to perform two simple steps:

    1. In DAL, open the "settings.setting" file in design-mode and change the connection string setting:
    - "Type" must be "String"
    - "Scope" must be "User" (because "Application" scope would made this setting readonly)

    2.In some class of the Data Access Layer, expose that setting. Something like:

    Public Class DTADBManager
        Public Shared Property ConnectionString() As String
            Get
                Return DTACuestionario.My.MySettings.Default.cuestionariosConnectionString
            End Get
            Set(ByVal value As String)
                DTACuestionario.My.MySettings.Default.cuestionariosConnectionString = value
            End Set
        End Property
        ...
    End Class


    Once this is done, upper layers can change the connection string at runtime, so when a new DataAdapter object is created it use the new connection.
    Monday, May 28, 2007 4:59 PM
  •  JesusAbizanda wrote:
    This is the solution I've sucessfully used in this scenario:

    Problem:
    We have a 3-tier application developed using the new features of VS 2005. Connection string is stored as a setting in the DAL (Data Access Layer).
    We want to change the connection in run-time from upper layers (for example, in GUI layer, user wants to change the access database file used)

    Solution: The Data Access Layer will expose that setting to upper layers. For that, we need to perform two simple steps:

    1. In DAL, open the "settings.setting" file in design-mode and change the connection string setting:
    - "Type" must be "String"
    - "Scope" must be "User" (because "Application" scope would made this setting readonly)

    2.In some class of the Data Access Layer, expose that setting. Something like:

    Public Class DTADBManager
    Public Shared Property ConnectionString() As String
    Get
    Return DTACuestionario.My.MySettings.Default.cuestionariosConnectionString
    End Get
    Set(ByVal value As String)
    DTACuestionario.My.MySettings.Default.cuestionariosConnectionString = value
    End Set
    End Property
    ...
    End Class


    Once this is done, upper layers can change the connection string at runtime, so when a new DataAdapter object is created it use the new connection.


    I've had the same thought... but what happens to the wizard when you want to update the data structure?  It wizard freaks out:
    ---------------------------
    Error
    ---------------------------
    Configure TableAdapter XXX failed.

    Unable to find connection 'xxxConnectionString' for object 'Settings'. The connection string could not be found in application settings, or the data provider associated with the connection string could not be loaded.
    ---------------------------
    OK  
    ---------------------------

    Someone must have a reference to setting a specific connection/connection class object when using the wizard.  Anyone?
    Thursday, July 19, 2007 2:35 PM
  • Hi SobyOne,

     

    if you want to update the data structure via VS2005 wizard, you have to restore original application setting type and scope.

    After updating you can modify application setting again.

    Friday, July 20, 2007 7:30 AM
  •  

    could you please post a VB.net version of this code..
    Wednesday, August 29, 2007 5:39 AM
  • ajmcalister has something close to the most usable solution to this problem.  I prefer this model, which I wrote up in a very long post that can be found at the end of http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1986118&SiteID=1.

     

    When you create a table adapter in your typed dataset, set the access modifier on its connection to Public.  Then implement this:

     

    Code Snippet

    public static class Adapters

    {

       public static MyDataSet.Table1TableAdapter Table1;

       public static MyDataSet.Table2TableAdapter Table2;

       // other adapters go here

     

       public static void Init(string csName)

       {

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

     

          Table1 = new MyDataSet.Table1TableAdapter();

          Table1.Connection = new SqlConnection(cs);

          Table2 = new MyDataSet.Table2TableAdapter();

          Table2.Connection = new SqlConnection(cs);

          // other adapters go here

       }

    }

     

    When you launch your program, call Adapters.Init() with the name of the connection string from app.config.  (Or you can just implement Init so that it takes the connection string as its parameter.  Whatever.)  From then on, whenever you need a table adapter you just use the Adapters, e.g.

     

    Code Snippet

    Adapters.Table1.Fill(Table1DataTable);

     

    This is so much better than the horrible idiom you see in nearly all ADO sample code:

     

    Code Snippet

    MyDataSet.Table1TableAdapter ta = new MyDataSet.Table1TableAdapter();

    ta.Fill(Table1DataTable);

     

    As with all static classes, don't use it in ADO.NET unless you know what you're getting into.

    Wednesday, August 29, 2007 8:19 AM
  • Please, someone, post this code in visual basic.net. Not all of us use C#.

    Tuesday, September 4, 2007 2:30 PM
  • Hi,

     

    I tried the code published at the start of this thread, i.e. directly change the connection string by:

     

    LoginAdapt.Connection.ConnectionString = ConnString

     

    However, this worked if you stepped through the debugger, examining LoginAdapt but not if you ran the code.

    I have now found the following VB.NET code works (at least for an Access database)

     

    Imports Ole = System.Data.OleDb

     

     

    Dim LoginAdapt As DALTableAdapters.LogonIDsTableAdapter

    Dim Conn As Ole.OleDbConnection

     

    Conn = New Ole.OleDbConnection

    Conn.ConnectionString = ConnString

    LoginAdapt.Connection = Conn

     

    Hope this helps

    Paul Ireland

    Friday, September 7, 2007 9:43 AM
  • I has having a simular issue but the link provided by NTDS totally helped out. In my code, if the prog is in debug mode I set it to use a local SQL server that I set up in My.Settings. Otherwise, just skip it and use the production version. Here's my code:

     

    My.Settings.DB2008 is the connection string to the production server

    My.Settings.DB2008_Local is the connection to the local server

     

    Namespace My

     

    Partial Friend NotInheritable Class MySettings

    Sub SetLocalMode(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles MyBase.SettingsLoaded

    If System.Diagnostics.Debugger.IsAttached Then

    Me.Item("DB2008") = My.Settings.DB2008_Local

    End If

     

    End Sub

    End Class

    End Namespace

    Friday, October 26, 2007 3:31 PM
  • I've solved this problem with the following:

    (1) Open the partial class for the table adapter and expose the connection property so it can be changed

    (2) In your code, you will add 1 line of code to change the connection (not the connection string) on the Constructor() of the object using the data

    (3) Call the .Fill() or .FillByX() statement in the Object_Load() event

     

    For example:

    Code Block

    // This code refereces Browsable(false)

    // the property does not need to appear in the IDE properties

    // since we are setting the property with code

     

    // (1) Expose the connection property

    partial class AnyTableAdapter

    {

    [System.ComponentModel.Browsable(false)]

    public System.Data.SqlClient.SqlConnection Connection

    {

    get

    {

    return this.Connection;

    }

    set

    {

    this.Connection = value;

    }

    }

    }

     

     

     

     

    Code Block

    // This code references a class called GLOBAL

    // Replace GLOBAL with your global or static class property that

    // contains the current connection

     

    public partial class Document : Form

    {

    public Document()

    {

    InitializeComponent();

    // (2) Change the connection of the TableAdapter object generated by VS2005

    AnyTableAdapter.Connection = GLOBAL.Connection; // Any active data connection

    }

     

    private void Document_Load(object sender, EventArgs e)

    {

    try

    {

    // This line of code loads data into the 'AnyDataSet.Any' table. You can move, or remove it, as needed.

     

    // (3) Fill the table with your newly connected table adapter

    this.AnyTableAdapter.Fill(this.AnyDataSet.Any);

    }

    .

    .

    .

    }

    .

    .

    .

    }

     

     

     

    Tuesday, November 13, 2007 4:42 PM
  • Hello SobyOne,

     

    The only disadvantage in your proposal is that you have to set the attribute "Connection" for each object you create. It would be better to change the connection once for the whole data access layer. However, it's a pretty reasonable solution.

    Thursday, November 15, 2007 8:28 AM
  •  Noyabronok wrote:
    After 5 hours of figuring out this stuff, I came up with this simple solution to update the app.config file at runtime with new string settings.  The only thing you have to know is the full name of the connection string.

    Hope this helps someone :-)


            public static void ChangeConnectionString(string strConn)
            {             
                 Configuration _config = ConfigurationManager.OpenExeConfiguration(
                            ConfigurationUserLevel.None);

                //the full name of the connection string can be found in the app.config file
                // in the "name" attribute of the connection string
                 _config.ConnectionStrings.ConnectionStrings[FULL NAME OF CONNECTION STRING HERE].ConnectionString = strConn;

                //Save to file
                _config.Save(ConfigurationSaveMode.Modified);
                
                //force changes to take effect so that we can start using
                //this new connection string immediately
                 ConfigurationManager.RefreshSection(_config.ConnectionStrings.SectionInformation.Name);
                Properties.Settings.Default.Reload();
            }

     

     

    VB Version to change App.Exe.Config file:

     

    Imports System.Configuration

     

    Public Shared Sub ChangeConnectionString(ByVal strConn As String)

     

    Dim _config As Configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)

     

    'Note: VB wants to use ConnectionStrings "Index number", and not name!

    'Turns out Index 0 is "LocalSqlServer", but Index 1 was our connection string "Name" we wanted to change

    'You might need to play with this...

    _config.ConnectionStrings.ConnectionStrings(1).ConnectionString = strConn

     

    'Save to file

    _config.Save(ConfigurationSaveMode.Modified)

     

    'force changes to take effect so that we can start using

    'this new connection string immediately

    ConfigurationManager.RefreshSection(_config.ConnectionStrings.SectionInformation.Name)

    My.MySettings.Default.Reload()

     

    End Sub

    Tuesday, January 29, 2008 7:22 PM
  • You are an absolute Star!!!

     

    been looking for an 'elegant' solution for some time, in a lot of other areas no luck - but you set me on the path to the solution.

     

    I use seperate projects for BL & DAL and these naturally compile the connection string into the dll's at build time.

     

    We however have to deploy our code to TEST & QA servers before we hit Production. this also naturally means we hit different SQL servers at each stage as well.

     

    Using your idea as a base, i now do the following:

    in the DAL I add a public property to hold the desired connection string.

     

    in Global.asax in Application_Start I read the required context (DEV/QA/TEST/PROD)  and the associated connection string from Web.Config and Set the Public prop(above)

     

    Then when the XSD requests the Connstring your "fake" settings class does it's job.

     

    moving the DB connection to TEST for a Debug session is a simple as setting the key in web.config!

     

    I owe you many many beers!

    re the first post, it's not quantity, it's QUALITY Smile
    Wednesday, January 30, 2008 2:54 PM
  • I think someone else did something similar to this in one of the previous posts, but I'm not sure.

     

    Here's the problem: Typed Datasets in VS 2005 use a read-only application setting to establish the connection string for all of it's tableadapters. We can't easily change the setting because it is read only. We can use partial classes for each and every table adapter or expose the Connection property for each table adapter and then every time we instantiate a table adapter, we can set the connection string. To me, this is not a great solution because I am bound to forget to set that everytime I use any table adapter.

     

    If you look into the generated code of the dataset designer, you'll see that each tableadapter is set to use the setting from my.settings. Honestly I have no idea why MS developed it this way. Why is the ConnectionString property read only? Why does it have to be an application setting and not a user setting? Why not derive the connection string from the dataset, and make that an alterable property? Well, who knows, that's microsoft for you. I guess they figured that nobody will ever need to change the connection string to a database huh? 

     

    So here is another possible solution that works, but to be honest I'm not 100% certain why it works.

     

    We want to be able to set the connection string for all table adapters easily, and without having to remember to add code for every new tableadapter we add to the dataset. We can't change the value of the setting in My.Settings. But, we can create a sub within our settings.vb file that can modify the application setting in memory.

     

    This way, you can set your connection string application setting once and then all of the tableadapters will use that connection string. In this example I created two functions, one that accepts a string and one that accepts a connection object, but they both basically work the same way. Just access the connection string property using the Item or Default collection.

     

    Code Snippet

    Namespace My

    Partial Friend NotInheritable Class MySettings

    Public Sub SetConnectionString(ByVal oConn As System.Data.OleDb.OleDbConnection)

    My.Settings.Item("LocalConnectionString") = oConn.ConnectionString

    End Sub

     

    Public Sub SetConnectionString(ByVal strConnection As String)

    My.Settings.Item("LocalConnectionString") = strConnection

    End Sub

    End Class

    End Namespace

     

     

    Wednesday, January 30, 2008 10:03 PM
  • Thank you.

     

    Friday, February 1, 2008 1:58 PM
  •  

    I have a 3-tier VS 2005 application. This is a problem that I encountered. Depending on the user's region, he/she needs to access his/her region's database. (All the region's databases have the same table structure.)  One user changing the setting/connection string shouldn't affect all users of the application.  Any idea on how to do it?  Thanks for your hlep.
    Monday, February 4, 2008 12:25 AM
  •  

    Sonic8888- Try any of the solutions posted in this thread. Any one of these approaches would work for you. The approach I listed above would work for you as well as the settings file is not actually changed, just the in memory collection for the settings is changed. Since the connection string is an Application setting it cannot be changed and so it would not affect any of your users other than the one who is currently using the program.
    Monday, February 4, 2008 8:18 PM
  •  

    Thanks a lot.  I will try it.
    Monday, February 4, 2008 11:17 PM
  • Lots of replies to this post... A very simple two step solution is:

     

    edit Settings.cs class file

     

    partial class settings

    {

    // add this property, can make it gettable too

    public string SetmydatasetConnectionString

    {

    set

    {

    this["mydatasetConnectionString"] = value;

    } // eos

    } // eop

    } // eoc

     

    then in a startup location (such as ApplicationContext) do

     

    public void SetGlobalDataSource(string new_datasource_path_and_filename)

    {

    string datasource = "Data Source=" + new_datasource_path_and_filename;

    string conn = myApplicationObject.Properties.Settings.Default.mydatasetConnectionString;

    conn = SetDataSource(conn, datasource); // swap out default data source for new data source

    myApplicationObject.Properties.Settings.Default.SetmydatasetConnectionString = conn;

    }

     

    where

    - new_datasource_path_and_filename is the full path and file name of the Access/Sql data file

    - mydataset is the name of the generated dataset (see the Settings.Designer.cs for actual name)

    - myApplicationObject is the object name of the application (created by default)

     

    private string SetDataSource(string connection, string datasource)

    {

    string result = "";

    try

    {

    string[] elements = connection.Split(new char[] { ';' });

    for (int i = 0; i < elements.Length; i++)

    {

    if (elementsIdea.StartsWith("Data Source="))

    {

    elementsIdea = datasource; // swap in new datasource

    break;

    }

    }

    result = String.Join(";", elements);

    }

    catch (Exception)

    {

    result = connection; // no change

    }

    return result;

    } // eom

     

    Lance Kujala

    Driftwood Technology

    Wednesday, February 27, 2008 12:21 AM
  • I have been fighting the same issue.  With the connection string saved in plain text in the config file, anyone could find it and gain access to my database. 

     

    I tried Lance's post (above) and it worked flawlessly.  I have seen many attempts and all require much work and many modifications.  This is the only one that makes sense and doesn't save the information back to the config file.

     

    THANKS LANCE!

     

    --------------------------

    Kyle

    Wednesday, May 14, 2008 8:51 PM
  • I was looking at the Settings.cs code but I was not quite sure if making such a change will work.
    Thanks man !
    Thursday, November 6, 2008 8:52 PM

  • Yesterday I googled a lot but I didn't find the solution I was lloking for.
    So this is my workaround,  it works GREAT and doesn't need an app.config at runtime:

    1) I set the settings.designer.cs build action to "none" (prev. "compile")
    2) create a fake Settings class where I can call Properties.Settings.Default.ConnectionString (get and set my connectionstring)
    3) I exclude the app.config from the project

    VS will use the connectionString in settings.settings at design-time, while run-time will be used the connection script in the "fake" class. No app.config is created.

    Feedbacks are wellcome!!

    PS: my first post ever... what a newbie Wink




    Hi Vittorio,
    this works fine for me. But I don't understood very well the rename of app.config.
    Maybe the app.config it's needed if I want to modifier in design the dataset...I  guess... but i'll try.
    IN my solution I have an assembly with some dataset...and now with  a setting at runtime from another assembly I'm able to take the right connection!
    Now i want to  try this on network , with domain etc...



    Tuesday, November 25, 2008 12:03 AM
  • After much searching, on how to dynamically update the connection string, or come to that ANY configuration value I have found the best solution is to override the settings namespace. This has the advantage that you can create design time values (in this example UxOnlineConnectionString, etc) allowing you to use the design tools, then at run-time, override the setting to provide a different value.

     

    Sorry the example below is a "bit complicated". It uses my UxConfig assembly which returns configuration values from "elsewhere" which override the design time settings. I even have a "UxDynamConnectionString" can be repointed by the calling programme.

     

    Using this code, I can use a single Design time connection string in any of MS controls or code that uses my.settings and it is overridden at run time.

     

    I have found this solution both flexible and reliable, just a little difficult to debug.

     

    Hope this helps

    Paul

     

    Option Strict On

    Option Explicit On

     

    Namespace My

    Partial Friend NotInheritable Class MySettings

    Inherits Global.System.Configuration.ApplicationSettingsBase

     

    Public Enum DynamSystem

    DefForWebSite = 0

    UxOnline = 1

    UxIntranet = 2

    End Enum

     

    Private ConfOverrides() As String = { _

    "UxOnlineConnectionString", _

    "UxIntranetConnectionString", _

    "UxDynamConnectionString" _

    }

     

    Private g_CurrSystem As DynamSystem = DynamSystem.DefForWebSite ' Sets dynmic configuration string

     

    Private Sub userOverride_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) _

    Handles Me.SettingsLoaded

    ' Used to intercept requests for application properties and if "ours" replace them with the

    ' system dependent ones

    Dim appProperty As String

    Dim DefSystem As String

     

    Using Conf As UxConfig = New UxConfig

    For Each appProperty In ConfOverrides

    Select Case appProperty

    Case "UxOnlineConnectionString"

    ' request for a connection string

    Me(appProperty) = Conf.GetNetConnString(SystemName.Online)

    Case "UxIntranetConnectionString"

    ' request for a connection string

    Me(appProperty) = Conf.GetNetConnString(SystemName.Intranet)

    Case "UxDynamConnectionString"

    ' use the connection string for the current dynamic system

    Select Case g_CurrSystem

    Case DynamSystem.DefForWebSite

    ' Default for web site set to appropriate value

    ................

    Call Me.userOverride_SettingsLoaded(sender, e)

    Case DynamSystem.UxIntranet

    Me(appProperty) = Conf.GetNetConnString(SystemName.Intranet)

    Case DynamSystem.UxOnline

    Me(appProperty) = Conf.GetNetConnString(SystemName.Online)

    Case Else

    Throw New Exception("Unrecognised dynamic system: " & g_CurrSystem.ToString)

    End Select

     

    Case Else

    ' request for a value

    Me(appProperty) = Conf.GetNetValue(appProperty)

    End Select

    Next

    End Using

    End Sub

     

    Private Sub userOverride_SettingsSaving(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) _

    Handles Me.SettingsSaving

    ' Settings are read only

    End Sub

     

    Public Sub SetDynamSystem(ByVal Sys As DynamSystem)

    g_CurrSystem = Sys

    End Sub

    End Class

    End Namespace

    Tuesday, November 25, 2008 9:17 AM
  • I think someone else did something similar to this in one of the previous posts, but I'm not sure.

     

    Here's the problem: Typed Datasets in VS 2005 use a read-only application setting to establish the connection string for all of it's tableadapters. We can't easily change the setting because it is read only. We can use partial classes for each and every table adapter or expose the Connection property for each table adapter and then every time we instantiate a table adapter, we can set the connection string. To me, this is not a great solution because I am bound to forget to set that everytime I use any table adapter.

     

    If you look into the generated code of the dataset designer, you'll see that each tableadapter is set to use the setting from my.settings. Honestly I have no idea why MS developed it this way. Why is the ConnectionString property read only? Why does it have to be an application setting and not a user setting? Why not derive the connection string from the dataset, and make that an alterable property? Well, who knows, that's microsoft for you. I guess they figured that nobody will ever need to change the connection string to a database huh? 

     

    So here is another possible solution that works, but to be honest I'm not 100% certain why it works.

     

    We want to be able to set the connection string for all table adapters easily, and without having to remember to add code for every new tableadapter we add to the dataset. We can't change the value of the setting in My.Settings. But, we can create a sub within our settings.vb file that can modify the application setting in memory.

     

    This way, you can set your connection string application setting once and then all of the tableadapters will use that connection string. In this example I created two functions, one that accepts a string and one that accepts a connection object, but they both basically work the same way. Just access the connection string property using the Item or Default collection.

     

     

    Code Snippet

    Namespace My

    Partial Friend NotInheritable Class MySettings

    Public Sub SetConnectionString( ByVal oConn As System.Data.OleDb.OleDbConnection)

    My .Settings.Item( "LocalConnectionString" ) = oConn.ConnectionString

    End Sub

     

    Public Sub SetConnectionString( ByVal strConnection As String )

    My .Settings.Item( "LocalConnectionString" ) = strConnection

    End Sub

    End Class

    End Namespace

     

     

     

     

    This is a great solution Josh, many thanks!

    I have a simple wizard-type form application written in VS 2008 Express that was originally created to connect to a single (SQL Express) database - therefore, the dreaded table adaptors were used for quickness!

    However, several new databases (SQL Server 2005) have since been created and so the application needs the functionality to connect to any of them based upon which one a user selects from a dynamically populated combo on the initial form.

    I had a real nightmare trying to amend the app.config file programmatically and was about to give up and rework the app by using SQLClient connectors - however, Josh's VB solution above has resovled the problem perfectly.

    To make the solution work, I simply define a global string variable that receives the database name that the user selects in the combo-box, and then I call the SetConnectionString Sub and pass the selected database name as a string.

    The result - all table adaptors now retrieve data from the user-selected database and everything works perfectly! :-)

    For reference, here is my code snippet:

    Private Sub Fm1NxtBt_Click(ByVal sender As System.Object, 
    ByVal e As System.EventArgs) Handles Fm1NxtBt.Click 'Write selected database into Global var for connection on next page SelectDB = ComboBox1.SelectedItem 'Call Sub to set DB Connection String for all bound table adaptors
    by passing selected DB Name to Sub Call My.Settings.SetConnectionString(SelectDB) End Sub Namespace My Partial Friend NotInheritable Class MySettings Public Sub SetConnectionString(ByVal strConnection As String) My.Settings.Item("DevConnectionString") =
    "Data Source=192.168.22.101;Initial Catalog=" & strConnection &
    ";Integrated Security=True" End Sub End Class End Namespace


    Keep up the good work guys!

    Cheers,

    Steve
    • Proposed as answer by Aymen.Ansary Tuesday, July 6, 2010 7:54 AM
    Friday, November 20, 2009 11:52 AM
  • I have a client app that consumes a web service that accesses an Oracle database. I have several table adapters in few different projects in the same web service solution.  I wanted to be able to change only one place my connection string for deployment of the web service (to point to different instances of the database).  The solution I got is rather simple and is very easy to implement.

    Similar to Cedric Boivin's proposed solution, this is what I did:

    1. In my web.config (of my web service of course) I added a section for the connection string:
       <connectionStrings>
          <add name="ConnectionString"
                 connectionString="DATA SOURCE=ORCL;USER ID=uuuu;PASSWORD=pppp"
                 providerName="Oracle.DataAccess.Client" />
      </connectionStrings>


    2. For EACH PROJECT in my solution I added a line like this on the Settings.cs file (see Cedric's code for the place to insert the line):

     

    this["ConnectionString"] = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; 

    Make sure that the name of the connection string used in the web.config and the Settings.cs match.

    This has to be done only once per project, as every table adapter on the project (even the ones created after this is implemented) will pick up the "ConnectionString" connection string at runtime. 

    NOTE: To change the Settings.cs file see https://blogs.msdn.com/smartclientdata/archive/2005/07/25/443034.aspx

    3. Done.  I did not have to change client app at all.

    So now, I only change the web.config file to change the connection string (to point to test, qa, prod, etc.). 

    Juan Camaney
    Wednesday, December 9, 2009 5:00 PM
  • Karamasov, This is just 4 years late, but this might work for you: http://rajmsdn.wordpress.com
    :-)

    • Proposed as answer by Raj Kashyap Wednesday, December 9, 2009 9:05 PM
    Wednesday, December 9, 2009 9:05 PM
  • What's the difference between "web.config" and "app.config" ? and where & Inwhich purposes they used?
    ---Sri's
    Sunday, March 14, 2010 4:00 AM
  • This is a great solution Josh, many thanks!

    I have a simple wizard-type form application written in VS 2008 Express that was originally created to connect to a single (SQL Express) database - therefore, the dreaded table adaptors were used for quickness!

    However, several new databases (SQL Server 2005) have since been created and so the application needs the functionality to connect to any of them based upon which one a user selects from a dynamically populated combo on the initial form.

    I had a real nightmare trying to amend the app.config file programmatically and was about to give up and rework the app by using SQLClient connectors - however, Josh's VB solution above has resovled the problem perfectly.

    To make the solution work, I simply define a global string variable that receives the database name that the user selects in the combo-box, and then I call the SetConnectionString Sub and pass the selected database name as a string.

    The result - all table adaptors now retrieve data from the user-selected database and everything works perfectly! :-)

    For reference, here is my code snippet:

    Private Sub Fm1NxtBt_Click(ByVal sender As System.Object, <br/>
    ByVal e As System.EventArgs) Handles Fm1NxtBt.Click

    'Write selected database into Global var for connection on next page
    SelectDB = ComboBox1.SelectedItem

    'Call Sub to set DB Connection String for all bound table adaptors <br/>
    by passing selected DB Name to Sub
    Call My.Settings.SetConnectionString(SelectDB)

    End Sub

    Namespace My

    Partial Friend NotInheritable Class MySettings

    Public Sub SetConnectionString(ByVal strConnection As String)

    My.Settings.Item("DevConnectionString" ) = <br/>
    "Data Source=192.168.22.101;Initial Catalog=" & strConnection &<br/>
    ";Integrated Security=True"

    End Sub

    End Class

    End Namespace

    Keep up the good work guys!

    Cheers,

    Steve

    Worked like a charm; however, using:

     

    ConfigurationManager.ConnectionStrings("ConName").ConnectionString

     

    would still resolve as the original text in the app.config file. You have to use:

     

    My.Settings.ConName

     

     

    Thanks to all contributors!

     

    • Edited by Aymen.Ansary Tuesday, July 6, 2010 8:04 AM ConnectionString Name
    Tuesday, July 6, 2010 8:02 AM
  • In response to graphicsxp on Wednesday, July 19, 2006 10:46 AM.

    If you changed the Type from Connection string to just plain string, you can change the scope to User and then it should work. You might have to remove surrounding XML tags and I think the string won't be encrypted.

    Friday, July 23, 2010 8:01 PM
  • So many feedback on the subject. I think reader must notice this also.

    This is very simple class which can save your headache of maintaining multiple connectionstring for an application.

    namespace TE.Helpdesk.SqlDataProvider
    {
      public static class DatabaseManager
      {
        public static string ConnectionString
        {
          set
          {
            Properties.Settings.Default.PropertyValues["HelpdeskDbConnectionString"].PropertyValue = value;
            Properties.Settings.Default.Save();
          }
          get
          {
            return Properties.Settings.Default.HelpdeskDbConnectionString;
          }
        }
      }
    }
    
    Set it once will remain same through all the process lifecycle. If using in web application than must call in global.asax so that you can make it sure working perfectly.

    Wednesday, April 27, 2011 1:15 PM
  • Hey,

    Please read this Microsoft article on Retrieving Connection Strings at Run Time.

    -Mohammed Ghouse Ibne Barq Kadapavi
    http://www.ProgrammersVision.blogspot.com
    https://sites.google.com/site/BarqKadapavi
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful.


    Mohammed Ghouse Ibne Barq Kadapavi
    Monday, May 16, 2011 7:41 AM
  • Here's how to change the SELECT command and the ConnectionString - using a partial class of the adapter.  GetAll1 is equivalent to the GetData that is automatically generated by the TableAdapter wizard.  GetAll2 is where I've put my own SELECT and ConnectionString values.  It's pretty straight forward once you create the partial class.

    using System.Configuration;
    
    namespace DAL
    {
        
        
        public partial class DataSet1 {
            partial class DepartmentDataTable
            {
            }
        }
    }
    
    namespace DAL.DataSet1TableAdapters
    {
        public partial class DepartmentTableAdapter
        {
            public virtual DAL.DataSet1.DepartmentDataTable GetAll1()
            {
                this.Adapter.SelectCommand = new System.Data.SqlClient.SqlCommand();
                this.Adapter.SelectCommand.CommandText = this.CommandCollection[0].CommandText;
                this.Adapter.SelectCommand.Connection = new System.Data.SqlClient.SqlConnection();
                this.Adapter.SelectCommand.Connection.ConnectionString = this.CommandCollection[0].Connection.ConnectionString;
                DataSet1.DepartmentDataTable dt = new DataSet1.DepartmentDataTable();
                this.Adapter.Fill(dt);
                return dt;
            }
    
            public virtual DAL.DataSet1.DepartmentDataTable GetAll2()
            {
                this.Adapter.SelectCommand = new System.Data.SqlClient.SqlCommand();
                this.Adapter.SelectCommand.CommandText = "SELECT * FROM HumanResources.Department";
                this.Adapter.SelectCommand.Connection = new System.Data.SqlClient.SqlConnection();
                this.Adapter.SelectCommand.Connection.ConnectionString = ConfigurationManager.ConnectionStrings["DAL.Properties.Settings.AdventureWorks2008R2ConnectionString"].ConnectionString;
                DataSet1.DepartmentDataTable dt = new DataSet1.DepartmentDataTable();
                this.Adapter.Fill(dt);
                return dt;
            }
        }
    }

    • Proposed as answer by paberglund Saturday, October 27, 2012 4:07 AM
    Saturday, October 27, 2012 4:05 AM
  • Simply elegant, to modify the settings.cs file,

    Double click the Settings.Settings in the project explorer

    Here i have a FilenameConnectionString added earlier by the "Add new Datasource" in the Datasources tab

    Then i add a RuntimeConnectionString ,type string, scope user

    then on the view menu select code  and edit the settings.cs file as follows

        internal sealed partial class Settings {

           // add this

            public override object this[string propertyName]

            {

                get 

                {

                    if (propertyName == "FilenameConnectionString")

                    {

                        return Settings.Default.RuntimeConnectionString;                }

                    else

                    {

                        return base[propertyName];

                    }

                }

                set

                {

                    base[propertyName] = value;

                }

            }

         //to here

    Then  I have a menu item  open in the main form off my application

            private void åpneToolStripMenuItem_Click(object sender, EventArgs e) 

            {

                    // Displays an OpenFileDialog 

                    OpenFileDialog dlgOpenFile = new OpenFileDialog();

                    dlgOpenFile.ShowReadOnly = true;

                    dlgOpenFile.Filter = "Access Databaser (*.accdb)|*.accdb";

                    if(dlgOpenFile.ShowDialog() == DialogResult.OK)

                    {

                        Properties.Settings.Default.RuntimeConnectionString  = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dlgOpenFile.FileName;

                        Properties.Settings.Default.Save();

                        Properties.Settings.Default.Reload();


                    }

            }


    • Edited by R02 Monday, April 29, 2013 2:19 PM
    • Proposed as answer by Leon Zeng Sunday, September 14, 2014 2:49 AM
    Monday, April 29, 2013 1:26 PM
  • THIS IS THE ANSWER!

    Specifically, the "Properties.Settings.Default.Reload();" line is what actually triggers the change.

    As suggested, you construct your new "dynamic" connection string.  Then, you use any number of methods to update the "connectionStrings" element, down to the specific "connectionString" attribute in the App.config xml.  Then, you use the ConfigurationManager.RefreshSection("connectionStrings").  Finally, you do the reload.

    Wednesday, May 31, 2017 1:58 PM
  • //This post was substantially pirated from a 2006 post (author unknown).  The key is to save, reload and refreshsection, as outlined in the last three lines of the code.  If you miss any of these, you will not get the desired result.
    
    //1. Set a Reference to "System.Configuration.dll" in the project references.
    
    //2. Add the "using System.Configuration" class to the top of the code that calls the procedure below
    
    //3. Add a procedure, that assumes fully qualified "Connection String" named "MyAPP.Properties.Settings.MyConnection" where "MyAPP" is your executable and "MyConnection" is the name of the connection string in the application settings.
    
    //Change Your Connection String Dynamically (Before Making Any Data Calls)
      public static void ChangeConnectionString(string strConnectionNew)
      {              
      //Instantiate A Configuration Object    
        Configuration objConfiguration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
    
      //Specify the full name of the connection string from the app.config file in the "name" attribute of the connection string
        objConfiguration.ConnectionStrings.ConnectionStrings["MyAPP.Properties.Settings.MyConnection"].ConnectionString = strConnectionNew;
    
      //Save the updated configuration file 
        objConfiguration.Save(ConfigurationSaveMode.Modified);
                
    
      //Reload the saved settings
        Properties.Settings.Default.Reload();
    
      //Force changes to take effect so that we can start using this new connection string immediately 
        ConfigurationManager.RefreshSection(objConfiguration.ConnectionStrings.SectionInformation.Name);
      }

    Wednesday, May 31, 2017 2:45 PM
  • I know this is an old topic, but I'd like some comments on the risks/issues associated with the following approach:

    1. As part of my initatilize() routine, in the first module I do:

        public static class StaticVals
        {
            public static string UserID { get; set; }
            public static string ServerID { get; set; }
            public static string ConnectionString { get; set; }
        }

    Then after a login process I would, for example using a login in button do:

            private void btn_Login_Click(object sender, EventArgs e)
            {
                StaticVals.UserID = tUser.Text;
                StaticVals.ServerID = tServer.Text;
                StaticVals.ConnectionString= "server=" + StaticVals.ServerID + ";uid=" +  tUser.Text + ";pwd=" + tPwd.Text + "; database=Ek_BE";
                this.Visible = false;
            }

    No I have a Global Variable called StaticVals.ConnectionString.

    Where I've used the system generated table adapters I will need to change the connection strings, so I search for all references to: <MySolutionj>.Properties.Settings.Default.EK_BEConnectionString;  and replace the line with 

    this._connection.ConnectionString = StaticVals.ConnectionString;

    I now don't need to edit the .Config files... I also don't need to store sensitive data in a text file anywhere.

    What are the downsides?

    Sunday, November 12, 2017 12:24 PM
  • Using .NET VB...

    Go to your ApplicationEvents.vb

    there scroll down to the following area.

    <STAThread()>

    PrivateSubMyApplication_Startup(ByValsender AsObject, ByVale AsMicrosoft.VisualBasic.ApplicationServices.StartupEventArgs) HandlesMe.Startup


    '--- add this next line


    My.Settings("MyConnectionString_NAME") = "Data Source=MY_SQL_ADDRESS;Initial Catalog=MY_DATABASE_NAME;Persist Security Info=True;User ID=username;Password=password"

    EndSub

    '--- Note: everything in this SUB will execute at app start.
    You could now delete the execute.config file generated if no other setting is needed.
    Just make sure you use the same settings NAME as you apps settings were I highlighted BOLD Unerlined 




    • Edited by SiRaDuDe Wednesday, March 14, 2018 5:02 PM Bold
    Wednesday, March 14, 2018 4:53 PM
  • I Think that the aswer you want is this:

    In the load event of form you can modifiy the connection of tableadapter :

    dataset.tableadapter.connection.connectionstring = "connecstion string you want!"

    I hope this is the right you want.

    Friday, April 6, 2018 3:17 PM