Simulating Stored Procedures in Microsoft Access using Enterprise Library Application Blocks

• Download source - 1.57 MB

Introduction

I had a problem. I wanted to give away free applications on the Internet. This seems like an easy thing to do, but the problem I had was creating an easy way for people to install the applications. The applications always needed a data source. I could easily distribute applications that used a Microsoft Access data source, but people usually wanted the application to run on SQL Server. I could create SQL Server setup scripts and directions to set up the application, but I would have to maintain a Microsoft Access version of the application and a SQL Server version. What I desired was a way to create a single set of code that would run on any data source.

The Microsoft Enterprise Library Application Blocks (ELAB) is an application that allows you to have a DAL (Data Access Layer) that will work with virtually any data source. You only need to create or obtain a provider for each data source. Many are available in addition to the SQL, DB2 and Oracle providers that are included. I was able to download an OleDB provider off the internet that allowed me to connect to an Access database.

The problem I had was that I wanted to write my application so that it used stored procedures. Microsoft Access will support stored procedures but only simple limited ones.

The Solution

My solution was to create an ELAB OleDB provider that would use reflection to load an assembly that would contain methods that would simulate the functionality of stored procedures in Microsoft Access. For the SQL Server version, I would just create stored procedures and configure the application to use the SQL provider. Now I can write an application that only needs the addition of a simple assembly to run on Microsoft Access. The rest of the application would be the same whether it used SQL Server or Microsoft Access.

To do this I altered the OleDB provider to intercept any call that was for a stored procedure. An “InvokeMethod” class attempts to find an entry in a .XML file that has the same name as the stored procedure. If it does not have an entry, it looks for all assemblies that begin with “DALII.Providers.AccessProvider_” and rebuilds the XML file. The XML file contains the name of all the methods, the class name of each, and the assembly name. These are the three things needed to dynamically load an assembly using reflection and execute a method.

The invoked method receives the parameters and then makes another call to the database. This time the call is a SQL query text call and is not intercepted by the custom code. Instead, the call is sent directly to the Microsoft Access database using the OleDB provider. The method retains control until it is complete and returns a IDataReader to the initial calling method. During the time it retains control, the method is able to simulate virtually any functionality of a stored procedure. It is able to make calls to the data source, interrogate the response, and make additional calls based on internal logic.

In the sample project provided, all calls to the database are contained in the “controller” class. This following code example inserts addresses into the database. First, all the needed information is passed to the method, which then uses the “GetStoredProcCommandWrapper” method to create a “wrapper” that will then be passed to the data source using the “ExecuteNonQuery” method.

Public Function InsertAddresses(ByVal FirstName As String, _
   ByVal LastName As String, ByVal Address As String, _
   ByVal City As String, ByVal State As String, _
   ByVal Zip As String, ByVal EmailAddress As String, _
   ByVal Phone As String) As Integer
     
    objCommandWrapper = db.GetStoredProcCommandWrapper("InsertAddresses", _
      FirstName, LastName, Address, City, State, Zip, EmailAddress, Phone)
    db.ExecuteNonQuery(objCommandWrapper)
    Return CType(objCommandWrapper.GetParameterValue("ID"), Integer)

End Function

The ELAB configuration (that is configured using the “dataConfiguration.config” file) passes the call to the currently configured provider. In this case, the OleDB provider receives the call but realizing that it is a stored procedure call, passes it to the “InvokeMethod” class. The “InvokeMethod” class locates the method in the XML file, and after obtaining the class name and assembly name, dynamically invokes the method using reflection, passing along the parameters:

arguments = New Object() {myParameters}
reader = myAssemblyInstance.GetType.InvokeMember(FunctionName, _
             BindingFlags.InvokeMethod, Nothing, _
             myAssemblyInstance, arguments)

The “InsertAddresses” method in the “DALII_Providers_AccesProvider.dll” assembly receives the parameters. Using a simple find and replace method (ReplaceParameters), the parameters are inserted into a SQL string. This string is then passed to the Access database with the “CommandType” set to “Text”. This prevents the call from being intercepted by the custom code. The OleDB provider simply passes the call directly to the Access database.

Next, another SQL string is built that will query the database for the “AddressID”. This value is then returned by the method as an IDataReader object.

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

In this case, the original calling method was "ExecuteNonQuery". Code in the “InvokeMethod” class detects this and executes:

AppendObjCommandWrapper(command, reader)

This method interrogates the reader object and inserts the parameter into the “objCommandWrapper” object. The original calling method in the “controller” class is then able to retrieve this parameter:

Return CType(objCommandWrapper.GetParameterValue("ID")

You will note the sample code uses IDataReader and does not use datasets. All data sources support IDataReader but they do not all support data sets sufficiently. Likewise they do not all support Transactions or other advanced functions. If you need a data set, you are able to populate it using IDataReader.

I plan to create future versions of this project. For example, the “InvokeMethod” class is written in VB while the OleDB provider is written in C#. I plan to make the “InvokeMethod” class part of the OleDB provider. There really is no reason that it is a separate class other than I am a beginner C# programmer and I felt more comfortable coding it in VB. Also, the assembly that contains the SQL commands should be cached as reflection is expensive resource wise.

My intention is to show the incredible functionality that the ELAB provides. I did have to make changes to the ELAB source code to create this example and I have detailed those changes here.

I hope that in future versions of the ELAB they will implement these changes. See the directions below to convert this example to run on SQL Server. Notice that the only thing that is changing (after you create the database and stored procedures in SQL Server) is the configuration to direct the application to run on SQL Server.

Additional

To run the project using SQL Server:

1. Create a database in your SQL Server called: ELAB_Example.
2. Create a user in the database called: "ELAB_User" with the password: "password12".
3. Locate the "ELAB_Example.sql" file in the "SQL_Scripts" directory and run the script to create the Addresses table and the stored procedures.
4. In the "dataConfiguration.config" file (in the main directory), find the following lines and update them if you need to:
   XML

   <connectionString name="Sql Connection String">
     <parameters>
       <parameter name="database" value="ELAB_Example" isSensitive="false" />
       <parameter name="Integrated Security" value="False" isSensitive="false" />
       <parameter name="pwd" value="password12" isSensitive="false" />
       <parameter name="server" value="(local)" isSensitive="false" />
       <parameter name="uid" value="ELAB_User" isSensitive="false" />
     </parameters>
   </connectionString>

5. Find the following lines in the "dataConfiguration.config" file (in the main directory):
   XML

   <instances>
     <instance name="SQL_CurrentInstance" type="Sql Server" 
                  connectionString="Sql Connection String" />
     <instance name="CurrentInstance" type="OleDB" 
                  connectionString="OleDB Connection String" />
     <instance name="OleDBInstance" type="OleDB" 
                  connectionString="OleDB Connection String" />
     <instance name="SQLInstance" type="Sql Server" 
                  connectionString="Sql Connection String" />
   </instances>

6. Change:
   XML

   <instance name="CurrentInstance" type="OleDB" 
                  connectionString="OleDB Connection String" />

   to

   XML

   <instance name="Old_CurrentInstance" type="OleDB" 
                  connectionString="OleDB Connection String" />

7. Change:
   XML

   <instance name="SQL_CurrentInstance" type="Sql Server" 
                  connectionString="Sql Connection String" />

   to

   XML

   <instance name="CurrentInstance" type="Sql Server" 
                  connectionString="Sql Connection String" />

8. Rebuild the application.