Merging dictionaries within powershell

I needed to join dictionaries from within a list together, so that they become one whole dictionary.
First I thought it may be a trivial task. Than I used google and realized, that everything on the first page was just wrong or impracticable for lists with unknown size.

What I found, was stuff like:

$a = @{"foo"=1}
$b = @{"bar"=2}
$c = @($a,$b) | ForEach-Object {$_}


$a = @{"foo"=1}
$b = @{"bar"=2}
$c = $a + $b

The console output of $c will look identical for both:

Name                           Value
----                           -----
foo                            1
bar                            2

But the issue is with the data structure. This becomes a problem when we would like to access the data.


Will output $null for the first example and “2” for the 2nd. But why is this?
Lets convert it to json (without piping, this is important) to see:

ConvertTo-Json -InputObject $c

which outputs for the first case:

    "foo": 1
    "bar": 2

and for the 2nd what we originally expected:

  "bar": 2,
  "foo": 1

So now we know that the first one did not join the dictionaries, but warped them into a list. As we originally looked for a solution to join a list of dictionaries, this is not helpful.
(From now on $c is the output from the first example, as it is the same as my original input)
The 2nd one however looks helpful, but has two issues:

  1. It only works if we know how many elements we have (hard coded element count)
  2. It copies the list once for each element we want to add to it.

So let’s address these issues:

  1. For the 1st one something like this will do the trick:
    $c | ForEach-Object -Begin {[Hashtable]$aa = @{}} -Process {$aa += $_} -End {$aa} | ConvertTo-Json
  2. But for the 2nd we need something a bit more advanced like
    $c | ForEach-Object -Begin {[Hashtable]$aa = @{}} -Process {foreach($element in ($_.GetEnumerator())) {$aa.Add($element.Key,$element.Value)}} -End {$aa} | ConvertTo-Json

PowerShell and SQL Server

To be able to use the “sqlps” PowerShell Module you first need to install it from: Link

  • SQLSysClrTypes.msi – CLR Types for SQL Server
  • SharedManagementObjects.msi – Shared Management Objects
  • PowerShellTools.msi – PowerShell Extension for SQL Server

For information on how using this cmdlets, look it up in the ISE or here.

Using .net api:

# SQL-Server settings
$Database = "Database" # Database name
$Server = "SERVER\SQLEXPRESS"; # SQL-Server Instanz
# Connect to SQL and query data, extract data to SQL Adapter
$SqlQuery = "SELECT [Report],[Filiale],[E-Mail] FROM [dbo].[verteiler]"; # The query

## Example Database Layout
## "Report","Filiale","E-Mail"
## "012","12",""
## "033","33",""
## "112","112",""
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null
$objTable = $DataSet.Tables[0]