如何在VS 2012中的SQL Server数据库项目中存储静态数据 [英] How do you store static data in your SQL Server Database Project in VS 2012
问题描述
我试图使用SQL Server数据库项目来保存我们的所有表，存储过程，视图等脚本。我现在想办法能够保留所有我们的参考（或静态）数据。当工具或项目运行时，它会安装所有的数据库对象并插入所有的参考数据。
我发现了类似的文章，数据库专业人员的团队版。
- 在来源控制下获取我们的数据库。
- 将我们的本地开发数据库与最新版本
- 尽可能使用.Net工具，而不是像Redgate这样的工具但如果我可以在VS 2012中使用工具，我不想出去）
您可以使用此方法：
- 将参考数据放入XML文件中，每个表格一个
- 使用引用数据将XML文件添加到数据库项目
- 使用后部署脚本从XML提取数据并将其合并到表中
这里是每个步骤的更详细的描述，用一个例子说明。假设您需要初始化具有以下结构的国家/地区表：
创建表国家（
CountryId uniqueidentifier NOT NULL，
CountryCode varchar（2）NOT NULL，
CountryName varchar（254）NOT NULL
）
在数据库项目下创建一个名为 ReferenceData 的文件夹。它应该是 Schema Objects 和脚本的同级文件夹
向 ReferenceData 文件夹中添加一个名为 Country.xml 的新XML文件。按如下所示填充文件：
< countries&
< country CountryCode =CACountryName =Canada/>
< country CountryCode =MXCountryName =Mexico/>
< country CountryCode =USCountryName =United States of America/>
< / countries>
查找 Script.PostDeployment.sql 添加以下代码：
DECLARE @h_Country int
DECLARE @xmlCountry xml =
：r ..\..\ReferenceData\Country.xml
'
EXEC sp_xml_preparedocument @h_Country OUTPUT，@xmlCountry
MERGE国家AS目标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中尝试了这个解决方案，但是它对你的开发环境是不可知的。 p>
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.
I found similar articles for vs 2010 but they were using things like Team Edition for Database professionals.
- Get our DB under source control.
- Synchronize our local development DB with latest version in source control.
- Work with Visual Studio 2012 and 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)
You can use this approach:
- Put your reference data into XML files, one per table
- Add XML files with reference data to your database project
- Use a Post-Deployment script to extract the data from XML and merge it into your tables
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.
Add a new XML file called Country.xml to the ReferenceData folder. Populate the file as follows:
<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:
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 2012中的SQL Server数据库项目中存储静态数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
