为 SQL Server 数据库项目发布生成配置

• 下载演示 - 320 KB

摘要

在 Visual Studio 中开发和部署 SQL Server 数据库有多种方法。SQL Server 数据库项目就是其中之一。对于小型项目，使用 SQL Server 数据项目有时比手动编写迁移脚本更快。然而，这些项目与 Visual Studio 的构建配置（VS 2017.15.3.0）不兼容。本文档将介绍这些项目的优势，并演示如何使用 PowerShell、预构建和后构建脚本来改变 Visual Studio 编译 SQL Server 数据库项目的行为，并添加对调试、发布等多种配置的支持。

引言

SQL Server 是许多使用 Visual Studio 开发应用程序的 .NET 开发人员常用的数据库工具。通常，部署 SQL Server 数据库主要有两种方式：迁移和状态。在迁移方式中，开发人员编写转换脚本，将数据库从一个状态转换为另一个状态。例如，他们可能在初始状态使用 CREATE TABLE 来创建表，然后使用 ALTER TABLE 来向该表添加新列。在状态方式中，开发人员使用可视化工具或直接更新 CREATE TABLE SQL 脚本来修改同一个表。开发人员可以使用比较工具将前一个状态与后一个状态进行比较，以生成迁移脚本。SQL Server 数据库项目是使用状态方法进行数据库开发和部署的强大工具。在接下来的章节中，我们将讨论使用 SQL Server 数据库项目的优势，并解释如何添加这些项目中的一个缺失功能。

SQL Server 数据库项目的优势

SQL Server 数据库项目是一个强大的工具，它有利有弊。在本节中，我们将它与其他开发和部署 SQL Server 数据库的方法进行比较。如果您熟悉数据库项目，可以跳过本节。

相较于设计器工具（SQL Server Management Studio）的优势

使用 SQL Server 数据库项目开发数据库比使用 SQL Server Management Studio 等数据库设计器工具具有许多优势。

1. 数据库模型可以与代码更改一起进入源代码控制。这对于跟踪多个开发人员在同一数据库上进行更改非常有帮助。
2. 由于更改会被源代码控制跟踪，模型可以由多个开发人员同时设计和编辑。他们可以尽早意识到冲突。
3. 数据库可以部署到多个目标。例如，可以将数据库部署到 SQL Azure 而不是本地 SQL Server。
4. 可以在任何时候将已部署的数据库与源代码进行比较，以分析更改或修复问题。
5. 重新创建和重新发布数据库非常快速。这对于开发阶段的测试非常有用，因为它使得填充测试数据变得非常容易。
6. 脚本中的许多语法错误可以在编译时捕获。例如，当开发人员删除数据库中的一个字段时，Visual Studio 会找到所有使用该字段的视图，并为此类视图引发编译错误。此功能在 SQL Server Management Studio 中不可用，开发人员可能会在未更改相关视图的情况下删除表中的字段，这将在部署后导致运行时错误。
7. 可以进行脚本的静态代码分析。通过执行静态分析可以发现一些潜在问题。例如，开发人员可能由于代码复杂性而在某个执行路径中忘记赋值，但存储过程的输出变量应在所有路径中赋值。静态代码分析可以在编译时发现此问题并报告。在发布产品之前解决此问题，并调试几个月后才发生的逻辑错误非常有益。

相较于 Entity Framework Code First 的优势

Entity Framework Code First 是开发 SQL Server 数据库的一种非常常见的方式，但它也有其局限性。以下是使用数据库项目相较于 Code First 方法的优势列表：

1. 它不与像 Entity Framework 这样的特定框架绑定。
2. T-SQL 的所有功能都可以在数据库的设计和开发中使用。
3. 它为存储过程的开发提供了更好的支持。
4. 使用 Entity Framework 无法对存储过程进行 SQL 静态代码分析。
5. 调试脚本中的潜在问题通常比在复杂的 ORM 模型中查找问题更容易。Entity Framework 自动生成的脚本可能变得不可预测且难以处理。
6. 性能调优更加容易。例如，开发人员不太可能忘记为字段设置最大长度或定义适当的索引。

相较于迁移方法的优势

1. 特定数据库对象（表、视图、存储过程）的更改可以轻松地在源代码控制中跟踪。
2. 重新创建和重新发布数据库更容易，因为 Visual Studio 保留了发布配置文件，并提供了易于使用的数据库发布用户界面。在迁移方法中，程序员需要自己管理这些。
3. 多个开发人员可以处理同一个数据库对象并合并他们的更改。在迁移方法中，更改存储过程并不容易。例如，两个开发人员可能会更改同一个存储过程代码，而最后的更改将覆盖之前的更改。

值得注意的是，SQL Server 数据库项目可以与迁移方法结合使用，这本身就是一个值得尝试的有趣主题。

缺点

