使用SQL Server数据工具进行SQL单元测试

最新推荐文章于 2024-08-10 08:03:29 发布

culuo4781

最新推荐文章于 2024-08-10 08:03:29 发布

阅读量801

点赞数

文章标签：数据库单元测试 python java mysql

原文链接：https://www.sqlshack.com/unit-testing-sql-server-data-tools/

版权

本文是关于SQL Server Data Tools（SSDT）和数据库开发系列的第二部分，主要介绍如何进行SQL单元测试。内容包括如何在Visual Studio中创建和运行SQL Server单元测试，检查SSDT最新版本，以及在SQL Server对象资源管理器中使用项目。

摘要由CSDN通过智能技术生成

This article on SQL Unit Testing is the second part on the series about SSDT and database development

有关SQL单元测试的本文是关于SSDT和数据库开发的系列的第二部分

Part I: Continuous integration with SQL Server Data Tools and Team Foundation Server

第一部分：与SQL Server数据工具和Team Foundation Server的持续集成

介绍 (Introduction)

In December 2012, a great addition was made to SSDT: The ability to do SQL unit testing.

在2012年12月，SSDT有了很大的增加：可以执行SQL单元测试。

However, this feature is not available on the free edition of SSDT. For doing unit test with SSDT you will at least need a Visual Studio Professional edition or above.

但是，此功能在SSDT的免费版中不可用。要使用SSDT进行单元测试，您至少需要Visual Studio Professional版本或更高版本 。

As you and the team members make changes to the database schema you can verify that these changes are working as expected or if they have broken existing functionality.

当您和团队成员对数据库架构进行更改时，您可以验证这些更改是否按预期进行，或者它们是否破坏了现有功能。

进行中 (Proceeding)

Usually you will want to baseline your database and then run some unit tests on the changes that you have made. Personally, I have made a habit out of taking a snapshot of the database I am working with at different stages and at least once a week. This way I can always go back to the state it was before I made the subsequent changes without having to roll back changes with TFS.

通常，您将希望对数据库进行基准测试，然后对所做的更改进行一些单元测试。就个人而言，我习惯于在不同阶段（至少每周一次）对要使用的数据库进行快照。这样，我始终可以返回到进行后续更改之前的状态，而不必使用TFS回滚更改。

Because SQL unit testing of your database is just a “Unit Test Project”, you can create and run database unit test without a database project. However, the only way to auto-generate tests for specific database objects is to use the database project.

由于数据库SQL单元测试只是一个“单元测试项目”，因此您可以在没有数据库项目的情况下创建和运行数据库单元测试。但是，自动生成特定数据库对象的测试的唯一方法是使用数据库项目。

When creating a test project from a database project Visual Studio will automatically generate some of classes for you. Most of the plumbing will be done that way. The most important class in that regard is:
Microsoft.Data.Tools.Schema.Sql.UnitTesting;

从数据库项目创建测试项目时，Visual Studio会自动为您生成一些类。大多数管道将以这种方式完成。在这方面最重要的一类是：
Microsoft.Data.Tools.Schema.Sql.UnitTesting;

检查最新版本的SSDT (Checking for the latest version of SSDT)

First you need to check if you have the latest version of SSDT. This is done with the Check for Updates menu under the SQL menu.

首先，您需要检查是否具有最新版本的SSDT。这是通过“ SQL”菜单下的“ 检查更新”菜单完成的。

SQL unit testing - SQL unit testing - Getting the latest version of SSDT

Figure 1: Getting the latest version of SSDT

图1：获取最新版本的SSDT

When checking for updates you also have the possibility to let Visual Studio do it automatically for you by selecting automatically check for updates to SQL Server Data Tools.

在检查更新时，您还可以通过选择自动检查SQL Server数据工具的更新来让Visual Studio为您自动完成更新 。

SQL unit testing - Automatically checking for new versions

Figure 2: Automatically checking for new versions

图2：自动检查新版本

在SQL Server对象资源管理器（SSOX）中使用项目 (Using Projects in SQL Server Object Explorer (SSOX))

Each time you hit F5, SSDT will deploy your database to your LocalDB. By the way, it is possible to change this behavior by going to the Debug tab of the project’s property page and change the connection string there so your database gets deployed somewhere else than in this local instance.

