Managing Connection Strings with Data Link Files 

When an application connects to a database via ActiveX Data Objects (ADO), it needs a connection string to specify the data provider and other connection parameters. Often connection strings are hardcoded somewhere in the application source code. (Hopefully only in one place!)

Instead of embedding connection strings in the application source code, you can store them in separate files called Microsoft Data Link files. With this approach, the connections properties can be conveniently set and edited using the Data Link Properties dialog, rather than by editing the application source code.

Here I'm going to describe how to store your ADO connection strings in Microsoft Data Link files.

Creating Data Link Files

A Data Link file is simply a separate file, with a .udl extension (for Universal Data Link), that contains an OLE DB connection string. Microsoft Data Link files are created from Windows NT Explorer, through the File menu. Click File/New/Microsoft Data Link . The file is created in the current directory with the extension .udl. Give the file an appropriate name, then right click on it and pick Open from the context menu to open the Data Link Properties dialog box. This is how you build and save the connection string in the file.

In the dialog box, go first to the Provider tab and select the provider, then click Next to go to the Connection tab. You need to set the provider first, because the contents of the Connection tab are provider specific. (You can click Help for an explanation of the items on the dialog for your chosen provider.) The nice thing is that you can test the data connection to verify the setup before saving the file. You can specify a password for the test, if necessary, but it won't be saved in the file as long as you leave Allow saving password unchecked.

Caveat: Udl files should be edited only with Data Link Properties dialog. They are written in the Unicode (16bit) character set. You can view them like any other text, but if you edit or save them with an ASCII (8-bit) text editor, they will become unusable for their intended purpose.

If you click Help in the Data Link Properties dialog, you get the "Microsoft Data Link API Help" file; if you are going to work with Data Link files, it is recommended that you become familiar with all of it. This help file is named msdasc.hlp, and you can also open it by double clicking on it in the NT Explorer. (On my system, this file was located in c:\Program Files\Common Files\System\ole db\msdasc.hlp.)

Using Data Link Files

So that's what data link files are, and how you create them. Let's see how to use them in our applications.

One of the statements that is valid in an ADO connection string is " File name= ". So if you pass the name of the udl file this way, it can be used to open the connection. This is a VBScript example:

Dim strConn, strUserid, strPassword, objConn
strConn = "File name=dblink.udl;"
strUserid= "thisuser"
strPassword = ""
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConn, strUserid, strPassword

Although the ADO Connection Open method is rarely shown this way, it does support passing the user ID and password as optional parameters, discrete from the rest of the argument = value statements that compose the connection string. It certainly is advantageous: for one thing, it's really not too desirable to have a user ID and password embedded in a string which can be viewed (in source code or in a udl file) as plain text, and for another, it is more convenient to program in a situation where the user ID and password are required to be different for individual users of an application.

For desktop applications, the udl file must be in the current working directory, or else its full path must be in the connection string. Web applications, such as Active Server Pages, that run in a virtual web directory should use the Server.MapPath method to set the physical file path for the connection string. For example, for an ASP to map a udl file that is in the same virtual directory as itself, line 2 in the above VBScript example would be replaced with the following:

strConn = "File name=" & Server.MapPath("dblink.udl")

Reading Data Link Files

In VB or VBScript, use FileSystemObject methods to read udl files, if desired for your application. This VBScript subroutine writes the contents of a udl file to the web page, wrapped with HTML <CODE> tags.

'---Subroutine to write the contents of a data link file to the web page.
'---It is called with the virtual path of the file.
'
Sub OutputSource(strVirtualPath)
    Dim strFilepath
    strFilepath = Server.MapPath(strVirtualPath)
    
    Dim FileObject, oInStream, strOutput
    Set FileObject = CreateObject("Scripting.FileSystemObject")
    Set oInStream = FileObject.OpenTextFile(strFilepath, adModeRead, false, -2)
    Response.Write "<CODE>"
    While NOT oInStream.AtEndOfStream
      strOutput = oInStream.ReadLine
      Response.Write strOutput & "<BR>"
    Wend
    oInstream.Close
    Response.Write "</CODE>"
    set oInStream = Nothing
    set FileObject = Nothing
End Sub

In particular, notice the FileObject.OpenTextFile command. The optional fourth parameter of the OpenTextFile method indicates the format of the file to be opened. This is essential, because if it's left out the file is opened as ASCII, and as I mentioned earlier, udl files are Unicode.

The parameter value must be one of three so-called TriState constants, which are defined as TriStateFalse (0), TriStateTrue (-1), and TriStateUseDefault (-2) for ASCII, Unicode, and "system default" respectively. TriStateUseDefault works, so that is what I chose to use. Why is it hardcoded? Well, for some reason, the TriState constants are not included in adovbs.inc, and the VBScript Language Reference in the MSDN Library explicitly stipulates that " For VBScript, you must explicitly declare these constants in your code ."

Use with Data Environment Designer in VB

If you use the Data Environment Designer in Visual Basic, you will recognize that adding a connection to the Data Environment invokes the same Data Link Properties dialog that was described above. The ConnectionSource property of the DEConnection is then set according to the input from the dialog. To utilize a previously created udl file with a Data Environment connection, use the Properties window to set the ConnectionSource property of the DEConnection object to a " File name= " connection string that names your udl file. This must be done at design time.

Advantages of Data Link Files

The advantages of managing connection strings with Data Link files should be clear, but to reiterate:

• You avoid hardcoding connection details in the application code.
• Customizing an application from one installation to another is made easier.
• Use of the Data Link Properties dialog enforces correctness of the connection string content.
• The connection can be tested and verified before the udl file is saved.
• Maintenance and troubleshooting are also made easier. It isn’t necessary to have the application source code at hand, or to rebuild an executable, in order to view or change the data connection parameters.
• Separate management of security parameters (user ID and password) and the rest of connection specifications are supported.
• Udl files can be checked in and out of your version control system with the rest of the files that make up an application.