Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

SQL Server Connection Dialog (Extensible)

4.69/5 (8 votes)
28 Dec 2014CPOL3 min read 33.3K   3.4K  
Display a SQL Connection Dialog with extensible functionality to persist the resulting connection string to any location...

Introduction

FIRST OF ALL, I'd like to give full credit to TWallick for the original idea of using Microsoft.Data.ConnectionUI.dll and Microsoft.Data.ConnectionUI.Dialog.dll to present the SQL Connection UI provided for Visual Studio.

(Original code by TWallick, at http://www.codeproject.com/Articles/21186/SQL-Connection-Dialog)

This version is written in C# (instead of VB), but I did a little clean up to show the Visual Studio connection control in design time rather than building it in code and only showing at run time.

Background

The SAVE() functionality is abstracted so that one can still use the dialog even if they intend to save the connection string to any other location whatsoever. My hope is that this module can be simply plugged in 'as is' to anyone's solution and they can extend it as they need without having to rewrite anything.

The save functionality is moved to a new class:

C#
public class SaveHelperDefault : ISaveHelper

Anyone can create a new SaveHelper class by implementing the interface ISaveHelper:

C#
public interface ISaveHelper
{
    /// <summary>
    /// A SaveHelper class requires an event to initialize persisting data
    /// </summary>
    /// <param name="connectionString">The ConnectionString to persist</param>
    void Save(String connectionString);

    /// <summary>
    /// Allows read or assignment of the save method. If set to null,
    /// the default delegate is assigned.
    /// </summary>
    SaveMethodPointer SaveMethod { get; set; }
}

Or they can use the existing SaveHelperDefault class and set the SaveMethod property to reference another method as the delegate signature below:

C#
public delegate void SaveMethodPointer(String connectionString);

The default save helper class' ConnectionName property is the name of the 'connectionString' in the App.config which is "SqlConnString" if not assigned after the instance is created.

Basically, you can use the existing SaveHelper to persist a connection string to the app.config file of the program you run this module from without changing any code. On the other hand, you can entirely alter the save method by assigning the save method delegate or creating your own SaveHelper class and passing that as a parameter on Ctor or assignment of the property. The form will automatically call the SAVE() method of the SaveHelper class when closing the form with the OK button.

Using the Code

The tester project included in this example includes multiple ways of using the code. For example, using the default functionality from the original code, you create an instance, but to assign the setting name in the app.config file, you need to cast the SaveHelper to the subclass that supports changing this property. It is just not part of the interface ISaveHelper. Customization of the form Title is the same and setting the default/starting connection string is the same.

C#
SqlConnectionDialog dlg = new SqlConnectionDialog();
(dlg.SaveHelper as SaveHelperDefault).ConnectionName = 
"TestConn"; // required for the default method of identifying the ConnectionString key
//dlg.SaveHelper = ; // the ISaveHelper can be assigned with a customer object 
                    // that has totally independent functionality!
dlg.Title = "Test 1";
dlg.ConnectionString = Properties.Settings.Default.TestConn;
dlg.Show();

Another example is to use the Ctor overloads to assign the default settings before opening the form. This gives an example of assigning the SaveMethod to a new method outside the SQLConnectionDialog assembly. AppConfigSave1() is in the tester executable.

C#
SqlConnectionDialog dlg = new SqlConnectionDialog(Properties.Settings.Default.TestConn, "Test 2");
dlg.SaveHelper.SaveMethod = AppConfigSave1;     // overwrite the default save method 
                        // to one in this local assembly
dlg.Show(); 

Last, you can assign the SaveHelper property to an instance of a new ISaveHelper subclass.

C#
SqlConnectionDialog dlg = new SqlConnectionDialog
        (Properties.Settings.Default.TestConn, "Test 3");
dlg.SaveHelper = new SaveHelperTest(); // assign a new instance of a SaveHelper
dlg.Show(); 

Of course, the last two examples show how easy it is to completely adapt the SQLConnectionDialog to your own environment.

Points of Interest

Delegates are pretty interesting. The degree of flexibility resulting from typed pointers to methods is incredible. To use this code, one can assign the delegate to another named method in the user code, an anonymous method, or a lambda expression. In any event, it entirely changes the Save() functionality as needed by the user.

History

First version, no history yet...

License

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