在sp_executesql语句中显式替换参数的快速方法

Question

我正在C#中编写一个程序，该程序使用传递给sp_executesql的参数运行一些select语句。测试时遇到的一个问题是，无论是从SQL事件探查器还是从Visual Studio中的监视中获取执行的命令，参数的值都是在语句末尾指定的，而不是在查询中显式指定的。出于测试目的，我想要一种用参数值替换参数的快速方法。

所以，不是：

exec sp_executesql N'
SELECT CustomerName
FROM CustomerTable ct WITH(NOLOCK)
WHERE ct.CustomerId <> @CustomerId
AND ct.ItemId <> @ItemId
AND ct.TransactionId = @TransactionId'
,N'@CustomerId bigint,@ItemId nvarchar(1),@TransactionId nvarchar(30), @CustomerId = 3000, @ItemId = N'4', @TransactionId=N'43281'

我想要：

exec sp_executesql N'
SELECT CustomerName
FROM CustomerTable ct WITH(NOLOCK)
WHERE ct.CustomerId = 3000
AND ct.ItemId <> N'4'
AND ct.TransactionId = N'43281''

请不要太注意这个例子的语法，因为它只是用来演示这个概念的。有人知道快速的方法吗？基本上，我希望将其替换为测试目的，因为这将使我更容易修改条件，以测试它们如何影响返回的结果。我将感谢任何人能提供的帮助。谢谢

Answer 1

参数化sp_executesql有很多好处，包括

• 通过显式参数化，您为SQL提供了在确定类型上缓存像样的查询计划的机会
• 通过参数化，它有助于防止像SQL注入攻击这样的恶意攻击，但也避免了对有问题的字符进行转义的需要

因此，即使您设法"取消参数化"生成的sp_executesql，如果您执行内联sql，查询计划也可能与参数化版本有很大不同，并且您还需要执行转义等操作（即，它不适合进行apples与apples的测试）。

我能想到为什么您不想要参数化sp_executesql的唯一原因是为了易读性？

编辑：尝试替代将取决于您使用的技术

正如@mellamokb所建议的，如果你使用的是ExecuteReader，这可能是一个非常简单的

假设你的代码类似

string sqlCmd = "SELECT CustomerName 
FROM CustomerTable ct WITH(NOLOCK) 
WHERE ct.CustomerId <> @CustomerId 
AND ct.ItemId <> @ItemId 
AND ct.TransactionId = @TransactionId";
cmd.CommandText = sqlCmd;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter("CustomerId", DbType.Int32, myCustomerId));
cmd.Parameters.Add(new SqlParameter("ItemId", DbType.String, myItemId));
..
cmd.ExecuteReader()

然后，您可以添加代码来构建测试查询：

string sqlMyTest = sqlCmd.Replace("@CustomerId", myCustomerId.ToString());
sqlMyTest = sqlMyTest.Replace("@ItemId", specialEscapeFunction(myItemId));
.. do something with sqlMyTest

然而，像Linq2SQL或EF这样的ORM不会像那样容易

customerTable.Where(c => (c.CustomerId != myCustomerId) && (c.ItemId != myItemId) && (c.TransactionId == myTransactionId))

像LinqPad这样的工具可能会有所帮助吗？