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:
using (var dlg = new QueryDesignerDialog())
{
dlg.ConnectionString = ConnectionString;
if (dlg.ShowDialog(this) == DialogResult.OK)
{
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:
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:
void UpdateTableTree()
{
TreeNodeCollection nodes = _treeTables.Nodes;
nodes.Clear();
var ndTables = new TreeNode(Properties.Resources.Tables, 0, 0);
var ndViews = new TreeNode(Properties.Resources.Views, 1, 1);
if (Schema != null)
{
_treeTables.BeginUpdate();
foreach (DataTable dt in Schema.Tables)
{
var node = new TreeNode(dt.TableName);
node.Tag = dt;
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;
}
}
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);
}
}
ndTables.Expand();
_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.
void FixGridColumns()
{
for (int i = 0; i < _grid.Columns.Count; i++)
{
var col = _grid.Columns[i];
if (col.ValueType.IsEnum)
{
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;
_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:
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:
void _btnConnPicker_Click(object sender, EventArgs e)
{
_toolStrip.Capture = false;
string connString = _cmbConnString.Text;
if (string.IsNullOrEmpty(connString) ||
connString.IndexOf("provider=", StringComparison.OrdinalIgnoreCase) < 0)
{
connString = "Provider=SQLOLEDB.1;";
}
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:
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:
void PreviewData()
{
var dt = new DataTable("Query");
if (_tab.SelectedTab == _pgTables)
{
var table = _treeTables.SelectedNode.Tag as DataTable;
dt.TableName = table.TableName;
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;
}
}
}
try
{
using (var da = new OleDbDataAdapter(SelectStatement, ConnectionString))
{
da.Fill(0, MAX_PREVIEW_RECORDS, dt);
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
{
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
{
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.
Software Architect/Developer with several years experience creating and delivering software.
Full-stack Web development (including React, Firebase, TypeScript, HTML, CSS), Entity Framework, C#, MS SQL Server.
Passionate about new technologies and always keen to learn new things as well as improve on existing skills.