Changing Application-Wide Connection String at Runtime

[Compeek]

Hello,

Let me just first say that I learned VB about 7 years ago, but I moved on to web development about 5 years ago. Since then, I have dabbled with VB.NET enough to get used to the differences between VB 6 and VB.NET, but I didn't really start actively using it again until a couple of weeks ago. I am a very capable programmer, but my VB skills are a little rusty, and VB.NET is still somewhat new to me.

I'm writing a simple piece of software to manage donations and donors for a mission in town. The database I'm using is an Access 2007 file. To keep it simple, I just let VB automatically create my TableAdapters, BindingSources, etc when I created a new Database Connection with the wizard.

My problem is that I need to be able to easily change the connection string of the TableAdapters at runtime, but VB set up the connection string as a setting with a scope of Application for the project, making the setting read-only at runtime. The only option I can think of is to manually set the connection strings of all my TableAdapters every time the program runs, but that seems like too much of a hassle. I have multiple forms with one or two TableAdapters on each, and I don't want to have to keep track of all of them. I could write a subroutine to handle changing all of them, but again, that seems like I would be making it more complicated than necessary.

I have tried to Google solutions to the problem, but I just can't find a good answer. So my question is, what is the best approach to what I am trying to do? I would really like to only have one connection string to change that affects all my TableAdapters, but I'll do what is necessary.

Thanks much in advance for the help.

 

[cjard]

Either:

Make it a User Scoped setting - you line yourself up for a real headache if you ever forget to revert it to Application Scope before you do more development in the dataset designer, but at least it is saved and persisted across restarts of the program.

Click the Show All Files button in the Solution Explorer. Open and READ (dont edit) the Settings.Designer.vb file. Find the property that gets the value for the Connection String. In the Partial class (right click the Settings.settings file and choose View Code) code view of the settings add another property named something else that has the corresponding Set to set the connection string, or make a Sub called something like SetConnectionString(str as String)
Caveat: you have to change it every run of the program

 

[Compeek]

Thanks very much for the reply.

I did what you said, but the problem I'm facing now is how to get my new property to show up as a connection string in the TableAdapter's properties. Any ideas?

I copied and pasted the original connection string property into the partial class and made the necessary changes, so I know I didn't make an error there. But the only property that shows up in the list when I go to choose the connection string for the TableAdapters is the original one.

Thanks.

 

[cjard]

If you took the first route:
If you made the Connection String a User Scoped setting then it should show in the list, but as soon as you try to edit the ataset or do anything that necessitates the designer conneting to the db, it will fall over

If you took the second route:
You seem to have created a whole new property targeting the connection string. Do not do this. Instead create just a "set" to allow you to alter the connection string. The original getter must stay entirely as is so the tableadapters can use it. To change the connection string you must say in code My.Mysettings.Default.ConnectionStringEx = "blah"


Actually this whole second route might have been less confusing if I'd told you to do this in your main form, assuming that your Connection String is called ABCConnectionString:

Sub MainForm_Load()...) Handles Form.Load
  InitializeComponent() 'put there by VB designer

  My.MySettings.Default("ABCConnectionString") = "blah"
End Sub

By adding a property (named something other than ABCConnectionString, because a class cannot have two properties of the same name) and having it perform the SET side of things, leaving the original GET side alone, it makes the experience of using MySettings more consistent but there is no need to use it. It's perfectly acceptable to call My.MySettings.Default which returns a hashtable type collection of string->object mappings, and just index it by the name of the property youre wanting to retrieve, setting a new value:

My.MySettings.Default("ABCConnectionString") = "blah"

You just dont get intellisense help, that's all

 

[Compeek]

It works perfectly now. Thanks so much for your help!

Edit: Okay, one more question. Is there an easy way to sort of "refresh" my table adapters when I update the connection string? At some times, the connection string will be changed after the program has already been running with the previous one, and I would like to not require the user to restart the program. Thanks!

 

[cjard]

every time you make a new tableadapter, it gets its connection info again. If your form designer contains the tableadapter, then just recreate a new instance:

Me._whateverTableAdapter = New MyDataSetTableAdapters.WhateverTableAdapter()


If you are creating it on a per-use basis, there is nothing to worry about:

Sub CountNewButton_Click(...) Handles CountNewButton.Click
  Dim ta as New Blah.QueriesTableAdapter()
  _countLabel.Text = ta.CountNewRecordsScalarQuery()
