如何使用PowerShell批量调用Update-Database [英] How to use PowerShell to batch call Update-Database

问题描述

我们使用Azure Elastic Pool生成多个客户端数据库和一个引用该客户端数据库的主数据库。

We use an Azure Elastic Pool resulting in multiple client databases and one master database with references to the client database.

我们已经有多个数据库，并且正在开发一个新版本的代码。我们使用EF6代码优先。
更改模型（添加属性）后，我们将创建迁移文件，并且需要为所有现有客户端数据库调用 Update-Database 。
这是我们要跳过的猴子工作。

We already have multiple databases and are working on a new version of the code. We use EF6 Code-First. When we make a change to our model (add a property) we create the migration file and need to call Update-Database for all existing client databases. This is monkey work we want to skip.

我已经有一个Powershell脚本来连接到master数据库并在表上执行查询。这将返回子数据库的名称。
有了它，我可以更改Web.config并将模板数据库名称替换为子数据库的正确名称。

I already have a Powershell script to connect to the master database and execute a query on a table. This returns the names of the child databases. With it I can change the Web.config and replace the Template database name with the proper name of the child database.

现在我需要调用更新数据库执行迁移脚本。在最后一部分中，我很努力，因为我在Visual Studio之外运行ps1-script，因此命令 Update-database 是未知的。我尝试使用 migrate.exe ，但随后出现很多错误。

Now I need to call Update-Database to execute the migration scripts. With this last part I'm struggling because I'm running the ps1-script outside Visual Studio and thus the command Update-database is unknown. I tried using migrate.exe but then I get lots of errors.

我认为最简单的解决方案是运行 Package Manager控制台中的脚本，但是我不知道该怎么做。

I think the easiest solution is to run my script within the Package manager console but I can't figure out how to do that.

推荐答案

我设法使其正常运行。将ps1-文件放置在代码文件夹的根目录中之后，可以使用 .\UpdateDatabases.ps1 在Package Manager控制台中运行它。

I managed to get it working. After I placed the ps1-file in the root of my code folder I could run it in the Package Manager Console using .\UpdateDatabases.ps1.

为完整起见，这是我创建的脚本。我是PowerShell的新手，因此可能可以进行一些优化。

For completeness here's the script I created. I'm new to PowerShell so some optimizations might be possible.

cls
$currentPath = (Get-Item -Path ".\" -Verbose).FullName
#Read Web.config
$webConfig = $currentPath + "\<your project>\Web.config"

$doc = (Get-Content $webConfig) -as [Xml]
$DatabaseNamePrefix = $doc.configuration.appSettings.add | where {$_.Key -eq 'DatabaseNamePrefix'}

#Get Master connectionstring
$root = $doc.get_DocumentElement();
foreach($connString in $root.connectionStrings.add | where {$_.Name -eq "Master"})
{
   $masterConn = $connString.connectionString
}

#Connect to master database
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $masterConn

#Query Client table for the child database names
$SqlQuery = "select Code from Clients"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
#Put query result in dataset
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

foreach ($row in $DataSet.Tables[0].Rows)
{
    $clientDbName = $row[0].ToString().Trim()    
    #Change Web.Config
    foreach($connString in $root.connectionStrings.add | where {$_.Name -eq "DevelopmentDb"})
    {
       $newDatabaseName = "Database=" + $DatabaseNamePrefix.value + $clientDbName + ";";
       $newConn = $connString.connectionString -replace "(Database=.*?;)",$newDatabaseName
       $connString.connectionString = $newConn;
    }
    $doc.Save($webConfig)

    #Update database
    Update-Database -ConfigurationTypeName Application
}    
"Finished"

这篇关于如何使用PowerShell批量调用Update-Database的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！