This is the second of a two-part series focusing on a WinForm user control called the QueryPicker. In part 1, I described QueryPicker from the user perspective: its greater versatility compared to the template explorer in SQL Server Management Studio (SSMS); the meta-query library available for SQL Server, Oracle, and MySQL; and how to easily add your own meta-queries to the library. In this article, I will demonstrate how to manipulate the QueryPicker programmatically with its straightforward API. Treating it (and a few other controls I’ve developed) as .NET Building blocks, I’ll describe (in detail) all the steps you need to wrap it in a subform and make it seamlessly blend with your own application – abstract concepts are useful, but when I discuss software design in my articles I like to give you everything you need for implementation, so you don’t have to waste your time figuring it out.

For greater flexibility, I’ll also take you a step up the food chain to another user control in my open-source library, the SqlEditor. This control is a complete SQL editor akin to SSMS, but packaged as a user control so that it can easily be incorporated into a .NET application. If you’re finding these articles interesting, then my next discussion will dive into some of the inner workings of my QueryPicker control.

Wrapping the QueryPicker in a Form

The QueryPicker is available as a user control to be embedded on your own form, but as I’ve mentioned, I have incorporated it into higher-level components also available as building blocks, which I’ll discuss later on.
If you choose to start with the QueryPicker itself, I recommend placing the control on a subform of your main application. You only need three controls on this subform: the QueryPicker plus two buttons to process or cancel the operation, as shown in Figure 1.

The backing this form contains very few lines of code, as I’ll show you in a moment. On the startup side, there is a two-line constructor and a one-line Setup method. The Setup method needs to be called just before calling ShowDialog on the form; it identifies the type of the database being used (SQL Server, Oracle, or MySQL) so that the QueryPicker is populated with the correct set of queries. The constructor includes the standard InitializeComponent call plus a second line to hook up an event handler that watches which node is selected. If a leaf node is selected (i.e. an actionable query template) then the event handler enables the Execute button, otherwise it disables it. On the output side, there are handlers for the Execute and the Cancel buttons, along with the Query, RevealQuery, and Description properties. The three-line Execute button handler does the following:

• The first line of the handler implements the behavioral convention that if the Shift key is depressed, the query should be revealed (it is invisible by default). As shown below, it’s only setting the RevealQuery property, whose value should be checked by your main application, triggering an appropriate action.
• The second line does the work of generating the query from the template and your inputs. The single argument to the ProcessInputs method implements the behavioral convention that depressing the Alt key unmasks universal matches.
• The final line of the handler checks that there is, in fact, a query of non-zero length available after processing, and if so closes the dialog.

Once the QuerySelectorForm closes, your application has the three updated properties (Query, Description, and RevealQuery), ready to use. This next code fragment shows the interaction from that perspective. As just described, first it calls the Setup method to identify the database type, and then invokes the dialog. If it comes back with a non-empty query (i.e. the operation was not cancelled by the user), it then honors the RevealQuery property by inserting the query’s description (queryForm.Description) as a comment along with the query text (queryForm.Query) into the QueryTextBox (a RichTextBox or similar control), automatically selecting the text of the query as well. Finally, it calls ExecuteTheQuery, a method in your application aligned with the function of the Execute button of the subform:

Encapsulating the Multiple Database Support

The QueryPicker itself is completely database-neutral, as are the QuerySelectorForm and the PerformMetaQuery code to interface with it. Yet the secret to creating generic, multiple-database access components is actually in grasping the underlying parallelisms, illustrated in Figure 2. Typical .NET applications use the data access objects for SQL Server (e.g. SqlConnectionStringBuilder, SqlDataAdapter, SqlConnection, and so forth), and parallel components for other database types are also available, some of which are already incorporated in Microsoft’s .NET framework (e.g. the ODBC<DAO components>). Others are available from the database vendor (e.g. the MySQL<DAO components>). Oracle is right in the middle: Microsoft has supplied an Oracle client in the framework right up through to .NET framework 3.5, but this will be discontinued as of .NET 4.0 and you will need to obtain a third-party version.

(If you’re interested, the announcement from the OracleClient program manager, Himanshu Vasishth, is available here.)

