TSQL + C#: Dynamically changing the Database in use

I have stumbled across a problem and I can't think of a nice, SAFE way of completing it. I have a wrapper class around the "Sql.Helper" class (Microsoft.ApplicationBlocks.Data) and I am using Parameters everywhere.

I am trying to change the current DB in use for C#

USE @DBName

That code can't be parametarised, and thus everywhere I read suggests running that as dynamic SQL (shudder) like so

DECLARE @SQL_SCRIPT NVARCHAR(100); 
SET @SQL_SCRIPT = REPLACE('USE {DBName};', '{DBName}', @DBName); 
exec sp_executesql @SQL_SCRIPT;

However, outside of this "sp_executesql" 开发者_Python百科the DB remains unchanged. I then thought about appending this to the front of all the SQL calls, however this will not help.

When running this, the profiler tells me the code is finally run as:

exec sp_executesql N'DECLARE @SQL_SCRIPT NVARCHAR(100); SET @SQL_SCRIPT = REPLACE(''USE {DBName};'', ''{DBName}'', @DBName); exec sp_executesql @SQL_SCRIPT;',N'@DBName nvarchar(100)',@DBName=N'MyDatabaseName'

it is contained in yet another "exec sp_executesql" by default :(

Currently, and I know this isn't the best method, but I am doing the following at the start of each call.

commandText = "USE " + this._currentDB + ";" + commandText;

Does anyone know a proper way of selecting a DB for this solution?

p.s. this._currentDB is SQL escaped (as best as I can)

---

You can change the database in your connection with the SqlConnection.Database property.

EDIT: You have to use the SqlConnection.ChangeDatabase method.

---

Your connection string contains the database that you are connected to. I would just change the database in the connection string and you will connect to the correct database.

---

Yes, the proper way of changing the DB is by changing the connection string.

You can get the connection string from your App.config and replace the database name programmatically.

string connString = ConfigurationManager.ConnectionStrings["YourKey"].ConnectionString;

Now replace the DB name for the new one before you create a new instance of DBConnection

---

Exactly how dynamic does it need to be?

Unless you have an unknown number of databases with unknown names, why not just create a ConnectionString for each one and then select the correct one based on an Enum, switch case, If-Else or similar.