65.9K
CodeProject 正在变化。 阅读更多。
Home

可视化 SQL 查询设计器

Bernardo Castilho
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.94/5 (73投票s)

2009年10月20日

CPOL

15分钟阅读

viewsIcon

409472

downloadIcon

23264

本文描述了一个 QueryDesignerDialog 类的实现，该类允许用户根据给定的 OLEDB 连接字符串创建 SQL 查询。

Query Designer

引言

本文描述了一个 QueryDesignerDialog 类的实现，该类允许用户根据给定的 OLEDB 连接字符串创建 SQL 查询。

该设计器类似于 SQL Server Management Studio 和 Microsoft Access 等数据库工具中的设计器。它支持排序、分组和过滤，允许最终用户构建 SQL 查询。

QueryDesignerDialog 的主要限制是它不解析现有的 SQL 语句。这是一个单向工具；您可以使用它来创建新查询，但不能编辑现有查询。此外，它仅支持 OLEDB 数据源，包括 SQL Server 和 Access。未来的版本可能会解决这些限制。

背景

QueryDesignerDialog 的第一个版本是为报表设计器应用程序编写的。我找不到任何工具（免费或商业）能够按照我想要的方式完成这项工作，所以我决定自己编写。之后，我在其他几个应用程序中重新使用了它，并认为它对其他人也可能有用。

Using the Code

QueryDesignerDialog 有两个主要属性

  • ConnectionString：获取或设置用于从数据库架构（包括将用于构建查询的表、视图、字段和关系列表）中检索 OLEDB 连接字符串。
  • SelectStatement：获取用户设计的 SQL 语句。目前，这是一个只读属性。该对话框不能用于编辑现有 SQL 语句。也许未来版本会添加此功能。

下面的代码片段展示了 QueryDesignerDialog 的典型用法。您为其分配一个连接字符串，显示对话框，然后读取 SQL 查询。

// 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);
  }
}

实现

QueryDesignerDialog 依赖于两个重要的辅助类

  • OleDbSchema：此类扩展了 ADO.NET DataSet 类。它接受一个 OLEDB 连接字符串，并将 DataSet 填充所有在数据库中定义的表、视图、列、关系和约束（它不检索任何数据）。应用程序随后可以将这些元素公开在可视化 UI 中，以预览数据、创建和编辑查询等。OleDbSchema 类还提供实用方法，用于检查表类型、在必要时使用括号对表名进行编码、管理存储过程参数等。
  • QueryBuilder：此类使用 OleDbSchema 类，并维护一个具有定义排序、分组等属性的查询字段列表。它还负责根据查询字段和数据库架构构建 SQL 语句。

选择连接字符串

为了使用 QueryDesignerDialog，您需要一个 OLEDB 连接字符串。某些应用程序可能使用预定义连接字符串列表，而另一些则允许用户在运行时创建连接字符串。本文随附的示例属于第二类。

代码中包含的 OleDbConnString 类提供了处理连接字符串的实用工具。该类中的主要方法是 GetConnectionString 和 EditConnectionString，它们都显示用于创建或编辑连接字符串的 DataLinks 对话框。

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

这些方法依赖于项目必须引用的以下系统程序集

  • OLEDB32.dll：包含 DataLinks 类（以前在 MSDASC.DLL 中）。此文件位于 C:\Program Files\Common Files\System\Ole DB\OLEDB32.DLL。
  • ADODB.dll：这是从 DataLinks 返回 COM 对象所必需的。此文件位于 C:\Program Files\Microsoft.NET\Primary Interop Assemblies\ADODB.DLL。

一旦用户选择了连接字符串，大多数应用程序会将其保存在列表中以供将来重用。由于连接字符串通常很长，向用户显示它们可能会很棘手，因此 OleDbConnString 类提供了一个 TrimConnectionString 方法，该方法会缩短连接字符串以供显示，只保留提供程序和数据源部分。本文提供的示例应用程序使用 TrimConnectionString 在一个自定义绘制的组合框中显示最近使用的连接字符串。

检索数据库架构

