Introduction
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(provider As ProviderType) '
End Sub 'New
Property Provider() As ProviderType
Get
Return _provider
End Get
Set
_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
Overloads Public Function CreateConnection(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
Overloads Public Function CreateCommand(cmdText As String) As IDbCommand
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
Overloads Public Function CreateCommand(cmdText As String, 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
Overloads Public Function CreateCommand(cmdText As String, connection As IDbConnection, 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
Overloads Public Function CreateDataAdapter(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
Overloads Public Function CreateDataAdapter(selectCommandText As String, 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
Overloads Public Function CreateDataAdapter(selectCommandText As String, 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
Overloads Public Function CreateDataParameter(parameterName As String, 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
Overloads Public Function CreateDataParameter(parameterName As String, 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
Overloads Public Function CreateDataParameter(parameterName As String, dataType As DbType, 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
Overloads Public Function CreateDataParameter(parameterName As String, dataType As DbType, size As Integer, 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
|
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
