Design patterns could be divided in three groups, Creational, Structural and Behavioral. The Factory pattern is a creational pattern. But what is really in simple words the factory pattern? Let us consider the following example.
Suppose there is a disc wheel factory. A variety of different disc wheels can be produced. The management of the factory does not really have to know how each type of disc wheel is made. The workers on the other hand need to know how. In the beginning of the day the manager decides which disc wheel will be created today and tells to the workers to make it. So in order to produce a specific disc wheel the manager has to simply say “Let’s produce today the x disc wheel”. Moreover the customers that buy the wheels don’t care and do not know about this process. They simply say we want to buy this kind of disc wheel.
In bottom line we want a generic disc wheel form which all disc wheels have to follow, but each kind of disc wheel can have each specific characteristics. And we want the manager to say “Produce the x wheel type”. These can be represented in UML as following (with one method :
But now let us move to the Data Access Layer. Let’s implement the same logic in the existing layer (explained in a previous article). We want a Factory class to produce to us the desired layer (Sql Server specific, Oracle specific and so forth). So we simply create the class and set a public enum for the providers. And of course we have a CreateDataAccessLayer function that returns the layer. This function can have parameters (for connection string and provider) or can read from the Web config file to get the desired elements.
We also had a sql server specific layer developed. But we want an oracle specific too and some others. So we design an Interface called IDataAccess. Each of the layers that we are going to develop will implement this interface.
The whole code will not be provided (it’s not the case of this article), the code only for one function will be.
It is time now for the code:
Here is our Factory class:
Public Enum Enum_Providers
SqlServer
Oracle
Oledb
End Enum
Public Class Factory
Private _mProvider As String = System.Configuration.ConfigurationSettings.AppSettings.Item("Provider")
Private _mConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings.Item("ConnectionString")
Public Function CreateDataAccessLayer() As IDataAccess
Select Case _mProvider
Case "Oledb"
'do something here
Case "Oracle"
Dim ms As New OracleDataAccess(_mConnectionString)
Return ms
Case "SqlServer"
'do something here
End Select
End Function
End Class
The interface:
Public Interface IDataAccess
Property myCommand() As IDbCommand
Property myConnection() As IDbConnection
Property ConnectionString() As String
Property myDataAdapter() As IDbDataAdapter
Property myprovider() As Enum_Providers
Property myConnectionString() As String
Function Get_record_byID(ByVal myFieldsCollection As FieldsCollection, _
ByVal storedprocedurename As String) As DataSet
Function Get_Dataset(ByVal myFieldsCollection As FieldsCollection, _
ByVal storedprocedurename As String, _
ByVal datatable As String) As DataSet
Function Get_record_byID(ByVal myFieldsCollection As FieldsCollection, _
ByVal storedprocedurename As String, _
ByVal datatable As String) As DataSet
Function Get_record_byIDs(ByVal myFieldsCollection As FieldsCollection, _
ByVal storedprocedurename As String, _
ByVal datatable As String) As DataSet
Function Delete_record(ByVal myFieldsCollection As FieldsCollection, _
ByVal storedprocedure As String) As String
Function Update_Record(ByVal storedprocedure As String, _
ByVal myFieldsCollection As FieldsCollection) As String
Function Insert_Record(ByVal storedprocedure As String, _
ByVal myFieldsCollection As FieldsCollection) As String
Function Get_records_by_fields(ByVal myFieldsCollection As FieldsCollection, _
ByVal storedprocedurename As String, _
ByVal datatable As String) As DataSet
End Interface
And a simple Oracle specific data access layer (methods and properties are missing).
Imports System.Data.OracleClient
Public Class OracleDataAccess
Implements IDataAccess
'a lot here ...
Public Function Get_Dataset(ByVal myFieldsCollection As FieldsCollection, _
ByVal storedprocedurename As String, _
ByVal datatable As String) As System.Data.DataSet Implements IDataAccess.Get_Dataset
Dim sqlDataAdapter As New OracleDataAdapter
Try
sqlDataAdapter.SelectCommand = New OracleCommand
If _conn.ConnectionString = "" Then
_conn.ConnectionString = _mConnectionString
End If
sqlDataAdapter.SelectCommand.Connection = _conn
If Not _conn.State = ConnectionState.Open Then
_conn.Open()
End If
sqlDataAdapter.SelectCommand.CommandText = storedprocedurename
sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
If Not IsNothing(_trans) Then
sqlDataAdapter.SelectCommand.Transaction = _trans
End If
'Parameters
Dim myparam As OracleParameter
For i As Integer = 0 To myFieldsCollection.Count - 1
Dim obj As Field
obj = myFieldsCollection.Item(i)
If obj.ColumnLength > 0 Then
myparam = New OracleParameter(obj.ColumnName, _
obj.columnTypeOracle, obj.ColumnLength)
Else
myparam = New OracleParameter(obj.ColumnName, _
obj.columnTypeOracle)
End If
myparam.Direction = obj.Direction
If myparam.Direction = ParameterDirection.Input Then
If obj.ColumnValue Is Nothing Then
Else
myparam.Value = obj.ColumnValue
End If
End If
sqlDataAdapter.SelectCommand.Parameters.Add(myparam)
Next
Dim myDataSet As DataSet = New DataSet
sqlDataAdapter.Fill(myDataSet, datatable)
Return myDataSet
Catch ex As Exception
Throw ex
Finally
If IsNothing(_trans) Then
_conn.Close()
End If
End Try
End Function
Limitations:
Note that when we call a layer and run a procedure from our business layer we must add parameters. But oracle, sql server e.t.c. do not have the same parameters. Some define their own. So we have to re-design the class Field of the existing data layer. We have to set an enum for the Columntype and during the creation of a column set the type to the specific type.
Imports System.Data.OracleClient
Imports System.Data.SqlClient
<AttributeUsage(AttributeTargets.Parameter, AllowMultiple:=True, Inherited:=True)> _
Public Class Field
Inherits System.Attribute
Public Enum myColumnType
Sql_Varchar
SqlDb_BigInt
SqlDb_Bit
SqlDb_Char
SqlDb_DateTime
SqlDb_Decimal
SqlDb_Int
SqlDb_NChar
SqlDb_NVarChar
SqlDb_SmallDateTime
SqlDb_Text
SqlDb_VarChar
'Oracle
Oracle_Cursor
Oracle_Byte
Oracle_Char
Oracle_DateTime
Oracle_Double
Oracle_Float
Oracle_Int32
Oracle_NChar
Oracle_LongVarChar
Oracle_Number
Oracle_VarChar
End Enum
Private _columnName As String = ""
Private _columnTypeOracle As OracleClient.OracleType
Private _columnTypeSqlServer As SqlDbType
Private _columnLength As Integer = 0
Private _columnValue As Object
Private _direction As ParameterDirection = ParameterDirection.Input
Public Property ColumnName() As String
Get
Return _columnName
End Get
Set(ByVal Value As String)
_columnName = Value
End Set
End Property
Public Property Direction() As ParameterDirection
Get
Return _direction
End Get
Set(ByVal Value As ParameterDirection)
_direction = Value
End Set
End Property
Public Property columnTypeSqlServer() As SqlDbType
Get
Return _columnTypeSqlServer
End Get
Set(ByVal Value As SqlDbType)
_columnTypeSqlServer = Value
End Set
End Property
Public Property columnTypeOracle() As OracleClient.OracleType 'OracleClient.OracleType
Get
Return _columnTypeOracle
End Get
Set(ByVal Value As OracleClient.OracleType) 'OracleClient.OracleType)
_columnTypeOracle = Value
End Set
End Property
Public Property ColumnLength() As Integer
Get
Return _columnLength
End Get
Set(ByVal Value As Integer)
_columnLength = Value
End Set
End Property
Public Property ColumnValue() As Object
Get
Return _columnValue
End Get
Set(ByVal Value As Object)
_columnValue = Value
End Set
End Property
Public Property SourceColumn() As String
Get
End Get
Set(ByVal Value As String)
End Set
End Property
Public Sub New(ByVal colName As String, ByVal colType As myColumnType, _
ByVal colValue As Object, ByVal colLength As Integer)
Me.ColumnName = colName
Me.ColumnValue = colValue
Select Case colType
'Sql Server
Case myColumnType.Sql_Varchar
Me.columnTypeSqlServer = SqlDbType.VarChar
'A lot here
'SqlDbType.BigInt()
'SqlDbType.Bit()
'SqlDbType.Char()
'SqlDbType.DateTime()
'SqlDbType.Decimal()
'SqlDbType.Int()
'SqlDbType.NChar()
'SqlDbType.NVarChar()
'SqlDbType.SmallDateTime()
'SqlDbType.Text()
'SqlDbType.VarChar()
Case myColumnType.Oracle_Cursor
Me.columnTypeOracle = OracleType.Cursor
Case myColumnType.Oracle_Byte
Me.columnTypeOracle = OracleType.Byte
Case myColumnType.Oracle_Char
Me.columnTypeOracle = OracleType.Char
Case myColumnType.Oracle_DateTime
Me.columnTypeOracle = OracleType.DateTime
Case myColumnType.Oracle_Double
Me.columnTypeOracle = OracleType.Double
Case myColumnType.Oracle_Float
Me.columnTypeOracle = OracleType.Float
Case myColumnType.Oracle_Int32
Me.columnTypeOracle = OracleType.Int32
Case myColumnType.Oracle_NChar
Me.columnTypeOracle = OracleType.NChar
Case myColumnType.Oracle_LongVarChar
Me.columnTypeOracle = OracleType.LongVarChar
Case myColumnType.Oracle_Number
Me.columnTypeOracle = OracleType.Number
Case myColumnType.Oracle_VarChar
Me.columnTypeOracle = OracleType.VarChar
End Select
End Sub
End Class
So for our business layer we just:
Dim mycol As New Factory_dal.FieldsCollection
Dim fakeobject As String = ""
Dim myfactory As New Factory_dal.Factory
Dim mydataccesslayer As Factory_dal.IDataAccess
mydataccesslayer = myfactory.CreateDataAccessLayer()
Try
Dim myField1 As New Field("return_rec", Field.myColumnType.Oracle_Cursor, fakeobject, 0)
myField1.Direction = ParameterDirection.Output
mycol.Add(myField1)
Me.DataGrid1.DataSource = mydataccesslayer.Get_Dataset(mycol, "xxx", "xxx")
Me.DataGrid1.DataBind()
Catch ex As Exception
End Try
That’s all. Remember I could use the feedback.