MultiConnectionStringManager Class

CleanCode C# Libraries v1.2.03 API

Provides the capability for a user to manipulate and test a set of database connection strings and associated details, leveraging the ConnectionStringManager control.

Inheritance Hierarchy

System Object
Online

System MarshalByRefObject
Online

System.ComponentModel Component
Online

System.Windows.Forms Control
Online

System.Windows.Forms ScrollableControl
Online

System.Windows.Forms ContainerControl
Online

System.Windows.Forms UserControl
CleanCode.DatabaseControls MultiConnectionStringManager

Namespace: CleanCode.DatabaseControls
Assembly: CleanCode.DatabaseControls (in CleanCode.DatabaseControls.dll) Version: 1.2.3.0 (1.2.03)

Syntax

C++

Copy to Clipboard Print

public class MultiConnectionStringManager : UserControl

public class MultiConnectionStringManager : UserControl

Public Class MultiConnectionStringManager _
	Inherits UserControl

Public Class MultiConnectionStringManager _
	Inherits UserControl

public ref class MultiConnectionStringManager : public UserControl

public ref class MultiConnectionStringManager : public UserControl

Remarks

This control provides a way to easily manage not just one connection string but a set of connection strings, allowing one-click switching between databases whether they be on the same server, on different servers, or even dissimilar systems (e.g. SQL Server vs. Oracle.) The control supports connections to SQL Server, Oracle, and MySql, as well as ODBC data sources. It allows you to test the connection immediately, as well as to save or load sets of connections so you can easily migrate a set to other machines.

There are three functional levels to consider to make best use of this control. The developer creates a separate credential form containing this control along with Accept and Cancel buttons; you open this credential form as needed from your main application. The administrative user is typically the one to use this control, He/she uses this control to define connections for different servers, databases, or users, each of which may be either fully-qualified or partially-qualified. Fully-qualified means that every attribute for a given database connection is provided, i.e. a connection string derived from it is complete. Partially-qualified, on the other hand, omits either the password or the user name and the password, providing all the other details. The end user could also use the control directly, depending on your design. Alternately, you may choose to allow the end user only to interact indirectly when partially-qualified connections are used. In this case, you would use the related ConnectionStringManager to let the user complement the partially-qualified connection to make it complete. You will see an example of this below. Besides the API for ConnectionStringManager, I have also published an article discussing the practical uses of it -- Online .NET Building Blocks: Build a Configurable Database Credential Selector -- available on the DevX online magazine.

Let's first look at developing with the control. The basic steps are: (a) Retrieve the persistent connection list on startup. (b) Provide a way to edit connections with the control. (c) Store any changes to the connection list persistently. (d) Retrieve a specific connection for actually doing work(!). The code sample below shows one approach to implement each of these.

Copy to Clipboard Print

private CredentialForm credentialForm = new CredentialForm();

// Put this in the main application initialization 
// to retrieve the persistent connection list on startup.
credentialForm.LoadConnectionList(Properties.Settings.Default.ConnectionList);

// Call this to invoke the control as a connection editor. 
// This displays the credential form as a dialog; upon closing it, 
// the changes are stored persistently in the user.config file. 
private void EditConnections()
{
    // Set control to display currently selected connection when it opens.
    credentialForm.SelectConnection(currentConnectionName);

    // Open connection editor.
    credentialForm.ShowDialog();

    if (!credentialForm.Cancelled)
    {
        // Persist any changes.
        Properties.Settings.Default.ConnectionList = credentialForm.RetrieveConnectionList();

        // In case the connection in use was edited, refresh it here as needed.
    }
}

// Get a handle to the connection details for a selected connection 
// to do some real work. 
// Then use the connection string in your application 
// via credentialInfo.ConnectionString
ConnectionDetails credentialInfo =
    credentialForm.GetConnectionDetails(currentConnectionName);

private CredentialForm credentialForm = new CredentialForm();

