database - 如何在 VS 2012 中将静态数据存储在 SQL Server 数据库项目中

Question

我正在尝试使用 SQL Server 数据库项目来保留我们所有的表、存储过程、 View 等脚本。我现在想要一种方法来保存我们所有的引用(静态)数据。当工具或项目运行时，它将安装所有数据库对象并插入所有引用数据。

我找到了 vs 2010 的类似文章，但他们使用的是针对数据库专业人员的 Team Edition。

• 让我们的数据库处于源代码控制之下。
• 将我们的本地开发数据库与源代码管理中的最新版本同步。
• 使用 Visual Studio 2012 和 SQL Server 2012
• 尽可能使用 .Net 工具，而不是像 Redgate 这样的工具(Redgate 很棒，但如果我可以在 VS 2012 中使用工具，我还不想放弃它)

Answer 1

您可以使用这种方法:

• 将您的引用数据放入 XML 文件中，每张表一个
• 将带有引用数据的 XML 文件添加到您的数据库项目
• 使用部署后脚本从 XML 中提取数据并将其合并到您的表中

以下是对每个步骤的更详细描述，并附有示例。假设您需要初始化具有以下结构的国家/地区表:

create table Country (
    CountryId uniqueidentifier NOT NULL,
    CountryCode varchar(2) NOT NULL,
    CountryName varchar(254) NOT NULL
)

在您的数据库项目下创建一个名为 ReferenceData 的新文件夹。它应该是 Schema Objects 和 Scripts 的同级文件夹。

将名为 Country.xml 的新 XML 文件添加到 ReferenceData 文件夹。按如下方式填充文件:

<countries>
    <country CountryCode="CA" CountryName="Canada"/>
    <country CountryCode="MX" CountryName="Mexico"/>
    <country CountryCode="US" CountryName="United States of America"/>
</countries>

找到Script.PostDeployment.sql，添加如下代码:

DECLARE @h_Country int

DECLARE @xmlCountry xml = N'
:r ..\..\ReferenceData\Country.xml
'

EXEC sp_xml_preparedocument @h_Country OUTPUT, @xmlCountry

MERGE Country AS target USING (
    SELECT c.CountryCode, c.CountryName
    FROM OPENXML(@h_Country, '/countries/country', 1)
    WITH (CountryCode varchar(2), CountryName varchar(254)) as c) AS source (CountryCode, CountryName)
ON (source.CountryCode = target.CountryCode)
WHEN MATCHED THEN
    UPDATE SET CountryName = source.CountryName
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CountryId, CountryCode, CountryName) values (newid(), source.CountryCode, source.CountryName)
;

我只在 VS 2008 中尝试过这个解决方案，但它应该与您的开发环境无关。