如何检查我的查询是错还是错？ [英] How can I check my query is it wright or wrong ?

问题描述

我正在尝试编写一个名为QueryChecker的函数。



函数的目的是通过MS SQL服务器检查查询。

以下是我的代码示例：

I am trying to write a function named QueryChecker.

The purpose of function is to check the query through MS SQL server.
Here is example of my code:

string expectedQuery = GenrerateQuery();

SqlDataAdapter ndaGlobalClass = new SqlDataAdapter(expectedQuery, cn2);

一般来说，expectedQuery是通过一种方法生成的。我想测试expectQuery是否正确，但我不想在sql server中执行它。是否可能？



如果查询不正确，则显示SQL服务器指示的相应错误消息。

In general expectedQuery is generated through a method. I want to test the expectedQuery is correct or not but i don't want to execute it in the sql server. Is it possible ?

If the query is not correct then shows the appropriate wrong message that is instructed by SQL server.

推荐答案

有可能，请阅读以下文章



SQL SERVER - 如何验证语法而不执行语句 - 未经探索的调试提示 [ ^ ]

It is possible, read below article

SQL SERVER – How to Validate Syntax and Not Execute Statement – An Unexplored Debugging Tip[^]

不太实用，我很害怕。

问题是如果表或任何列名错误，以及语法错误，正确的SQL查询可能会失败 - 所以你的检查出来方法必须正确理解SQL语法，并且能够解析和检查它，并且还知道表及其布局，包括数据类型。否则这个有效的SQL查询：

Not really practical, I'm afraid.
The trouble is that a "correct" SQL query can fail if the table or any of the column names is wrong, as well as if the syntax is wrong - so your "check it out" method would have to understand SQL syntax properly, and be capable of parsing and checking it, and also know the tables and their layouts including datatypes. Otherwise this valid SQL query:

UPDATE MyTable SET MyCol=@VAL

如果出现以下情况仍可能失败：

1）没有MyTable表

2）没有 MyTable中的MyCol列

3）您未能提供@VAL参数

3）您为参数证明的数据类型与列不匹配。例如，如果列是DATETIME并且您提供包含Hello！的字符串。



您可能会这样做，但这是一项大量的工作，并且不太可能特别有帮助。

could still fail if:
1) There is no "MyTable" table
2) There is no "MyCol" column in "MyTable"
3) You fail to provide the "@VAL" parameter
3) The datatype that you prove for the parameter does not match the column. For example, if the column is DATETIME and your provide a string containing "Hello!".

You could probably do it, but it's an enormous amount of work, and unlikely to be particularly helpful.

您可以尝试在最后放弃的SqlTransaction中执行查询。

这应该是这样的：

You could try to execute your query in a SqlTransaction that you will discard at the end.
This should give something like:

string expectedQuery = GenerateQuery();
using (SqlConnection con = GenerateSqlConnection()) {
   con.Open();
   using (SqlTransaction tran = con.BeginTransaction("TestTransaction")) 
   using (SqlCommand cmd = new SqlCommand(expectedQuery, con)) {
      cmd.Transaction = tran;
      try {
         cmd.ExecuteNonQuery();
      }
      catch (Exception ex) {
         // If you get here there was an issue with your query
      }
      finally {
         tran.RollBack();
      }
   }
}

希望这会有所帮助。

Hope this helps.

这篇关于如何检查我的查询是错还是错？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！