每次您按F5时，SSDT都会将数据库部署到LocalDB。顺便说一句，可以通过转到项目属性页的“调试”选项卡并在那里更改连接字符串来更改此行为，以便将数据库部署到该本地实例以外的其他位置。

从数据库中的现有对象创建SQL Server单元测试 (Creating a SQL Server Unit Test from an existing object in the database)

From SSDT You can automatically create stubs for SQL unit testing stored procedures, functions and triggers.

从SSDT中，您可以自动创建用于SQL单元测试存储过程，函数和触发器的存根。

Let us say that we want to test a stored procedure called uspRankCustomer that we created in a previous blog post. See Continuous integration with SQL Server Data Tools and Team Foundation Server for a script for creating the database. Alternatively, use this embedded script with the complete database schema used in this example.

假设我们要测试在先前的博客文章中创建的名为uspRankCustomer的存储过程。有关创建数据库的脚本，请参阅与SQL Server数据工具和Team Foundation Server持续集成。或者，将此嵌入式脚本与本示例中使用的完整数据库架构一起使用。

Find the stored procedure you want to create the test stub for and right-click on it in the project node of SSOX (keep in mind that the Projects node will not contain your database project before you have successfully deployed / publish your database).

在SSOX的项目节点中找到要为其创建测试存根的存储过程，然后在它上单击鼠标右键（请注意，在成功部署/发布数据库之前，“项目”节点将不包含数据库项目）。

SQL unit testing - Creating unit tests from Projects node

Figure 3: Creating unit tests from Projects node

图3：从Projects节点创建单元测试

In the window that shows next, you can chose if you want to create a VB.Net or a C# test project as well as a list of all the database object that support SQL unit testing and the class name for the test file being created.

在接下来显示的窗口中，您可以选择是否要创建VB.Net或C＃测试项目以及支持SQL单元测试的所有数据库对象的列表以及要创建的测试文件的类名。

SQL unit testing - Choosing which element to create test for

Figure 4: Choosing which element to create test for

图4：选择要为其创建测试的元素

I chose C# and after project creation, you are presented with a SQL Server Test Configuration dialog where you can specify which database to run the test on even a secondary connection to validate those tests as well as the option to deploy the database prior to you run your tests.

我选择了C＃，在创建项目后，会看到一个“ SQL Server测试配置”对话框，您可以在其中指定在哪个数据库上甚至在辅助连接上运行测试以验证那些测试，以及在运行之前部署数据库的选项。您的测试。

Remark : If you must test views or stored procedures that have restricted permissions, you would typically specify that connection in this step. You would then specify the secondary connection, with broader permissions, to validate the test. If you have a secondary connection, you should add that user to the database project, and create a login for that user in the pre-deployment script.

备注：如果必须测试具有受限权限的视图或存储过程，则通常在此步骤中指定该连接。 然后，您可以指定具有更大权限的辅助连接来验证测试。 如果您具有辅助连接，则应将该用户添加到数据库项目中，并在预部署脚本中为该用户创建登录名。

In my case I want to run my tests on the database I deployed earlier This is how my setup looks like:

就我而言，我想在我之前部署的数据库上运行测试，这是我的设置的样子：

SQL unit testing - Configuring the test project

Figure 5: Configuring the test project

图5：配置测试项目

If you look at the solution explorer you will see that following was created:

如果您查看解决方案资源管理器，您将看到创建了以下内容：

A test project
一个测试项目
A SqlDatabaseSetupFile
SqlDatabaseSetupFile
A SQLUnitTest class
SQLUnitTest类
The app.config of the project contains the settings for deployment and database connection
项目的app.config包含用于部署和数据库连接的设置

定义测试逻辑 (Define test logic)

My database is quite simple and contains 3 tables and 3 stored procedures. In my previous post, I created a table and a procedure for ranking customers. I now want to test if my ranking procedure uspRankCustomers works as intended and ranks the customer as specified in the ranking table.

我的数据库非常简单，包含3个表和3个存储过程。在上一篇文章中，我创建了一个表格和一个对客户进行排名的过程。现在，我想测试我的排名过程uspRankCustomers是否按预期工作，并按照排名表中指定的客户排名。

