Click here to Skip to main content
Click here to Skip to main content

UDL Helper for .NET

By , 10 Apr 2013
Rate this:
Please Sign up or sign in to vote.

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 GetConnectionString() method 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 shown 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))
    {
        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

License

This article, along with any associated source code and files, is licensed under A Public Domain dedication

About the Author

CognitiveFeedback

Australia Australia
codes
is vegetarian
likes cats
Follow on   Twitter

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web01 | 2.8.140421.2 | Last Updated 10 Apr 2013
Article Copyright 2013 by CognitiveFeedback
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid