解析Transact SQL以检查语法

寒冰屋

于 2022-06-06 22:45:00 发布

阅读量225

点赞数

分类专栏： CSharp.NET ASP.NET CORE 数据库文章标签： sql wpf c#

原文链接：https://www.codeproject.com/Articles/410081/Parse-Transact-SQL-to-Check-Syntax

版权

CSharp.NET 同时被 3 个专栏收录

1239 篇文章 58 订阅

订阅专栏

ASP.NET CORE

1124 篇文章 54 订阅

订阅专栏

数据库

228 篇文章 13 订阅

订阅专栏

编辑介绍

背景

使用代码

属性FireInfoMessageEventOnUserErrors和事件InfoMessage

介绍

我将在本文中解决的问题是如何将一些Transact-SQL文本传递给指定的SQL Server实例，并要求它解析代码，返回任何语法错误。这在您的应用程序允许用户输入一些T-SQL文本以执行，或者T-SQL从脚本文件动态执行的情况下非常有用，等等。可能性是无穷无尽的，请记住，如果本文启发您实现这样的设计，这可能带来的安全隐患。

例如，假设您有一个系统，它允许一个用户（当然具有特殊访问权限）编写T-SQL代码并以脚本的形式（在数据库或文件中）将其存储在系统中。然后，系统的另一个用户会进来并根据程序员提供的名称和描述选择其中一个脚本，然后单击一个按钮来执行它。显然，在允许将代码存储在系统上之前，您需要一些机制来检查代码的有效性。这就是我的解决方案希望有用的地方。

背景

我做了一些研究，并决定介绍一些关于SQL Server内部工作原理的背景知识，或者任何其他DBMS的相关背景知识。那么，当您的应用程序对数据库执行查询时，究竟会发生什么呢？DBMS是否遵循特定的流程来返回请求的数据，或者更新或删除数据子集？在您首选的DBMS的底层发生的事情是相当复杂的，我只会在高层次上解释或提及关键流程。

SQL Server被拆分成多个组件，这些组件中的大部分被组合起来形成关系引擎和存储引擎。关系引擎负责接收、检查和编译代码，以及管理执行过程，而存储引擎负责检索、插入、更新或删除数据库文件中的底层数据。我想接触的组件是查询处理器，它是关系引擎的一部分。

顾名思义，查询处理器的工作是在服务器执行之前准备提交的SQL语句。查询处理器在提供执行计划之前将经过三个过程。此执行计划是DBMS为查询提供服务而选择的最佳路径。提到的三个过程包括：

解析和规范化
编译
优化

解析器检查语法错误，包括关键字的正确拼写。规范器执行绑定，包括检查指定的表和列是否存在，收集有关指定表和列的元数据，并执行一些语法优化。程序员经常使用术语编译来指代编译和优化过程。真正的编译只影响特殊的T-SQL语句，如变量声明和赋值、循环、条件处理等。这些语句为SQL代码提供功能，但它们不构成DML语句的一部分，如SELECT、或INSERTUPDATEDELETE。另一方面，只有这些DML语句需要优化。到目前为止，优化是查询处理器中最复杂的过程。它采用一系列算法首先收集合适的执行计划样本，然后过滤它们，直到选择最佳候选者。

在查询处理器确定并返回最优执行计划后，将其存储在缓存中。SQL Server将自动确定将此执行计划保留在缓存中的时间，因为它可能会经常被重用。当应用程序执行查询时，SQL Server会检查查询的缓存中是否存在执行计划。SQL Server根据查询文本生成缓存键，并在缓存中搜索相同的键。当列定义或索引等元数据发生变化时，查询需要重新编译和重新优化，而不是参数、系统内存、数据缓存中的数据等发生变化。

最后，查询处理器将执行计划传达给存储引擎并执行查询。

使用代码

我已经创建并包含了一个简单的代码编辑器应用程序，但请记住，本文的主要目的是为您提供解析功能，此处仅介绍围绕这一点的代码片段和注释。有很多非常有用的文章可用于构建WPF应用程序。我将假设您对Visual Studio和C#有一定的经验。我已将用Visual C# Express 2010编写的示例应用程序作为WPF应用程序包含在内。不需要WPF知识，因为我将详细解释相关的C#代码。

本质上，我希望我的应用程序有一个执行按钮和一个解析按钮（就像在MS SQL Server Management Studio中一样）。按下Execute按钮，SQL Server将按照上面描述的整个过程，首先准备语句，然后确定执行计划，然后再执行。对于解析按钮，自然它应该只解析查询。我将创建一个类来封装我的所有ADO.NET对象，并提供方法Execute和Parse以将功能连接到按钮。我还将提供使用指定连接字符串连接和断开SQL Server实例的方法。该类称为SqlHandler。

