If you have been following my blog you will have a good idea of the challenges I ran into in creating the Access provider. Here is a overview of how it works.

Basically I am simulating stored procedures in Microsoft Access using the Enterprise Library Application Blocks. Microsoft Access will support stored procedures but only simple limited ones.

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.

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 an .XML file that has the same name of 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.

This following code example adds a survey 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.

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 “AddSurvey” method in the “DALII_Providers_AccesProvider.dll” assembly receives the parameters. Using a simple find and replace method, 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 “SurveyID”. This value is then returned by the method as an IDataReader object.

Public Function AddSurvey(ByVal ParameterArray As ArrayList) As IDataReader

Dim DateNow As Date = Now()
' Insert the data

sqlQuery = "Insert into Surveys (ModuleID,Question,ViewOrder,OptionType,CreatedByUser,CreatedDate) "

sqlQuery = sqlQuery & "values ([Parameter1],'[Parameter2]',[Parameter3],'[Parameter4]','[Parameter5]',#" & DateNow & "#)"

parameters = ParameterArray.Count

For i = 1 To parameters
ReplacedValue = "[Parameter" & CStr(i) & "]"
ReplacingValue = CStr(ParameterArray.Item(i - 1))
If ReplacedValue = "[Parameter3]" Then
ReplacingValue = "0"
End If

sqlQuery = sqlQuery.Replace(ReplacedValue, ReplacingValue)

Next

db.ExecuteNonQuery(CommandType.Text, sqlQuery)

' Return the autonumber ID

sqlQuery = "SELECT Surveys.SurveyID FROM Surveys "

sqlQuery = sqlQuery & "WHERE (((Surveys.ModuleID)=[Parameter1]) AND ((Surveys.Question)='[Parameter2]') AND ((Surveys.ViewOrder)=[Parameter3]) AND ((Surveys.OptionType)='[Parameter4]') AND ((Surveys.CreatedByUser)=[Parameter5]) AND ((Surveys.CreatedDate)=#" & DateNow & "#))"

parameters = ParameterArray.Count
For i = 1 To parameters
ReplacedValue = "[Parameter" & CStr(i) & "]"
ReplacingValue = CStr(ParameterArray.Item(i - 1))
If ReplacedValue = "[Parameter3]" Then
ReplacingValue = "0"
End If

 

sqlQuery = sqlQuery.Replace(ReplacedValue, ReplacingValue)

Next

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:

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

 I did have to make changes to the ELAB source code to create this example and I have detailed those changes here:

http://adefwebserver.com/dal/Blogs/tabid/162/ctl/ViewEntry/EntryID/20/mid/547/Default.aspx