[2005] Generic Provider Factory Class

[Jenner]

While ADO.NET 2.0 has it's own methods and specifications for ProviderFactories built directly into the providers themselves, I found the code limiting and unsupported. Limiting, in that none of the built-in ProviderFactory methods operated like the "New" constructors of the objects they were generating. Unsupported, in that some 3rd party providers didn't include a factory method, which would blow down all efforts to use such a system with them like a house of cards.

This object turns this:

Dim factory As DbProviderFactory = GetFactory()
Dim connection As DbConnection = factory.CreateConnection()
 
connection.ConnectionString = strConString
 
Dim adapter As DbDataAdapter = factory.CreateDataAdapter()
Dim selectCommand As DbCommand = factory.CreateCommand()
 
selectCommand.CommandText = strSQL
selectCommand.Connection = connection
 
adapter.SelectCommand = selectCommand

Into this:

Dim factory As DbProviderFactory = GetFactory()
Dim connection As DbConnection = factory.CreateConnection(strConString)
 
Dim adapter As DbDataAdapter = factory.CreateDataAdapter(strSQL, connection)

This example is configured for 7 different .NET data providers. The Pervasive v8.7 to 9.x provider being one that doesn't include a built-in factory. Different providers can easily be added in or taken out of the class by tweaking a few functions.

This code uses precompiler flags, which you can toggle at the top of the code block or integrate them into your builds for the Configuration Manager. With them, you can set which database systems you wish to support and those you choose not to, don't even need their assemblies included in the final build.

Code in 3-parts due to forum limitations. File is attached below for simplicity. Also, as a warning, thorough testing of it in all modes of operation hasn't been completed. Like all submissions, use at your own risk.

#Const USE_ODBC = False         'ODBC Generic Provider
#Const USE_OLEDB = False        'OLEDB Generic Provider
#Const USE_MSSQL = True         'Microsoft SQL Server Provider
#Const USE_ORACLE = False       'Oracle Provider
#Const USE_MYSQL = True         'MySQL Provider
#Const USE_SQLITE = False       'SQLite Provider
#Const USE_PERVASIVE = False    'Pervasive v8.7 to v9.x Provider

#If USE_ODBC Or USE_OLEDB Or USE_MSSQL Or USE_ORACLE Or USE_MYSQL Or USE_SQLITE Or USE_PERVASIVE Then
Imports System
Imports System.Reflection
Imports System.Data
Imports System.Data.Common

#If USE_ODBC Then
Imports System.Data.Odbc
#End If
#If USE_OLEDB Then
Imports System.Data.OleDb
#End If
#If USE_MSSQL Then
Imports System.Data.SqlClient
#End If
#If USE_ORACLE Then
Imports System.Data.OracleClient
#End If
#If USE_MYSQL Then
Imports MySql.Data.MySqlClient
#End If
#If USE_SQLITE Then
Imports System.Data.SQLite
#End If
#If USE_PERVASIVE Then
Imports Pervasive.Data.SqlClient
#End If

Public Enum ProviderType
#If USE_ODBC Then
    Odbc
#End If
#If USE_OLEDB Then
    OleDb
#End If
#If USE_MSSQL Then
    SQLServer
#End If
#If USE_ORACLE Then
    Oracle
#End If
#If USE_MYSQL Then
    MySQL
#End If
#If USE_SQLITE Then
    SQLite
#End If
#If USE_PERVASIVE Then
    Pervasive
#End If
End Enum

Public Class clsProviderFactory
    Private typConnectionTypes As New List(Of Type)
    Private typCommandTypes As New List(Of Type)
    Private typDataAdapterTypes As New List(Of Type)
    Private typParameterTypes As New List(Of Type)
    Private typCommandBuilderTypes As New List(Of Type)
    Private typConnectionStringBuilderTypes As New List(Of Type)

    Private _provider As ProviderType

    Public Property Provider() As ProviderType
        Get
            Return _provider
        End Get
        Set(ByVal value As ProviderType)
            _provider = value
        End Set
    End Property