Here is the simple stored procedure used:

这是使用的简单存储过程：

 
CREATE PROCEDURE [dbo].[uspRankCustomers]
AS
            DECLARE @CustomerId int
            DECLARE @OrderTotal money
            DECLARE @RankingId int
 
          DECLARE curCustomer CURSOR FOR
          SELECT c.CustomerID, ISNULL(SUM(oh.OrderTotal), 0) AS OrderTotal
          FROM dbo.Customer AS c
          LEFT OUTER JOIN dbo.OrderHeader AS oh ON c.CustomerID = oh.CustomerID
          Group BY c.CustomerId
 
          OPEN curCustomer
          FETCH NEXT FROM curCustomer INTO @CustomerId, @OrderTotal
 
          WHILE @@FETCH_STATUS = 0
          BEGIN
                     IF @OrderTotal = 0 SET @RankingId = 1
                     ELSE IF @OrderTotal < 100 SET @RankingId = 2
                     ELSE IF @OrderTotal < 1000 SET @RankingId = 3
                     ELSE IF @OrderTotal < 10000 SET @RankingId = 4
                     ELSE SET @RankingId = 5
 
                     UPDATE Customer
                      SET RankingId = @RankingId
                      WHERE CustomerId = @CustomerId
 
                         FETCH NEXT FROM curCustomer INTO @CustomerId, @OrderTotal
          END
 
            CLOSE curCustomer
            DEALLOCATE curCustomer

Test: For my existing customers I want to update the ranking using the Ranking table.

测试：对于我现有的客户，我想使用排名表更新排名。

步骤1：定义测试脚本 (Step 1: Define the test script)

I want to execute my stored procedure and make sure that there are no nulls in the rankingID of the customer table. This way I can be sure that all customers have been ranked!

我要执行存储过程，并确保客户表的rankingID中没有空值。这样，我可以确定所有客户都已排名！

 
-- database unit test for dbo.uspRankCustomers
DECLARE @RC AS INT;
 
SELECT @RC = 0;
 
-- execute the stored procedure
EXECUTE @RC = [dbo].[uspRankCustomers] ;
 
-- select the customer ids without any ranking (should be 0)
SELECT @RC = CustomerID from Customer where RankingID IS NULL
 
SELECT @RC AS RC;

In Test conditions delete the automatically created condition and create a new one. Change its type to Scalar Value and in the property window make sure the expected value is 0.

在“测试条件”中，删除自动创建的条件并创建一个新条件。将其类型更改为“ 标量值”，并在属性窗口中确保期望值为0。

SQL unit testing - Writing the unit test

Figure 6: Writing the unit test

图6：编写单元测试

步骤2：执行测试 (Step 2: Running the test)

In the Test tab click Windows and then Test Explorer the Test Explorer window should appear, listing your test. Right-click on it and choose Run Selected Test. It should now run and show a green icon if it a success (or a red one in event of a failure).

在测试选项卡中单击Windows，然后测试资源管理器应该出现在测试资源管理器窗口，列出您的测试。右键单击它，然后选择“运行选定的测试” 。现在，它应该运行并显示绿色图标（如果成功）（如果失败，则显示红色图标）。

Figure 7: Successful test run

图7：成功的测试运行

If you remember the previous post in this series, I made it possible to do continuous deployment with MSBuild and Team Foundation Server. I showed how you are able to build and deploy your database changes for each check-in. Well, it is also possible to have this procedure run the test for you. This is useful for running automated tests and analyze the impact of code changes on your tests as part of your build process.

如果您还记得本系列的上一篇文章，则可以使用MSBuild和Team Foundation Server进行连续部署。我展示了如何在每次签入时构建和部署数据库更改。好了，也可以让此过程为您运行测试。这对于运行自动化测试以及在构建过程中分析代码更改对测试的影响很有用。

But for now we’ll disable it by removing it in the Build Definition file. On the left pane choose Process and Click the ellipsis on the right side of Automated Test and click Remove in the dialog that shows !!

但是现在我们将通过在构建定义文件中将其删除来禁用它。在左侧窗格中，选择“ 处理”，然后单击“ 自动测试 ”右侧的省略号，然后在显示!!的对话框中单击“ 删除 ”。