How to change the database in the corporate library 5.0

Question

I created a db object as

sqlDB = EnterpriseLibraryContainer.Current .GetInstance<Database>("ProdConn"); 

But later in the code, I want to change the database name. In the previous version of the enterprise, we use

 conn.ChangeDatabase("ABCD"); 

to change the database, but how can we do it here?

Please advice.

Thank you, Mujib.

Answer 1

I do not think ChangeDatabase is an Enterprise Library method (I also could not find it in version 4.1). I think this is just an ADO method on IDbConnection .

There are three ways that I can do to do what you want:

• Create a new database entry in the Enterprise Library configuration and use this value
• Use ADO.NET to change the connection and access to data.
• Programmatically create a new Enterprise Library Database object using a different database value

1. Create a new database entry in Config

Personally, I think this is the cleanest option. Add the database to the new entry in the configuration and treat it as a separate database. However, if you need to maintain dynamic databases, because the database names are unknown at design time or retrieved from another system, then this will not work.

2. Use ADO.NET

You can get a connection and just use ADO.NET (I think it could be what you already did?):

 // Get Original EL DB Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("MYDB"); object result = db.ExecuteScalar(CommandType.Text, "select top 1 name from sysobjects"); Console.WriteLine(result); // Change DB with ADO.NET using (IDbConnection conn = db.CreateConnection()) { conn.Open(); conn.ChangeDatabase("AnotherDB"); using (IDbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "select top 1 RoleName from Roles"; cmd.CommandType = CommandType.Text; result = cmd.ExecuteScalar(); } } Console.WriteLine(result); 

Mixing EL code with ADO.NET code seems a bit wrong.

3. Creating a new Enterprise Library database object

Instead of using ADO.NET, you can use the Enterprise Library Database class. You cannot change ConnectionString (it readonly ), but you can create a new Database object with a new connection string.

 // Get Original EL DB Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("MYDB"); object result = db.ExecuteScalar(System.Data.CommandType.Text, "select top 1 name from sysobjects"); Console.WriteLine(result); // Change Database DbConnectionStringBuilder builder = new DbConnectionStringBuilder() { ConnectionString = db.ConnectionString }; builder["database"] = "AnotherDB"; // Create new EL DB using new connection string db = new GenericDatabase(builder.ConnectionString, db.DbProviderFactory); result = db.ExecuteScalar(CommandType.Text, "select top 1 RoleName from Roles"); Console.WriteLine(result); 

I think this looks better than option 2. We can make it a little cleaner by adding the logic of the change database to the helper method or, as in the following, the extension method:

 public static class DatabaseExtensions { public static Database ChangeDatabase(this Database db, string databaseName) { // Change Database DbConnectionStringBuilder builder = new DbConnectionStringBuilder() { ConnectionString = db.ConnectionString }; builder["database"] = databaseName; // Create new EL DB using new connection string return new GenericDatabase(builder.ConnectionString, db.DbProviderFactory); } } 

...

 // Get Original EL DB Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("MYDB"); object result = db.ExecuteScalar(System.Data.CommandType.Text, "select top 1 name from sysobjects"); Console.WriteLine(result); db = db.ChangeDatabase("AnotherDB"); result = db.ExecuteScalar(CommandType.Text, "select top 1 RoleName from Roles"); Console.WriteLine(result);