How to switch databases using ADO.NET?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a webpage, developed as a utility (and "Hello World"-type
ASP.NET project) to allow our developers to select a database name from
a dropdown list. Once a database has been selected, a list of all
user-defined stored procedures in the database is displayed. I've
already completed this page in Classic ASP, and now I'm trying to
develop a comparable page using ASP.NET 2.0 (& VS.NET 2005).

Once I've captured the database name, what is the best way to establish
a connection to that database? I've already added an entry in
Web.config's <connectionStrings> section, that allows me to connect to
the server's [master] database (to get a list of all databases on that
server). But that's static -- how do I dynamically connect to a
database, depending on which one the user has selected?

Any advice would be greatly appreciated. Thanks in advance!


-= Tek Boy =-
 
The one thing I don't like about ChangeDatabase() is that it requires
an open connection before it will work. In any case, I finally figured
out how to use DbConnectionStringBuilder class to open a new connection
to the user-selected database:

================================================================================
// [dbName] contains the user-selected database name.
DbConnectionStringBuilder dbConnStringBuilder = new
DbConnectionStringBuilder();
dbConnStringBuilder.ConnectionString =
WebConfigurationManager.ConnectionStrings["MasterDatabase"].ConnectionString;
dbConnStringBuilder["Initial Catalog"] = dbName;

/// Connect to the user-selected database w/ the new connection string.
selectedDatabaseConnection = new SqlConnection();
selectedDatabaseConnection.ConnectionString =
dbConnStringBuilder.ConnectionString;
selectedDatabaseConnection.Open();
================================================================================


Thank you for replying!


-= Tek Boy =-


W.G. Ryan said:
Using the ConnectionStringBuilder will allow you to build a new connection
string, but if you're already connected to Master, you can also use
http://msdn.microsoft.com/library/d...ientsqlconnectionclasschangedatabasetopic.asp
the ChangeDatabase method of the SqlConnection if you're using SqlServer
I have a webpage, developed as a utility (and "Hello World"-type
ASP.NET project) to allow our developers to select a database name from
a dropdown list. Once a database has been selected, a list of all
user-defined stored procedures in the database is displayed. I've
already completed this page in Classic ASP, and now I'm trying to
develop a comparable page using ASP.NET 2.0 (& VS.NET 2005).

Once I've captured the database name, what is the best way to establish
a connection to that database? I've already added an entry in
Web.config's <connectionStrings> section, that allows me to connect to
the server's [master] database (to get a list of all databases on that
server). But that's static -- how do I dynamically connect to a
database, depending on which one the user has selected?

Any advice would be greatly appreciated. Thanks in advance!


-= Tek Boy =-
Click to expand...
Click to expand...
 
Back
Top