该类封装并隐藏了以下对象：

SqlConnection conn
SqlCommand cmd
SqlDataAdapter adapter
List<SqlError> errors

我相信您知道，您需要将using System.Data.SqlClient;和using System.Data;包括到代码文件顶部的using指令列表中。该conn对象用于连接到数据库。ConnectionString属性直接获取和设置conn.ConnectionString属性。这允许您从类外部获取或设置连接字符串。cmd对象用于执行命令，适配器用于从数据库中获取查询结果。该errors对象是SqlError类型的通用列表。此列表将用于捕获和返回在执行或解析T-SQL代码时生成的错误。

属性FireInfoMessageEventOnUserErrors和事件InfoMessage

阅读本文的大多数人已经熟悉这些ADO.NET类。大多数时候，我都在使用ADO.NET开发应用程序；我只使用了几个选定的属性和方法。该SqlConnection类包含一个不太知名且不太常用的FireInfoMessageEventOnUserErrors属性和InfoMessage事件（至少在我看来）。我必须自己通过挖掘对象来发现它们，因为我找不到相关的文章来解释如何完成我想要的。最终，通过反复试验，我得到了一个可行的解决方案。

FireInfoMessageEventOnUserErrors是一个布尔属性。当设置为false（默认）时，发生错误时不会触发该InfoMessage事件，并且ADO.NET API将引发异常。当设置为true时，不会抛出异常，但会触发InfoMessage事件。为了使我的代码正常工作，我必须启用此事件以通过SqlInfoMessageEventArgs事件参数对象捕获所有消息。以下代码片段显示了如何在构造函数中设置此属性和事件：

conn.FireInfoMessageEventOnUserErrors = true;
conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);

conn_InfoMessage是事件触发时将调用的事件处理程序方法的名称。需要注意的是，虽然这看起来像一个异步操作，但它实际上是同步的。这意味着当通过将T-SQL查询传递给cmd.ExecuteNonQuery或adapter.Fill来执行T-SQL查询时，将在继续执行之前触发该事件。这允许我们在从调用ExecuteNonQuery和Fill的类的Execute和Parse方法返回之前将所有消息吸到列表errors中。下面的片段描述了如何在事件处理程序中捕获消息。

private void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
	//ensure that all errors are caught
	SqlError[] errorsFound = new SqlError[e.Errors.Count];
	e.Errors.CopyTo(errorsFound, 0);
	errors.AddRange(errorsFound);
}

值得一提的是，对于T-SQL脚本可能包含的每个错误都会触发该事件。例如，如果您的脚本包含两个错误，则conn_InfoMessage事件处理程序将被调用两次！我只是在测试我的应用程序时才发现这一点，我试图解析一个包含多个错误的脚本。最初的结果是我的Parse方法总是只返回一个错误，而SSMS为同一脚本报告了正确数量的错误。只有当我在事件处理程序中插入一个消息框时，我才发现它是如何工作的。之所以会产生误导，是因为我们的事件处理程序的第二个参数，即类型为SqlInfoMessageEventArgs对象的e对象有一个Errors属性。这个属性是类型SqlErrorCollection的，对我来说暗示它包含多个SqlError对象。自然地，我假设这个集合将同时包含所有错误。经过一些代码修改，我得到了想要的结果。现在发生的情况是，每次触发事件时，都会创建一个SqlError数组，并将对象e.Errors集合复制到该SqlError数组中。尽管每次我测试我的代码时这个集合只包含一个项目，但为了安全起见，我确保所有SqlError对象都被捕获。然后将整个数组复制到errors列表中，这是我的类定义中的一个private字段。此列表用于在将所有错误返回给客户端代码之前汇总所有错误。还有一点值得一提的是，每次调用Parse或Execute调用时都要清空errors列表。

执行方法

该方法的第一个参数，sqlText包含要执行的T-SQL代码。第二个参数是一个SqlError数组对象。注意out关键字。这意味着参数是一个out参数，我们必须在方法中的某处设置它的值。这允许该方法返回一个DataTable对象（通过正常的返回类型和return语句）和一个包含我们的SqlError对象的数组。客户端代码将负责检查数组的长度以确定是否生成了任何错误。

