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