CodeProject: A Generic Data Access Component using Factory Pattern in VB.NET. Free source code and programming help

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).

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

c#corner

Member

Occupation:	Web Developer
Location:	United States

Other popular VB.NET articles:

Using PropertyGrid Part-I
Explains about PropertyGrid and how to use, with detailed examples
Add Custom Properties to a PropertyGrid
Extend a PropertyGrid with an Item collection; easy customization of properties with custom editor, custom converter and databinding.
A Visual Studio 2005-like Interface
Apply a theme that resembles Visual Studio 2005 to the DockPanel Suite, using an Extender class.
Rich OutlookBar in XP and Vista style
Creating a rich OutlookBar in XP or Vista style
About... The About Box
A reusable About Box form for developers and users.