65.9K
CodeProject is changing. Read more.
Home

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

starIconstarIconstarIcon
emptyStarIcon
starIcon
emptyStarIcon

3.70/5 (12 votes)

Aug 1, 2003

4 min read

viewsIcon

70880

I just read Mahesh's article Writing a Generic Data Access Component.

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