当您将连接字符串分配给 QueryDesignerDialog 时，它会首先检索数据库架构，以便向用户显示可用于查询的表和视图列表。这项工作由前面提到的 OleDbSchema 类完成。代码如下所示

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

OleDbSchema 类扩展了 ADO.NET DataSet 类。您可以使用它来枚举数据库中可用的元素，包括表、视图、存储过程、字段、关系和约束。如果连接字符串无效，或在获取架构时发生任何错误，则 GetSchema 返回 null。

从数据库检索的一些信息存储在表和字段的 ExtendedProperties 属性中。例如，数据库中的视图和存储过程由 DataTable 对象表示，可以通过其 ExtendedProperties[TABLE_TYPE] 值来标识。OleDbSchema 类提供了处理这些的辅助方法，因此调用者不必这样做。例如，GetTableType 方法返回一个值，该值指示 OleDbSchema 中的 DataTable 是表示常规表、视图还是存储过程。

OleDbSchema 类的实现基于 OleDbConnection.GetOleDbSchemaTable 方法。此方法允许您检索连接定义的表、视图、存储过程、关系和约束。一旦有了表，就使用 OleDbDataAdapter.FillSchema 方法来检索字段。如果您对详细信息感兴趣，请参阅源代码。

下面的示例展示了 QueryDesignerDialog 类如何用数据库中的表和视图填充 TreeView 控件。

// 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;
  }
}

OleDbSchema 类由 QueryDesignerDialog 类在内部使用，但它是 public 的，也可以被需要访问架构的应用程序直接使用。本文随附的示例应用程序使用它来填充数据库中所有表、视图和存储过程的列表。

构建 SQL 查询

一旦数据库架构可用，我们就可以使用它来构建查询。这由 QueryBuilder 类完成，该类维护 QueryField 对象列表并生成 SQL 语句。QueryBuilder 类的主要属性是

  • ConnectionString：用于检索数据库架构。架构定义了查询中使用的表和字段，以及构建 SQL JOIN 语句所需的表之间的关系。
  • QueryFields：一个 QueryField 对象集合，用于构建查询。每个 QueryField 对象代表一个数据库字段或表达式，并具有定义排序、分组、别名等的属性。这是一个可绑定集合，适合显示在 DataGridView 控件中，例如。
  • Sql：构建的 SQL Select 语句，用于表示 QueryFields 集合的当前状态。

Sql 属性是只读的。它基于 QueryFields 集合分部分构建 SQL 查询。

首先，通过扫描字段并输出正确的表/视图和列名来构建 SELECT 子句。接下来，分析 QueryFields 集合以确定表如何连接。这使得 QueryBuilder 能够创建具有必需 JOIN 语句的 FROM 子句，这是该类中最复杂也是最有趣的部分。最后，根据 QueryField 对象的属性构建 ORDER BY 和 WHERE 子句。

为了构建 SQL 语句的 FROM 子句，QueryBuilder 类首先构建一个表列表，使得列表中的每个表都与下一个表相关联。这是通过 InsertRelatedTable 方法实现的。接下来，它扫描列表以查找连接每个表与下一个表的关联。然后使用每个关联来构建相应的 JOIN 语句。

QueryField 类包含定义查询中每个字段的信息。它具有以下属性

  • Column：指定表内列名（例如，“FirstName”）或表达式（例如，“LEFT(FirstName, 2)”）的字符串。
  • Alias：一个可选字符串，用于标识字段而不是 Column 值。如果提供了此值，则该值将用作查询结果表中的列名。例如，如果 Column 属性设置为“LEFT(FirstName, 1)”并且 Alias 属性设置为“FirstInitial”，则输出表将包含一个名为“FirstInitial”的列。
  • Table：标识包含该列的表的字符串。此属性是只读的；它仅用于绑定目的（以便表名出现在后面描述的字段网格中）。
  • Output：一个布尔值，用于确定字段是否应包含在输出表中。这用于隐藏需要定义查询但又不应出现在输出中的字段（例如，用于计算或提供相关表之间连接的字段）。
  • GroupBy：分组字段时使用的聚合。仅当 QueryBuilder.GroupBy 属性设置为 true 时，才使用此列。在这种情况下，原始数据记录将分组并由聚合（如总和或平均值）表示。例如，要创建显示每个类别的平均产品价格的查询，您将使用两个字段：“CategoryName”，其 GroupBy 设置为 GroupBy；“ProductPrice”，其 GroupBy 设置为 Average。
  • Sort：指定是否应使用该字段按升序或降序对输出进行排序。
  • Filter：用于选择包含在输出中的记录的筛选表达式。如果提供，表达式应为 [OPERATOR] [VALUE] 或 BETWEEN [VALUE1] AND [VALUE2] 格式。为了减少指定 Filter 值时出现语法错误的可能性，QueryDesignerDialog 类为此属性提供了一个编辑器，而不是允许用户直接键入表达式。

