Skip navigation

One skill they say is needed for software development is problem solving, and as a developer myself I know this to be true. It often seems like not a day will go by without encountering something outside of the original plan. Just the other day I was testing an application, which simply reads some data from a MS Access database. The database is part of a purchased software product, so I don’t really have any choice in the matter, it must be Access. Then up pops an exception The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

Well some internet research quickly found that the default Jet database driver is not 64 bit compatible. The official resolution? Force your application to be 32 bit and all will be well. Well, not today. Although I hate leaving such decisions up dumb incompatibility issues, in this case I couldn’t make my app 32 bit. The application is an add-on extension to AutoCAD, which, if your running 64 bit windows, will only install as 64 bit. That means that any assemblies it loads, like mine, must also run 64 bit.

So, here I though I was up that darn creek again without my paddle. But low and behold I found my original research was a little dated, there was a new 64 bit driver that could be used for Access databases. It turns out, that even though Microsoft really wanted to steer developers away from using Access in favor of SQL Express, they had to make a driver so that the MS Office suite could be 64 bit. Ok, cool. So I download the new driver, but of course, it doesn’t install. It complains that I must first uninstall my 32 bit version of Office before installing the driver. Ok, I definitely don’t want to go down this road, because not only does it mean making sure that this new driver gets installed on the users PC, but that the correct version of Office is on there as well.

Well, there I sat, convinced that this would either not work or be one of those things I regret doing much later. Then a little inspiration. I though, maybe I just need a separate assembly that can run 32 bit? Well that’s not possible, the 32/64 bit decision is made for the whole process, not just an assembly. It would have to be a separate executable.
I started to feel good about this, a separate ‘proxy’ executable to interact with access for me. The interprocess communication wouldn’t be bad, I would just have to send it SQL statements and it could send me back the results as a DataTable. The DataTable object has its own ReadXML and WriteXML functions, so the matter of moving the object would be simple.

Ok, so a separate executable. That would mean another project in my solution and in source control. It would also have to get deployed with the client with a known location so it can be started when its needed. Not really that big of a hurdle, but I wanted to try for something else anyways. More internet research turned up some example of using the CodeDOMProvider class to compile and emit an executable file. I liked that concept, create the executable on demand from a short piece of code. Wrap all this up in a single class and I’m happy.

First thing to do was write the proxy code. I really only needed two operations from the database. The ‘Execute Scalar’ option to return a singe value, and an option to fill a DataTable object with results from an query. If I used the System.Diagnostics.Process to start the proxy process, I could read and write to the processes standard input and standard output. That way the proxy could use the Console.In and Console.Out streams to handle its communication with the client process.

So I pass the proxy the connection string on the command line, so it can create a connection object to be used later. Then it reads from standard input. I will send it commands and end the commands with the null character. Once it reads the command, either ‘GetDT’, ‘Scalar’, or ‘Die’, it then looks for the query to written to standard input, again terminated with the null character. It executes the query, then either writes the singe value to standard out for scalar, or it writes the XML representation of the DataTable object. The calling process reads the result and we are done.

Now the client class was designed to be created, used, and then closed. The constructor, which also contains the source code to the proxy as a simple string constant, compiles the proxy code and generates the executable file. The constructor then runs the executable, saving the Process object so the standard in and out can be used later. The object then defines a function for each command it can run in the proxy. The function sends the commands and retrieves the result from the proxy’s standard output. When you are done using the object, simply call the Close method to send the proxy the ‘Die’ command. When the proxy receives this command the process exists.

See the full class code below:

Imports System.CodeDom.Compiler


