public class QueryPicker : UserControl, 
	IResourceUser

public class QueryPicker : UserControl, 
	IResourceUser

Public Class QueryPicker _
	Inherits UserControl _
	Implements IResourceUser

Public Class QueryPicker _
	Inherits UserControl _
	Implements IResourceUser

public ref class QueryPicker : public UserControl, 
	IResourceUser

public ref class QueryPicker : public UserControl, 
	IResourceUser

This control is handy for probing details of a database server, providing support for SQL Server, Oracle, and MySql out of the box. The default query library includes a large variety of queries, including those for:

• Display DB version
• List available databases
• Find columns for a table
• Find tables containing a column
• Find non-empty tables

Each of these queries are specified separately for each supported database type. Some (such as identifying the database version) are invariant while others require you to specify parameters like a table name or a column name. Each meta-query, therefore, is actually provided as a meta-query template. Each template contains place holders of the form {name} for any parameters that the user needs to supply at runtime, e.g. a table or column filter, or an owner name (for Oracle). For any place holder so defined, when a given query template is selected on the control, it will dynamically generate a TextBox input field. You eventually call the ProcessInputs(Boolean) method back in your main line code, which then makes two values available via properties: Query and Description. Click on the thumbnail at right for a screenshot of the QueryPicker.

I have published a three-part series on the Simple-Talk online magazine discussing the design and use of the QueryPicker.
Part 1: A Unified Approach to Multi-DataBase Query Templates
Part 2: How to build a Query Template Explorer
Part 3: Using Three Flavors of LINQ To Populate a TreeView

The query library is stored as a resource in the DatabaseControls project. Upon first use of a QueryPicker control, a query file (QueryLibrary.xml) is created by externalizing the embedded resource to a file. The application stores externalized files in a subdirectory of the system-defined Application Data directory, where the subdirectory name is the base name of the currently running application. For example, if your application that uses a QueryPicker is named MyProgram.exe and the user's name is "smith," the folder is ...\smith\Application Data\MyProgram. The initial portion of the path depends upon which version of Windows you are running. Important note: Once the file has been externalized in this fashion, it will never be updated by the QueryPicker control. That is, if you modify the master library in the DatabaseControls project and recompile it, then recompile your application which uses the DatabaseControls DLL, the QueryLibary.xml file will not be automatically updated. If you want one to be refreshed from the master copy you must delete the existing ones so that QueryPicker will again be triggered to create one on first use. In my article Using LINQ to Manage File Resources and Context Menus (available on the DevX online magazine) I discuss in detail a technique to accomplish this purge automatically with just a couple lines of code. The file will be purged each time a user installs a new version of your application unless he/she has made the file read-only to preserve some customized changes.

Here is one example of a meta-query template for Oracle, where there are three place holders, for Owner, Column, and Table. The final clause in the where predicate filters out any table names containing underscores or dollar signs, which seems to be a reasonable choice for limiting the noise. Note that due to the like operators, the user is free to either specify a specific value for a field or to include wildcards (%).

select table_name as TableName, column_name as ColumnName
from sys.ALL_TAB_COLS
where owner LIKE '{Owner}' 
and column_name LIKE '{Column}' 
and table_name LIKE '{Table}' 
and not regexp_like(table_name, '[$_]', 'i')
order by table_name, column_name

select table_name as TableName, column_name as ColumnName
from sys.ALL_TAB_COLS
where owner LIKE '{Owner}' 
and column_name LIKE '{Column}' 
and table_name LIKE '{Table}' 
and not regexp_like(table_name, '[$_]', 'i')
order by table_name, column_name

The LIKE operators are significant for the default behavior when the user leaves a field blank. In that event, the SQL wildcard (%) is substituted for the place holder, matching all values. Note that it would be more efficient to suppress the predicate completely. But one situation where explicitly including the match of all values is useful is in the case where you want to hand-modify the query with successive values. That is, rather than opening the QueryPicker and setting values each time open it once and set no values. You then get a query where each placeholder now matches all values. You can then change any or all of them individually in your application's editor. QueryPicker lets you choose to show match-all predicates or to suppress them completely (via a Boolean argument to the ProcessInputs method and properly instrumented queries). Rather than writing and column_name LIKE '{Column}' surround the containing clause with suppress-on-empty brackets, as in: [[and column_name LIKE '{Column}']].

A special notation is available for Boolean fields. Normally a text box is generated for each place holder. By using a label containing a question mark (?) you can force the component to generate a set of radio buttons instead, indicating true, false, and either choices. These choices are labeled "Yes", "No", and "Any" by default, but you can specify different labels if you prefer. For example, {IsIdentity?} generates radio buttons with default labels while {IsIdentity?True/False/Either} generates buttons with the specified labels.