End Sub

 

[Compeek]

Once again, thank you very much for all your help. Everything is working great now.

 

[cjard]

If you ever get into multithreading, ensure that no two threads access the same tableadapter instance simultaneously (get every thread to make a new TA for itself when needed)

 

[Compeek]

I don't think I'll be getting into multithreading with this project, but that's much for the tip. I'll keep it in mind.

 

[mfsav2]

Either:
add another property named something else that has the corresponding Set to set the connection string, or make a Sub called something like SetConnectionString(str as String)
Caveat: you have to change it every run of the program

cjard,
can you provide an example?

 

[Ken W]

cjard,
can you provide an example?

I use a little bit of code I found on the net.
On the settings page I create my connection string set to Type = ConnectionString, Scope = Application. Name = conn2.

Then I create another setting Name = newConn set it to Type = String, Scope = User.
Then Click the button above the setting "View Code"

The settings.vb code will open with the following

Namespace My
 
    '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.
    Partial Friend NotInheritable Class MySettings
 
 
    End Class
End Namespace

Inside the class I added

Namespace My
 
    '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.
    Partial Friend NotInheritable Class MySettings
 
        Private Sub MySettings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded
            Me.Item("conn2") = My.MySettings.Default.newConn
        End Sub
 
    End Class
End Namespace

In my program where I want to change my connection string on the fly I use the following

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] btnSelect_Click([/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] btnSelect.Click[/SIZE]
 
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ofd2 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] OpenFileDialog[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] dbLocation [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2].Empty[/SIZE]
 
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ofd2[/SIZE]
[SIZE=2].Filter = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"ACCESS files (*.mdb)|*.mdb"[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2].InitialDirectory = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]My[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2].Computer.FileSystem.SpecialDirectories.Desktop[/SIZE]
 
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] .ShowDialog() = DialogResult.OK [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE][/COLOR][/SIZE]
 
[SIZE=2]dbLocation = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & .FileName[/SIZE]
 
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Call[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ChangeConString(dbLocation)[/SIZE]
 
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE][/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[/COLOR][/SIZE]

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ChangeConString([/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] dbLoc [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/SIZE]
 
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] dbLoc <> [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"" [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE][/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]My[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2].Settings.newConn = dbLoc[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]My[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2].Settings.Save()[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]My[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2].Settings.Reload()[/SIZE]
 
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Else[/COLOR][/SIZE][/COLOR][/SIZE]
 
[SIZE=2]MsgBox([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"NO SUCH DATABASE LOCATION FOUND!"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/SIZE]
 
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
 
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[/COLOR][/SIZE]

The call to Settings.Reload causes the new connection to be loaded and your off.
I don't know if it's the best method, but I know it works pretty well.

 

[cjard]

It's not technically necessary to save and reload the settings to get them to see it, though I do note that you've got an event handler attached to the reload mechanism so you may be hacking something in that way..

The simplest example is as you have done here, to simply declare a partial class for your code side of the settings, and just provide a method or property that changes the conenction string in the collection:

Public WriteOnly Property ConnectionStringW
Set
Me.Item("NAME OF YOUR CONN STR SETTING") = value
End Set
End Property

Now while your My.MySettings.ConenctionString is application scope read only during runtime, your My.MySettings.ConnectionStringW is writable. It doesnt save when you save the settings though, so you have to reset it every time you launch the app. The advantage of using the User Scope method is that settings DO save, but the caveat is that it breaks the DataSet Designer

 

[Ken W]

I worked on this a little more and I can change the connection string and have it available immediately. It will also persist the settings.connectionstring after app closes. I have added numerous datasets and adapters for comboboxes and a datagridview changing the connection string doesn't effect them they just return the expected results.

I can make available the entire app and databases. If anyone wants let me know and I'll post them.

 

[jeremyyeow]

Still not changing the connection

Hi there, i'm teaching myself VB.Net and am having a go at creating a simple database program. Followed the examples you gave, my program compiles, but it doesnt seem to change the connection string...

Any ideas?

 

[polychromenz]

I worked on this a little more and I can change the connection string and have it available immediately. It will also persist the settings.connectionstring after app closes. I have added numerous datasets and adapters for comboboxes and a datagridview changing the connection string doesn't effect them they just return the expected results.

I can make available the entire app and databases. If anyone wants let me know and I'll post them.

That would be really helpful Ken