Simulating Stored Procedures in Microsoft Access using Enterprise Library Application Blocks

Imports System
Imports System.Data
Imports System.Data.Common
Imports System.Data.OleDb
Imports Microsoft.Practices.EnterpriseLibrary.Data

Namespace DALII.Providers.AccessProvider
    Public Class Resources
        'Create a database object
        Dim db As Database = DatabaseFactory.CreateDatabase("CurrentInstance")
        Dim myParameterArray As New ArrayList

        Dim sqlQuery As String
        Dim GetArray As ArrayList
        Dim ReplacedValue As String
        Dim ReplacingValue As String
        Dim parameters As Integer
        Dim i As Integer
        Public Function GetAllAddresses(ByVal ParameterArray As Object) As IDataReader
            myParameterArray = BuildParameterArray(ParameterArray)

            sqlQuery = "SELECT AddressID, FirstName, LastName "
            sqlQuery += "FROM Addresses;"
            sqlQuery = ReplaceParameters(sqlQuery, myParameterArray)

            Dim reader As IDataReader = db.ExecuteReader(CommandType.Text, sqlQuery)
            Return reader
        End Function
        Public Function GetAddresses(ByVal ParameterArray As Object) As IDataReader
            myParameterArray = BuildParameterArray(ParameterArray)

            sqlQuery = "SELECT AddressID, FirstName, LastName, Address, City, State, Zip, EmailAddress, Phone, DateUpdated "
            sqlQuery += "FROM Addresses WHERE (((AddressID)=[Parameter1]));"
            sqlQuery = ReplaceParameters(sqlQuery, myParameterArray)

            Dim reader As IDataReader = db.ExecuteReader(CommandType.Text, sqlQuery)
            Return reader
        End Function
        Public Function UpdateAddresses(ByVal ParameterArray As Object) As IDataReader
            Dim dtNow As DateTime = Date.Now
            myParameterArray = BuildParameterArray(ParameterArray)

            sqlQuery = "UPDATE Addresses SET FirstName = '[Parameter2]', LastName = '[Parameter3]', Address = '[Parameter4]', City = '[Parameter5]', "
            sqlQuery += "State = '[Parameter6]', Zip = '[Parameter7]', EmailAddress = '[Parameter8]', Phone = '[Parameter9]', DateUpdated = #" & dtNow & "# "
            sqlQuery += "WHERE (((AddressID)=[Parameter1]));"

            sqlQuery = ReplaceParameters(sqlQuery, myParameterArray)

            Dim reader As IDataReader = db.ExecuteReader(CommandType.Text, sqlQuery)
            Return reader
        End Function
        Public Function InsertAddresses(ByVal ParameterArray As Object) As IDataReader
            Dim dtNow As DateTime = Date.Now
            myParameterArray = BuildParameterArray(ParameterArray)

            sqlQuery = "Insert into Addresses  (FirstName, LastName, Address, City, State, Zip, EmailAddress, Phone, DateUpdated) "
            sqlQuery += "values ('[Parameter1]','[Parameter2]','[Parameter3]','[Parameter4]','[Parameter5]','[Parameter6]','[Parameter7]','[Parameter8]',#" & dtNow & "#)"

            sqlQuery = ReplaceParameters(sqlQuery, myParameterArray)
            db.ExecuteNonQuery(CommandType.Text, sqlQuery)

            ' Return the autonumber ID
            sqlQuery = "SELECT AddressID As ID FROM Addresses "
            sqlQuery = sqlQuery & "WHERE (((FirstName)='[Parameter1]') AND ((LastName)='[Parameter2]') AND ((Address)='[Parameter3]') AND ((City)='[Parameter4]') AND ((State)='[Parameter5]')  AND ((Zip)='[Parameter6]') AND ((EmailAddress)='[Parameter7]') AND ((Phone)='[Parameter8]') AND ((DateUpdated)=#" & dtNow & "#))"
            sqlQuery = ReplaceParameters(sqlQuery, myParameterArray)

            Dim reader As IDataReader = db.ExecuteReader(CommandType.Text, sqlQuery)
            Return reader
        End Function
        Public Function DeleteAddresses(ByVal ParameterArray As Object) As IDataReader
            myParameterArray = BuildParameterArray(ParameterArray)

            sqlQuery = "DELETE * "
            sqlQuery += "FROM Addresses WHERE (((AddressID)=[Parameter1]));"
            sqlQuery = ReplaceParameters(sqlQuery, myParameterArray)

            Dim reader As IDataReader = db.ExecuteReader(CommandType.Text, sqlQuery)
            Return reader
        End Function
        Private Function BuildParameterArray(ByVal ParameterArray As Object) As ArrayList
            GetArray = ParameterArray

            myParameterArray.Clear()
            For i = 1 To GetArray.Count
                myParameterArray.Add(GetArray(i - 1).ToString)
            Next
            Return myParameterArray
        End Function
        Private Function ReplaceParameters(ByVal sqlQuery As String, ByVal ParameterArray As ArrayList) As String
            parameters = ParameterArray.Count
            For i = 1 To parameters
                ReplacedValue = "[Parameter" & CStr(i) & "]"
                ReplacingValue = CStr(ParameterArray.Item(i - 1))
                sqlQuery = sqlQuery.Replace(ReplacedValue, ReplacingValue)
            Next
            Return sqlQuery
        End Function
    End Class
End Namespace