如何在VS 2012中存储SQL Server数据库项目中的静态数据?

I am trying to use the SQL Server Database Project to keep all our table, stored procedure, views etc scripts. I now wan a way to be able to keep all our reference (or static) data as well. When the tool or project is run it will install all the DB objects and the insert all the reference data.

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

I found similar articles for vs 2010 but they were using things like Team Edition for Database professionals.

我在vs 2010上发现了类似的文章，但是他们在数据库专业人员中使用Team Edition之类的东西。

Get our DB under source control.
使我们的DB处于源代码控制之下。
Synchronize our local development DB with latest version in source control.
同步本地开发数据库与最新版本的源代码控制。
Work with Visual Studio 2012 and SQL Server 2012
使用Visual Studio 2012和SQL Server 2012
Use .Net tools as far as possible and not something like Redgate (Redgate is great but I don't want to for out for it just yet if I can use tools in VS 2012)
尽量使用。net工具，而不是Redgate (Redgate很好，但是我现在还不想用它，如果我能在VS 2012中使用工具的话)

1 个解决方案

#1

You can use this approach:

你可以使用这个方法:

Put your reference data into XML files, one per table
将您的引用数据放入XML文件中，每个表一个。
Add XML files with reference data to your database project
向数据库项目添加带有引用数据的XML文件
Use a Post-Deployment script to extract the data from XML and merge it into your tables
使用部署后脚本从XML中提取数据并将其合并到表中

Here is a more detailed description of each step, illustrated with an example. Let's say that you need to initialize a table of countries that has this structure:

下面是对每个步骤的更详细的描述，并举例说明。假设需要初始化具有这种结构的国家表:

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

Create a new folder called ReferenceData under your database project. It should be a sibling folder of the Schema Objects and Scripts.

在数据库项目下创建一个名为ReferenceData的新文件夹。它应该是模式对象和脚本的兄弟文件夹。

Add a new XML file called Country.xml to the ReferenceData folder. Populate the file as follows:

添加一个名为Country的新XML文件。xml到ReferenceData文件夹。填充文件如下:

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

Find Script.PostDeployment.sql, and add the following code to it:

找到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)
;

I tried this solution only in VS 2008, but it should be agnostic to your development environment.

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

#1