Click here to monitor SSC
  • Av rating:
  • Total votes: 23
  • Total comments: 2
Michael Sorens

How to build a Query Template Explorer

23 October 2009

Having introduced his cross-platform Query Template solution, Michael now gives us the technical details on how to integrate his .NET controls into applications both simple and complex. With screenshots and code samples, this has everything you need to build your own powerful SQL editor or Query Template explorer.

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.

Figure 1. QuerySelectorForm - The QueryPicker is joined only by an Execute button and a Cancel button to provide a complete subform for your application.

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.

public partial class QuerySelectorForm : Form
{
    
public QuerySelectorForm()
    
{
        InitializeComponent
();
        
queryPicker.AfterSelect += queryPicker_AfterSelect;
    
}
    
    
public void Setup(ConnectionDetails connectionDetails)
    
{
        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) { Close(); }
    }

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

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:

public void PerformMetaQuery()
{
    
if (DbConnection != null)
    
{
        queryForm.Setup
(DbConnection);

        
queryForm.ShowDialog();
        
if (queryForm.Query.Length > 0)
        
{
            
if (queryForm.RevealQuery)
            
{
          
       QueryTextBox.AppendText("\n\n" +
                    
"--" + queryForm.Description + "\n");
                 
int startPos = QueryTextBox.SelectionStart;
                 
QueryTextBox.AppendText(queryForm.Query);
                 
int endPos = QueryTextBox.SelectionStart;
                 
if (endPos > startPos)
                 
{
               
     QueryTextBox.Select(startPos, endPos - startPos + 1);
                 
}
       
      }
        
     // Take appropriate action here…
            
 ExecuteTheQuery(queryForm.Query);
      
  }
    }
    
else
    
{
        MessageBox.Show
("Please select a DB connection first.", "No connection",
             
MessageBoxButtons.OK, MessageBoxIcon.Warning);
    
}
}

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.)

Figure 2. Common Denominators for Multiple Database Access - (click on the image for a closer look) The typical .NET data access objects (e.g. SqlDataAdapter) are implementations of a broader-scoped component (e.g. the DbDataAdapter).

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:

Figure 3. Neatly Grouped Signatures of the DbDetails Class

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.

public static DbDataAdapter GetDbDataAdapter(
    
ConnectionStringManager.DBTypes dbType,
    
DbConnection connection,
    
string query)
{
    
switch (dbType)
    
{
        
case ConnectionStringManager.DBTypes.SqlServer:
            
return new SqlDataAdapter(query, (SqlConnection)connection);
        
case ConnectionStringManager.DBTypes.Oracle:
            
return new OracleDataAdapter(query, (OracleConnection)connection);
        
case ConnectionStringManager.DBTypes.MySql:
            
return new MySqlDataAdapter(query, (MySqlConnection)connection);
        
case ConnectionStringManager.DBTypes.Odbc:
            
return new OdbcDataAdapter(query, (OdbcConnection)connection);
        
default:
            
return new OdbcDataAdapter(query, (OdbcConnection)connection);
    
}
}

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:

dataGridView.DataSource = bindingSource;
SqlConnection connection = new SqlConnection(connectionString);
SqlDataAdapter dataAdapter = new SqlDataAdapter(selectQuery, connectionString);
DataTable dataTable = new DataTable(); dataAdapter.Fill(dataTable);
bindingSource.DataSource = dataTable;

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:

dataGridView.DataSource = bindingSource;
DbConnection connection = DbDetails.GetDbConnection(dbType, connectionString);
DbDataAdapter dataAdapter =
     DbDetails.GetDbDataAdapter(dbType, connection, selectQuery);
DataTable dataTable = new DataTable(); dataAdapter.Fill(dataTable);
bindingSource.DataSource = dataTable

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:

Figure 5. The SqlEditor User Control - This user control encapsulates a full-fledged single pane SQL editor complete with syntax highlighting, file loading/saving, output grid formatting/filtering, and more.

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:

Figure 6. The SqlEditor Components - (click on the image for a closer look) The central portion shows the 4 key user controls used to provide a platform for the QueryPicker, while the side bars show the ample help and tip displays that come along for free.

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.

Figure 7. The Basic QueryPicker Demo

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.

Figure 8. The Embedded QueryPicker Demo

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.

Figure 9. Assembly relationships used by SqlEditor and QueryPicker

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.

Michael Sorens

Author profile:

Michael Sorens is passionate about software to be more productive, evidenced by his open source libraries in several languages (see his API bookshelf) as well as SqlDiffFramework (a DB comparison tool for heterogeneous systems including SQL Server, Oracle, and MySql). With degrees in computer science and engineering he has worked the gamut of companies from Fortune 500 firms to Silicon Valley startups over the last 25 years or so. Current passions include PowerShell, .NET, SQL, and XML technologies (see his full brand page). Spreading the seeds of good design wherever possible, he enjoys sharing knowledge via writing (see his full list of articles), teaching, and StackOverflow. Like what you have read? Connect with Michael on LinkedIn and Google +

Search for other articles by Michael Sorens

Rate this article:   Avg rating: from a total of 23 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Abc
Posted by: Alok singh (view profile)
Posted on: Saturday, October 15, 2011 at 11:13 AM
Message: thank for this nice article.............

Subject: Problem running the Demo
Posted by: David D. (view profile)
Posted on: Tuesday, June 19, 2012 at 2:50 AM
Message: Hi Michael, I'm very interested on your QueryControl. Unluckily the QueryPickerDemo downloaded here doesn't work. It misses a few of assemblies. Thank you very much.
David

 

Top Rated

Acceptance Testing with FitNesse: Multiplicities and Comparisons
 FitNesse is one of the most popular tools for unit testing since it is designed with a Wiki-style... Read more...

Acceptance Testing with FitNesse: Symbols, Variables and Code-behind Styles
 Although FitNesse can be used as a generic automated testing tool for both applications and databases,... Read more...

Acceptance Testing with FitNesse: Documentation and Infrastructure
 FitNesse is a popular general-purpose wiki-based framework for writing acceptance tests for software... Read more...

TortoiseSVN and Subversion Cookbook Part 11: Subversion and Oracle
 It is only recently that the tools have existed to make source-control easy for database developers.... Read more...

TortoiseSVN and Subversion Cookbook Part 10: Extending the reach of Subversion
 Subversion provides a good way of source-controlling a database, but many operations are best done from... Read more...

Most Viewed

A Complete URL Rewriting Solution for ASP.NET 2.0
 Ever wondered whether it's possible to create neater URLS, free of bulky Query String parameters?... Read more...

Visual Studio Setup - projects and custom actions
 This article describes the kinds of custom actions that can be used in your Visual Studio setup project. Read more...

.NET Application Architecture: the Data Access Layer
 Find out how to design a robust data access layer for your .NET applications. Read more...

Calling Cross Domain Web Services in AJAX
 The latest craze for mashups involves making cross-domain calls to Web Services from APIs made publicly... Read more...

Web Parts in ASP.NET 2.0
 Most Web Parts implementations allow users to create a single portal page where they can personalize... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.