The QueryPicker, as well as every other relevant class in my open-source libraries, interacts only with the leftmost objects in Figure 2. There is just a single generic class, DbDetails, which references the database-specific classes and their underlying DLLs. The method signatures of the DbDetails class are:

The first parameter of each method (dbType) specifies the database type, which lets the class know which database-specific object to reference. I have grouped the methods in Figure 3 to make this discussion a little easier to follow…

The implementation of each method in the first group is just a switch statement, as shown here for GetDbDataAdapter, taking advantage of the relationships shown in Figure 2.

The second group provides database-neutral methods to access the database-type properties (known to the DbDetails class), such as:

• SQL Server allows a choice of windows authentication or database authentication, while neither Oracle nor MySQL does.
• SQL Server and MySQL have the concept of separate databases while Oracle does not. The GetDatabaseEnumeratorQuery method returns either a SQL expression to enumerate the database, or null to indicate it does not support it.
• Oracle is case-sensitive in queries while SQL Server is not. References to meta-objects in Oracle, therefore, must take this into account. Table names, column names, and so forth, are stored as uppercase in Oracle so must be referenced that way. The GetRequiresUpperCase method allows the QueryPicker to glean this fact and automatically convert user inputs to uppercase so your users will not have to worry about it.

The final group contains a single method, GetBaseDbType, which is used for enhancing ODBC data sources. ODBC sources can represent standard databases, but they may also tie to Excel spreadsheets, Access databases, even plain text files. This method looks into the data source to determine if it is, in fact, a conduit for one of the supported database types (SQL Server, Oracle, or MySQL).

The second and third groups provide support, but the first group of methods is the key. At the beginning of this section I mentioned that a typical .NET application uses SqlDataAdapter among other components. Despite that generic sounding name, SqlDataAdapter is specific to SQL Server. But now you have the GetDbDataAdapter method that returns a DbDataAdapter, the generic base class of the SqlDataAdapter. The same applies to SqlConnection vs. DbConnection and SqlConnectionStringBuilder vs. DbConnectionStringBuilder. Typical code for programmatically retrieving data and displaying it in a DataGridView from SQL Server (as for example, in Microsoft’s How to: Bind Data to the Windows Forms DataGridView Control) looks like this:

That is, you connect your DataGridView to your binding source, get a connection object from your connection string, then use that and the query to get a data adapter. The Fill method of the data adapter loads a data-table that you finally connect to the binding source, completing the necessary links. Converting the above code fragment to one that connects generically to any database type is a trivial substitution of methods:

Typical applications use a fixed connection string and a fixed query, which works fine unless your application is something like SSMS, where you want the user to be able to select a server, a database, and/or an arbitrary query at runtime. Clearly, the QueryPicker is geared for just such an environment: it supplies arbitrary queries and can handle different database types. However, the QueryPicker requires additional infrastructure-a framework for pointing to a server and database as well as input and output venues.

(You can find a comprehensive review of these elements of dynamic data sourcing in an earlier article of mine)

Figure 4. Connection Editor – The
MultiConnectionStringManager user
control on this form manages a set
of database connections.

A Control to Manage a Set of Connection Strings

The first component needed to supplement the QueryPicker is the MultiConnectionStringManager, a user control that lets you create, edit, store, import, and export a set of connection strings.

The control (Figure 4) comprises the bulk of the Connection Editor form; only the OK and Cancel buttons on the form are supplemental. The drop-down selector at top lets you switch between connections and the DB Type selector conveniently supports the same set of database types we’ve been discussing: SQL Server, Oracle, MySQL, and ODBC.

The MultiConnectionStringManager itself builds on the ConnectionStringManager user control, described in detail in a previous article ‘.NET Building Blocks‘ article of mine. Combining this connection editor with a RichTextBox for input and a DataGridView for output is enough to quickly build a working framework to use the QueryPicker. The SqlEditor provides just that.

A SQL Editor Control

The eponymous SqlEditor user control combines a QueryPicker, a MultiConnectionStringManager, a DataGridView, and a RichTextBox to provide an editor pane that supports multiple database-types right out of the box! You could literally drag this onto an otherwise empty form in the Visual Studio designer, then compile and go:

