A Visual SQL Query Designer

• Download demo project - 47.7 KB
• Download source (C#) - 83.7 KB
• Download source (VB) - 79.8 KB

Introduction

This article describes the implementation of a QueryDesignerDialog class that allows users to create SQL queries based on a given OLEDB connection string.

The designer is similar to the ones found in database tools such as the SQL Server Management Studio and Microsoft Access. It allows end users to build SQL queries with support for sorting, grouping, and filtering.

The main limitation of the QueryDesignerDialog is that it does not parse existing SQL statements. This is a one-way tool; you can use it to create new queries, but not to edit existing ones. Also, it only supports OLEDB data sources, which includes SQL Server and Access. Future versions may address these limitations.

Background

The first version of the QueryDesignerDialog was written for use with a Report Designer application. I could not find a tool (free or commercial) to do the job the way I wanted, so I decided to write it myself. After that, I re-used it in a few other applications, and thought it might be useful to others as well.

Using the Code

The QueryDesignerDialog has two main properties:

• ConnectionString: Gets or sets the OLEDB connection string used to retrieve the database schema with the list of tables, views, fields, and relations from which the query will be built.
• SelectStatement: Gets the SQL statement designed by the user. For now, this is a read-only property. The dialog cannot be used to edit existing SQL statements. Perhaps this will be added in future versions.

The code snippet below shows how the QueryDesignerDialog is typically used. You assign it a connection string, show the dialog, and read back the SQL query:

// create the QueryDesignerDialog
using (var dlg = new QueryDesignerDialog())
{
  // set the connection string
  dlg.ConnectionString = ConnectionString;
  
  // show the dialog
  if (dlg.ShowDialog(this) == DialogResult.OK)
  {
    // get the new Sql query and do something with it
    string newSql = dlg.SelectStatement;
    DoSomething(newSql);
  }
}

Implementation

The QueryDesignerDialog relies on two important helper classes:

• OleDbSchema: This class extends the ADO.NET DataSet class. It takes an OLEDB connection string and fills the DataSet with all the tables, views, columns, relations, and constraints defined in the database (it does not retrieve any data). Applications can then expose these elements in a visual UI to preview the data, create and edit queries, and so on. The OleDbSchema class also provides utility methods for checking table types, encoding their names with brackets when necessary, managing Stored Procedure parameters, and so on.
• QueryBuilder: This class uses the OleDbSchema class, and maintains a list of query fields with properties that define sorting, grouping, etc. It is also responsible for building SQL statements based on the query fields and on the database schema.

Selecting a Connection String

In order to use the QueryDesignerDialog, you need an OLEDB connection string. Some applications may use a list of pre-defined connection strings, others allow the user to create the connection string at run time. The sample included with this article falls in the second category.

The OleDbConnString class included in the code provides utilities for dealing with connection strings. The main methods in that class are GetConnectionString and EditConnectionString, both of which show the DataLinks dialog used to create or edit connection strings:

string newConnString = OleDbConnString.GetConnectionString(this);
string editConnString = OleDbConnString.EditConnectionString(this, editConnString);

These methods rely on the following system assemblies which must be referenced by the project:

• OLEDB32.dll: Contains the DataLinks class (which used to be in MSDASC.DLL). This file can be found at C:\Program Files\Common Files\System\Ole DB\OLEDB32.DLL.
• ADODB.dll: This is required to read the COM object passed back from DataLinks. This file can be found at C:\Program Files\Microsoft.NET\Primary Interop Assemblies\ADODB.DLL.

Once the user has picked a connection string, most applications will save it in a list for later re-use. Because the connection strings tend to be quite long, showing them to users may be challenging, so the OleDbConnString class provides a TrimConnectionString method that shortens the connection strings for display purposes, keeping only the provider and data source parts. The sample application provided with the article uses TrimConnectionString to display recently used connection strings in an owner-drawn combobox.

Retrieving the Database Schema

When you assign a connection string to a QueryDesignerDialog, it starts by retrieving the database schema so it can show the user a list of the tables and views available for use in the query. This job is done by the OleDbSchema class mentioned above. The code looks like this:

// get schema for the new connection string
OleDbSchema schema = OleDbSchema.GetSchema(connectionString);

The OleDbSchema class extends the ADO.NET DataSet class. You can use it to enumerate the elements available in the database, including tables, views, Stored Procedures, fields, relations, and constraints. If the connection string is invalid, or if some error occurs while getting the schema, then GetSchema returns null.

Some of the information retrieved from the database is stored in the ExtendedProperties property of the tables and fields. For example, views and Stored Procedures in the database are represented by DataTable objects, and can be identified by their ExtendedProperties[TABLE_TYPE] value. The OleDbSchema class provides helper methods that deal with this, so callers don't have to. For example, the GetTableType method returns a value that indicates whether a DataTable in an OleDbSchema represents a regular table, a view, or a Stored Procedure.

The implementation of the OleDbSchema class is based on the OleDbConnection.GetOleDbSchemaTable method. This method allows you to retrieve tables, views, Stored Procedures, relations, and constraints defined for the connection. Once you have a table, the OleDbDataAdapter.FillSchema method is used to retrieve the fields. If you are interested in the details, please refer to the source code.

The example below shows how the QueryDesignerDialog class populates a TreeView control with the tables and views available in the database:

// update table tree to reflect new connection string
void UpdateTableTree()
{
  // initialize table tree
  TreeNodeCollection nodes = _treeTables.Nodes;
  nodes.Clear();
  var ndTables = new TreeNode(Properties.Resources.Tables, 0, 0);
  var ndViews = new TreeNode(Properties.Resources.Views, 1, 1);

  // populate using current schema
  if (Schema != null)
  {
    // populate the tree
    _treeTables.BeginUpdate();
    foreach (DataTable dt in Schema.Tables)
    {
      // create new node, save table in tag property
      var node = new TreeNode(dt.TableName);
      node.Tag = dt;

      // add new node to appropriate parent
      switch (OleDbSchema.GetTableType(dt))
      {
        case TableType.Table:
          ndTables.Nodes.Add(node);
          node.ImageIndex = node.SelectedImageIndex = 0;
          AddDataColumns(node, dt);
          break;
        case TableType.View:
          ndViews.Nodes.Add(node);
          node.ImageIndex = node.SelectedImageIndex = 1;
          AddDataColumns(node, dt);
          break;
        }
      }

      // add non-empty nodes to tree
      foreach (TreeNode nd in new TreeNode[] { ndTables, ndViews })
      {
        if (nd.Nodes.Count > 0)
        {
          nd.Text = string.Format("{0} ({1})", nd.Text, nd.Nodes.Count);
          nodes.Add(nd);
        }
      }

      // expand tables node
      ndTables.Expand();

      // done
      _treeTables.EndUpdate();
  }
}
void AddDataColumns(TreeNode node, DataTable dt)
{
  foreach (DataColumn col in dt.Columns)
  {
    var field = node.Nodes.Add(col.ColumnName);
    field.Tag = col;
    field.ImageIndex = 2;
    field.SelectedImageIndex = 2;
  }
}

The OleDbSchema class is used internally by the QueryDesignerDialog class, but it is public, and can also be used directly by applications that need access to schemas. The sample application included with the article uses it to populate a list with all the tables, views, and Stored Procedures in the database.

Building SQL Queries

Once the database schema is available, we can use it to build queries. This is done by the QueryBuilder class, which maintains a list of QueryField objects and generates SQL statements. The main properties of the QueryBuilder class are:

• ConnectionString: Used to retrieve the database schema. The schema defines the tables and fields used in the query, and also the relations between the tables which are needed to build the SQL JOIN statements.
• QueryFields: A collection of QueryField objects used to build the query. Each QueryField object represents a database field or an expression, and has properties that define sorting, grouping, aliasing, and so on. This is a bindable collection suitable for display in a DataGridView control, for example.
• Sql: The SQL Select statement built to represent the current state of the QueryFields collection.

The Sql property is read-only. It builds the SQL query in parts, based on the QueryFields collection.

First, the SELECT clause is built by scanning the fields and outputting the proper table/view and column names. Next, the QueryFields collection is analyzed to determine how the tables are connected. This allows the QueryBuilder to create the FROM clause with the required JOIN statements, which is by far the most complicated and interesting part of the class. Finally, the ORDER BY and WHERE clauses are built based on the properties of the QueryField objects.

To build the FROM clause of the SQL statement, the QueryBuilder class starts by building a list of tables so that each table on the list is related to the next one. This is accomplished by the InsertRelatedTable method. Next, it scans the list to find the relation that connects each table with the following one. Each relation is then used to build the corresponding JOIN statement.

The QueryField class contains the information that defines each field in the query. It has the following properties:

• Column: The string that specifies the name of a column within a table (e.g., "FirstName") or an expression (e.g., "LEFT(FirstName, 2)").
• Alias: Optional string used to identify the field instead of the Column value. If provided, this value is used as the column name on the query results table. For example, if the Column property is set to "LEFT(FirstName, 1)" and the Alias property is set to "FirstInitial", the output table will contain a column named "FirstInitial".
• Table: String that identifies the table that contains the column. This property is read-only; it is provided only for binding purposes (so the table names appear on the field grid described later).
• Output: Boolean value that determines whether the field should be included in the output table. This is used to hide fields that are needed to define the query but should not appear in the output (e.g., fields used in calculations or to provide connections between related tables).
• GroupBy: Aggregate to use when grouping the field. This column is used only if the QueryBuilder.GroupBy property is set to true. In this case, the original data records are grouped and represented by an aggregate such as a sum or average. For example, to create a query showing the average product price per category, you would use two fields: "CategoryName", with GroupBy set to GroupBy; and "ProductPrice", with GroupBy set to Average.
• Sort: Specifies whether the field should be used to sort the output in ascending or descending order.
• Filter: A filter expression used to select the records included in the output. If provided, the expression should be of the format [OPERATOR] [VALUE] or BETWEEN [VALUE1] AND [VALUE2]. To reduce the possibility of syntax errors when specifying the Filter value, the QueryDesignerDialog class provides an editor for this property instead of allowing users to type the expressions directly.

Designing SQL Queries

The QueryDesignerDialog class provides the user interface for creating queries. It uses the QueryBuilder class described above, and adds the following UI elements:

• Table/View tree: This is a TreeView control containing all the tables and views in the data source and all the fields within each table/view. Fields can be double-clicked or dragged onto the QueryField grid to be added to the query. The TreeView has a context menu that allows users to remove specific tables/views from the list or navigate to tables that are related to the one that is currently selected.
• QueryField grid: This is a DataGridView control that shows the fields currently included in the query and their properties. Users may reorder the fields by dragging the grid rows, remove fields by deleting rows, and edit fields directly on the grid.
• Sql view: This is a TextBox control that shows the SQL statement as the user adds and edits individual fields on the grid. The TextBox is read-only. Users can see the SQL statement and copy it to the clipboard, but they cannot change it by typing on the TextBox.
• ToolStrip: The ToolStrip on top of the dialog contains buttons for toggling the query's GroupBy property, editing query properties, checking the SQL syntax, previewing the query results, and for clearing the query.

The Table/View tree is the element that deviates the most from the traditional query design tools available in tools such as SQL Server Management Studio and Microsoft Access. It shows all the tables by default, instead of asking users to add and remove them individually. Tables and views are shown as tree nodes, rather than as small floating lists. The tree-based UI is substantially simpler. The main drawback it has is that the connections between tables are not readily visible to the user. This is somewhat alleviated by the context menu that shows related tables on demand.

The image below shows the QueryDesignerDialog in action, creating a query against the popular AdventureWorks database:

QueryField Grid Implementation

The QueryField grid is a DataGridView that displays a list of QueryField objects. By default, the DataGridView uses text boxes for editing non-boolean cells and check boxes for booleans. This is not ideal for editing values that are enumerations, such as the Sort and GroupBy properties of the QueryField class. Those are much easier to edit with combo box controls instead. Also, we wanted to use a custom editor for the Filter field, which is of type string but has formatting requirements that are better handled with a custom editor.

These requirements seem very common, so I think showing the code here might be useful to some developers. The code below shows how you can replace regular DataGridView columns with combo box ones for fields that are enumerations.

// replace regular grid columns combo box columns for enum types
void FixGridColumns()
{
  for (int i = 0; i < _grid.Columns.Count; i++)
  {
    var col = _grid.Columns[i];
    if (col.ValueType.IsEnum)
    {
      // create combo column for enum types
      var cmb = new DataGridViewComboBoxColumn();
      cmb.ValueType = col.ValueType;
      cmb.Name = col.Name;
      cmb.DataPropertyName = col.DataPropertyName;
      cmb.HeaderText = col.HeaderText;
      cmb.DisplayStyleForCurrentCellOnly = true;
      cmb.DataSource = Enum.GetValues(col.ValueType);
      cmb.Width = col.Width;

      // replace original column with new combo column
      _grid.Columns.RemoveAt(i);
      _grid.Columns.Insert(i, cmb);
    }
  }
}

The sample also replaces the "Filter" column with one that shows a button instead of a text box. Clicking the button brings up a filter editor dialog which can be used to edit the filter value. I would rather give users a choice, allowing them to type filter values directly into the cell or click a button on the right of the cell to show the editor dialog. That is in the to-do list for a future version.

Sample Application

The sample application included with this article is a dialog that allows users to select a connection string, see all the tables, views, and Stored Procedures in the corresponding database, create queries against the database, and see the corresponding data. It could be used as a data source selection tool in applications such as report designers (which is actually why this was written in the first place).

The image below shows the sample application in action:

The application has a ToolStrip along the top of the main dialog.

The ToolStrip contains a combobox that provides a list of recently used connection strings, and allows users to type or paste connection string values.

The dropdown part of the combobox is owner-drawn to show a trimmed version of the connections strings which are easier to read than the full version. The owner-draw code uses the TrimConnectionString method in the OleDbConnString class as shown below:

// trim items in combo box (they're very long)
void cmb_DrawItem(object sender, DrawItemEventArgs e)
{
  var fmt = new StringFormat();
  fmt.LineAlignment = StringAlignment.Center;
  fmt.Trimming = StringTrimming.EllipsisPath;

  var text = (string)_cmbConnString.Items[e.Index];
  text = OleDbConnString.TrimConnectionString(text);

  var brush = (e.State & DrawItemState.Selected) != 0
    ? SystemBrushes.HighlightText
    : SystemBrushes.WindowText;

  e.DrawBackground();
  e.Graphics.DrawString(text, _cmbConnString.Font, brush, e.Bounds, fmt);
  e.DrawFocusRectangle();
}

The list of recent connection strings is saved as an application setting so it can be reused across sessions.

The button next to the combobox allows users to create new connection strings using the familiar "DataLink" dialog. The button uses the EditConnectionString method in the OleDbConnString class as shown below:

// pick a new connection
void _btnConnPicker_Click(object sender, EventArgs e)
{
  // release mouse capture to avoid wait cursor
  _toolStrip.Capture = false;

  // get starting connection string
  // (if empty or no provider, start with SQL source as default)
  string connString = _cmbConnString.Text;
  if (string.IsNullOrEmpty(connString) || 
      connString.IndexOf("provider=", StringComparison.OrdinalIgnoreCase) < 0)
  {
    connString = "Provider=SQLOLEDB.1;";
  }

  // let user change it
  ConnectionString = OleDbConnString.EditConnectionString(this, connString);
}

This code invokes the "DataLink" dialog seen below:

The next button (with a magic wand image) invokes the QueryDesignerDialog which allows users to design SQL queries. Once the query is ready, it is shown in a TextBox on the second tab of the main form. The code that invokes the QueryDesignerDialog looks like this:

// invoke SQL builder
void _btnSqlBuilder_Click(object sender, EventArgs e)
{
  using (var dlg = new QueryDesignerDialog())
  {
    dlg.Font = this.Font;
    dlg.ConnectionString = ConnectionString;
    if (dlg.ShowDialog(this) == DialogResult.OK)
    {
      _txtSql.Text = dlg.SelectStatement;
      _tab.SelectedTab = _pgSql;
      UpdateUI();
    }
  }
}

The code creates a QueryDesignerDialog, initializes its ConnectionString property, then shows the dialog and retrieves the results by reading the dialog's SelectStatement property.

The last button (with a preview image) loads the data from the currently selected source (table, view, Stored Procedure, or SQL statement) into a DataTable and shows the table on a modal dialog. The implementation is given below:

// preview data for currently selected node
void PreviewData()
{
  // create table to load with data and display
  var dt = new DataTable("Query");

  // if a table/view is selected, get table name and parameters
  if (_tab.SelectedTab == _pgTables)
  {
    // get table/view name
    var table = _treeTables.SelectedNode.Tag as DataTable;
    dt.TableName = table.TableName;

    // get view parameters if necessary
    var parms = OleDbSchema.GetTableParameters(table);
    if (parms != null && parms.Count > 0)
    {
      var dlg = new ParametersDialog(parms);
      dlg.Font = Font;
      if (dlg.ShowDialog(this) != DialogResult.OK)
      {
        return;
      }
    }
  }

  // get data
  try
  {
    using (var da = new OleDbDataAdapter(SelectStatement, ConnectionString))
    {
      // get data
      da.Fill(0, MAX_PREVIEW_RECORDS, dt);

      // show the data
      using (var dlg = new DataPreviewDialog(dt, Font, Size))
      {
        dlg.ShowDialog(this);
      }
    }
  }
  catch (Exception x)
  {
    Warning(Properties.Resources.ErrGettingData, x.Message);
  }
}

The first part of the code handles the case where the TreeView page is selected. It gets the name of the table that is currently selected, and uses the ParametersDialog helper class to prompt the user for any required parameters. The parameters entered by the user are stored as extended properties of the selected table.

Next, the code builds an OleDbDataAdapter to read the actual data. The parameters are given by the SelectStatement and ConnectionString properties. The SelectStatement is a SQL string that reflects the current user selection. It could be either the node currently selected on the TreeView, or the custom SQL generated with the QueryDesignerDialog.

This is the code that implements the SelectStatement property:

public string SelectStatement
{
  get
  {
    // table/view/sproc
    if (_tab.SelectedTab == _pgTables)
    {
      var nd = _treeTables.SelectedNode;
      return nd == null || nd.Tag == null || _schema == null
        ? string.Empty
        : OleDbSchema.GetSelectStatement(nd.Tag as DataTable);
    }
    else // explicit sql statement
    {
      return _txtSql.Text;
    }
  }
}

The implementation uses the GetSelectStatement method of the OleDbSchema class. This method returns a string that depends on the type of table passed as a parameter. If the table is a regular table or view, the method returns a Select statement. If the table represents a Stored Procedure, the method returns an exec statement including the name of the Stored Procedure and the parameter values stored as extended properties. In our case, the parameter values were set by the ParametersDialog helper used earlier.

Below the ToolStrip, there is a TabControl with two pages. The first contains a TreeView that lists all the tables, views, and Stored Procedures found in the database defined by the current connection string. Users may preview the data by double-clicking the tree nodes or by selecting a node and clicking the Preview button.

The second tab page contains a TextBox that contains the SQL statement generated by the QueryDesignerDialog. This TextBox is read/write, so users can cut, paste, or edit the SQL statement manually if they choose to do so.

Limitations

The main limitation in this initial version is the fact that it can generate new SQL statements, but it cannot edit existing ones. To overcome this limitation, the next version will need a SQL parser that will take an existing SQL string apart and generate the corresponding QueryField objects. This may be easy to do in some cases, but SQL is a rich and flexible language, so the task is not trivial and that is why it is not included here.

Another limitation is the fact that the whole implementation relies on OLEDB connection strings. This is not a serious limitation since OLEDB is a flexible data source, supporting SQL Server, Oracle, ODBC, Access (Jet), and many others. However, native implementations may be more efficient than the corresponding OLEDB version, and the SQL may require syntax adjustments. I have not looked into this very much at all, so if you have feedback in this area, I am very interested.

Finally, although the UI was loosely based on traditional tools like SQL Server Management Studio and Access, it deviates from that in the way tables and views are presented to the users. Personally, I like the approach used here, using a simple TreeView that is complete and easy to navigate, and configured by expanding and collapsing nodes with the mouse or keyboard. But I am sure many people will prefer the more traditional approach showing a pane with tables represented by floating lists and lines showing the connections between the tables. I am interested in your feedback in this area as well.

Conclusion

Thanks for your interest. I hope you enjoy the QueryDesignerDialog class, and would love to get your feedback.