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 + Environment.NewLine); result.Append("Database: " + conn.Database); resultTextBox.Text = result.ToString( );
The ChangeDatabase( )
method is defined in the
IDbConnection
interface that represents a
connection to a data source and is implemented by .NET data providers
for relational databases including those for SQL Server, Oracle, and
OLE DB. The ChangeDatabase( )
method is used to
change 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 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.
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.