C#实现SQL查询分析器(C# Visual SQL Query Designer)|C/S框架网

C#实现SQL查询分析器(C# Visual SQL Query Designer)

作者:C/S框架网发布日期:2011/06/16 22:34:44

C#实现SQL查询分析器(C# Visual SQL Query Designer)

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.

贴图图片

介绍

本文描述了QueryDesignerDialog类的实现, 通过OLEDB连接服务器后允许用户创建SQL查询语句.
该工具类似SQL Server Management Studio和Microsoft Access中的设计器. 甚至允许用户建立支持排序，分组和过滤的SQL语句.
QueryDesignerDialog是一个单向的工具, 主要限制是该类不能分析现有的SQL语句。可以创建新的SQL查询但不能修改现有的SQL查询。
另外，它仅支持通过OLEDB连接的SQL Server和Access数据库.将来的版本会解决这些问题.

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.

背景

QueryDesignerDialog的第一个版本是为报表设计器应用程序(Report Designer application)开发的。我找不到一个能实现上述功能的工具(免费或商业化).
所以我决定自己开发，在此之后，该工具在其它程序中也有些应用，我希望它能帮助一些人.

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:

使用方法

QueryDesignerDialog 有两个主要属性:
ConnectionString: 获取或设置OLEDB连接字符串，通过建立的SQL用来检索的表，视图，字段，关联的数据结构.
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);