G'day

We have a suite of programs that will run on a single dedicated server via numerous terminal server sessions, with every program in every session for every user accessing the same database which is usually on a second dedicated server. When the second server fails (which it will), we need to be able to be able to have the user quickly swap the connection to the database on the Application server (which is transactionally replicated from the database server). The users we deal with are shop floor supervisors in a very low tech industry (Leather Tanning), and this is a task that we may have to direct them to do after having been woken up at 4am in the morning by a phone call from the other side of the country, and that has to be done quickly because production will have stopped with potentially 50 workers standing around waiting, so simplicity is essential. I want to provide a form with a single button, that upon pressing the button will swap the connection for them and confirm that it has been done (with appropriate safeguards to ensure it isn't done accidently, i.e. limit the access to a single user specific to the task e.g. user id "Swap").

ConnectionManager.ShowAvailableConnectionStrings() is a convenient way of maintaining the connection strings, and the "Select" capability does what we want, except for the fact that the user performing the task could have the wrong connection selected at the time (our users are not renowned for their clarity at describing what is on the screen in front of them at such times, or any other times for that matter). We would use it for configuring the system while on-site installing. And switching servers is an installation test we always perform, to ensure that we have the settings right.

What I would find useful would be

public function SetApplicationActiveConnectionString(ByVal ApplicationKey as String, _
                                                                        ByVal DataSourceKey As String, _
                                                                        ByVal ApplicationDefaultTitle As String) _
                                                                        As Boolean

which would do just as pressing the "Select"  in ShowAvailableConnectionStrings does returning true if sucessful false otherwise (i.e. there is no connection string for the given combination).

Any chance of something like this?

I have investigated storing Connection Strings in each App's .config, but then I have a problem with ensuring that they are all kept in sync, and I don't really want to reinvent the wheel.

Peter

Dustin,

I've had a look at the SetConnections() method, and by itself I can't see how it helps me do what I want.

I’ve used ConnectionManager.ShowAvailableConnectionStrings() to configure two alternative connection strings for our system using the ApplicationKey “TMS”.

All the programs that make up our system have the following code in AppMain.vb

We only have one DataSource for all users for all programs in the system, which runs on a single server through Terminal services sessions. We are not looking to selectively change bits and pieces, we want to change everything in a single hit. It is not a choice a user can make that they want to use a particular datasource, the whole system must be using the same datasource, and it is only ever changed by a supervisor under our instruction when the server running the database fails.

In a test program I’ve populated a textbox as follows

TextBox1.Text = ConnectionManager.GetApplicationActiveConnectionString("TMS", "")

What this shows me is the following Connection String regardless of the value of ApplicationDefaultDescription whether it is "TMS Database Connection", "TMS AppServer Database Connection", "TMS DataServer Database Connection", or "Mary had a little lamb".

Data Source=HAHNDORF;Initial Catalog=TMS201;Integrated Security=True;Persist Security Info=False;Asynchronous Processing=True

If I then run ConnectionManager.ShowAvailableConnectionStrings() again and select the other Connection as below:

Then I get the following Connection string for each of the values of ApplicationDefaultDescription described above.

Data Source=PGD-VISTA;Initial Catalog=TMS201;Integrated Security=True;Persist Security Info=False;Asynchronous Processing=True

This is pretty much what I want to happen (except that it gives the person performing the task too many choices) and I thought it was what the ShowAvailableConnectionStrings was for. 

I know if I changed the ApplicationKey I would get a different Connection String, but that would mean storing that value somewhere central accessible by all the programs in the system, which I might have to do, but it would be far easier if I could press that “Select” button in code.

I hope this expands on what I'm trying to achieve to make it a bit more understandable. Am I misunderstanding something fundamental?

Peter

Really Peter, you will probably not want to use the connection manager at all in this case but rather create a solution on your side to just manually add the data sources.  This is actually what we do within our medical software (along with a number of other things).  We actually prevent our end-users from ever entering a connection string on the client side altogether as we have a server service that we distribute with our medical software which "tells" all of the clients what their connection string will be.  Now you don't have to take this approach, but you will still want to manually create your data source connections (which ultimately may save some installation steps anyway for your end-user).

To manually setup a connection string within the AppMain.vb, just comment out the SetConnections line...this is what forces the Connection Manager to do its thing.

Instead, manually add a connection like this:

That is how you manually provide a connection string.  Now you can get more sophisticated as to how the end-user to obtain this connection.  As previously mentioned, we have a server service that all of the clients connection to first via an http request (you can also use a TCP socket).  If the server cannot be found, a UDP broadcast is sent out to which the server will listen and then respond with it's own IP so the client knows how to connection to it...once the client knows the IP of the server, it connects on a particular port, and then the server gives it the connection string that needs to be used.

This is probably not the approach that you need, but it might get some wheels turing as to how you may need to connect.  One other thought is that you can have a dialog that pops up with a combo asking which location a user is at or which database to which they want to connection which is populated froma network shared file.  Once selected then you would know how to connect...this is just another approach and idea.

Hope this helps