Home » Creating a database independent connection in your application

  • Creating a database independent connection in your application

    Creating a database independent connection in your application
    When writing database code many times the code is database specific. The code in this sample is not.
     
    First you must add to the config file (web.config or MyApplication.exe.config) an appSettings section like this:
     

    <appSettings>

      <add key=”dbAssemblyName” value=”System.Data, version=1.0.5000.0, publicKeyToken=b77a5c561934e089, culture=neutral” />
      <add key=”connectionTypeName” value=”System.Data.SqlClient.SqlConnection” />
      <add key=”commandTypeName” value=”System.Data.SqlClient.SqlCommand” />
      <add key=”connectionString” value=”data source=localhost;initial catalog=pubs;integrated security=true” />
    </appSettings>
     
     
    And then write code like this:
     
     
    string dbAssemblyName = ConfigurationSettings.AppSettings[“dbAssemblyName”];
    string connectionTypeName = ConfigurationSettings.AppSettings[“connectionTypeName”];
    string commandTypeName = ConfigurationSettings.AppSettings[“commandTypeName”];
    string connectionString = ConfigurationSettings.AppSettings[“connectionString”];

    ObjectHandle obj;
    obj = Activator.CreateInstance(dbAssemblyName, connectionTypeName);
    IDbConnection con = obj.Unwrap() as IDbConnection;
    con.ConnectionString = connectionString;
    con.Open();
    obj = Activator.CreateInstance(dbAssemblyName, commandTypeName);
    IDbCommand com = obj.Unwrap() as IDbCommand;
    com.CommandText = “SELECT au_fname + ‘ ‘ + au_lname AS Author FROM authors”;
    com.Connection = con;
    IDataReader reader = com.ExecuteReader();

    Share this