Question Changing Sql Server Database reference at run time

[VentureFree]

I'm new to using datasets and I'm not sure if I can actually accomplish what I'm trying to do, so I want to verify before I mess something up.

Basically, if I create a dataset called MyDataSet in my data access layer at build time pointing to a table called MyData, and I have two different databases that it could potentially point to (i.e. MyDatabase and MyTestDatabase which is essentially an exact copy of MyDatabase purely for testing), can I decide at run-time which database to point to?

My initial guess is that I can simply change the ConnectionString at run time and have it connect to whichever one I want. For example, I could do the following:

Dim MyTable As MyDataSet.MyDataSetDataTable
Dim MyAdapter As New MyDataSetTableAdapters.MyDataTableAdapter()
' Assuming that MyAdapter has a Connection String already defined which points it to MyDatabase
If (Me.Testing = True) Then
    'TestConnectionString is a connection string that points to MyTestDatabase
    MyAdapter .Connection.ConnectionString = TestConnectionString
End If
' The following should get data from the original database if Testing = False, and from the test database if Testing = True, right?
MyTable = MyAdapter.GetData()

At this point, since MyTestDatabase is basically an exact copy of MyDatabase, any Updates/Inserts/Deletes/etc... should affect the test database and not the original database without having to include any other code, right?

Or would that cause problems since I created the data set at build time meaning it's expecting that specific connection string?

 

[VentureFree]

I've got to remember that I can test things myself

Okay, I created some dummy tables and tested this out myself. I can actually do this, but with a few modifications to my original code (which I wrote without testing anyway).

Dim MyTable As MyDataSet.MyDataSetDataTable
Dim MyAdapter As New MyDataSetTableAdapters.MyDataTableAdapter()

' I need to make sure the connection is open before changing the database
MyAdapter.Connection.Open()

' The original data set is pointing to my main database, so I need to do the following
If (Me.Testing = True) Then
    ' Since everything else is the same in this case I just need to change the name of the database that it's pointing to
    MyAdapter.Connection.ChangeDatabase("MyTestDatabase")
End If

' Now the following should get data from the original database if Testing = False, and from the test database if Testing = True
MyTable = MyAdapter.GetData()
' The same is true for any updates/insert/deletes/etc...

' And of course I don't want to forget to do this...
MyAdapter.Connection.Close()

Seriously, I need to remember that I can probably figure this stuff out on my own if I just mess around with it a little.

Disclaimer: I haven't tested this exact code either, though it's based on code that has been tested. Be careful if you're going to try and do this yourself.

 

[Hack]

My initial guess is that I can simply change the ConnectionString at run time

That is exactly what I do at runtime in all of my apps.

I have a drop down menu item that contains two choices:

Production
Test

The menu item is visible only to System Admins so users do not even know it exists.

The code in each menu item is very basic.

It closes the existing connection and opens a new connection with a different connection string.

It can be a bit confusing remembering where you are however, so when I switch to test, I set a global string variable that I've predeclared to the word "Test" and that is displayed the caption of each form.

Naturally, it is not displayed when I'm in production which is the default.

 

[cjard]

Or would that cause problems since I created the data set at build time meaning it's expecting that specific connection string?

Conditional compilation:

If somethingDumbHappened Then

#If DEBUG Then
  //code for when the IDE is in debug mode goes here
  MessageBox.Show("Youre the dumbest coder on earth")
#Else
  //code for release here
  SendAutoErrorReport() ' sends emails to helpdesk, don't want it in debugging phase
  MessageBox.Show("There was a problem, and it has been automatically reported")
#End If

End If

What gets compiled into the exe depends on what mode Visual Stuio is in