How to decide database connection instance dynamically ?

This was a question asked by one interviewer in my early interviews. The scenario was that,
an application makes use of several database providers. How one can decide which connection instance to be used at run time.
The answer lies in the question itslef. I couldn't figure it out at that time.

This can be achieved by ProviderName attribute available in ConnectionString format. Let's do it by an example.

Consider a connection string as,

1 2 3 4 5 6 7 8

<connectionStrings> <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS; Integrated Security=SSPI; AttachDBFilename=|DataDirectory|aspnetdb.mdf; User Instance=true" providerName="System.Data.SqlClient" /> </connectionStrings>

Here we have ProviderName as 'System.Data.SqlClient'. The configuration can have multiple connectionStrings with different providerName values. We can retrive providerName value and can create an instance based upon it.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

ConnectionStringSettings connection = ConfigurationManager.ConnectionStrings["LocalSqlServer"]; DbConnection db = null; switch (connection.ProviderName) { case "System.Data.SqlClient": db = new SqlConnection(connection.ConnectionString); break; case "System.Data.OleDb": db = new OleDbConnection(connection.ConnectionString); break; case "System.Data.Odbc": db = new OdbcConnection(connection.ConnectionString ); break; case "System.Data.OracleClient": db = new OracleConnection(connection.ConnectionString); break; }