Connect Data ADO NET—Change Database Open Connection

Jump to: navigation, search
Visual C# Tutorials
Database Tutorials

Connecting to Data with ADO.NET

© 2008 O'Reilly Media

Problem: Changing the Database for an Open Connection

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


The output is shown in Figure 1-17.


Figure 1-17. Output for ChangeConnectionDatabase solution

Image:ADOCook2fig1-17.jpg


Discussion

The ChangeDatabase() method is defined in the IDbConnection interface that represents a connection to a data source and is implemented by .NET data providers. The ChangeDatabase() method changes the current database for an open connection. It takes a single parameter that specifies the name of the database to use in place of the current database. The name of the database must be valid or an ArgumentException will be raised. If the connection is not open when the method is called, an InvalidOperationException is raised. A provider-specific exception (e.g., SqlException for SQL Server data provider) is raised if the database cannot be changed for any reason.

The Database property of the Connection object is updated dynamically and returns the current database for an open connection or the name of a database that will be used by a closed connection when it is opened.

When the Connection is closed after ChangeDatabase() is called, the database is reset to that specified in the original connection string.


prevpp.png  nextpp.png
C# Online.NET