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 object has its own
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.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.