解析 T-SQL 语句
我正在为我的应用程序制作一个 SQL 脚本编辑器/执行器组件，本质上与 SQL Server Management Studio 或旧的查询分析器非常相似。我正在使用 C#、.NET Framework 3.5 SP1。我的问题是我想让组件解析 SQL 以确定是否应该使用 SqlCommand.ExecuteNonQuery()、SqlCommand.ExecuteScalar() 或 SqlDataAdapter.Fill(dataTable) 来运行脚本。目的是允许用户执行非查询，但也返回结果集供用户查看，就像 SSMS/查询分析器所做的那样，但也向他们隐藏 ADO.NET 实现细节。
我通常会默认使用 SqlDataAdapter.Fill(dataTable) 并始终返回 DataTable 供用户查看。这仍然会像 ExecuteNonQuery 那样运行非查询，但它会产生使用较慢的 SqlDataAdapter 类来运行的开销。这在许多非查询情况下并不是最佳的。
我采取的方法是允许用户选择结果类型，“Table”（将运行 SqlDataAdapter.Fill(...)、“Value”（将运行 ExecuteScalar）或“None”（将运行将运行 ExecuteNonQuery），但我不希望他们了解 ADO.NET 实现细节，我希望他们只需在文本框中键入 SQL 脚本，单击“执行”按钮，程序就会运行该脚本并执行。如果存在，则返回数据网格中的结果集。
所以...我需要组件能够在执行 SQL 之前对其进行解析，以辨别运行脚本是否会产生结果集。
I am making a SQL script editor / executor component for my application, very similar in nature to the SQL Server Management Studio or ye old Query Analyzer. I'm using C#, .NET Framework 3.5 SP1. My issue is that I would like to have the component parse the SQL to determine whether it should use SqlCommand.ExecuteNonQuery(), SqlCommand.ExecuteScalar(), or SqlDataAdapter.Fill(dataTable) to run the script. The purpose is to allow the user to execute non-queries but also return result sets for the user to view, just like the SSMS / Query Analyzer would do, but also hide the ADO.NET implementation details from them.
I would normally default to SqlDataAdapter.Fill(dataTable) and always return the DataTable for the user to view. This would still run non-queries as ExecuteNonQuery would do, but it has the overhead of using the slower SqlDataAdapter class to run things. This is not optimal in many non-query situations.
The approach I've taken is to allow the user to select the result type, either 'Table' (which will run SqlDataAdapter.Fill(...), 'Value' (which will run ExecuteScalar), or 'None' (which will run ExecuteNonQuery). But I don't want them to be exposed to the ADO.NET implementation details. I want them to just type a SQL script in a text box, hit an Execute button, and the program will run the script and return a result set in a datagrid if there is one.
So... I need the component to be able to parse the SQL before executing it, to discern whether there will be a result set from running the script or not.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您不会解析文本，因为您在客户端中没有必要的信息来确定请求是否会返回结果。接受像
execute usp_anonymousProcedure 1,2;这样的请求，你能判断它是否返回结果吗？显然不是。因此，您将请求发送到服务器并要求它返回有关结果的元数据：您将其设置为打开，在服务器上运行 request 您的请求，然后您就会知道请求返回的结果的形状。
Yo don't parse the text, since you don't have the information necessary in the client to determine if the requests would return a result. Take a requests like
execute usp_anonymousProcedure 1,2;, can you tell if it returns a result or not? Obviously, not. So you send the request to the server and ask it to just return the metadata about the result:You set this on, run request your request on the server, then you'll know the shape of the result returned by the request.
为什么不直接使用 ExecuteReader 执行 SQL？
IDataReader 将包含零个或多个结果集。
您可以迭代结果集，并使用 DataTable.Load(IDataReader) 将每个结果集加载到 DataTable 中 - 或者仅使用 IDataReader/IDataRecord 成员来获取列名称和数据。
我不知道通过提前了解结果集的形状可以获得什么优势，而且我很确定现有的脚本执行器（例如查询分析器）不会这样做。
Why not just execute the SQL using ExecuteReader?
The IDataReader will contain zero or more result sets.
You can iterate through the result sets, and load each one into a DataTable using DataTable.Load(IDataReader) - or just use the IDataReader/IDataRecord members to get the column names and data.
I don't see what advantage you gain by knowing the shape of the result set(s) in advance, and I'm pretty sure existing script executors like Query Analyzer don't do this.