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;
}


No comments:

Post a Comment

Labels

.net .Net Instrumentation logging .net localization Agile amazon amazon elasticache amazon services AppDomain Application Domain architecture asp ASP.Net authentication authentication mechanisms Byte order mark c# cache cdata certifications class classic mode cloud cloud computing cluster code-behind Combobox compilation Configuration providers configurations connection connectionString constructors control controls contructor CSV CTS .net types conversion database DataGridView DataSource DataTable DataType DBML delegates design pattern dispose double encoding Entity framework Events exception handling expiry fault contracts fault exceptions function pointers functions generics help HostingEnvironmentException IIS inner join instance management integrated mode javascript join left outer join LINQ LINQ join LINQ to SQL memory leak methods microsoft modes in IIS MSIL multiple catch blocks no primary key Nullable Osmos Osmotic Osmotic communication Osmotic communications page events page life cycle partial class PMI preserve precision points private contructor ProcessExit Project management properties property protect connectionString providerName providers query regular expression repository Responsive Web Design return type run-time RWD Saas self join session session expiry sessions singelton singleton pattern software as a service source control system SQLMetal string toolstrip ToolStrip controls ToolStripControlHost tortoise SVN ToString() try catch finally update wcf web application web design web site web.config where-clause xml