Using Powershell to Create JSON From SQL Server

While searching for ways to generate JSON from SQL Server I found a video on using Powershell to generate JSON using calls to SQL Server. This looked interesting so I tried it out and it worked. Unfortunately it still didn’t validate and just generated rubbish. I loaded the data into a text editor and found that when it hit the line return point it chopped data and added three dots to the end of each line.

This is the code I used to generate data from Powershell. It is just using .Net framework commands to connect to SQL Server and is not too much different to the way I manipulate SQL Server from C# code.

 1    # Establish connection to SQL Server
 2    $Instance = "LION\TIGER"
 3    $ConnectionString = "Server=$Instance;Database=RecordDB;
 4        User Id=sa;Password=passwordhere;integrated Security=false;"
 5
 6    # Main query
 7    $query = "SELECT a.ArtistId, a.FName, a.LName, a.Name AS ArtistName, 
 8    r.RecordId,r.Name, r.Field, r.Recorded, r.Label,  r.  Pressing, r.Rating, 
 9    r.Discs, r.Media, r.Bought, r.Cost FROM Artist a 
10    INNER JOIN Record r ON a.ArtistId = r.ArtistId 
11    ORDER  BY a.LName, a.FName, r.recorded FOR JSON path, root"
12
13    $connection = New-Object System.Data.SqlClient.SqlConnection
14    $connection.ConnectionString = $ConnectionString
15
16    $connection.Open()
17    $command = $connection.CreateCommand()
18
19    $command.CommandText = $query
20
21    $result = $command.ExecuteReader()
22
23    $table = New-Object "System.Data.DataTable"
24
25    $table.Load($result)
26
27    $table | select $table.Columns.ColumnName | ConvertTo-Json
28
29    $connection.Close()

If I remove the JSON section from the query it just generates a dump of the data which is useful.

Powershell data dump.

Note: before I can start working with Powershell I have to run the following command.

  Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

I need to run the program using the following command:

  .\get-data.ps1

It needs the .\ to run.