Monday, May 9, 2011

Changing database when using ADO.NET

I like ADO.NET and the entity framework, but it also has it shortcomings.
Recently I discovered another shortcoming.
I have a lot databases on the same server, with identical table design, the only reason for using two databases, are for security purposes. The problem here is that the Entity Framework, doesn't allow me to change database, when I need to query all the databases. Of course you can change to connection string at runtime, but this also creates a new connection, and with 200 databases, it is alot of connections.
The solution I came up with was to use the underlying StoreProvider's ChangeDatabase method, the extension method looks like this:
public static void ChangeDatabase(this ObjectContext context, string databaseName)
{
  DbConnection connection = ((EntityConnection)context.Connection).StoreConnection;
            
  // The connection must be open before changing database.
  if (connection.State != ConnectionState.Open)
  {
    connection.Open();
  }

  connection.ChangeDatabase(databaseName);
}
A very easy to use extension method, which works as long as the underlying store provider supports changing the database. Of course you have to ensure that the table schemas are exactly alike, otherwise all kinds of errors could occur.

No comments:

Post a Comment