如何确保递归执行SQL文件的所有Invoke-Sqlcmd命令在同一事务内运行

原来的实现中每次调用Invoke-Sqlcmd都会创建独立的数据库会话，天然无法共享事务，所以需要从「复用同一个数据库连接、全局控制事务生命周期」两个核心方向改造，保证所有SQL操作要么全部执行成功，要么全部回滚不生效。

# 数据库连接与执行配置
$ServerInstance = "替换为你的SQL实例地址"
$DBName = "替换为目标数据库名称"
$SvcAdminAccount = "替换为数据库用户名"
$SvcAdminPassword = "替换为数据库密码"
$execRootPath = "替换为SQL文件根目录"

# 递归获取所有排序后的.sql文件
function Get-AllSqlFiles($path = $pwd) {
    $sqlFileList = @()
    # 先收集当前目录下的SQL文件，按文件名升序排列
    $currentLevelFiles = Get-ChildItem -Path $path -Filter *.sql | Sort-Object -Property Name
    $sqlFileList += $currentLevelFiles
    # 按目录名升序遍历子目录，递归收集文件
    $subFolders = Get-ChildItem -Path $path -Directory | Sort-Object -Property Name
    foreach ($folder in $subFolders) {
        Write-Host "`n扫描子目录: $($folder.FullName)"
        $sqlFileList += Get-AllSqlFiles $folder.FullName
    }
    return $sqlFileList
}

# 带事务控制的主执行逻辑
try {
    # 初始化持久化数据库连接
    $connString = "Server=$ServerInstance;Database=$DBName;User ID=$SvcAdminAccount;Password=$SvcAdminPassword;Connect Timeout=60;"
    $dbConn = New-Object System.Data.SqlClient.SqlConnection($connString)
    $dbConn.Open()
    # 开启全局事务
    $globalTran = $dbConn.BeginTransaction()
    Write-Host "全局数据库事务已开启"

    # 提前收集所有待执行的SQL文件
    $allExecFiles = Get-AllSqlFiles $execRootPath
    Write-Host "`n共扫描到$($allExecFiles.Count)个待执行SQL文件"

    # 逐文件执行SQL语句
    foreach ($sqlFile in $allExecFiles) {
        Write-Host "正在执行文件: $($sqlFile.FullName)"
        # 读取SQL文件内容
        $sqlContent = Get-Content -Path $sqlFile.FullName -Raw -Encoding UTF8
        # 绑定连接与全局事务执行
        $sqlCommand = New-Object System.Data.SqlClient.SqlCommand($sqlContent, $dbConn, $globalTran)
        $sqlCommand.CommandTimeout = 65535
        $sqlCommand.ExecuteNonQuery() | Out-Null
    }

    # 全部执行无异常，提交事务
    $globalTran.Commit()
    Write-Host "`n所有SQL执行完成，事务已提交，所有操作已生效"
}
catch {
    # 任何异常触发事务回滚
    if ($globalTran) {
        $globalTran.Rollback()
        Write-Host "`n执行出错，事务已回滚，所有操作未生效"
    }
    Write-Error "执行失败，错误详情: $_"
    throw $_
}
finally {
    # 统一释放资源
    if ($dbConn.State -eq 'Open') {
        $dbConn.Close()
        $dbConn.Dispose()
    }
    if ($globalTran) {
        $globalTran.Dispose()
    }
}

• 拆分文件遍历与执行逻辑：先递归扫描全量SQL文件并按规则排序，避免遍历过程中执行出错还要额外做清理操作
• 复用同一数据库连接：全程只创建一个持久化的数据库连接，所有SQL操作都在该连接上执行，保证事务可以共享
• 全局事务控制：执行前主动开启事务，所有SQL命令都绑定该事务；全部执行成功则提交，任何步骤报错则直接回滚
• 资源自动释放：通过finally块保证不管执行成功还是失败，都会主动关闭连接、释放事务资源，避免连接泄漏

• 如果你的SQL脚本中包含GO批处理分隔符，原生SqlCommand无法识别该非T-SQL关键字，需要额外引入SQL Server SMO组件的执行方法，或提前手动按GO拆分脚本逐段执行
• 所有SQL脚本内部不要手动编写BEGIN TRANSACTION、COMMIT、ROLLBACK这类事务操作语句，避免和外层全局事务产生冲突
• 事务执行时长根据所有SQL总执行时长决定，注意调整数据库的事务超时相关配置，避免事务被自动中断

内容的提问来源于stack exchange，提问作者Alex Gordon