// Put this in the main application initialization 
// to retrieve the persistent connection list on startup.
credentialForm.LoadConnectionList(Properties.Settings.Default.ConnectionList);

// Call this to invoke the control as a connection editor. 
// This displays the credential form as a dialog; upon closing it, 
// the changes are stored persistently in the user.config file. 
private void EditConnections()
{
    // Set control to display currently selected connection when it opens.
    credentialForm.SelectConnection(currentConnectionName);

    // Open connection editor.
    credentialForm.ShowDialog();

    if (!credentialForm.Cancelled)
    {
        // Persist any changes.
        Properties.Settings.Default.ConnectionList = credentialForm.RetrieveConnectionList();

        // In case the connection in use was edited, refresh it here as needed.
    }
}

// Get a handle to the connection details for a selected connection 
// to do some real work. 
// Then use the connection string in your application 
// via credentialInfo.ConnectionString
ConnectionDetails credentialInfo =
    credentialForm.GetConnectionDetails(currentConnectionName);

The above code references a CredentialForm that you must create. The next code sample shows an actual working example of such a form.

Copy to Clipboard Print

public partial class CredentialForm : Form
{
    public CredentialForm()
    {
        InitializeComponent();
    }

    private bool cancelled;

    public bool Cancelled
    {
        get { return cancelled; }
    }

    private void CredentialForm_VisibleChanged(object sender, EventArgs e)
    {
        if (Visible) { cancelled = true; }
    }

    private void acceptButton_Click(object sender, EventArgs e)
    {
        cancelled = false;
        multiConnStrMgr.Accept();
        Close();
    }

    private void cancelButton_Click(object sender, EventArgs e)
    {
        Close();
    }

    // Display appropriate data on the ConnectionStringManager control. 
    public void SelectConnection(string connectionName)
    {
        multiConnStrMgr.SelectConnection(connectionName);
    }

    // Return the details of the specified connection name. 
    public ConnectionDetails GetConnectionDetails(string connectionName)
    {
        return multiConnStrMgr.GetConnectionDetails(connectionName);
    }

    // Load the persistent connection list into the control for manipulation. 
    public void LoadConnectionList(ConnectionDetailsCollection connectionList)
    {
        multiConnStrMgr.LoadConnectionList(connectionList);
    }

    // Retrieve the updated (Accept) or original (Cancel) list from the control. 
    public ConnectionDetailsCollection RetrieveConnectionList()
    {
        return multiConnStrMgr.RetrieveConnectionList();
    }
}

public partial class CredentialForm : Form
{
    public CredentialForm()
    {
        InitializeComponent();
    }

    private bool cancelled;

    public bool Cancelled
    {
        get { return cancelled; }
    }

    private void CredentialForm_VisibleChanged(object sender, EventArgs e)
    {
        if (Visible) { cancelled = true; }
    }

    private void acceptButton_Click(object sender, EventArgs e)
    {
        cancelled = false;
        multiConnStrMgr.Accept();
        Close();
    }

    private void cancelButton_Click(object sender, EventArgs e)
    {
        Close();
    }

    // Display appropriate data on the ConnectionStringManager control. 
    public void SelectConnection(string connectionName)
    {
        multiConnStrMgr.SelectConnection(connectionName);
    }

    // Return the details of the specified connection name. 
    public ConnectionDetails GetConnectionDetails(string connectionName)
    {
        return multiConnStrMgr.GetConnectionDetails(connectionName);
    }

    // Load the persistent connection list into the control for manipulation. 
    public void LoadConnectionList(ConnectionDetailsCollection connectionList)
    {
        multiConnStrMgr.LoadConnectionList(connectionList);
    }

    // Retrieve the updated (Accept) or original (Cancel) list from the control. 
    public ConnectionDetailsCollection RetrieveConnectionList()
    {
        return multiConnStrMgr.RetrieveConnectionList();
    }
}

