可以查看存储的过程为基础的SqlCommand的T-SQL语法？ [英] Possible to view T-SQL syntax of a stored proc-based SqlCommand?

问题描述

我想知道是否有人知道的一种方式来获取实际的T-SQL是通过一个SqlCommand对象来执行（与StoredProcedure时的的CommandType）执行之前，它...

I was wondering if anybody knows of a way to retrieve the actual T-SQL that is to be executed by a SqlCommand object (with a CommandType of StoredProcedure) before it executes...

我的方案涉及可选节电DB操作的文件或MSMQ是实际执行的命令。我的假设是，如果你创建像下面的一个SqlCommand：

My scenario involves optionally saving DB operations to a file or MSMQ before the command is actually executed. My assumption is that if you create a SqlCommand like the following:

Using oCommand As New SqlCommand("sp_Foo")
    oCommand.CommandType = CommandType.StoredProcedure
    oCommand.Parameters.Add(New SqlParameter("@Param1", "value1"))
    oCommand.ExecuteNonQuery()
End Using

据蜿蜒执行一些T-SQL这样的：

It winds up executing some T-SQL like:

EXEC sp_Foo @Param1 = 'value1'

时的假设是正确的？如果是这样，是否有可能以某种方式获取实际的T-SQL？我的目标是获得解析，有效性等使用SqlCommand类，因为我要去反正用它的好处。这可能吗？我要对这个错误的方式？在此先感谢您的任何输入！

Is that assumption correct? If so, is it possible to retrieve that actual T-SQL somehow? My goal here is to get the parsing, validation, etc. benefits of using the SqlCommand class since I'm going to be using it anyway. Is this possible? Am I going about this the wrong way? Thanks in advance for any input!

推荐答案

您正试图将其参数的记录SP呼叫友好的形式？或者你想保存这个文本来运行它作为脚本以后呢？

You're trying to the log the SP call with its parameters in a friendly form? Or you want to save this text to run it as a script later?

我觉得你的运气了在第二种情况下，因为，我不认为SqlCommand的，当你在CommandType.StoredProcedure模式下使用它与SQL调用的SP。如果您在SQL Server探查一下与SP的sp_whatever和CommandType.Text用EXEC sp_whateverCommandType.StoredProcedure之间的区别，这是一个不同的接口 - 的RPC接口。我总是在IM pression ADO.NET从未使在第一种情况下SQL字符串。这就是为什么你永远不必担心注射在这一点上，因为参数总是发送带外 - 即使你已经CommandType.Text一个参数化查询

I think you're out of luck in the second case, because, I don't think SQLCommand calls SPs with SQL when you use it in CommandType.StoredProcedure mode. If you look in the SQL Server profiler at the difference between CommandType.StoredProcedure with an SP "sp_whatever" and CommandType.Text with "EXEC sp_whatever", it's a different interface - the RPC interface. I was always under the impression ADO.NET was never making a SQL string in the first case. That's why you never have to worry about injection at that point, because parameters are always sent out of band - even if you have CommandType.Text with a parametrized query.

请参阅此博客文章。

如果你正在尝试登录的操作，我想你可以通过你的SqlCommand对象，其中提取命令文本，所有的参数，并把它们用于记录一个很好的组织文本形式一些通用的对象/方法，你写的。

If you are trying to log operations, I guess you could pass your SQLCommand object to some generic object/method you write which extracts the command text and all the parameters and puts them in a nicely organized text form for logging.

这篇关于可以查看存储的过程为基础的SqlCommand的T-SQL语法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！