设计 SQL 查询

QueryDesignerDialog 类为用户提供创建查询的用户界面。它使用前面描述的 QueryBuilder 类，并添加以下 UI 元素

  • 表/视图树：这是一个 TreeView 控件，包含数据源中的所有表和视图以及每个表/视图中的所有字段。可以通过双击或将字段拖到 QueryField 网格中来将其添加到查询中。TreeView 具有上下文菜单，允许用户删除特定表/视图，或导航到与当前选定表相关的表。
  • QueryField 网格：这是一个 DataGridView 控件，显示当前包含在查询中的字段及其属性。用户可以通过拖动网格行来重新排序字段，通过删除行来删除字段，以及直接在网格上编辑字段。
  • SQL 视图：这是一个 TextBox 控件，在用户在网格上添加和编辑各个字段时显示 SQL 语句。TextBox 是只读的。用户可以看到 SQL 语句并将其复制到剪贴板，但他们不能通过在 TextBox 中键入来更改它。
  • ToolStrip：对话框顶部的 ToolStrip 包含用于切换查询的 GroupBy 属性、编辑查询属性、检查 SQL 语法、预览查询结果以及清除查询的按钮。

表/视图树是与 SQL Server Management Studio 和 Microsoft Access 等工具中传统的查询设计工具最不同的元素。它默认显示所有表，而不是要求用户单独添加和删除它们。表和视图显示为树节点，而不是小浮动列表。基于树的 UI 明显更简单。它有一个主要的缺点是用户无法立即看到表之间的连接。通过按需显示相关表的上下文菜单，可以在一定程度上缓解这个问题。

下图显示了 QueryDesignerDialog 在处理流行的 AdventureWorks 数据库时创建查询的示例。

Query Designer

QueryField 网格实现

QueryField 网格是一个 DataGridView，用于显示 QueryField 对象列表。默认情况下，DataGridView 使用文本框来编辑非布尔单元格，并使用复选框来处理布尔值。这对于编辑枚举值（如 QueryField 类的 Sort 和 GroupBy 属性）并不理想。使用组合框控件编辑这些值要容易得多。此外，我们希望为 Filter 字段使用自定义编辑器，该字段是 string 类型，但其格式要求最好由自定义编辑器处理。

这些需求似乎非常普遍，因此我认为在这里展示代码可能会对一些开发人员有所帮助。下面的代码展示了如何用组合框列替换常规 DataGridView 列，以处理枚举字段。

// 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);
    }
  }
}

该示例还将“Filter”列替换为显示按钮而不是文本框的列。单击按钮会弹出筛选器编辑器对话框，可用于编辑筛选器值。我宁愿给用户一个选择，允许他们直接在单元格中键入筛选值或单击单元格右侧的按钮来显示编辑器对话框。这在将来的版本中已列入待办事项。

示例应用

本文随附的示例应用程序是一个对话框，允许用户选择连接字符串、查看相应数据库中的所有表、视图和存储过程、创建数据库查询以及查看相应的Data。它可以作为报表设计器等应用程序中的数据源选择工具（实际上，这就是最初编写它的原因）。

下图显示了示例应用程序的运行情况。

Sample Application

应用程序的主对话框顶部有一个 ToolStrip。

ToolStrip 包含一个组合框，提供最近使用的连接字符串列表，并允许用户键入或粘贴连接字符串值。