状态方法最大的缺点是，没有直接的方法来处理数据操作。当数据库部署到生产环境时，它将包含数据。不幸的是，在状态方法中，不容易在模型更改的同时修改这些数据。例如，假设我们在名为“User”的表中有一个“DisplayName”字段，其中存储了我们用户的名字和姓氏。产品负责人要求一项新功能，可以按姓氏对用户进行排序。我们需要将 DisplayName 分割成两个列，名为“FirstName”和“LastName”。在状态方法中添加这两个列很容易，但没有简单的方法来分割 DisplayName 字段中存储的数据。这只是一个例子。在状态方法中，我们可能找不到简单方法的场景有很多。

为什么选择 SQL Server 数据库项目？

迁移方法仍然是在生产环境（在线数据库）中部署和更新数据库的最佳方式。然而，我们在使用状态方法进行小型短期项目方面取得了成功的经验。基于我们之前的经验，编写和测试迁移脚本以及为脚本部署到测试和生产环境进行良好自动化需要更多的时间。因此，使用状态方法进行开发有时比编写迁移脚本更快。

我们最近的一些项目使用了状态方法，原因如下：

1. 项目有一个严格的、短期的截止日期。我们需要使用最快的方式来开发项目。因此，所有开发人员都应该参与数据库开发。
2. 这是一个本地部署的应用程序，客户需要在部署后对数据库有很好的控制。因此，使用存储过程访问和修改数据库是毋庸置疑的。

项目结束时我们只有一个可交付成果。项目的所有文件都应在一个包中交付。这意味着我们不需要在线更新或更改客户的数据。

问题定义

数据库项目有很多功能，但它们缺乏在编译过程中使用 Visual Studio 构建配置的功能。因此，不可能拥有调试、发布等不同的配置。此限制使得使用多个种子数据（甚至模式结构）部署数据库变得更加困难。拥有此功能即使对于小型项目也很有帮助。例如，在调试数据库中填充随机数据用于测试，而在发布数据库中只保留硬编码数据是很好的。

解决方案

Visual Studio 项目可以通过调用预构建和后构建脚本来在构建过程前后执行任务。为了解释解决方案，我们将提供一个分步示例。我们从一个空的 SQL Server 数据库项目开始。

首先，我们在其中创建三个文件夹（参见图 1）：

1. Compare：用于存储比较文件。比较文件用于比较模式与已发布的数据库。
2. Deploy：我们将部署脚本（调试、发布等）存储在此文件夹中。
3. Objects：所有数据库对象，如表和存储过程，都位于此文件夹中。

SQL 数据库项目一个相当有用的功能是它允许开发人员以他们需要的任何方式组织文件。换句话说，建议的文件夹结构不是强制性的，您可以定义适合项目需求的自己的结构。此结构与默认的数据库项目结构不同，但当数据库中对象很多时，我们发现它非常有帮助。原则是每个实体都应具有所有相关文件，如表创建、存储过程、触发器等，在同一个文件夹中。如果一个存储过程对多个表执行操作，它应该存储在正在更改的主对象的文件夹中。例如，如果“spCompanyInsert”在 Company 表中插入公司数据，并插入映射用户或组的默认数据，它应该存储在 Company 表中。

图 1. 数据库对象的文件夹结构

我们添加了一个示例公司表（Company.sql）和一个用于保存时区信息的表（TimeZone.sql）。

Deploy 文件夹

在本节中，我们将描述如何通过添加后部署脚本来添加配置功能。第一步是创建如图 2 所示的文件夹结构，并添加所有必要的文件。

图 2. Deploy 文件夹结构

我们将在下面的内容中详细解释这些文件、它们的目的和配置。

Common.PostDeployment.sql

“Common”文件夹包含应该包含在任何配置构建过程中的脚本文件。我们可以使用它来为调试和发布配置插入种子数据。例如，TimeZone 表的插入语句可以存储在名为“Common.PostDeployment.sql”的文件中。

Debug.PostDeployment.sql

我们需要为创建本地数据库运行的脚本可以存储在“Debug”文件夹中；这些脚本可能包含用于测试目的的种子数据。例如，我们可以在名为“Debug.PostDeployment.sql”的文件中向 Company 表插入随机数据。

Release.PostDeployment.sql

所有用于生产发布的脚本都可以存储在“Release”文件夹中。例如，我们可以插入用于 Company 表的真实种子数据。

Script.PostDeployment.sql

我们向项目中添加一个后部署脚本，用我们的发布或调试脚本替换其内容。该脚本有一个名为“PostDeploy”的特殊构建操作属性，可以从图 3 所示的 Visual Studio 属性窗口设置。此文件不应包含任何内容。

图 3. Script.PostDeployment.sql 的属性窗口

prebuild.ps1

我们添加了一个 PowerShell 脚本来更改 Script.PostDeployment.sql 文件的内容，使用调试或发布脚本。这是该文件的内容：

我们添加了一个 PowerShell 脚本来更改 Script.PostDeployment.sql 文件的内容，使用调试或发布脚本。这是该文件的内容：

Param (

        [string] $ProjectDir = "",

        [string] $ConfigurationName = ""

)

$ErrorActionPreference = "Stop"


write-host '----------Creating PostDeployment script Started  ------------'

