SQL Server单元测试:可以在“预测试”脚本中创建临时表，并在主测试中使用它们吗?

I am an SQL newbie trying to create unit tests for stored procedures in a legacy database. The stored procedures retrieve input data from temporary tables, which seems like it would make it easy to test them. I would like to create and populate those tables in a test harness setup routine, run the stored procedure, and then evaluate the results.

我是一个SQL新手，尝试为遗留数据库中的存储过程创建单元测试。存储过程从临时表中检索输入数据，这似乎使测试它们变得很容易。我希望在测试管理设置例程中创建并填充这些表，运行存储过程，然后评估结果。

I set up my test environment using Visual Studio 2013 with a SQL Server Unit Test project as shown in this post.

我使用Visual Studio 2013设置了一个SQL Server单元测试项目的测试环境，如本文所示。

My Pre-test looks pretty boring:

我的预考看起来很无聊:

CREATE TABLE #foo(
/* fields */)

/* Populate temp table here */

Then in the Test body, I call the stored procedure, which depends on table #foo:

然后在测试主体中，我调用存储过程，这取决于表#foo:

DECLARE @RC AS INT;
SELECT @RC = 0;
EXECUTE @RC = [dbo].[MyStoredProcedure] ;
SELECT @RC AS RC;

But when I run the test, I get the following error:

但是当我运行测试时，我得到以下错误:

Sql Error: 'Invalid object name '#foo'.' (Severity 16, State 0)

Test method DatabaseTestProj.TestSpike.dbo_SomeStoredPocTest threw exception: 
System.Data.SqlClient.SqlException: Invalid object name '#foo'.

In fact, it seems that I can't even create a variable in Pre-Test and access it from the main part of the test.

事实上，我甚至不能在测试前创建一个变量并从测试的主要部分访问它。

What's the "right" way to populate the temporary table? Should the Pre-test area not be used for creating temporary tables?

填充临时表的“正确”方法是什么?测试前区域不应该用于创建临时表吗?

2 个解决方案

#1

The issue here is that by default pre and post-test scripts are executed using a different connection (PrivilegedContext) to the test script (ExecutionContext). You are using a local temporary table and the scope of this is only within the current connection. Hence the test cannot access the table since it's not available in its connection scope. There are two possible solutions here:

这里的问题是，默认情况下，测试前和测试后脚本是使用与测试脚本(ExecutionContext)不同的连接(PrivilegedContext)来执行的。您正在使用一个本地临时表，其范围仅在当前连接中。因此，测试无法访问表，因为它在连接范围内不可用。这里有两个可能的解决方案:

1. Use a global temp table (##foo instead of #foo). This is available for any connection so you can access it from both Privileged and Execution contexts. The benefit is you don't need to change your test code, the downside is you might not want to change production code just to get your test working

   使用全局临时表(##foo而不是#foo)。这对于任何连接都是可用的，因此您可以从特权和执行上下文中访问它。好处是您不需要更改测试代码，缺点是您可能不希望仅仅为了使测试工作而更改生产代码

2. Change the connection used to execute either the test action or the pre-test action. This requires editing the C#/VB code that the unit test designer creates. If you right-click on your test class in Solution Explorer and choose "View Code", you can see the contents of the test method. You should be able to change the "preTestResults" line to use the ExecutionContext. See sample below (in this case the test was named "dbo_Procedure1Test").

   更改用于执行测试操作或预测试操作的连接。这需要编辑单元测试设计器创建的c# /VB代码。如果您在解决方案资源管理器中右键单击您的测试类并选择“查看代码”，您可以看到测试方法的内容。您应该能够更改“preTestResults”行以使用ExecutionContext。参见下面的示例(在这种情况下，测试被命名为“dbo_procedure dure1test”)。

   [TestMethod()]
   public void dbo_Procedure1Test()
   {
       SqlDatabaseTestActions testActions = this.dbo_Procedure1TestData;
       // Execute the pre-test script
       // 
       System.Diagnostics.Trace.WriteLineIf((testActions.PretestAction != null), "Executing pre-test script...");
   
       // Original code: uses the "PrivilegedContext" for the connection.
       //SqlExecutionResult[] pretestResults = TestService.Execute(this.PrivilegedContext, this.PrivilegedContext, testActions.PretestAction);
   
       // New code: uses the same "ExecutionContext" for the connection that the test section uses
       SqlExecutionResult[] pretestResults = TestService.Execute(this.ExecutionContext, this.PrivilegedContext, testActions.PretestAction);
   
       try
       {
           // Execute the test script
           // 
           System.Diagnostics.Trace.WriteLineIf((testActions.TestAction != null), "Executing test script...");
   
           // The test action uses an ExecutionContext, which is useful if you want to limit the permissions
           // to what an actual caller of the stored procedure would have, but do setup/teardown of test data using
           // a more privileged context. In this case you don't want that, so use the same context for both pre-test and the test itself
           SqlExecutionResult[] testResults = TestService.Execute(this.ExecutionContext, this.PrivilegedContext, testActions.TestAction);
       }
       finally
       {
           // Execute the post-test script
           // 
           System.Diagnostics.Trace.WriteLineIf((testActions.PosttestAction != null), "Executing post-test script...");
           SqlExecutionResult[] posttestResults = TestService.Execute(this.PrivilegedContext, this.PrivilegedContext, testActions.PosttestAction);
       }
   }
   

#2

This is a little diagram I made that helps me visualize where the different bits of the class are used and also where they show in the designer.

这是我制作的一个小图表，它可以帮助我可视化类的不同部分在哪里被使用，以及它们在设计器中显示的位置。