Changing the Database for an Open Connection (Ado.net)

You want to change the database that a connection uses without recreating the connection.
Use the ChangeDatabase() method to change the database for a connection.
The solution creates a Connection to the AdventureWorks database using the SQL Server .NET data provider. The database for the connection is then changed to use the ReportServer database. Finally, the connection is explicitly closed.

Sample:

using System;
using System.Data.SqlClient;
namespace ChangeConnectionDatabase
{
class Program
{
static void Main(string[] args)
{
string sqlConnectString = "Data Source=(local);" +
"Integrated security=SSPI;Initial Catalog=AdventureWorks;";
using (SqlConnection connection = new SqlConnection(sqlConnectString))
{
Console.WriteLine("ConnectionString = {0}\n", connection.ConnectionString);
// Open the connection
connection.Open();
Console.WriteLine("=> Connection opened.\n");
Console.WriteLine("Connection.State = {0}", connection.State);
Console.WriteLine("Database = {0}\n", connection.Database);
// Change the database.
connection.ChangeDatabase("ReportServer");
Console.WriteLine("=> Database changed to ReportServer.\n");
Console.WriteLine("Connection.State = {0}", connection.State);
Console.WriteLine("Database = {0}\n", connection.Database);
// Close the connection
connection.Close();
Console.WriteLine("=> Connection closed.\n");
Console.WriteLine("Connection.State = {0}", connection.State);
Console.WriteLine("Database = {0}", connection.Database);
}
Console.WriteLine("\nPress any key to continue.");
Console.ReadKey();
}
}
}