Flexible data provider class

[kevininstructor]

The class presented here provides developers with simplified code to switch from one database provider to another database provider regardless of the type of provider, unlike System.Data.Common.DbProviderFactories in the .NET Framework that may not provide you with a specific provider such as IBM DB2. Another benefit of this class when used with IDbConnection, IDbCommand and IDataReader interfaces allows developers to code for one data provider then switch to another data provider with very little effort. The main sections of code, which would be targets for change, would be SQL elements, which are specific to a particular provider.

Example, a solution used MS-Access as a backend and has been targeted to migrate to a IBM-DB2 backend. The code presented below needs only two elements changes, provider type and connection string.

Note: The attached project is VS2008 with an MS-Access 2007 database.

MS-Access

Dim MainConnection As IDbConnection = New DatasourceManager( _
    DatasourceManager.EnumProviders.IDB2, _
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Demo.accdb").GetConnection()

IBM-DB2

Dim MainConnection As IDbConnection = New DatasourceManager( _
    DatasourceManager.EnumProviders.OLEDB, _
    "datasource=Revenue3;DefaultCollection=DEV_DEMO").GetConnection()

Both would use the following to return data

If Not MainConnection Is Nothing Then
    Dim cmd As IDbCommand
    cmd = MainConnection.CreateCommand
    cmd.CommandText = "SELECT * FROM Customer"
    cmd.Connection = MainConnection
    cmd.ExecuteNonQuery()

    Dim dt As New DataTable
    Dim dr As IDataReader = cmd.ExecuteReader
    dt.Load(dr)

    DataGridView1.DataSource = dt

End If

The next step would be to centralize the provider and connection string perhaps to a property or variable. One option is to store this information in a configuration file. In the attached demo project, I use an XML configuration file to store various configurations along with a class to make it easy to retrieve configuration information. Since good programming means not working with production data the configuration class coupled with the configuration file allow a developer to switch between test, quality assurance and production environments. Since in the attached demonstration I am dealing with one form all variables are marked as private. In a project that most access this information from various modules and forms you could create a Singleton class accessible to all modules and forms that need the connection information.

Private ConfigurationName As String
Private Sections As New AppSections("ApplicationSettings.xml")
Private DefaultEnvironment As String = ""
Private MainConnectionString As String = ""

Private MainConnection As IDbConnection = Nothing
Private Provider As DatasourceManager.EnumProviders
Private Sub frmMainForm_Load() Handles MyBase.Load

    DefaultEnvironment = Sections.DefaultEnvironment
    ConfigurationName = Sections.UseConfiguration
    MainConnectionString = Sections.Configuration(ConfigurationName, _
                                                  DefaultEnvironment).ConnectionString
    Provider = Sections.Configuration(ConfigurationName, DefaultEnvironment).Provider
End Sub

To load data

Private Sub LoadAllData()

    MainConnection = New DatasourceManager(Provider, MainConnectionString).GetConnection()

    If Not MainConnection Is Nothing Then
        Dim cmd As IDbCommand
        cmd = MainConnection.CreateCommand
        cmd.CommandText = "SELECT FirstName, LastName, City FROM Customers ORDER BY LastName"
        cmd.Connection = MainConnection
        cmd.ExecuteNonQuery()

        Dim dt As New DataTable
        Dim dr As System.Data.IDataReader = cmd.ExecuteReader
        dt.Load(dr)

        DataGridView1.DataSource = dt

    End If

End Sub

How the XML file is setting up. Under ApplicationSettings section are the configurations, one for each data provider. Each element under the configuration section store the environment (test/development, QAC and production), provider and several other items demoing that other information may be stored too such as a log file location and name.

  <Configuration Name="DB2_CUSTOMERS">
    <Environment Name="Development">
      <Connection>datasource=Revenue3;DefaultCollection=DEV_DEMO</Connection>
      <Provider>IDB2</Provider>
      <ImageFolder/>
      <AuditFolder/>
      <LogFile/>
    </Environment>
    <Environment Name="QAC">
      <Connection>datasource=Revenue2;DefaultCollection=QAC_DEMO</Connection>
      <Provider>IDB2</Provider>
      <ImageFolder/>
      <AuditFolder/>
      <LogFile/>
    </Environment>
    <Environment Name="Production">
      <Connection>datasource=Revenue1;DefaultCollection=PROD_DEMO</Connection>
      <Provider>IDB2</Provider>
      <ImageFolder/>
      <AuditFolder/>
      <LogFile/>
    </Environment>
  </Configuration>

The following indicates which configuration to use and which environment to work out from.

<Use Default="Development" ConfigurationName="MS_ACCESS_CUSTOMERS"/>

Another thought is if you have to retrieve data from various data source. In the example below each section has an element for the main library from where application data resides along with an element for two other libraries/databases.

<?xml version="1.0" encoding="utf-8"?>
<ApplicationSettings>
  <Configuration Name="Main">
    <Environment Name="Development">
      <Connection></Connection>
      <SecurityLibrary></SecurityLibrary>
      <CountryLibrary></CountryLibrary>
      <ImageFolder />
      <AuditFolder />
      <LogFile> </LogFile>
    </Environment>
    <Environment Name="QAC">
      <Connection></Connection>
      <SecurityLibrary></SecurityLibrary>
      <CountryLibrary></CountryLibrary>
      <ImageFolder />
      <AuditFolder />
      <LogFile></LogFile>
    </Environment>
    <Environment Name="Production">
      <Connection></Connection>
      <SecurityLibrary></SecurityLibrary>
      <CountryLibrary></CountryLibrary>
      <ImageFolder />
      <AuditFolder />
      <LogFile> </LogFile>
    </Environment>
  </Configuration>
  <Use Default="Development" />
</ApplicationSettings>

How to configure the class DatasourceManager for providers other than OleDbConnection, SqlConnection and OdbcConnection is to review the code within the custom constrain “USE_DB2” which is defined under “My Project”, Compile, Advance compiler option.

Special note, EnumProviders order must match the order in the type array ConnectionTypes in DatasourceManager class.