ADO.NET Cookbook

1.18. 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 sample code creates a Connection to the Northwind database using the SQL Server .NET data provider. The connection is changed to use the pubs database. Finally the connection is closed. The Database property of the SqlConnection object is displayed throughout the sample for the different connection states.

The C# code is shown in Example 1-12.

Example 1-12. File: ChangeDatabaseForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Text; using System.Data; using System.Data.SqlClient; // . . . StringBuilder result = new StringBuilder( ); // Create the connection accessing Northwind database. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); result.Append("Connection String:" + Environment.NewLine); result.Append(conn.ConnectionString + Environment.NewLine + Environment.NewLine); // Open the connection. conn.Open( ); result.Append("Connection.State: " + conn.State + Environment.NewLine); result.Append("Database: " + conn.Database + Environment.NewLine); // Change the database to pubs. conn.ChangeDatabase("pubs"); result.Append("Database: " + conn.Database + Environment.NewLine); // Close the connection. conn.Close( ); result.Append("Connection.State: " + conn.State ...