I just read Mahesh's article Writing a Generic Data Access Component.
Another way to solve this problem is to utilize the System.Activator class and a factory pattern to create the concrete provider classes as was pointed-out in Dan Fox's article "Design an Effective Data-Access Architecture" (.netmagazine, vol. 2, no. 7). I took this idea and refined it a bit so that the only steps necessary to add a new provider to the factory is to add a new enum and the associated type values to the factory's static type arrays (a total of 5 lines of code).
Here is the sample code. See the attached source code for a test application.
Imports System
Imports System.Reflection
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Namespace CSharpCorner.ProviderFactory
' <summary>
' The collection of ADO.NET data providers that are supported by <see cref="ProviderFactory"/>.
' </summary>
Public Enum ProviderType
' <summary>
' The OLE DB (<see cref="System.Data.OleDb"/>) .NET data provider.
' </summary>
OleDb = 0
' <summary>
' The SQL Server (<see cref="System.Data.SqlClient"/>) .NET data provider.
' </summary>
SqlClient
End Enum 'ProviderType
' <summary>
' The <b>ProviderFactory</b> class abstracts ADO.NET relational data providers through creator methods which return
' the underlying <see cref="System.Data"/> interface.
' </summary>
' <remarks>
' This code was inspired by "Design an Effective Data-Access Architecture" by Dan Fox (.netmagazine, vol. 2, no. 7)
' </remarks>
Public Class ProviderFactory
Private Shared _connectionTypes() As Type = {GetType(OleDbConnection), GetType(SqlConnection)}
Private Shared _commandTypes() As Type = {GetType(OleDbCommand), GetType(SqlCommand)} '
Private Shared _dataAdapterTypes() As Type = {GetType(OleDbDataAdapter), GetType(SqlDataAdapter)}
Private Shared _dataParameterTypes() As Type = {GetType(OleDbParameter), GetType(SqlParameter)}
Private _provider As ProviderType
Sub New() ' force user to specify provider
End Sub 'New
Public Sub New(ByVal provider As ProviderType)
End Sub 'New
Property Provider() As ProviderType
Get
Return _provider
End Get
Set(ByVal Value As ProviderType)_provider = value
End Set
End Property
Overloads Function CreateConnection() As IDbConnection
Dim conn As IDbConnection = Nothing
Try
conn = CType(Activator.CreateInstance(_connectionTypes(CInt(_provider))), IDbConnection)
Catch e As TargetInvocationException
Throw New SystemException(e.InnerException.Message, e.InnerException)
End Try
Return conn
End Function 'CreateConnection
Public Overloads Function CreateConnection(ByVal connectionString As String) As IDbConnection
Dim conn As IDbConnection = Nothing
Dim args As Object() = connectionString
Try
conn = CType(Activator.CreateInstance(_connectionTypes(CInt(_provider)), args), IDbConnection)
Catch e As TargetInvocationException
Throw New SystemException(e.InnerException.Message, e.InnerException)
End Try
Return conn
End Function 'CreateConnection
Overloads Function CreateCommand() As IDbCommand
Dim cmd As IDbCommand = Nothing
Try
cmd = CType(Activator.CreateInstance(_commandTypes(CInt(_provider))), IDbCommand)
Catch e As TargetInvocationException
Throw New SystemException(e.InnerException.Message, e.InnerException)
End Try
Return cmd
End Function 'CreateCommand
Public Overloads Function CreateCommand(ByVal cmdText As String) As DbCommand
Dim cmd As IDbCommand = Nothing
Dim args As Object() = cmdText
Try
cmd = CType(Activator.CreateInstance(_commandTypes(CInt(_provider)), args), IDbCommand)
Catch e As TargetInvocationException
Throw New SystemException(e.InnerException.Message, e.InnerException)
End Try
Return cmd
End Function 'CreateCommand
Public Overloads Function CreateCommand(ByVal cmdText As String, ByVal connection As IDbConnection) As IDbCommand
Dim cmd As IDbCommand = Nothing
Dim args As Object() = {cmdText, connection}
Try
cmd = CType(Activator.CreateInstance(_commandTypes(CInt(_provider)), args), IDbCommand)
Catch e As TargetInvocationException
Throw New SystemException(e.InnerException.Message, e.InnerException)
End Try
Return cmd
End Function 'CreateCommand
Public Overloads Function CreateCommand(ByVal cmdText As String, ByVal connection As IDbConnection, ByVal transaction As IDbTransaction) As IDbCommand
Dim cmd As IDbCommand = Nothing
Dim args As Object() = {cmdText, connection, transaction}
Try
cmd = CType(Activator.CreateInstance(_commandTypes(CInt(_provider)), args), IDbCommand)
Catch e As TargetInvocationException
Throw New SystemException(e.InnerException.Message, e.InnerException)
End Try
Return cmd
End Function 'CreateCommand
Overloads Function CreateDataAdapter() As IDbDataAdapter
Dim da As IDbDataAdapter = Nothing
Try
da = CType(Activator.CreateInstance(_dataAdapterTypes(CInt(_provider))), IDbDataAdapter)
Catch e As TargetInvocationException
Throw New SystemException(e.InnerException.Message, e.InnerException)
End Try
Return da
End Function 'CreateDataAdapter
Public Overloads Function CreateDataAdapter(ByVal selectCommand As IDbCommand) As IDbDataAdapter
Dim da As IDbDataAdapter = Nothing
Dim args As Object() = selectCommand
Try
da = CType(Activator.CreateInstance(_dataAdapterTypes(CInt(_provider)), args), IDbDataAdapter)
Catch e As TargetInvocationException
Throw New SystemException(e.InnerException.Message, e.InnerException)
End Try
Return da
End Function 'CreateDataAdapter
Public Overloads Function CreateDataAdapter(ByVal selectCommandText As String, ByVal selectConnection As IDbConnection) As IDbDataAdapter
Dim da As IDbDataAdapter = Nothing
Dim args As Object() = {selectCommandText, selectConnection}
Try
da = CType(Activator.CreateInstance(_dataAdapterTypes(CInt(_provider)), args), IDbDataAdapter)
Catch e As TargetInvocationException
Throw New SystemException(e.InnerException.Message, e.InnerException)
End Try
Return da
End Function 'CreateDataAdapter
Public Overloads Function CreateDataAdapter(ByVal selectCommandText As String, ByVal selectConnectionString As String) As IDbDataAdapter
Dim da As IDbDataAdapter = Nothing
Dim args As Object() = {selectCommandText, selectConnectionString}
Try
da = CType(Activator.CreateInstance(_dataAdapterTypes(CInt(_provider)), args), IDbDataAdapter)
Catch e As TargetInvocationException
Throw New SystemException(e.InnerException.Message, e.InnerException)
End Try
Return da
End Function 'CreateDataAdapter
Overloads Function CreateDataParameter() As IDbDataParameter
Dim param As IDbDataParameter = Nothing
Try
param = CType(Activator.CreateInstance(_dataParameterTypes(CInt(_provider))), IDbDataParameter)
Catch e As TargetInvocationException
Throw New SystemException(e.InnerException.Message, e.InnerException)
End Try
Return param
End Function 'CreateDataParameter
Public Overloads Function CreateDataParameter(ByVal parameterName As String, ByVal value As Object) As IDbDataParameter
Dim param As IDbDataParameter = Nothing
Dim args As Object() = {parameterName, value}
Try
param = CType(Activator.CreateInstance(_dataParameterTypes(CInt(_provider)), args), IDbDataParameter)
Catch e As TargetInvocationException
Throw New SystemException(e.InnerException.Message, e.InnerException)
End Try
Return param
End Function 'CreateDataParameter
Public Overloads Function CreateDataParameter(ByVal parameterName As String, ByVal dataType As DbType) As IDbDataParameter
Dim param As IDbDataParameter = CreateDataParameter()
If Not (param Is Nothing) Then
param.ParameterName = parameterName
param.DbType = dataType
End If
Return param
End Function 'CreateDataParameter
Public Overloads Function CreateDataParameter(ByVal parameterName As String, ByVal dataType As DbType, ByVal size As Integer) As IDbDataParameter
Dim param As IDbDataParameter = CreateDataParameter()
If Not (param Is Nothing) Then
param.ParameterName = parameterName
param.DbType = dataType
param.Size = size
End If
Return param
End Function 'CreateDataParameter
Public Overloads Function CreateDataParameter(ByVal parameterName As String, ByVal dataType As DbType, ByVal size As Integer, ByVal sourceColumn As String) As IDbDataParameter
Dim param As IDbDataParameter = CreateDataParameter()
If Not (param Is Nothing) Then
param.ParameterName = parameterName
param.DbType = dataType
param.Size = size
param.SourceColumn = sourceColumn
End If
Return param
End Function 'CreateDataParameter
End Class 'ProviderFactory '
End Namespace 'CSharpCorner.ProviderFactory