Click here to Skip to main content
15,867,594 members
Articles / Desktop Programming / Windows Forms

UDL.Net, A ConnectionString Editor Built from Scratch

Rate me:
Please Sign up or sign in to vote.
4.80/5 (23 votes)
24 Feb 2010CPOL5 min read 49.9K   2.8K   54   15
UDL.Net is a ConnectionString editor built from scratch, modeled after the Universal Data Link (UDL) and the Visual Studio DataConnectionDialog.

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.

UDL_img1.pngUDL_img2.png

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:

C#
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:

C#
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:

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

Bypassing validation:

C#
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:

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

    Or, if we only want to support MySQL:

    C#
    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.

C#
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

UDL_img3.pngUDL_img4.png

UDL_img5.png

History

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Other
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Adam Guy20-Aug-22 23:42
Adam Guy20-Aug-22 23:42 
Questionremerciement Pin
Member 1018620410-Feb-15 0:09
Member 1018620410-Feb-15 0:09 
QuestionEditor for SqlLocalDB Pin
regattamaster22-Dec-14 17:27
regattamaster22-Dec-14 17:27 
GeneralMy vote of 5 Pin
Tradon-Dev27-Jun-12 4:48
Tradon-Dev27-Jun-12 4:48 
GeneralMy vote of 5 Pin
ingenvzla31-May-12 7:33
ingenvzla31-May-12 7:33 
BugMinor bug in SqlClientEditor.cs Helper Routine Pin
vanleeuwena17-Jan-12 5:25
vanleeuwena17-Jan-12 5:25 
SuggestionYou Got my 5 Pin
Tradon-Dev21-Sep-11 3:51
Tradon-Dev21-Sep-11 3:51 
GeneralGiving Problem with Oracle provider Pin
dushyantdubey15-Jun-11 5:41
dushyantdubey15-Jun-11 5:41 
GeneralMy vote of 5 Pin
Jorge Gomez Prada18-Sep-10 7:06
professionalJorge Gomez Prada18-Sep-10 7:06 
QuestionPermission to use in another application? Pin
SohjSolwin23-Jun-10 7:46
SohjSolwin23-Jun-10 7:46 
AnswerRe: Permission to use in another application? Pin
aztekka23-Jun-10 12:26
aztekka23-Jun-10 12:26 
QuestionWhy not use Visual Studio Connection Builder Stand Alone Version? Pin
Abbas Malik22-Feb-10 23:26
Abbas Malik22-Feb-10 23:26 
AnswerRe: Why not use Visual Studio Connection Builder Stand Alone Version? Pin
aztekka23-Feb-10 5:34
aztekka23-Feb-10 5:34 
GeneralYou got my 5... Pin
2LM22-Feb-10 20:20
2LM22-Feb-10 20:20 
GeneralRe: You got my 5... Pin
aztekka23-Feb-10 5:38
aztekka23-Feb-10 5:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.