Access the database by connection string

Question

Context: I am trying to automate a few steps in the existing Sitecore 10.1.1 environment using Sitecore PowerShell.

Problem: We have a command Get-Database to access the database either by providing the database name or the item. In my scenario, I need to access the database by providing the connection string.

Do we have a way?

Answer 1

The ISE includes a sample snippet SQL script.

Import-Function -Name Invoke-SqlCommand

$connection = [Sitecore.Configuration.Settings]::GetConnectionString("master")

$query = @"
SELECT TOP (10) *
  FROM [dbo].[Items]
"@
$records = Invoke-SqlCommand -Connection $connection -Query $query

Answer 2

You can get the database details using this way by passing the connection string.

# Using $AppPath you will get your application folder path
# Then append the path of connectionstring.config
$path = $AppPath + 'App_Config\ConnectionStrings.config'

# Get the content of this file
$content = [xml](Get-Content -Path $path)

# Give your connection string to read the node
$content.SelectSingleNode("connectionStrings/add[@connectionString='Data Source=RP-INTUNE-5VUYB\MSSQLSERVER01;Initial Catalog=Sitecore10.1_Core;User ID=coreuser;Password=uiFxkSw6tlyKaScBgZre']")

This will give you the output of your connection string like this.

Now if you want to read the connection string name then you need to get the value in a variable like this.

$connectionString = $content.SelectSingleNode("connectionStrings/add[@connectionString='Data Source=RP-INTUNE-5VUYB\MSSQLSERVER01;Initial Catalog=Sitecore10.1_Core;User ID=coreuser;Password=uiFxkSw6tlyKaScBgZre']")

And then get your connection string name.

Write-Host $connectionString.name

You will get this.