PowerShell

In this section

• Mysql

  Example

   1# Import values with details connexion
   2. .\values.ps1
   3
   4$scriptFilePath ="$MyPath\Install\MysqlBase\Script.sql"
   5
   6# Load the required DLL file (depend on your connector)
   7[void][System.Reflection.Assembly]::LoadFrom("C:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.23\Assemblies\v4.5.2\MySql.Data.dll")
   8
   9# Load in var the SQL script file
  10$scriptContent = Get-Content -Path $scriptFilePath -Raw
  11
  12# Execute the modified SQL script
  13$Connection = [MySql.Data.MySqlClient.MySqlConnection]@{
  14    ConnectionString = "server=$MysqlIP;uid=$MysqlUser;Port=3306;user id=$MysqlUser;pwd=$MysqlPassword;database=$MysqlDatabase;pooling=false;CharSet=utf8;SslMode=none"
  15    }
  16    $sql = New-Object MySql.Data.MySqlClient.MySqlCommand
  17    $sql.Connection = $Connection
  18    $sql.CommandText = $scriptContent
  19    write-host $sql.CommandText
  20    $Connection.Open()
  21    $sql.ExecuteNonQuery()
  22    $Connection.Close()
  

• Parsing

  POO

  1# Convert your json in object and put it in variable
  2$a = Get-Content 'D:\temp\mytest.json' -raw | ConvertFrom-Json
  3$a.update | % {if($_.name -eq 'test1'){$_.version=3.0}}
  4
  5$a | ConvertTo-Json -depth 32| set-content 'D:\temp\mytestBis.json'
  

  Example updating a XML

   1#The file we want to change
   2$xmlFilePath = "$MyPath\EXAMPLE\some.config"
   3
   4   # Read the XML file content
   5   $xml = [xml](Get-Content $xmlFilePath)
   6
   7   $node = $xml.connectionStrings.add | where {$_.name -eq 'MetaData' -And $_.providerName -eq 'MySql.Data.MySqlClient'}
   8   $node.connectionString = $AuditDB_Value
   9
  10   $node1 = $xml.connectionStrings.add | where {$_.name -eq 'Account'}
  11   $node1.connectionString = $Account_Value
  12
  13   # Save the updated XML back to the file
  14   $xml.Save($xmlFilePath)
  15
  16   Write-Host "$xmlFilePath Updated"
  

  Nested loop between a JSON and CSV

   1# Read the JSON file and convert to a PowerShell object
   2$jsonContent = Get-Content -Raw -Path ".\example.json" | ConvertFrom-Json
   3
   4# Read CSV and set a Header to determine the column
   5$csvState = Import-CSV -Path .\referentials\states.csv -Header "ID", "VALUE"  -Delimiter "`t"
   6# Convert in object
   7$csvState | ForEach-Object { $TableState[$_.ID] = $_.VALUE  }
   8
   9# Loop through the Entities array and look for the state
  10foreach ($item in $jsonContent.Entities) {
  11    $stateValue = $item.State
  12
  13    # Compare the ID and stateValue then get the Value
  14    $status = ($csvState | Where-Object { $_.'ID' -eq $stateValue }).VALUE
  15
  16    Write-Host "Status: $status"
  17}
  

  Sources

  https://devblogs.microsoft.com/powershell-community/update-xml-files-using-powershell/