The SqlEditor control does not just use a plain vanilla DataGridView or RichTextBox, though. It includes fully customized versions: an ExtendedDataGridView and ChameleonRichTextBox, respectively. My ExtendedDataGridView is built upon a foundation provided by Chris McGrath’s Extending the DataGridView. The ChameleonRichTextBox is built upon the work of two other open source components, the SyntaxHighlightingTextBox (from a developer known as “kabwla“, who built his improvements upon the work of “uri guy“) and Jim Blackler’s SearchableRichTextBox. Table 1 shows highlights of my controls’ additional capabilities.

ExtendedDataGridView

ChameleonRichTextBox

• Arbitrary filtering • Column width snap-to-data or snap-to-headers • Row height adjustment • Export to Excel • Date format customization • Display column datatype • Hide/show columns • Searching

• Dialect-specific syntax highlighting • SQL Server, Oracle, MySQL out-of-the-box; customizable to any   other • Enable/disable highlighting • Automatic or on-demand highlighting • Differentiates end-of-line and block comment highlighting • Auto-convert keywords to uppercase if desired • Differentiates groups of keyword highlighting • Case-sensitive keyword recognition if desired • Keyword completion • Macro instantiation with placeholder highlighting and snap-to-   placeholders • Comment/uncomment region • Search/replace by text or regular expression • Indent region left or right

Table 1. ExtendedDataGridView and ChameleonRichTextBox Additional Features

Here’s a visual overview of the principal controls from my open source library that I’ve used here:

Although the SqlEditor control provides its own rich features to expose the capabilities of the ExtendedDataGridView and the ChameleonRichTextBox, at its heart it is just the same six lines of code you saw earlier, loading a DataGridView from a database.

Exercising the Demo Applications

I have included two demo applications with this article to give you some hands-on practice with the QueryPicker control. I’ve included the Visual Studio solution with source code, as well as the compiled executables (in each project’s bin/Debug directory) so you need only rename each name.executable file to name.exe, and you can run them right out of the box.

The Basic QueryPicker Demo gives you the QueryPicker control distilled down to the simplest possible interface, with a couple on-screen reminders for what to do with it. The form (Figure 7) gives you a drop-down control to select the database type, a Launch button to open the QueryPicker, and a text box to contain the output when you return from the QueryPicker subform.

The Embedded QueryPicker Demo literally took seconds to create: I created a WinForm application and added the production-quality SqlEditor control to the toolbox by including my open-source DLL file, then dragged it onto the visual designer. The only other piece I added was a pop-up that gives a brief explanation on how to use it, in case you don’t have this article in front of you at the time.

Here’s what to do: Alt-click the connection selector to open the connection editor (item 1 in Figure 8). To create a new connection, just type a descriptive name in the top box of the connection editor, enter the details of your connection, and test it with (unsurprisingly) the test button. Once you’re satisfied, close the connection editor to return to the main window and then select the connection you just defined. Access the QueryPicker via the button with the question mark (item 2 in the figure), drill down to select a query – the simplest is the Version query in the System category – and press Execute (or Shift-Execute if you want to see it) and the meta-query auto-executes.

Finally, if you are one of those people who like to be able to understand the relationship of components you use, Figure 9 shows the assemblies and their relationships brought together when you instantiate a SqlEditor control. The QueryPicker resides in the CleanCode.DatabaseControls assembly, highlighted in the figure.

Call to Action

Table 1 in part 1 of this article listed all the meta-queries I supplied with the QueryPicker at the time of this writing. The unfilled cells in the table are not indicative of unavailable, unfathomable, or otherwise undeterminable queries, but rather only that limited time has prevented me from tracking those down. If you can fill in any of those missing ones or, indeed, supply other new, interesting queries, please post a comment at the bottom of this article, so that others could benefit from your find. I will likely include your contribution (with your name attached to it!) in a future release of my code, so be sure to check my website for updates (this article coincides with release 0.9.29). If you missed out on the background to the QueryPicker Control, you can read more about it here. If you’ve keep up with my this far, then you might like to read more about how I implemented some of the QueryPicker’s functionality.