UDL.Net, A ConnectionString Editor Built from Scratch

• Download sample - 35.71 KB
• Download code - 80.82 KB

Introduction

UDL.Net is a Connection String editor built from scratch and modeled after the old Universal Data Link (UDL) and the newer Visual Studio DataConnectionDialog. Its purpose is to provide a visual tool to create and edit connection strings for virtually any database system.

Background

I've had this idea for a long time, but for one reason or another, there is just never enough time, but finally, I made an extra effort, and here it is. Please be aware that this is a work in progress, and there is still a lot of testing to be done, but for the most part, it is working properly.

I tested it with most data providers, and it works great with relatively simple and moderately complex connection strings, but I haven't tested it with really complex connection strings, but in theory, it should work the same; if anybody finds bugs, please send them to me, and I'll work on them on the first chance I get.

The Problem

After countless hours searching for a visual tool for creating connection strings for a project I was working on, I was really surprised that there is really nothing even close available; in fact, to build a connection string (meaning at runtime), the only options we have are UDL files. There is also the DataConnectionDialog shipped with VS2005, but from what I know, it is not part of the .NET framework, but instead a part of the Visual Studio package and can't be used for re-distribution.

Finally, with no other options, I got frustrated and decided to write my own, and since the output came out pretty decent, I decided to share it with the community.

Features

Here is a list of some of the features included in this release:

• 100% .NET
• Works with .NET Framework 2.0 or higher
• Self-contained. No references to any assemblies or files other than the .NET framework
• External providers. Support for data providers that are not registered in the GAC
• Extensible. Allows you to add your own providers and editors
• Filters. Limit what providers are visible to the end users
• Works with any version ADO.NET data providers (means the data provider itself; this version only runs on .NET framework 2.0 or higher)
• Familiar and simple user interface
• Validation. Validates the connection string, or can be switched off for testing purposes

Using the Code

UDL.Net inherits from CommonDialog, which means it can be used the same way you would use any other dialog box like OpenFileDialog, SaveFileDialog, etc. For example:

Creating a new connection string:

ConnectionStringDialog fd = new ConnectionStringDialog();
if (fd.ShowDialog(this) != DialogResult.OK)
    return;

string providerID = fd.Provider;
string connStr = fd.ConnectionString;
// do other stuff here

Editing an existing connection string:

ConnectionStringDialog fd = new ConnectionStringDialog();
// Note: provider is required to edit an existing connection strings
// without it it's impossible to know which provider to use
fd.Provider = "System.Data.SqlClient"; 
fd.ConnectionString = myExistingConnectionString;
if (fd.ShowDialog(this) != DialogResult.OK)
    return;
// do other stuff here

Showing a specific provider at startup:

ConnectionStringDialog fd = new ConnectionStringDialog();
fd.Provider = "System.Data.OleDb"; 
if (fd.ShowDialog(this) != DialogResult.OK)
    return;
// do other stuff here

Bypassing validation:

ConnectionStringDialog fd = new ConnectionStringDialog();
// this will allow invalid connection string settings
fd.ValidateConnection = false;
if (fd.ShowDialog(this) != DialogResult.OK)
    return;
// do other stuff here

Filtering the List of Providers

There are two ways of filtering items from the providers list:

• FilterMode.Include (default) - This setting will cause that only the providers included in the Filters collection will be available for the end user.
• FilterMode.Exclude - This means that the items included in the Filters collection won't be available to the end user. For example:

  If we don't want to support DBC and OLEDB:

  ConnectionStringDialog.FilterMode = FilterMode.Exclude;
  ConnectionStringDialog.Filters.Add("System.Data.Odbc");
  ConnectionStringDialog.Filters.Add("System.Data.OleDb");

  Or, if we only want to support MySQL:

  ConnectionStringDialog.FilterMode = FilterMode.Include;
  ConnectionStringDialog.Filters.Add("System.Data.MySql");

The Public Classes

Here is a brief description of the main classes. There are some comments in the code, but for the most part, it is very self explanatory.

ConnectionStringDialog: This is the main class the user will instantiate, here are the most relevant members.

• Properties
  • Provider - Gets or sets the selected provider for the dialog. If it's not set before showing the dialog, it will show the list of available providers.
  • ConnectionString - Gets or sets the connection string for the dialog.

    Note: Setting the connection string will not have any effect unless you also set the Provider since it is impossible to know which provider the connection string is for.

  • ValidateConnections - Gets or sets if the dialog will accept only valid connection strings (default is true).
• Methods
  • GetConnection() - Returns an initialized IDbConnection for the selected provider using the selected connection string (the connection string is set, but the connection is not opened).
  • GetFactory() - Returns the DbProviderFactory instance of the selected provider, or null if the provider doesn't support the Provider Factory Model.
• Static members
  • Filters - Gets the collection of filters.
  • FilterMode - Gets or sets the filtering mode for the control.
  • RegisterProvider() - Allows you to add additional providers. By default, the dialog will get the list of providers installed in the GAC, but if you want to use a provider that's not in the GAC, here is where you do it. The only requirement for a provider is that it inherits from DbProviderFactory (ideally) or implements IdbConnection (which is pretty much a standard). If you are not sure if is in the GAC or not, you can add it, and if it is already in the GAC, it won't duplicate it.
  • RegisterEditor() - Allows you to add additional editors. By default, it will load a pre-defined list of editors, but if you want to create your own editor, just create a UserControl and implement IUDLEditor and add it to the static list of editors.

IUDLEditor: Here is the interface for the editors.

public interface IUDLEditor
{
    string ConnectionString { get;set;}

    //gets called everytime the controls need to be
    // populated with fresh data.
    void RefreshData();

    // the object that contains the advanced 
    //properties or null if not implemented
    object GetAdvancedPropertiesObject();
}

Supported Databases

In theory, it's supposed to support any ADO.NET compliant database, but in reality, each and every database system is very different from the others, and there is absolutely no standard on the parameters they accept on their respective connection strings.

That said, I created editors for the most popular database systems that I know of; depending on the feedback, I'll keep adding more editors as needed.

As a last resource, if we are creating or editing a connection string for a provider we don't have an editor available, the dialog will display a generic editor which is basically a textbox where the user can type the connection string; not so pretty, but the good thing is that all the other functionalities still work the same, like test connection, validation etc.

This is the list of editors available on this first release:

• System.Data.SqlClient
• System.Data.OleDb
• System.Data.Odbc
• System.Data.SqlCe
• System.Data.OracleClient
• System.Data.SQLite
• MySql.Data.MySqlClient
• Firebird.Data.FirebirdClient
• Postgre.Data.PostgreClient

Editor's Sample Images

History

• 2.23.2010 - Improved OleDb editors for SQL Server, MySql and PostgreSql
• 2.17.2010 - Initial release