#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

#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

#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