Public Class Access64Bridge

    Private Proc As Process


    Public Sub New(ByVal ConnectionString)

        'just use the temp folder for the exe location
        Dim output As String = My.Computer.FileSystem.SpecialDirectories.Temp & "Access64Bridge.exe"

        Dim CDP As CodeDomProvider = CodeDomProvider.CreateProvider("VB")
        Dim params As New CompilerParameters
        params.OutputAssembly = output
        params.GenerateExecutable = True
        'Compiler options, most important are x86 flag and winexe
        '/target:winexe doesn't show a console window
        params.CompilerOptions &= "/platform:x86 /optioninfer /optionstrict- /target:winexe"
        'Add references required by code
        params.ReferencedAssemblies.Add("System.Data.dll")
        params.ReferencedAssemblies.Add("System.Xml.dll")
        params.ReferencedAssemblies.Add("Microsoft.VisualBasic.dll")

        'CDATA xml trick from the internet, good way to do multiline 
        'string literal like some other languages allow
        Dim Source As String = <![CDATA[
        Imports System
        Imports System.Data
        Imports Microsoft.VisualBasic

        Class Test
        Public Shared Sub Main(ByVal args() As String)
            Try
                Dim connstr As String = args(0)
            Dim conn = New
                OleDb.OleDbConnection(connstr)

                Do
                    'Get Command
                    Dim Comm As String = ReadInputValue()
                    Select Case Comm
                        Case "GetDT"
                            'Next thing in is the SQL
                            Dim SQL As String = ReadInputValue()

                            Dim DT As DataTable = New DataTable()
                            Dim DAObjOLE As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
                            Dim CommandObj As New OleDb.OleDbCommand(SQL, conn)

                            conn.Open()
                            DAObjOLE.SelectCommand = CommandObj
                            DAObjOLE.Fill(DT)
                            conn.Close()

                            DT.TableName = "Output"
                            'Serialize data table to the
                            output()
                            DT.WriteXml(Console.Out,
                            XmlWriteMode.WriteSchema)
                            'End with the null char
                            Console.Out.Write(vbNullChar)
                        Case "Scalar"
                            'Next thing in is the SQL
                            Dim SQL As String = ReadInputValue()
                            Dim CommandObj As New OleDb.OleDbCommand(SQL, conn)

                            conn.Open()
                            Dim Value As Object = CommandObj.ExecuteScalar
                            conn.Close()


                            Console.Out.Write(Value.ToString)
                            Console.Out.Write(vbNullChar)
                        Case "Die"
                            Return
                        Case Else
                            'Unrecognized command, host possibly is gone, just end
                            Return

                    End Select

                Loop
            Catch ex As Exception
                MsgBox("Access 64 to 32 bit Bridge: " &
                ex.Message & ex.StackTrace)
            End Try
        End Sub

        Public Shared Function ReadInputValue() As String
            Dim Value As New Text.StringBuilder
            Dim ch As Integer = Console.In.Read
            While ch <> 0 And ch <> -1
                Value.Append(Chr(ch))
                ch = Console.In.Read
            End While
            Return Value.ToString
        End Function
    End Class

        ]]>.Value

        Dim Result As CompilerResults = CDP.CompileAssemblyFromSource(params, Source)
        Dim X As New System.Diagnostics.ProcessStartInfo
        'Must redirecte standard in and out so we can use it
        X.RedirectStandardOutput = True
        X.RedirectStandardInput = True
        'Path of executable to run
        X.FileName = output
        'Required for redirection
        X.UseShellExecute = False
        'Connection string surrounded by double quotes so it comes
        'into the proxy as a single argument
        X.Arguments = """" & ConnectionString & """"
        'Start Process
        Proc = Process.Start(X)


    End Sub

    ''' <summary>
    ''' Reads a value from the access bridge program
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function ReadValue() As String
        Dim Value As New Text.StringBuilder
        Dim ch As Integer = Proc.StandardOutput.Read
        'use null termniated messages, -1 means the stream is dead
        While ch <> 0 And ch <> -1
            Value.Append(Chr(ch))
            ch = Proc.StandardOutput.Read
        End While
        Return Value.ToString
    End Function

    ''' <summary>
    ''' Runs sql and returns the first result
    ''' </summary>
    ''' <param name="Sql"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteScalar(ByVal Sql As String) As Object
        'Command
        Proc.StandardInput.Write("Scalar")
        'Null terminate
        Proc.StandardInput.Write(vbNullChar)
        'Sql
        Proc.StandardInput.Write(Sql)
        'Null termniate
        Proc.StandardInput.Write(vbNullChar)

        Return ReadValue()
    End Function

    ''' <summary>
    ''' Executes the SQL on the bridge and returns the resulting DataTable
    ''' </summary>
    ''' <param name="Sql"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CreateDT(ByVal Sql As String) As DataTable
        'Command
        Proc.StandardInput.Write("GetDT")
        'Null terminate
        Proc.StandardInput.Write(vbNullChar)
        'Sql
        Proc.StandardInput.Write(Sql)
        'Null termniate
        Proc.StandardInput.Write(vbNullChar)

        Dim Value = ReadValue()
        Dim DT As New DataTable
        DT.ReadXml(New IO.StringReader(Value))
        'Accept changes else all rows will be new
        'New rows are removed from the collection when deleted
        DT.AcceptChanges()
        Return DT
    End Function

    Public Sub Close()
        'Command
        Proc.StandardInput.Write("Die")
        'Null terminate
        Proc.StandardInput.Write(vbNullChar)
    End Sub
End Class


You never know what your going to have to do to get the job done.

Leave a Reply

Your email address will not be published. Required fields are marked *

 characters available

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current day month ye@r *