ADO.NET and UDL

• Download demo project - 15.1 Kb
• Download source - 4.35 Kb

Introduction

I used to love the simplicity of making connection strings by referring to a Data Link file while using OLE DB. This was a flexible way and it reduced the hassle of making packaged installations that refers to the development server. Then ADO.NET introduced the concept of optimized connectors for both SQL Server and Oracle. To my surprise, these optimized connecters are not able to read connection string stored in the data link file. Then I had to resort to saving the connection string to a text file which can then be edited by hand whenever a change in the connection is needed. This is re-invention of the wheel. Unacceptable!

Using the code

To use the code, import the cConnection class in your project. Then create an instance of the object like this:

Friend WithEvents oConnection As New _
                  cConnection("MyDataLink.udl")

If the name of the data link file is not included, the default name is used. The data link file is created in the same directory as the calling assembly.

The next step is to pass the connection string to the connection object.

m_cnAuthors.ConnectionString = oConnection.GetSQLConnectionString()

Data Link File Creation

Some times, one forgets to package the data link file or forgets to change the connection to the appropriate server. One has to go through the agony of navigating to the installation directory and creating it in front of the customer. They don't like it. They expect the usual next, next, next ... finish way of installation. It is with this in mind that the data link file creation was included.

To create a Data Link file, create a new empty text file, change its extension to .udl and then ask the operating system to call for you the Data Link app by double clicking over the file. This is translated to:

'Create an empty text file
Dim fs As New System.IO.FileStream(strFileName, System.IO.FileMode.CreateNew)
fs.Close()


'Call Data Link App and write information to it
Dim psiProcess As New ProcessStartInfo
psiProcess.FileName = strFileName
Dim pProcess As Process = Process.Start(psiProcess)
pProcess.WaitForInputIdle()
pProcess.WaitForExit()
pProcess.Close()

The trick here is to close the filestream before starting the data link app process. Otherwise we will get errors.

Reading a Data Link file

The Data Link app writes all sorts of delights for parameters but only a few of them are essential to make a successful connection. To find all those appropriate parameters, I had to split the connection string and put it in an array. Searching had to be done in a loop as parameters are not always in a special order.

For Each strString In strArray

            If strString.IndexOf("Provider") > -1 Then
                strProvider = strString.Substring(strString.IndexOf("Provider"))
            End If

            If strString.IndexOf("Password") > -1 Then
                m_strPassword = strString
            End If
            ...
            ...
            ...
            
Next

The searched parameters are then reconstructed and passed to to the appropriate connection object.