Results 1 to 6 of 6

Thread: [2005] Generic Provider Factory Class

  1. #1

    Thread Starter
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,712

    [2005] Generic Provider Factory Class

    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:
    Code:
    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:
    Code:
    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.
    Code:
    #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
    Last edited by Jenner; Mar 24th, 2008 at 02:27 PM.

  2. #2

    Thread Starter
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,712

    Re: [2005] Generic Provider Factory Class

    Code:
    #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

  3. #3

    Thread Starter
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,712

    Re: [2005] Generic Provider Factory Class

    Code:
    #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.
    Last edited by Jenner; Mar 24th, 2008 at 02:25 PM.

  4. #4
    Evil Genius alex_read's Avatar
    Join Date
    May 2000
    Location
    Espoo, Finland
    Posts
    5,538

    Re: [2005] Generic Provider Factory Class

    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.

    Please rate this post if it was useful for you!
    Please try to search before creating a new post,
    Please format code using [ code ][ /code ], and
    Post sample code, error details & problem details

  5. #5

    Thread Starter
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,712

    Re: [2005] Generic Provider Factory Class

    Nice! I'll have to check it out!
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  6. #6
    Freelancer akhileshbc's Avatar
    Join Date
    Jun 2008
    Location
    Trivandrum, Kerala, India
    Posts
    7,652

    Re: [2005] Generic Provider Factory Class

    Hi

    Do you have any updates for this submission ?

    Regards,
    - Akhilesh

    If my post was helpful to you, then express your gratitude using Rate this Post.
    And if your problem is SOLVED, then please Mark the Thread as RESOLVED (see it in action - video)
    My system: AMD FX 6100, Gigabyte Motherboard, 8 GB Crossair Vengance, Cooler Master 450W Thunder PSU, 1.4 TB HDD, 18.5" TFT(Wide), Antec V1 Cabinet

    Social Group: VBForums - Developers from India


    Skills: PHP, MySQL, jQuery, VB.Net, Photoshop, CodeIgniter, Bootstrap,...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width