A special notation is also available for required text fields. Affixing an exclamation point (!) to the end of a field name triggers validation functionality to ensure that the user enters a value for the field. Once the user types something in the field, it will display an ErrorProvider icon if the field later becomes empty. Also, the field displays the ErrorProvider if it is empty when you call ProcessInputs. It is up to the caller, though, to honor the RequiredFieldsSatisfied. property.

This example code fragment shows instrumenting your main program by setting the database type, invoking a separate query-picker form as a dialog, then processing the results.

bool shiftPressed = ((Control.ModifierKeys & Keys.Shift) == Keys.Shift);
queryPickerForm.Setup(DbConnection, shiftPressed);
queryPickerForm.ShowDialog();
if (queryPickerForm.Query.Length > 0)
{
    if (queryForm.RevealQuery)
    {
        queryTextBox.AppendText("\n\n" +
            SQL_COMMENT + " " + queryPickerForm.Description + "\n");
        int startPos = queryTextBox.SelectionStart;
        queryTextBox.AppendText(queryPickerForm.Query);
        int endPos = queryTextBox.SelectionStart;
        if (endPos > startPos)
        { queryTextBox.Select(startPos, endPos - startPos + 1); }
    }
    PerformExecuteQuery();
}

bool shiftPressed = ((Control.ModifierKeys & Keys.Shift) == Keys.Shift);
queryPickerForm.Setup(DbConnection, shiftPressed);
queryPickerForm.ShowDialog();
if (queryPickerForm.Query.Length > 0)
{
    if (queryForm.RevealQuery)
    {
        queryTextBox.AppendText("\n\n" +
            SQL_COMMENT + " " + queryPickerForm.Description + "\n");
        int startPos = queryTextBox.SelectionStart;
        queryTextBox.AppendText(queryPickerForm.Query);
        int endPos = queryTextBox.SelectionStart;
        if (endPos > startPos)
        { queryTextBox.Select(startPos, endPos - startPos + 1); }
    }
    PerformExecuteQuery();
}

The queryPickerForm above requires very little to interface with the control. Your QueryPickerForm needs only a QueryPicker control along with Execute and Cancel buttons.

public partial class QueryPickerForm : Form
{
    public QueryPickerForm()
    {
        InitializeComponent();
        queryPicker.AfterSelect += queryPicker_AfterSelect;
    }

    public void Setup(ConnectionDetails connectionDetails, bool reload)
    {
        if (reload) { queryPicker.LoadLibrary(); }
        queryPicker.DbType = connectionDetails.BaseDbType;
    }


    // Properties 
    public string Query { get { return queryPicker.Query; } }

    public string Description { get { return queryPicker.Description; } }

    public bool RevealQuery { get; set; }


    // Event Handlers 
    private void queryPicker_AfterSelect(object sender, TreeViewEventArgs e)
    {
        executeButton.Enabled = (e.Node.Nodes.Count == 0);
    }

    private void executeButton_Click(object sender, EventArgs e)
    {
        RevealQuery = (Control.ModifierKeys & Keys.Shift) > 0;
        queryPicker.ProcessInputs((Control.ModifierKeys & Keys.Alt) > 0);
        if (Query.Length > 0 && queryPicker.RequiredFieldsSatisfied) { Close(); }
    }

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

public partial class QueryPickerForm : Form
{
    public QueryPickerForm()
    {
        InitializeComponent();
        queryPicker.AfterSelect += queryPicker_AfterSelect;
    }

    public void Setup(ConnectionDetails connectionDetails, bool reload)
    {
        if (reload) { queryPicker.LoadLibrary(); }
        queryPicker.DbType = connectionDetails.BaseDbType;
    }


    // Properties 
    public string Query { get { return queryPicker.Query; } }

    public string Description { get { return queryPicker.Description; } }

    public bool RevealQuery { get; set; }


    // Event Handlers 
    private void queryPicker_AfterSelect(object sender, TreeViewEventArgs e)
    {
        executeButton.Enabled = (e.Node.Nodes.Count == 0);
    }

    private void executeButton_Click(object sender, EventArgs e)
    {
        RevealQuery = (Control.ModifierKeys & Keys.Shift) > 0;
        queryPicker.ProcessInputs((Control.ModifierKeys & Keys.Alt) > 0);
        if (Query.Length > 0 && queryPicker.RequiredFieldsSatisfied) { Close(); }
    }

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

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.