组合框的下拉部分是自定义绘制的，以显示连接字符串的修剪版本，这比完整版本更容易阅读。自定义绘制代码使用 OleDbConnString 类中的 TrimConnectionString 方法，如下所示。

// 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();
}

最近连接字符串列表作为应用程序设置保存，以便在会话之间重用。

组合框旁边的按钮允许用户使用熟悉的“DataLink”对话框创建新的连接字符串。该按钮使用 OleDbConnString 类中的 EditConnectionString 方法，如下所示。

// 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);
}

此代码调用下图所示的“DataLink”对话框。

DataLinks

下一个按钮（带有魔杖图像）调用 QueryDesignerDialog，允许用户设计 SQL 查询。查询准备好后，它显示在主窗体的第二个选项卡上的 TextBox 中。调用 QueryDesignerDialog 的代码如下所示。

// 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();
    }
  }
}

代码创建一个 QueryDesignerDialog，初始化其 ConnectionString 属性，然后显示对话框并通过读取对话框的 SelectStatement 属性来检索结果。

最后一个按钮（带有预览图像）将数据从当前选定的源（表、视图、存储过程或 SQL 语句）加载到 DataTable 中，并在模态对话框中显示该表。实现如下。

// 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);
  }
}

代码的第一部分处理选择了 TreeView 页面的情况。它获取当前选定表的名称，并使用 ParametersDialog 帮助类提示用户输入任何必需的参数。用户输入的参数作为选定表的扩展属性存储。

接下来，代码构建一个 OleDbDataAdapter 来读取实际数据。参数由 SelectStatement 和 ConnectionString 属性提供。SelectStatement 是一个反映用户当前选择的 SQL 字符串。它可以是 TreeView 上当前选定的节点，也可以是使用 QueryDesignerDialog 生成的自定义 SQL。

这是实现 SelectStatement 属性的代码。

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;
    }
  }
}

该实现使用 OleDbSchema 类的 GetSelectStatement 方法。此方法返回一个字符串，该字符串取决于作为参数传递的表的类型。如果表是常规表或视图，则该方法返回一个 Select 语句。如果表代表存储过程，则该方法返回一个 exec 语句，其中包括存储过程的名称以及作为扩展属性存储的参数值。在这种情况下，参数值由前面使用的 ParametersDialog 帮助程序设置。

在 ToolStrip 下方，有一个包含两个页面的 TabControl。第一个包含一个 TreeView，列出当前连接字符串定义的数据库中找到的所有表、视图和存储过程。用户可以通过双击树节点或选择一个节点并单击“预览”按钮来预览数据。

第二个选项卡页面包含一个 TextBox，其中包含 QueryDesignerDialog 生成的 SQL 语句。此 TextBox 是读/写的，因此用户可以选择手动剪切、粘贴或编辑 SQL 语句。

限制

此初始版本的主要限制是它能够生成新的 SQL 语句，但不能编辑现有语句。要克服此限制，下一个版本将需要一个 SQL 解析器，该解析器可以解析现有的 SQL 字符串并生成相应的 QueryField 对象。在某些情况下，这可能很容易做到，但 SQL 是一种丰富且灵活的语言，因此这项任务并不简单，这也是它未包含在本文中的原因。

另一个限制是整个实现依赖于 OLEDB 连接字符串。这不是一个严重的限制，因为 OLEDB 是一个灵活的数据源，支持 SQL Server、Oracle、ODBC、Access (Jet) 等。但是，本机实现可能比相应的 OLEDB 版本更有效，并且 SQL 可能需要语法调整。我对此的了解不多，所以如果您在此方面有反馈，我非常感兴趣。

最后，虽然 UI 在很大程度上基于 SQL Server Management Studio 和 Access 等传统工具，但在向用户呈现表和视图的方式上有所不同。我个人喜欢这里使用的方法，即使用一个简单、完整且易于导航的 TreeView，通过鼠标或键盘扩展和折叠节点进行配置。但我相信很多人会更喜欢传统的显示一个包含由浮动列表表示的表以及显示表之间连接的线的窗格的方法。我对此领域也很感兴趣。

结论

感谢您的关注。我希望您喜欢 QueryDesignerDialog 类，并乐意收到您的反馈。

© . All rights reserved.