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.