1 Attachment(s)
[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
Attachment 63047
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
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.
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.
Re: [2005] Generic Provider Factory Class
Nice! I'll have to check it out! :D
Re: [2005] Generic Provider Factory Class
Hi :wave:
Do you have any updates for this submission ?
Regards,
- Akhilesh
