How to change Database in Enterprise Library 5.0

I 开发者_StackOverflow社区created a db object as

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

But later in code, i want to change the Database name. In previous enterprise version, we use

conn.ChangeDatabase("ABCD");

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

Please advice.

thanks, Mujeeb.

I don't think ChangeDatabase is an Enterprise Library method (I couldn't find it in version 4.1 either). I think it's just an ADO method on IDbConnection.

There are 3 ways I can think of to do what you want:

1. Create a new Database entry in Enterprise Library configuration and use that value
2. Use ADO.NET to change the connection and perform data access
3. 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 as a new entry in configuration and treat it as a separate database. However, if you need to support dynamic databases because the database names are not known at design time or are retrieved from a different system then this won't work.

2. Use ADO.NET

You can retrieve a connection and just use ADO.NET (I think this may be what you were already doing?):

// 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 the EL code with the ADO.NET code feels a bit wrong, though.

3. Create a new Enterprise Library Database Object

Instead of using ADO.NET you can use the Enterprise Library Database class. You can't modify the ConnectionString (it's 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 bit cleaner by adding the change database logic to a helper method or, as in the following, an 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);