A Generic Data Access Component using Factory Pattern in VB.NET

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

About the Author

This article is a VB.NET conversion of C# version posted on C# Corner ( www.c-sharpcorner.com ). To see C# version of this article, visit C# Corner ( www.c-sharpcorner.com).