[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

[Jenner]

#Region "CreateConnection"
    Public Overloads Function CreateConnection() As DbConnection
        Dim conn As DbConnection = Nothing

        Try
            conn = CType(Activator.CreateInstance(typConnectionTypes(CInt(_provider))), DbConnection)
        Catch e As TargetInvocationException
            Throw New SystemException(e.InnerException.Message, e.InnerException)
        End Try

        Return conn
    End Function

    Public Overloads Function CreateConnection(ByVal connectionString As String) As DbConnection
        Dim conn As DbConnection = Nothing
        Dim args As Object() = {connectionString}

        Try
            conn = CType(Activator.CreateInstance(typConnectionTypes(CInt(_provider)), args), DbConnection)
        Catch e As TargetInvocationException
            Throw New SystemException(e.InnerException.Message, e.InnerException)
        End Try

        Return conn
    End Function
#End Region

#Region "CreateCommand"
    Public Overloads Function CreateCommand() As DbCommand
        Dim cmd As DbCommand = Nothing
        CreateCommand_Factory(cmd)

        Return cmd
    End Function

    Public Overloads Function CreateCommand(ByVal cmdText As String) As DbCommand
        Dim cmd As DbCommand = Nothing
        Dim args As Object() = {cmdText}
        CreateCommand_Factory(cmd, args)

        Return cmd
    End Function

    Public Overloads Function CreateCommand(ByVal cmdText As String, ByVal connection As DbConnection) As DbCommand
        Dim cmd As DbCommand = Nothing
        Dim args As Object() = {cmdText, connection}
        CreateCommand_Factory(cmd, args)

        Return cmd
    End Function

    Public Overloads Function CreateCommand(ByVal cmdText As String, ByVal connection As DbConnection, ByVal transaction As DbTransaction) As DbCommand
        Dim cmd As DbCommand = Nothing
        Dim args As Object() = {cmdText, connection, transaction}
        CreateCommand_Factory(cmd, args)

        Return cmd
    End Function

    Private Sub CreateCommand_Factory(ByRef cmd As DbCommand, Optional ByVal args As Object() = Nothing)
        Try
            If IsNothing(args) Then
                cmd = CType(Activator.CreateInstance(typCommandTypes(CInt(_provider))), DbCommand)
            Else
                cmd = CType(Activator.CreateInstance(typCommandTypes(CInt(_provider)), args), DbCommand)
            End If
        Catch e As TargetInvocationException
            Throw New SystemException(e.InnerException.Message, e.InnerException)
        End Try
    End Sub
#End Region

#Region "CreateDataAdapter"
    Public Overloads Function CreateDataAdapter() As DbDataAdapter
        Dim da As DbDataAdapter = Nothing
        CreateDataAdapter_Factory(da)
       
        Return da
    End Function

    Public Overloads Function CreateDataAdapter(ByVal selectCommand As IDbCommand) As DbDataAdapter
        Dim da As DbDataAdapter = Nothing
        Dim args As Object() = {selectCommand}
        CreateDataAdapter_Factory(da, args)

        Return da
    End Function

    Public Overloads Function CreateDataAdapter(ByVal selectCommandText As String, ByVal selectConnection As DbConnection) As DbDataAdapter
        Dim da As DbDataAdapter = Nothing
        Dim args As Object() = {selectCommandText, selectConnection}
        CreateDataAdapter_Factory(da, args)

        Return da
    End Function

    Public Overloads Function CreateDataAdapter(ByVal selectCommandText As String, ByVal selectConnectionString As String) As DbDataAdapter
        Dim da As DbDataAdapter = Nothing
        Dim args As Object() = {selectCommandText, selectConnectionString}
        CreateDataAdapter_Factory(da, args)

        Return da
    End Function

    Private Sub CreateDataAdapter_Factory(ByRef da As DbDataAdapter, Optional ByVal args As Object() = Nothing)
        Try
            If IsNothing(args) Then
                da = CType(Activator.CreateInstance(typDataAdapterTypes(CInt(_provider))), DbDataAdapter)
            Else
                da = CType(Activator.CreateInstance(typDataAdapterTypes(CInt(_provider)), args), DbDataAdapter)
            End If
        Catch e As TargetInvocationException
            Throw New SystemException(e.InnerException.Message, e.InnerException)
        End Try
    End Sub

#End Region

[Jenner]

#Region "CreateParameter"
    Public Overloads Function CreateParameter() As DbParameter
        Dim param As DbParameter = Nothing
        CreateParameter_Factory(param)

        Return param
    End Function

    Public Overloads Function CreateParameter(ByVal strName As String, ByVal value As Object) As DbParameter
        Dim param As DbParameter = Nothing
        Dim args As Object() = {strName, value}
        CreateParameter_Factory(param, args)

        Return param
    End Function

    Public Overloads Function CreateParameter(ByVal strName As String, ByVal dtypType As DbType) As DbParameter
        Dim param As DbParameter = Nothing
        Dim args As Object() = {strName, dtypType}
        CreateParameter_Factory(param, args)

        Return param
    End Function

    Public Overloads Function CreateParameter(ByVal strName As String, ByVal dtypType As DbType, ByVal intsize As Integer) As DbParameter
        Dim param As DbParameter = Nothing
        Dim args As Object() = {strName, dtypType, intsize}
        CreateParameter_Factory(param, args)

        Return param
    End Function

    Public Overloads Function CreateParameter(ByVal strName As String, ByVal dtypType As DbType, ByVal intSize As Integer, ByVal strSourceColumn As String) As DbParameter
        Dim param As DbParameter = Nothing
        Dim args As Object() = {strName, dtypType, intSize, strSourceColumn}
        CreateParameter_Factory(param, args)

        Return param
    End Function

    Private Sub CreateParameter_Factory(ByRef param As DbParameter, Optional ByVal args As Object() = Nothing)
        Try
            If IsNothing(args) Then
                param = CType(Activator.CreateInstance(typParameterTypes(CInt(_provider))), DbParameter)

            Else
                param = CType(Activator.CreateInstance(typParameterTypes(CInt(_provider)), args), DbParameter)
            End If
        Catch e As TargetInvocationException
            Throw New SystemException(e.InnerException.Message, e.InnerException)
        End Try
    End Sub
#End Region

#Region "CreateCommandBuilder"
    Public Overloads Function CreateCommandBuilder() As DbCommandBuilder
        Dim cb As DbCommandBuilder = Nothing

        Try
            cb = CType(Activator.CreateInstance(typCommandBuilderTypes(CInt(_provider))), DbCommandBuilder)
        Catch e As TargetInvocationException
            Throw New SystemException(e.InnerException.Message, e.InnerException)
        End Try

        Return cb
    End Function

    Public Overloads Function CreateCommandBuilder(ByVal daDataAdapter As DbDataAdapter) As DbCommandBuilder
        Dim cb As DbCommandBuilder = Nothing
        Dim args As Object() = {daDataAdapter}
        Try
            cb = CType(Activator.CreateInstance(typCommandBuilderTypes(CInt(_provider)), args), DbCommandBuilder)
        Catch e As TargetInvocationException
            Throw New SystemException(e.InnerException.Message, e.InnerException)
        End Try

        Return cb
    End Function
#End Region

#Region "CreateConnectionStringBuilder"
    Public Overloads Function CreateConnectionStringBuilder() As DbConnectionStringBuilder
#If USE_PERVASIVE Then
        If _provider = ProviderType.Pervasive Then Return Nothing
#End If
        Dim csb As DbConnectionStringBuilder = Nothing
        Try
            csb = CType(Activator.CreateInstance(typConnectionStringBuilderTypes(CInt(_provider))), DbConnectionStringBuilder)
        Catch e As TargetInvocationException
            Throw New SystemException(e.InnerException.Message, e.InnerException)
        End Try

        Return csb
    End Function

    Public Overloads Function CreateConnectionStringBuilder(ByVal strConnectionString As String) As DbConnectionStringBuilder
#If USE_PERVASIVE Then
        If _provider = ProviderType.Pervasive Then Return Nothing
#End If
        Dim csb As DbConnectionStringBuilder = Nothing
        Dim args As Object() = {strConnectionString}
        Try
            csb = CType(Activator.CreateInstance(typConnectionStringBuilderTypes(CInt(_provider)), args), DbConnectionStringBuilder)
        Catch e As TargetInvocationException
            Throw New SystemException(e.InnerException.Message, e.InnerException)
        End Try

        Return csb
    End Function
#End Region
End Class
#End If

Special thanks to mendhak and jmcilhinney for getting me straightened out with this one.

[alex_read]

Hiya Jenner,

If you wanted to take this a step further, you might want to check out the Wrox book "Professional Design Patterns in VB.NET: Building Adaptable Applications". Unfortunately there's no downloadable code to this one, but there's an awesome chapter on extending the data providers in that if it's of interest to you.

[Jenner]

Nice! I'll have to check it out!

[akhileshbc]

Hi

Do you have any updates for this submission ?

Regards,
- Akhilesh