UDL Helper for .NET

• Download UdlHelper.zip - 13.7 KB

Introduction

Retrieving connection strings using the common Data Link dialog is not supported in .NET.

This solution opens the UDL tool, retrieves a connection string from a user and returns the full connection string to the caller.

It is assumed that

• the consuming application has file-write access to the ApplicationBase directory,
• and the correct association exists for UDL files.

Requirement

We require the following:

• A class that exposes a method GetConnectionString() which when called.
• Queries the user and returns the result as a standard connection string.
• We expect the class to use the standard DataLink Connection String dialog.

The UdlHelper Class

Implementation will execute the following steps:

1. Create a UDL file in the application base directory (if it does not already exist).
2. Execute a process with the UDL file as an argument, and wait for the process to complete.
3. Read the UDL file as text and extract the connection string.

Default UDL File

A default UDL file is created if not found. The location is specified by a private property called FilePath.

The minimal content for a UDL file to successfully launch the Data Link Dialog is show below, and this content is written to the new UDL file.

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;

This text is held in a private member field called _default. It is important to note that the Data Link tool expects the file to be Unicode encoded.

We can now write the CreateDefaultFile method:

private void CreateDefaultFile()
{
    if (!File.Exists(FilePath))
    {
        Directory.CreateDirectory(Folder);
 
        File.WriteAllText(FilePath, _default, Encoding.Unicode);
    }
}

Execute Data Link Modal Dialog

Once the UDL file is available we can simply create and execute a new process with Process.Start(string filename) method, and pass our UDL file path, and then wait for the process to complete:

private void ExecuteDataLinkProcessAndWait()
{
    var process = Process.Start(FilePath);
 
    process.WaitForExit(Int32.MaxValue);
}

Parse File

We can now open the file, read the Data Link content, then find and store the connection string:

private void ParseFile()
{
    var udl = File.ReadAllText(FilePath, Encoding.Unicode);
 
    var rex = new Regex("(Provider[^;]*);(.*)", RegexOptions.Multiline);
 
    var match = rex.Match(udl);
 
    if (match.Success)
    {
        Provider = match.Groups[1].ToString();
 
        ConnectionString = match.Groups[2].ToString();
 
        Success = true;
    }
}

Get Connection String

The publicly exposed GetConnectionString method simply calls the three methods discussed above, in sequence, and stores the result in the public ConnectionString property.

public string GetConnectionString()
{
    CreateDefaultFile();
 
    ExecuteDataLinkProcessAndWait();
 
    Parse();
 
    return ConnectionString;
}

Usage

Consumer code will simply create an instance of the UdlHelper class and call the GetConnectionString method which will return a complete connection string as specified by the user.

var udl = new UdlHelper();
 
var connString = udl.GetConnectionString();

Other Properties

The UdlHelper class also exposes the following properties:

• Provider: Database driver provider.
• DataSource: Name of database host.
• Catalog: Name of database
• Username: Username if persist security is true.
• Password: Password if persist security is true.

Regular expressions are used to match against the full connection string when the properties are accessed.

Risks

• Executable must have file-write access to the application base directory
• UDL files must be associated with Universal Data Link utility (This is the default association for UDL files on Windows).
• No exception handling.

References

• MSDN Universal Data Link Files