Replace Database connection string using PowerShell

Question

I am working on a script that should display the current database a program is connected to, and also give an option to replace with a new database (either by manually entering the database name but preferably listing all databases on the local server \ instance and give users "number selection" to select the database they want to use.

The connection string is saved in a text file called server.exe.config below is an example of what the file contains, its not the only data in the config file obviously

<add key="persistency.connection" value="data source=MyDatabaseServer;initial catalog=MyDatabase1;Integrated Security=True" />

I can use Get-Content to see the entire file and also use Where-Object {$_ -like "*initial catalog=*"} to see the only line which has the database configuration.

But I think this will be difficult for users to interpret what database is being used so if possible need a command that will display just the database name that is in the config file instead of the entire line, save that database name for future replacement when the user selects a new database to be replaced into the config file.

Possible?

Assuming the connection string is in the appSettings section, you could do something like ([xml](Get-Content "server.exe.config")).appSettings.add | foreach { $_.value.Split(";") | Where-Object {$_ -like "data source=*"} } to extract the value. — Dan Guzman
– Dan Guzman, Commented May 9, 2020 at 2:17

mklement0 · Accepted Answer · 2022-09-21 15:04:21Z

While plain-text processing, as shown in your own answer, works in simple cases, for robustness it is usually preferable to use XML parsing, such as via the Select-Xml cmdlet:

$file = './server.exe.config'

# Extract the XML element of interest, using an XPath query
$xpathQuery = '/configuration/appSettings/add[@key = "persistency.connection"]'
$el = (Select-Xml $xpathQuery $file).Node

# Replace the database name in the element's value...
$newDatabase = 'NewDatabase'
$el.value = $el.value -replace '(?<=\binitial catalog=)[^;]+', $newDatabase

# ... and save the modified document back to the file.
# IMPORTANT: Be sure to use a *full* path to the output file,
#            because PowerShell's current directory (location)
#            usually differs from .NET's; Convert-Path ensures that.
$el.OwnerDocument.Save((Convert-Path $file))

Note that this technique works in principle for any XML file, not just .config.exe files.

mklement0 · Accepted Answer · 2020-05-09 05:46:21Z

0

I think this is simpler and works for me

$ConnString = Get-Content -Path $filepath\server.exe.Config | Where-Object {$_ -like "*initial catalog=*"}
$ConnString -split '\s*;\s*' | ForEach-Object -Process {
    if ($_ -imatch '^initial catalog=(?<dbname>.+)$') {
        $ConnStringdb = $Matches.dbname
    }
}

I am then using the following simply to replace the text above back into the config file after a database name is enter - Manually. would love if I could get it to work automatically by maybe building an array or something.

Write-Host "Please Enter the database name you want to use for Production(Don't Leave Blank): "  -NoNewline; $NewDatabaseConfig = Read-Host;
(Get-Content -Path $filepath\server.exe.Config -Raw) -Replace $ConnStringdb,$NewDatabaseConfig | Set-Content -Path $IPSServerProd\UBERbaseOfficeServer.exe.Config

edited May 9, 2020 at 5:46

mklement0

452k68 gold badges728 silver badges987 bronze badges

answered May 9, 2020 at 5:23

Aasim Shakil Pathan

611 silver badge10 bronze badges

1 Comment

mklement0 Over a year ago

That works, but there's no reason not to use proper XML parsing - which is always the more robust choice. I've updated my answer with a more PowerShell-idiomatic solution based on Select-Xml.

Collectives™ on Stack Overflow

Replace Database connection string using PowerShell

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related