Creating Data Link (UDL) Files in Delphi

As I said in the introductory lines, Delphi has good support for ADO but the documentation says absolutely nothing about Data Link files. This becomes more confounding when you realize that there are functions in the Delphi library that make creating UDL files programmatically a cakewalk. To find out about them, open the unit ADODB.pas in Delphi code editor. You will find several helper routines that are not documented in the Delphi Help file. One of these helper routines is the CreateUDLFile routine which takes three parameters: the name of the UDL File to be created, the provider name and the name of the data source.

procedure CreateUDLFile(const FileName, ProviderName, DataSourceName: WideString);

Using this method, it is possible and pretty easy to create a UDL file which can be used to replace a hard-coded connection string in an application. After specifying the name of the UDL file and the provider we can concatenate the connection parameters like user id and password with the data source name and pass it as the parameter required by the CreateUdlFile method.

CreateUDLFile(ADOConnectProj.udl','SQLOLEDB.1','MyServer;'+ 'Password=myPass;' +'Persist Security Info=True;'+ 'User ID=sa;' + 'Initial Catalog=TestDB;');

Delphi libraries contain yet another undocumented routine: DataLinkDir, which gets from the registry the default path to save UDL files. This information can be used in the above function to create the file in the DataLinks folder. All we need to do is append the intended UDL file name in the constructor method with the DataLinkDir routine; the file can then be created and stored in the default folder instead of the root directory or any other folder.

CreateUDLFile(DataLinkDir + 'ADOConnectProj.udl' , 'SQLOLEDB.1' , 'MyServer; ' + 'Password=myPass;' +'Persist Security Info=True;' + 'User ID=sa;' + 'Initial Catalog=TestDB;' );

To read the Connection String from the UDL file we can assign the filename to the ConnectionString property of ADOConnection1.

ADOConnection1.ConnectionString:= 'FILE NAME=' + DataLinkDir + 'ADOConnectProj.udl';

The following code snippet demonstrates how to create a UDL file and use it to read the Connection String and connect to a Database server while using ADO:

if not FileExists(DataLinkDir + 'ADOConnectProj.udl') then
  begin
    CreateUDLFile(DataLinkDir + 'ADOConnectProj.udl','SQLOLEDB.1','MyServer;'+ 'Password=myPass;' +'Persist Security Info=True;'+ 'User ID=sa;' + 'Initial Catalog=TestDB;');
  end;
ADOConnection1.ConnectionString:= 'FILE NAME=' + DataLinkDir + 'ADOConnectProj.udl';
ADOConnection1.Open;
if ADOConnection1.Connected then
  ShowMessage('Successfully connected');

Being able to programmatically create UDL files and let the connection string be loaded from the file provides a very flexible approach to making ADO connections. As a programmer, you can allow the user to provide information during installation and let the the installation program create a UDL file that can be used later for server connections. Further, changing the connection string would not require recompilation since the UDL file can be executed to launch the Data Link property dialog to change the connection string. You can also let it create an interface to allow the user to specify parameters to be used to create the connection string and connect to the server.