Thomas Williams Tech Blog

As mentioned previously, I have been using IdeaBlade DevForce Express to generate a business object layer for a .NET 2.0 project.

For this project, I needed a way I could determine the SQL Server that the DevForce business objects would connect to, without necessarily creating a business object and testing the connection properties. IdeaBlade DevForce creates a helper assembly with an embedded XML document called "IdeaBlade.ibconfig" containing the SQLOLEDB connection string for data access, and so I needed a way to:

1. load the helper assembly
2. read the XML document from the assembly's embedded resources
3. find the connection string
4. get the server name from the connection string

Step 1: Before reading the resource, I had to create an Assembly object using System.Reflection.Assembly.Load (all the code here is VB.NET for .NET 2.0):

Dim helperassembly As System.Reflection.Assembly = System.Reflection.Assembly.Load("AppHelper")

This loads the assembly from the current directory - you must check that the returned "helperassembly" is not Nothing.

Step 2: The next part was to read the embedded resource (the "IdeaBlade.ibconfig" XML file) from this assembly. This was done with code based on Daniel McGivern's sample at http://mcgiv.com/blog/2005/06/16/Reading+Assembly+Embedded+Resource+Files++GetManifestResourceStream.aspx, which is separated out into a function:

    Public Function GetResourceTextFromAssembly(ByRef assembly As System.Reflection.Assembly, ByVal name As String) As String

        ' set up a stream for the XML resource
        Using sm As System.IO.Stream = assembly.GetManifestResourceStream(name)
            ' if we got a resource
            If sm IsNot Nothing Then
                ' get resource as text
                Using sr As System.IO.StreamReader = New System.IO.StreamReader(sm)
                    Return sr.ReadToEnd()
                End Using
            Else
                ' we could not find the resource specified by "name"
                Return Nothing
            End If
        End Using

    End Function

You need to call this function to get the XML as a string, passing the previously-created helperassembly Assembly and the name of the resource to retrieve, which is "AppHelper.IdeaBlade.ibconfig".

Steps 3 & 4: I won't post an example of the "IdeaBlade.ibconfig" XML, but the important part is that the connection string is stored in an XML element and could probably be retrieved using XPATH and tricky Mid() or Substring() calls. I chose to use a regular expression, thinking that I would look for a string like "Data Source=" and get whatever was after the equals sign.

Aside: Regular expressions are probably the closest thing to alchemy in modern times. I felt like I was muttering incoherent incantations as I inserted question marks, dollar signs and square brackets to make my regex spell work.

With the help of the excellent Regex .NET Tester tool, and MSDN documentation, I worked out that a regex that does the job is something like:

(?<=[D|d]ata [S|s]ource=)(\w+)

This looks for the string "Data Source=" but does not match it, then matches everything to the next word boundary (which includes not matching a trailing semi-colon, if there is one).

Big disclaimer: this code worked for me. I hope it helps someone else, but I make no guarantees. I have not tested it with SQL Server instances. Nor have I tested it with multiple connection strings. Or even connection strings which don't have a "Data Source" section.

And that's how you get the SQL Server from an IdeaBlade DevForce Express "AppHelper" assembly.

Technorati tags: ideablade, ORM, connection string