#Region "Constructor"
    Public Sub New()
        ConstructLists()
    End Sub

    Public Sub New(ByVal provider As ProviderType)
        _provider = provider
        ConstructLists()
    End Sub

    Private Sub ConstructLists()
#If USE_ODBC Then
        typConnectionTypes.Add(GetType(OdbcConnection))
        typCommandTypes.Add(GetType(OdbcCommand))
        typDataAdapterTypes.Add(GetType(OdbcDataAdapter))
        typParameterTypes.Add(GetType(OdbcParameter))
        typCommandBuilderTypes.Add(GetType(OdbcCommandBuilder))
        typConnectionStringBuilderTypes.Add(GetType(OdbcConnectionStringBuilder))
#End If
#If USE_OLEDB Then
        typConnectionTypes.Add(GetType(OleDbConnection))
        typCommandTypes.Add(GetType(OleDbCommand))
        typDataAdapterTypes.Add(GetType(OleDbDataAdapter))
        typParameterTypes.Add(GetType(OleDbParameter))
        typCommandBuilderTypes.Add(GetType(OleDbCommandBuilder))
        typConnectionStringBuilderTypes.Add(GetType(OleDbConnectionStringBuilder))
#End If
#If USE_MSSQL Then
        typConnectionTypes.Add(GetType(SqlConnection))
        typCommandTypes.Add(GetType(SqlCommand))
        typDataAdapterTypes.Add(GetType(SqlDataAdapter))
        typParameterTypes.Add(GetType(SqlParameter))
        typCommandBuilderTypes.Add(GetType(SqlCommandBuilder))
        typConnectionStringBuilderTypes.Add(GetType(SqlConnectionStringBuilder))
#End If
#If USE_ORACLE Then
        typConnectionTypes.Add(GetType(OracleConnection))
        typCommandTypes.Add(GetType(OracleCommand))
        typDataAdapterTypes.Add(GetType(OracleDataAdapter))
        typParameterTypes.Add(GetType(OracleParameter))
        typCommandBuilderTypes.Add(GetType(OracleCommandBuilder))
        typConnectionStringBuilderTypes.Add(GetType(OracleConnectionStringBuilder))
#End If
#If USE_MYSQL Then
        typConnectionTypes.Add(GetType(MySqlConnection))
        typCommandTypes.Add(GetType(MySqlCommand))
        typDataAdapterTypes.Add(GetType(MySqlDataAdapter))
        typParameterTypes.Add(GetType(MySqlParameter))
        typCommandBuilderTypes.Add(GetType(MySqlCommandBuilder))
        typConnectionStringBuilderTypes.Add(GetType(MySqlConnectionStringBuilder))
#End If
#If USE_SQLITE Then
        typConnectionTypes.Add(GetType(SQLiteConnection))
        typCommandTypes.Add(GetType(SQLiteCommand))
        typDataAdapterTypes.Add(GetType(SQLiteDataAdapter))
        typParameterTypes.Add(GetType(SQLiteParameter))
        typCommandBuilderTypes.Add(GetType(SQLiteCommandBuilder))
        typConnectionStringBuilderTypes.Add(GetType(SQLiteConnectionStringBuilder))
#End If
#If USE_PERVASIVE Then
        typConnectionTypes.Add(GetType(PsqlConnection))
        typCommandTypes.Add(GetType(PsqlCommand))
        typDataAdapterTypes.Add(GetType(PsqlDataAdapter))
        typParameterTypes.Add(GetType(PsqlParameter))
        typCommandBuilderTypes.Add(GetType(PsqlCommandBuilder))
        'Pervasive has no ConnectionStringBuilder
#End If
    End Sub

    Public Function InitializeLifetimeService() As Object
        'This object has to live "forever"
        Return Nothing
    End Function
#End Region

clsProviderFactory.vb