write-host ('ProjectDir=' + "$ProjectDir")

write-host ('ConfigurationName=' + "$ConfigurationName")


sp ("$ProjectDir"  + "Deploy\Script.PostDeployment.sql") IsReadOnly $false


Get-Content ("$ProjectDir" + "Deploy\Common\*.sql"),("$ProjectDir"+ "\Deploy\"+ "$ConfigurationName" + "\*.sql") > ("$ProjectDir" +"Deploy\Script.PostDeployment.sql")


write-host '----------Creating PostDeployment script Successful ------------'

exit $LastExitCode

“$ProjectDir”和“$ConfigurationName”是定义为脚本输入的两个参数。$ProjectDir 是一个字符串，包含项目所在的目录路径。$ConfigurationName 具有 Visual Studio 的配置设置，例如“Debug”或“Release”。这两个值都在 Visual Studio 的预构建事件命令中设置。

语句“$ErrorActionPreference = ‘Stop’”的目的是在编译过程中发生任何错误时停止构建。由于“Script.PostDeployment.sql”可能为只读，我们需要首先使用“sp”命令使其可写。之后，文件内容将被合并“Deploy\Common”文件夹和“Deploy\Debug”或“Deploy\Release”中的所有脚本文件来替换。在此示例中，每个文件夹下只有一个文件，但如您在脚本中看到的，它会合并所有文件。如果您打算在这些文件夹中有多个文件，需要注意执行顺序。最后，我们通过设置最后一个命令的退出代码（“exit $LastExitCode”）来退出 PowerShell。如果我们不为错误传递退出代码，Visual Studio 将继续部署有错误脚本。因此，如果您打算修改此行为，请确保您考虑了错误退出代码。

下一步是配置项目的预构建和后构建命令，以集成到我们的 Visual Studio 构建过程中。此配置如图 4 所示。我们在预构建事件部分添加以下命令：

powershell -NoProfile -ExecutionPolicy RemoteSigned  ^& '$(ProjectDir)Deploy\prebuild.ps1' -ProjectDir '$(ProjectDir)' -ConfigurationName '$(ConfigurationName)'

图 4. 项目构建事件对话框

这将在 PowerShell 中运行我们的“prebuild.ps1”脚本，并提供配置。额外的开关允许 Visual Studio 在不以管理员模式运行的情况下运行 PowerShell。由于预构建会更改我们 Script.PostDeployment.sql 文件的内容，而我们不需要将这些更改存储在源代码控制中，因此我们需要使用后构建事件中的复制命令将其文件替换为旧内容（空内容）。

COPY "$(ProjectDir)\Deploy\Empty.PostDeployment.sql" "$(ProjectDir)\Deploy\Script.PostDeployment.sql" /y

发布文件

现在是将数据库发布到本地 SQL Express 的时候了。通过右键单击项目并选择“发布…”菜单来创建发布文件。如图 5 所示。

图 5. 发布菜单

将出现发布对话框。单击“编辑”按钮并选择您的 SQL Express。然后在“数据库名称:”字段下输入数据库名称。我们将其命名为“CyberGroup.Demo”。发布对话框的优点是，如果数据库不存在，它将在 SQL Express 中创建它。此对话框如图 6 所示。

图 6. 发布对话框

发布对话框有很多高级设置。例如，每次需要新部署时，它可以重新创建数据库。要更改这些设置，我们需要单击“高级…”按钮。此对话框如图 7 所示。由于这是我们的本地开发数据库，我们希望选择“始终重新创建数据库”，以便每次发布数据库项目时都从头开始重新创建所有内容。这极大地简化了开发过程。开发人员无需担心迁移、字段名或数据库对象的更改。每次数据库发生更改时，他们都可以轻松地重新创建数据库。这对于大型数据库来说可能不是一个好主意，但对于小型数据库来说效果很好。此外，可以通过创建单独的数据库项目来将大型数据库分解为小型数据库；此过程超出了本文的范围。

图 7. 发布配置文件高级设置

我们使用“保存配置文件”将文件命名为“CyberGroup.DemoDatabase.local.publish.xml”并保存在项目根文件夹中。文件名不重要。我们将其保存在根目录，以便在每次需要新发布时都能轻松找到它。此文件也可以放入源代码控制，以便其他开发人员可以在他们的机器上部署该项目。

大功告成。现在我们只需设置 Visual Studio 以调试或发布模式进行构建，然后从根文件夹（双击）运行发布文件即可。

摘要

Visual Studio SQL Server 数据库项目可以加快小型数据库的开发。我们在过去成功地使用过它。不幸的是，这些项目缺乏许多功能。最有用、最需要的功能之一是能够根据 Visual Studio 的构建配置（如调试、发布等）配置构建过程。我们决定添加此功能，并在本文中与您分享我们的经验。我们通过修改预构建和后构建事件并使用 PowerShell 脚本来实现这一点。

历史

首次发布于 2018 年 12 月 30 日（感谢 Brad Darby 的校对和编辑）。