VB.NET 2010 - Generic Data Provider Factory

This code example uses reflection, factories, generics, and linq to dynamically load assemblies at runtime.

The example below allows me to distribute my data providers separately from my application. It very easily allows the use of a different data provider without having a reference to it in my application.

The reason for the custom ProviderFactory is to include overloaded constructors to match all the available constructor options instead of being limited to the parameterless defaults inherited from System.Data.Common.DbProviderFactory.

Usage example 1 makes a connection to a server and disconnects

Code:

---

        Dim Factory As New GenericDbProviderFactory("C:\MySql.Data.dll")
        Dim cs As String = "server=example.com;database=mydb;user id=myuserid;password=mypassword;pooling=false; connect timeout=30;charset=utf8;"


        Dim connection As DbConnection = Factory.CreateConnection(cs)
        connection.Open()
        Console.WriteLine(connection.ServerVersion)
        connection.Close()

---

Usage example 2 fetches a datatable.

Code:

---

        Dim Factory As New GenericDbProviderFactory("C:\z\current mysql\MySql.Data.dll")
        Dim cs As String = "server=example.com;database=mydb;user id=myuserid;password=mypassword;pooling=false; connect timeout=30;charset=utf8;"
        Dim table As New DataTable("mytablename")
        Dim dataadapter As DbDataAdapter = Factory.CreateDataAdapter("select * from sometable", cs)
        dataadapter.Fill(table)
        For Each row As DataRow In table.Rows
            Console.WriteLine(row("somecolumnname"))
        Next

---

Code:

---

Imports System.Data.Common
Public Class GenericProviderFactory
    Inherits System.Data.Common.DbProviderFactory
    Private assembly As System.Reflection.Assembly
    Public Sub New(ByVal filename As String)
        If My.Computer.FileSystem.FileExists(filename) Then
            assembly = System.Reflection.Assembly.LoadFrom(filename)
        End If
    End Sub
    Public Overrides Function CreateCommand() As DbCommand
        Return InstanceActivator(Of DbCommand)()
    End Function
    Public Overloads Function CreateCommand(ByVal commandText As String) As DbCommand
        Return InstanceActivator(Of DbCommand)(commandText)
    End Function
    Public Overloads Function CreateCommand(ByVal commandText As String, ByVal connection As DbConnection) As DbCommand
        Return InstanceActivator(Of DbCommand)(commandText, connection)
    End Function
    Public Overloads Function CreateCommand(ByVal commandText As String, ByVal connection As DbConnection, ByVal transaction As DbTransaction) As DbCommand
        Return InstanceActivator(Of DbCommand)(commandText, connection, transaction) 'not type safe
    End Function
    Public Overrides Function CreateCommandBuilder() As DbCommandBuilder
        Return InstanceActivator(Of DbCommandBuilder)()
    End Function
    Public Overloads Function CreateCommandBuilder(ByVal adapter As DbDataAdapter) As DbCommandBuilder
        Return InstanceActivator(Of DbCommandBuilder)(adapter)
    End Function
    Public Overrides Function CreateConnection() As DbConnection
        Return InstanceActivator(Of DbConnection)()
    End Function
    Public Overloads Function CreateConnection(ByVal connectionString As String) As DbConnection
        Return InstanceActivator(Of DbConnection)(connectionString)
    End Function
    Public Overrides Function CreateConnectionStringBuilder() As DbConnectionStringBuilder
        Return InstanceActivator(Of DbConnectionStringBuilder)() 'MySql          n/a sqlserverce
    End Function
    Public Overloads Function CreateConnectionStringBuilder(ByVal connStr As String) As DbConnectionStringBuilder
        Return InstanceActivator(Of DbConnectionStringBuilder)(connStr) 'MySql
    End Function
    Public Overrides Function CreateDataAdapter() As DbDataAdapter
        Return InstanceActivator(Of DbDataAdapter)()
    End Function
    Public Overloads Function CreateDataAdapter(ByVal selectCommandText As String, ByVal selectConnectionString As String) As DbDataAdapter
        Return InstanceActivator(Of DbDataAdapter)(selectCommandText, selectConnectionString)
    End Function
    Public Overloads Function CreateDataAdapter(ByVal selectCommandText As String, ByVal selectConnection As System.Data.Common.DbConnection) As DbDataAdapter
        Return InstanceActivator(Of DbDataAdapter)(selectCommandText, selectConnection)
    End Function
    Public Overloads Function CreateDataAdapter(ByVal selectCommand As System.Data.Common.DbCommand) As DbDataAdapter
        Return InstanceActivator(Of DbDataAdapter)(selectCommand) 'not type safe
    End Function
    Public Overrides Function CreateDataSourceEnumerator() As DbDataSourceEnumerator
        Throw New NotImplementedException
    End Function
    Public Overrides ReadOnly Property CanCreateDataSourceEnumerator As Boolean
        Get
            Throw New NotImplementedException
        End Get
    End Property
    Public Overrides Function CreateParameter() As DbParameter
        Throw New NotImplementedException
    End Function
    Public Overrides Function CreatePermission(ByVal state As System.Security.Permissions.PermissionState) As System.Security.CodeAccessPermission
        Throw New NotImplementedException
    End Function
    Public Overrides Function ToString() As String
        Return Me.GetType.ToString
    End Function
    Private Function InstanceActivator(Of thistype)(ByVal ParamArray args() As Object) As thistype
        Dim find As Type = GetType(thistype)
        Dim validTypes As IEnumerable = assembly.GetTypes().Where(Function(t) find.IsAssignableFrom(t))
        For Each typ As Type In validTypes
            Dim inst As Object = Activator.CreateInstance(typ, args)
            Return DirectCast(inst, thistype)
        Next
        Return Nothing
    End Function
End Class

---