public DataTable Execute(string sqlText, out SqlError[] errorsArray)
{
	if (!IsConnected)
		throw new InvalidOperationException
        ("Can not execute Sql query while the connection is closed!");

	errors.Clear();
	cmd.CommandText = sqlText;
	DataTable tbl = new DataTable();
	adapter.Fill(tbl);
	errorsArray = errors.ToArray();
	return tbl;
}

首先，我们需要使用该IsConnected属性测试连接是否打开，如果没有则抛出异常。接下来，清除errors列表以防止报告以前遇到的错误。然后使用adapter.Fill(tbl)执行查询，其中tbl是对新DataTable对象的引用。如果T-SQL代码返回任何数据，则该表将填充数据。如前所述，InfoMessage事件会同步引发，因此调用Fill后的下一行只有在通过事件引发所有错误后才会执行。所有错误（如果有）都被复制到一个新的SqlError对象数组中。这个数组被赋值给out参数errorsArray，允许我们类的客户端检查是否遇到任何错误。请记住，当您设置FireInfoMessageEventOnUserErrors为true时没有异常被抛出。

Parse方法

此方法接受一个参数sqlText，其中包含要解析的T-SQL代码。它返回一个包含SqlError对象的数组。客户端代码应测试此数组的长度以确定是否生成了任何错误。

public SqlError[] Parse(string sqlText)
{
	if (!IsConnected)
		throw new InvalidOperationException
        ("Can not parse Sql query while the connection is closed!");

	errors.Clear();
	cmd.CommandText = "SET PARSEONLY ON";
	cmd.ExecuteNonQuery();

	cmd.CommandText = sqlText;
	cmd.ExecuteNonQuery(); //conn_InfoMessage is invoked for every error, 
                           //e.g. 2 times for 2 errors
	
	cmd.CommandText = "SET PARSEONLY OFF";
	cmd.ExecuteNonQuery();

	return errors.ToArray();
}

同样，如果连接未打开，我们将抛出异常，并清除错误列表。SQL Server有一个选项“PARSEONLY”，我们将使用它来防止在解析阶段之后进一步处理我们的T-SQL代码。在执行我们的sqlText字符串之前，该PARSEONLY选项设置为ON。之后，它被设置回OFF。这里有一个潜在的陷阱：如果客户端代码是控制台类型的应用程序，并且用户执行了命令SET PARSEONLY ON以明确阻止在解析阶段之后的进一步执行，该怎么办。当客户端代码再调用该Parse方法时，PARSEONLY将被设置回OFF在方法返回之前，在用户不知情的情况下。本文不会进一步探讨此问题的解决方法，因为实施会根据项目的要求而有所不同。

关于类的附加说明

我们SqlHandler类的ConnectionString属性“转发”了它封装的SqlConnection对象的ConnectionString属性。在构造函数中，ConnectionString初始化为“模板”连接字符串。您必须在字符串中手动插入Data Source和Initial Catalog值。该Connect方法接受一个包含连接字符串的string参数。此连接字符串将替换SqlConnection对象上的现有连接字符串。

示例应用程序

我的示例项目包含该SqlHandler类和一个小型测试应用程序。该应用程序提供了一些基本的文本编辑器功能，例如打开文件、保存文件、剪切、复制和粘贴。此外，它实现了SqlHandler对象的方法来启用与SQL Server实例的连接和断开连接，以及执行和解析SQL代码。主窗口的布局设计得看起来很熟悉，顶部是菜单和工具栏，中间是文本区域，底部是错误网格和状态栏。当您构建并运行应用程序时，将弹出一个连接对话框窗口。在此窗口中，您必须输入有效的连接字符串才能连接到SQL Server实例。请记住，此应用程序不是多线程的。因此，输入错误的连接字符串将导致接口在连接超时时“挂起”并最终返回错误消息。

我在SqlHandler类中创建了一个区域，用于容纳自定义RoutedUICommand对象，以便将我自己的命令绑定到用户界面。我把它们放在单独的区域因为它们和类上的其他部分无关。这些命令对象都是static，该类还定义了一个static构造函数来初始化它们。这些命令也可以放在一个单独的类中。

在窗口中间的文本区域中键入您的T-SQL文本。要解析代码，请按Parse按钮，或按键盘上的F6键。要执行代码，请按执行按钮，或按键盘上的F5 。Parse和Execute函数都将在应用程序底部的错误网格中报告错误。错误网格嵌套在扩展器中，生成错误时会自动弹出。当您执行返回结果集的查询时，将出现一个结果查看器窗口。如命令绑定所定义，当应用程序未连接到SQL Server实例时，将禁用解析和执行。