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.
Note: before I can start working with Powershell I have to run the following command.
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUserI need to run the program using the following command:
.\get-data.ps1 It needs the .\ to run.