ADO.NET 3.5 Cookbook, 2nd Edition

1.15. Changing the Database for an Open Connection

Problem

You want to change the database that a connection uses without recreating the connection.

Solution

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.

The C# code in Program.cs in the project ChangeConnectionDatabase is shown in Example 1-16.

Example 1-16. File: Program.cs for ChangeConnectionDatabase solution

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(); ...