How to let user dynamically specify database provider and connection details in C# EF4.1 Code First?

Question

I am in the process of creating a C# app to replace a VB6 app that uses a MySQL database where multiple copies of the app use the same database. The new app must be able to use the current MySQL database but I would also like it to be database agnostic so future instances of the app can use whatever server the user wants. In a perfect world, I would like the app on first run to present the user with a dialog that lets them choose the database type (MySQL, SQL Server, etc...) and specify the server ip, user, password, and database name. The app would then connect to that server and either use the database if it is already there or create a new database if it isn't.

Using Code First I have gotten to the point where I understand how to use the existing database or create a new one but only by hard coding the connection string in the App.config file.

    <add name="GumpIndexDatabase"
     connectionString="server=localhost;userid=123;password=123;port=3306;database=gump_new_data;pooling=false;"
     providerName="MySql.Data.MySqlClient"
     />

I can change the connection string and provider before launching the app and everything works as expected. I can also change the connection string after launch, but not the provider, and I have to know whether the provider is MySQL or MSSQL in order to get the connection string details correct (ex: user or userid)

class GumpIndexDatabase: DbContext
{
    public GumpIndexDatabase(string connectionName)
        : base(MakeConnectionString(connectionName))
    {
    }
    private static string MakeConnectionString(string connectionName)
    {
         if (connectionName=MySQL) {
             //return MySQL string
         } else {
             //return SQL Server string
         }
    }

Hours of searching have not turned up an example of how to do such a thing, so I'm suspecting it isn't allowed or recommended, even though it seems like such a simple thing. I have seen some articles on connection string builder but did not understand how to get a database specific string from the generic objects.

So the simple question: how to specify the database connection details at run time?

Answer 1

I wouldn't recommend enforcing this feature, unless you 100% positive you cannot live without it. It adds a lot of maintenance tasks, that may not be obvious just now (such as updating to newer versions, bug fixes, spending lots of time to figure out common interfaces, maintaining those interfaces, hell lot of testing, etc). So unless it is a requested business feature - forget about it.

However, given you know what you are doing, this problem is generally solved via interfaces. There may be these common interfaces (it's a task by itslef to figure out them):

• IConnection
• IDataProvider
• IRepository<T>

At the moment you will implement interfaces using MySql database, such as class MySqlConnection : IConnection. If you need MS SQL, class MsSqlConnection : IConnection.

Effectively you must abstract all the functionality into common interfaces. You will have to provide implementations for each database/storage engine you want to support. At runtime, you will use IoC container and DI principle to set up the current implementation. All the child dependencies will use interfaces passed in as parameters to constructor (or properties or methods)