使用Visual Studio和Team Foundation Server自动化数据库测试

This is the third post in the series about database development and testing using SQL Server Data Tools and Team Foundation Server.

这是有关使用SQL Server数据工具和Team Foundation Server进行数据库开发和测试的系列文章中的第三篇。

Post 1: Continuous Integration with SSDT and TFS

帖子1： 与SSDT和TFS的持续集成

Post 2: Unit testing with SQL Server Data Tools

帖子2： 使用SQL Server数据工具进行单元测试

You can run test in Visual Studio to test the quality of your build. In VS 2012 and 2013 there are 5 kind of tests that are available for the user:

您可以在Visual Studio中运行测试以测试构建质量。 在VS 2012和2013中，有5种测试可供用户使用 ：

1. Unit tests
   单元测试
2. Coded UI Tests
   编码的UI测试
3. Web performance tests
   Web性能测试
4. Load tests
   负载测试
5. Generic tests
   通用测试

These tests are often called smoke tests or build verification tests (BVT).

这些测试通常称为冒烟测试或建筑验证测试（BVT）。

软件需求 (Software requirements)

You will need Visual Studio 2010, 2012 or 2013 and at least Professional edition to be able to run unit tests. SQL Server Data tools integrated shell is not enough.

您将需要Visual Studio 2010、2012或2013以及至少专业版才能运行单元测试。 SQL Server数据工具集成的shell是不够的。

成功构建应用程序后配置并运行计划的测试 (Configure and run scheduled tests after successfully building your Application)

When creating Build Verification Tests you need to use Visual Studio, Version control and Team foundation Build (msbuild). First of all you need to write your tests, check them in with Version control. In the build definition file you can then choose which test to execute. You can then queue a build or use continuous integration to run the tests upon a successful build.

创建构建验证测试时，您需要使用Visual Studio，版本控制和Team Foundation Build（msbuild）。 首先，您需要编写测试，并使用“版本”控件将其检入。 然后，您可以在构建定义文件中选择要执行的测试。 然后，您可以将构建放入队列中，也可以使用持续集成在构建成功后运行测试。

The steps to achieve that are simple:

实现该步骤很简单：

• Define and check in a Build Definition Test
  定义并签入构建定义测试
• Create a build definition
  创建一个构建定义
• Add the computer to TestBuildController user group
  将计算机添加到TestBuildController用户组
• Setup the build agent
  设置构建代理
• Run the BVT build definition
  运行BVT构建定义

定义并签入构建定义测试 (Define and check in a Build Definition Test)

For the purpose of this post we will be using the same project than in the previous posts. It is attached here

出于本文的目的，我们将使用与以前的文章相同的项目。 它附在这里

If you remember from previous post, I created a simple unit test to check if my continuous deployment script had worked correctly:

如果您还记得以前的文章，我创建了一个简单的单元测试，以检查我的连续部署脚本是否正常工作：

Figure 1 Very simple test script

图1非常简单的测试脚本

Now I want this script to run as part of my check-in process after the database has been deployed to my integration instance.

现在，我希望此脚本在数据库部署到集成实例后作为签入过程的一部分运行。

创建一个构建定义 (Create a build definition)

In the Build menu from the home page of Team Explorer click New Build definition:

在Team Explorer主页的“构建”菜单中，单击“ 新建构建定义” ：

Remark

You can also use the one from the previous blog post included in the project and edit it to suit your needs

备注

您还可以使用项目中包含的上一篇博客文章中的一篇，并对其进行编辑以适合您的需求

Give a name and a description to your build. On the Trigger menu choose Continuous integration:

给您的版本命名和描述。 在“触发器”菜单上，选择“连续积分”：

Figure 3 Output folder for the builds

图3构建的输出文件夹

Expand the Process menu to show all the details. In the Items to build menu make sure that all your projects or the solution are selected.

展开处理菜单以显示所有详细信息。 在“ 要构建的项目”菜单中，确保选择了所有项目或解决方案。

Under Basic -> Automated Tests -> Test Source click the ellipsis (…) and make sure that you’re test assembly is listed there. By default it specifies a pattern you can use for catching all dlls having “test” in their name. This search string will search recursively through directories to find all dlls matching the pattern in the binary directories.

在“基本”->“自动测试”->“测试源”下，单击省略号（...），并确保您要测试的组件列在其中。 默认情况下，它指定一种模式，可用于捕获名称中带有“ test”的所有dll。 该搜索字符串将递归搜索目录，以在二进制目录中找到与该模式匹配的所有dll。

Figure 4 Test settings in build definition

图4构建定义中的测试设置

So now when you check in, as part of the build process, the test will be executed.

因此，现在当您签入时，作为构建过程的一部分，将执行测试。

The only problem remaining now is that the user executing the test will be your machine name in the domain, and it probably doesn’t have read permissions to whatever database you are going to run the test on. Resulting in the test result below:

现在剩下的唯一问题是，执行测试的用户将是您在域中的计算机名称，并且它可能对要在其上运行测试的任何数据库都没有读取权限。 得出以下测试结果：

Figure 5: Partially successful test

图5：部分成功的测试

在SQL Server数据工具中执行单元测试所需的权限： (Permissions required to perform unit tests in SQL Server Data Tools:)

