在VS DBPro中动态读取文件（使用SQLCMD）

Question

我使用的是VS2008 DBPro。在我的 PostDeploy 文件中，我有很多像这样的行，

:r .\Data\Test\Classifiers.CodeType.data.sql
:r .\Data\Test\Classifiers.Currency.data.sql
:r .\Data\Test\Classifiers.LOB.data.sql

我想要的是创建一个变量 ProjectName，以便我可以轻松部署不同的项目数据。像这样的东西（不起作用）

:setvar ProjectName "Test"

:r .\Data\$(ProjectName)\Classifiers.CodeType.data.sql
:r .\Data\$(ProjectName)\Classifiers.Currency.data.sql
:r .\Data\$(ProjectName)\Classifiers.LOB.data.sql

如果我可以读取文件夹中的所有文件而不指定其路径，那就更好了。

Answer 1

我已经知道如何做到这一点。

首先，您需要启用 xp_cmdshell 实用程序

RAISERROR ('Enabling xp_cmdshell utility...', 0, 1) WITH NOWAIT
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

接下来，您必须定义一个将完成所有工作的存储过程。它的工作原理是将所有文件读入临时表，然后运行 ​​SQLCMD 命令来解析每个找到的 *.sql 文件。

CREATE PROCEDURE [Builder].[RunScriptsInFolder]
    @scriptsDir varchar(255)
AS

IF len(@scriptsDir) = 0
    RETURN 0

DECLARE @Message VARCHAR(254)
SET @Message = 'Loading files in ' + @scriptsDir + ' directory...'
RAISERROR (@Message, 0, 1) WITH NOWAIT 

DECLARE @FileList Table (FileNumber int identity(1,1), FileName varchar(255), Command varchar(2048))
DECLARE @OutputTable Table (Output varchar(MAX))
DECLARE @FileName varchar(255)
DECLARE @Command varchar(2048) 
DECLARE @FileNum int
DECLARE @databaseName varchar(255)

SET @databaseName = db_name()

SET @Command = 'DIR /B /O:-N ' + @scriptsDir + '*.sql'
INSERT INTO @FileList (FileName) EXEC xp_cmdshell @Command 
UPDATE @FileList SET Command = 'sqlcmd -d ' + @databaseName + ' -i "' + @scriptsDir + FileName + '"'

WHILE EXISTS(SELECT * FROM @FileList)
BEGIN
    SELECT TOP(1) @FileNum = FileNumber, @FileName = FileName, @Command = Command FROM @FileList 

    SET @FileName = '  :r ' + @FileName
    RAISERROR (@FileName, 0, 1) WITH NOWAIT 
    EXEC xp_cmdshell @Command

    DELETE FROM @FileList WHERE FileNumber = @FileNum
END
RETURN 0;

您现在要做的就是调用此存储过程（您需要传递包含 SQL 文件的文件夹的完整路径。您可以从 MSBuild 属性获取项目路径）。另请注意，我在对 RunScriptsInFolder 调用的调用周围添加了几行。因为您不知道文件夹中文件的执行顺序，所以您应该在执行之前禁用所有外键检查，并在完成后启用它们

RAISERROR ('Disabling all constraints...', 0, 1) WITH NOWAIT 
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

---- Run all files specified folder
BEGIN TRANSACTION
EXEC [Builder].[RunScriptsInFolder] '$(ProjectDir)Scripts\Post-Deployment\Data\'
COMMIT TRANSACTION

---- Enable all constraints
RAISERROR ('Re-enabling all constraints...', 0, 1) WITH NOWAIT 
exec sp_msforeachtable @command1="print '?'", @command2='ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

您可能还想知道为什么 $(ProjectDir) 变量不适合您。要启用它，请使用文本编辑器打开 *.dbproj 文件并在末尾添加此代码。

<PropertyGroup>
  <SetVariables>
    <Variable Name="ProjectDir" Value="$(ProjectDir)" />
  </SetVariables>
</PropertyGroup>

或者，您可以打开数据库项目属性，找到选项卡“变量”并添加设置变量 ProjectDir=$(ProjectDir)