Since the benefit of this control is to allow rapid switching between diverse database connections, it makes sense to provide an easy selection control such as a ComboBox, a ToolStripDropDownButton, or a dynamically generated menu list. Once the user make a selection from such a control you pass that connection name to a method similar to SetConnection, shown next, to get a DbConnection for use in your code or just use the connection string however is appropriate to your application.

Copy to Clipboard Print

private DbConnection SetConnection(string connectionName)
{
    ConnectionDetails credentialInfo =
        credentialForm.GetConnectionDetails(connectionName);
    string connString = credentialInfo.ConnectionString;
    return DbDetails.GetDbConnection(credentialInfo.DbType, connString);
}

private DbConnection SetConnection(string connectionName)
{
    ConnectionDetails credentialInfo =
        credentialForm.GetConnectionDetails(connectionName);
    string connString = credentialInfo.ConnectionString;
    return DbDetails.GetDbConnection(credentialInfo.DbType, connString);
}

That is all you need for fully-qualified connections. If, however, your set of connections omits the user name or password on one or more connections, you will need to first identify that condition, then ask the user for the missing items. A simple way to do that is shown in the following Securitize method.

Copy to Clipboard Print

private string Securitize(ConnectionDetails credentialInfo)
{
    string connectionName = credentialInfo.connectionName;
    if (credentialInfo.NeedsPassword() || credentialInfo.NeedsUsername())
    {
        loginForm.Setup(credentialInfo);
        loginForm.ShowDialog();
        secureConnStringsCache[connectionName] = loginForm.ConnectionString;
        return loginForm.ConnectionString;
    }
    return credentialInfo.ConnectionString;
}

private string Securitize(ConnectionDetails credentialInfo)
{
    string connectionName = credentialInfo.connectionName;
    if (credentialInfo.NeedsPassword() || credentialInfo.NeedsUsername())
    {
        loginForm.Setup(credentialInfo);
        loginForm.ShowDialog();
        secureConnStringsCache[connectionName] = loginForm.ConnectionString;
        return loginForm.ConnectionString;
    }
    return credentialInfo.ConnectionString;
}

To use Securitize, modify the second line of SetConnection to this:

Copy to Clipboard Print

string connString = Securitize(credentialInfo);

string connString = Securitize(credentialInfo);

The Securitize method makes use of another simple form (loginForm):

Copy to Clipboard Print

public partial class LoginForm : Form
{
    public string ConnectionString
    {
        get { return connStrManager.ConnectionString; }
    }

    public LoginForm()
    {
        InitializeComponent();
    }

    public void Setup(ConnectionDetails credentialInfo)
    {
        connStrManager.ControlTitle = credentialInfo.connectionName;
        connStrManager.ConnectionString = credentialInfo.ConnectionString;
        connStrManager.DbType = credentialInfo.DbType;
    }

    private void loginButton_Click(object sender, EventArgs e)
    {
        connStrManager.Accept();
        Close();
    }

    private void cancelButton_Click(object sender, EventArgs e)
    {
        Close();
    }
}

public partial class LoginForm : Form
{
    public string ConnectionString
    {
        get { return connStrManager.ConnectionString; }
    }

    public LoginForm()
    {
        InitializeComponent();
    }

    public void Setup(ConnectionDetails credentialInfo)
    {
        connStrManager.ControlTitle = credentialInfo.connectionName;
        connStrManager.ConnectionString = credentialInfo.ConnectionString;
        connStrManager.DbType = credentialInfo.DbType;
    }

    private void loginButton_Click(object sender, EventArgs e)
    {
        connStrManager.Accept();
        Close();
    }

    private void cancelButton_Click(object sender, EventArgs e)
    {
        Close();
    }
}

This class is instrumented with a StructuredTraceSource for diagnostic output. Its key name is MultiConnectionStringManager.

This control is instrumented with a ContainerTest property that provides additional functionality when run in the User Container of Visual Studio and set to true. See the property description for details of the added diagnostic functionality.

Since CleanCode 0.9.19.