You must have the following permissions to perform unit tests on a database:

您必须具有以下权限才能对数据库执行单元测试：

Action	Execution context connection	Privileged context Connection
Execute a test	1
Execute a pre-test or post-test		1
Run TestInitialize and TestCleanup scripts		1
Deploy database changes before you run tests		1
Generate data before you run tests		1

行动	执行上下文 连接	特权上下文 连接
执行测试	1个
执行前测或后测		1个
运行TestInitialize和TestCleanup脚本		1个
在运行测试之前部署数据库更改		1个
在运行测试之前生成数据		1个

As you can see there are two types of context database connection: Execution Context and Privileged Context. Where the privileged context connection requires more permissions due to the tasks performed.

如您所见，上下文数据库连接有两种类型：执行上下文和特权上下文。 由于执行的任务，特权上下文连接需要更多权限。

修改上下文权限 (Modifying Context permissions)

To modify context permissions you need to right click on your test project and choose SQL Server Test configuration:

要修改上下文权限，您需要右键单击测试项目，然后选择“ SQL Server测试配置”：

Figure 6 Modifying permissions context

图6修改权限上下文

You will see a dialog where you can specify or create a connection to a database and a few other options, like deploying the database before you run the test. Which in our case is not necessary since we are already doing that as part of a continuous integration project.

您将看到一个对话框，您可以在其中指定或创建与数据库的连接以及其他一些选项，例如在运行测试之前部署数据库。 在我们看来，这是没有必要的，因为我们已经将其作为持续集成项目的一部分。

1 is the Execution context and 2 if you specify it will be the Privileged Context (for running pre or post build scripts and/or deploying the database before running the tests).

1是执行上下文，而2（如果您指定的话）将是Privileged上下文（用于运行之前或之后的构建脚本和/或在运行测试之前部署数据库）。

陷阱 (Gotchas)

If you use integrated authentication in your execution context it will use the identity of the user running the build controller, in my case it is my machine name on the domain. I probably don’t want to give access to the continuous integration database to machine user.

如果在执行上下文中使用集成身份验证，它将使用运行构建控制器的用户的身份，在我的情况下，这是我在域上的计算机名。 我可能不想让机器用户访问持续集成数据库。

If you use a SQL Server user like I do in my example, its password will not be saved and you will end having to edit the app.config file of the test project to add the user password. This is one way to do it. But you probably either don’t want to user SQL Server users (remember those best practices) and you definitely don’t want to have its password in clear text in the app.config of your project!!

如果像我在示例中一样使用SQL Server用户，则将不会保存其密码，并且最终将不得不编辑测试项目的app.config文件以添加用户密码。 这是一种方法。 但是您可能既不想使用SQL Server用户（记住那些最佳实践），也绝对不想在项目的app.config中使用明文形式输入密码！

在单元测试中使用集成安全性 (Using integrated security with unit tests)

Your tests will always be run by the controller. In my case it has the same name than my machine name and it is running under NT AUTHORITY \ NETWORK SERVICE. It is a Windows Service running with configurable identity so changing it is simple.

您的测试将始终由控制器运行。 就我而言，它的名称与我的计算机名称相同，并且在NT AUTHORITY \ NETWORK SERVICE下运行。 它是一种Windows Service，以可配置的身份运行，因此更改它很简单。

Start the Team Foundation Server Administration Console and choose build configuration in the left pane:

启动Team Foundation Server管理控制台，然后在左窗格中选择构建配置：

Figure 7: Configuring the build agent service user

图7：配置构建代理服务用户

Here you can see what users it is running as.

在这里，您可以查看运行该用户的用户。

Click on properties to change it. Beware that you will need to restart the service in order to do that.

单击属性进行更改。 请注意，您需要重新启动服务才能这样做。

Pick a domain user who has access to your database.

选择有权访问您的数据库的域用户。

By the way it is not necessary for the build agent to run as the same user than the one connection to Team Foundation Server. But I find it easier. If you want them to run under 2 different users just remove the check-mark when altering the user:

顺便说一句，构建代理程序不必以与Team Foundation Server的一个连接相同的用户身份运行。 但我发现它更容易。 如果要让它们在2个不同的用户下运行，只需在更改用户时除去复选标记即可：

Figure 8 Changing the service agent identity

图8更改服务代理标识

Now try to run your test again and it will run under this user. And succeed: D

现在，尝试再次运行测试，它将在该用户下运行。 并成功：D

And showing that the test itself has passed!!

并显示测试本身已经通过！

Figure 9: Test results are green!

图9：测试结果为绿色！

In the next post I will explain one technique for deploying several databases automatically under the build process. Obviously it is not possible with only one line of command arguments for the msbuild agent.

在下一篇文章中，我将解释一种在构建过程中自动部署多个数据库的技术。 显然，仅使用msbuild代理的一行命令参数是不可能的。

We will need to modify the deployment XAML file or use post build scripts.

我们将需要修改部署XAML文件或使用构建后脚本。

Meanwhile happy testing!

同时祝您测试愉快！

翻译自: https://www.sqlshack.com/automating-database-tests-visual-studio-team-foundation-server/