如何在PowerShell健康检查脚本中通过SqlConnection执行SQL种子文件并获取结果

现有Docker容器内的PowerShell健康检查脚本通过System.Data.SqlClient.SqlConnection验证SQL实例状态，要求不更换Cmdlet，在健康检查成功时执行同目录下的SQL种子文件初始化数据库，同时处理Docker Compose循环执行（每10秒一次，共10次）的情况，避免重复执行种子数据。

修改后的完整脚本 #

$server = 'cas,1433'
$username = 'sa'
$password = 'myPass'
$database = 'cas'
# 假设种子文件为SQL格式，若为PowerShell脚本可调整后缀为.ps1
$seedFilePath = Join-Path -Path $PSScriptRoot -ChildPath 'seed_data.sql'

try {
    $connectionString = "Server=$server;Database=$database;Uid=$username;Pwd=$password"
    $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $connectionString
    $connection.Open()

    # 执行健康检查基础查询
    $command = $connection.CreateCommand()
    $command.CommandText = "SELECT 1"
    $result = $command.ExecuteScalar()

    if ($result -eq 1) {
        Write-Host "健康检查成功"

        # 检查种子数据是否已执行（避免重复初始化）
        $command.CommandText = @"
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'SeedExecutionLog')
BEGIN
    CREATE TABLE SeedExecutionLog (
        ExecutionId INT IDENTITY(1,1) PRIMARY KEY,
        ExecutionTime DATETIME DEFAULT GETDATE(),
        Status NVARCHAR(50)
    )
END
SELECT COUNT(*) FROM SeedExecutionLog WHERE Status = 'Success'
"@
        $seedExecutedCount = $command.ExecuteScalar()

        if ($seedExecutedCount -eq 0) {
            Write-Host "开始执行种子数据文件"
            # 验证种子文件是否存在
            if (Test-Path -Path $seedFilePath) {
                $seedSql = Get-Content -Path $seedFilePath -Raw

                # 执行种子SQL并捕获结果
                $command.CommandText = $seedSql
                try {
                    $command.ExecuteNonQuery()
                    $out = "种子数据执行成功"
                    Write-Host $out

                    # 记录执行成功状态
                    $command.CommandText = "INSERT INTO SeedExecutionLog (Status) VALUES ('Success')"
                    $command.ExecuteNonQuery()
                }
                catch {
                    $out = "种子数据执行失败: $($_.Exception.Message)"
                    Write-Host $out
                    # 记录失败状态
                    $command.CommandText = "INSERT INTO SeedExecutionLog (Status) VALUES ('Failed')"
                    $command.ExecuteNonQuery()
                }
            }
            else {
                $out = "未找到种子数据文件: $seedFilePath"
                Write-Host $out
            }
        }
        else {
            $out = "种子数据已执行过，跳过"
            Write-Host $out
        }

        $connection.Close()
        exit 0
    }
    else {
        Write-Host "健康检查失败"
        $connection.Close()
        exit 1
    }
}
catch {
    Write-Host "健康检查异常: $($_.Exception.Message)"
    if ($connection.State -eq 'Open') {
        $connection.Close()
    }
    exit 1
}

关键修改说明 #

• 避免重复执行：新增SeedExecutionLog表记录种子执行状态，每次健康检查成功后先查询该表，仅当未成功执行过才运行种子文件
• 路径处理：用$PSScriptRoot（脚本所在目录）结合Join-Path确保正确获取同目录下的种子文件路径
• 执行与结果捕获：通过现有SqlCommand执行种子SQL，捕获执行异常并记录状态，同时将结果赋值给$out并打印
• 连接安全：在异常块中判断连接状态后再关闭，避免因连接未打开导致额外报错

内容的提问来源于stack exchange，提问作者Jacques Ramsden