发布时如何不包含sql文件内容? [英] How not to include sql file content when publishing?

问题描述

我有一个与我的旧问题非常相似的问题使用sql postdeployment生成脚本中的变量? 但是，现在我不想执行if/else并在已发布的脚本中包含sql代码.我不想在客户之间共享信息.

I have a question that is very similar to an older question of mine Using variable in sql postdeployment build script? But now I don´t want to do an if/else and have the sql code in my published script. I don´t want to share information beetween customers.

所以...来了

• 我在Visual Studio中有一个数据库项目.
• 我也有2位有不同需求的客户

我知道我可以

• 通过在Project.Properties."SQLCMD变量"中设置变量，使用post.deployment脚本包含所需的sql文件的内容.

然后在Script.PostDeployment.sql

And then use it like this in Script.PostDeployment.sql

:r ..\$(Customer)Setup.sql

我有2个文件

• Customer1Setup.sql
• Customer2Setup.sql

然后我将SQLCMD变量设置为$(Customer)"Customer1"或"Customer2"，仅包括我在SQLCMD中输入的变量.

And then I set the SQLCMD Variable as $(Customer) "Customer1" or "Customer2" and only the one I had put in the SQLCMD is included.

但是但是当我想在发布窗口中使用该变量时，默认变量会覆盖它

But when I want to use the variable in the publishing window the default one overrides it

我知道我可以在构建脚本中执行此操作，但是我想知道是否可以使用当前工具并仅创建Customer1.publish.xml和Customer2.publish.xml文件，这样我就知道如果发布到Customer2，则Customer1不会使用该脚本.

I know I can do this somewho in a build script but I want to know if I can use the current tools and just create Customer1.publish.xml and Customer2.publish.xml files so that I know that something that is for Customer1 does not go with the script if published to Customer2.

编辑1 :

我创建了一个公共 GitHub存储库，以尝试解决我的问题.希望有人可以看一下并对其进行编辑以使其工作.

I created a public GitHub repository to try to address my problem. Hopefully somebody can take a look and edit it to work.

编辑2 :

这是我的测试项目(与我的真实项目相同)的结果，在这3个测试中的每一个中，我得到的结果都是相同的. Customer2/Customer3中的所有材料"都包含在结果中.如果该方法可行，则在我的设置(VS2015数据库项目)中缺少某些开关或设置

This is the result from my test project (same as my real one) where in every one of the 3 tests I have there result in the same. All the "stuff" in Customer2/Customer3 is included in the result. If this is supposed to work there is some switch or setting I´m missing in my setup (VS2015 database project)

SET NOEXEC ON
Print 'This print statement should not be in the publishing script if     Customer variable is not customer1'
print 'Customer1 stuff from Customer1.sql'
SET NOEXEC OFF

IF('$(Customer)' <> 'customer2')
SET NOEXEC ON
Print 'This print statement should not be in the publishing script if     Customer variable is not customer2'
print 'Customer2 stuff from Customer2.sql'
SET NOEXEC OFF

IF('$(Customer)' <> 'customer3')
SET NOEXEC ON
Print 'This print statement should not be in the publishing script if    Customer variable is not customer3'
print 'Customer3 stuff from Customer3.sql'
SET NOEXEC OFF

编辑3

所以，如果我能更好地想象这一点 这就是我想要得到的

So if I visualize this little better This is what I want to get

但这是我能得到的

推荐答案

以下内容无法按预期运行:

The below won't work as per expectations:

:r ..\$(Customer)Setup.sql

尽管如此，您可以使用IF ELSE方法:

Instead though, you could use the IF ELSE approach:

IF ('$(Customer)'='customer1')
BEGIN
    :r .\script_customer1.sql
END
IF ('$(Customer)'='customer2')
BEGIN
    :r .\script_customer2.sql
END

经过更多研究，我发现了类似的SO问题.事实证明，如果您在脚本中使用GO，则上述方法将不起作用.因此可以使用以下代码:

After a bit more research I found similar SO question. It turns out the above would not work in case you use GO in your scripts. So could use the below:

IF ('$(Customer)'<>'customer1')
    SET NOEXEC ON

:r .\script_customer1.sql
SET NOEXEC OFF

IF ('$(Customer)'<>'customer2')
    SET NOEXEC ON

:r .\script_customer2.sql
SET NOEXEC OFF

这篇关于发布时如何不包含sql文件内容?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！