Change Databases Without Closing Connection

by William Ryan

Traditionally, when I've had to switch between databases, I would usually create a new Connection object and change its ConnectionString property to point to the new database.  Something about this approach made me uncomfortable and as it turns out, for good reason.  Once again, it turns out that if something seems ackward, it's probably because you are doing it incorrectly.

Anyway, there is a very clean and elegant way to change a Database when you already have a live connection to the server, namely SqlConnection.ChangeDatabase  if you are using SqlClient, OleDbConnection.ChangeDatabase  for Oledb and OdbcConnection.ChangeDatabase  for ODBC.  From the documentation at MSDN, I couldn't find an implementation for an OracleConnection , but it's probably safe to assume one will be included in the next release of the framework since all of the others are simply implementations of the IDbConnection.ChangeDatabase method.
Anyway, all you need to do is open your connection to the original database(s), do whatever it is you wanted to do with them, call the ChangeDatabase method passing in the name of the database you want to change TO and it's done.  There are no overloads on this (which in this context isn't surprising) so all you do is pass in a string literal and it's done.

VB.NET

Dim cn as New SqlConnection("YourConnectStringHere") cn.Open() cn.ChangeDatabase("NameOfOtherDatabase") cn.Close()

C#

SqlConnection cn = new SqlConnection("YourConnectStringHere"); cn.Open(); cn.ChangeDatabase("NameOfOtherDatabase"); cn.Close();

As you can tell, I haven't wrapped this code in exception handlers and IMHO, it's always good practice to wrap Connection.Open calls in a Try/Catch due to the ever present possibility of something going wrong.  Accordingly, you would probably want to do the same with the ChangeDatabase call b/c you could have possibly misspelled the  name of the new database, not have access to it, or whatever else.  This really has nothign to do with how the method works, but I figured it wouldn't hurt to mention it.