Consume REST Service From PowerShell And Update The JSON Data To SQL Server Table

Introduction

PowerShell has the ability to consume REST services and work with the JSON data returned from the service. In this article we will see how to consume REST services from PowerShell and update the returned JSON data to SQL Server.

Prerequisite

Before getting started with the demo, let's set up the environment and get to know the REST web service that we will be using in this example.

REST web service 

Though we can create a custom REST service, for the time being we can try out an already existing one. We will be making use of the Free RESTful web services available here which we will consume from PowerShell. We shall make use of the specific REST web service that gets the states and territories of the US.

The REST end point that we will consume is -

http://services.groupkt.com/state/get/USA/all

PowerShell Version 3.0

We are using the cmdlet ‘Invoke-WebRequest’, which was introduced in V3.0, to invoke REST Web service and get data from the web. So we have to ensure that the system has PowerShell version 3.0 or above. We can check the version by running the below command.

1. (Get-Host).Version
   
   
   

Set up SQL Server Table

Once we have consumed the REST Web service from PowerShell, we will convert the data from JSON and update the SQL Server table directly from PowerShell. So, we will need a table with the columns that correspond to the REST Service data. We will use the below command to create the SQL Table.

CREATE TABLE USCountryDetails
(
Country varchar(50),
Name varchar(50),
Capital varchar(50),
LargestCity varchar(50)
);

Invoke REST Web Service from PowerShell

REST web service endpoint can be invoked from PowerShell using the ‘Invoke-WebRequest’ cmdlet. Once we have the returned data, it will be in JSON format but to work with it we have to convert it to PowerShell object. The ConvertFrom-Json cmdlet will be used to convert the JavaScript Object Notation (JSON) formatted string to a custom PSCustomObject object that has a property for each field in the JSON string. Similarly, to generate a JSON string from any object, we can use the ConvertTo-Json cmdlet.

Since the properties (country,capital,largest_city) are stored in the ‘RestResponse’ and ‘result’ property as embedded objects, we need to expand those properties using ‘-expand’ attribute and then choose the required properties from the embedded object.

Invoking the Web Request and retrieving the required properties after running the ConvertFrom-JSON command will fetch us the below results,

Code

$request = 'http://services.groupkt.com/state/get/USA/all'
$result = Invoke-WebRequest $request
$JSONResult = $result | ConvertFrom-Json | select -expand RestResponse | select -expand result
$JSONResult | Select country,capital,largest_city| Sort-Object capital

Update table with REST data

After we have converted the JSON returned data from the REST service, we will use it to update the SQL Server table using traditional ADO connection. We will create the connection object using System.Data.SqlClient namespace and open the connection. After converting the returned REST Service JSON data to custom PowerShell object, we will loop through it using the foreach loop.

foreach($obj in $JSONResult) {}

Within the foreach loop we will get the PowerShell object properties and update the SQL Table by calling the ExecuteNonQuery method.

$cmd.CommandText ="INSERT USCountryDetails VALUES (@Country, @Name,@Capital, @LargestCity)"
$cmd.Parameters.AddWithValue("@Country", $obj.country);
$cmd.Parameters.AddWithValue("@Name", $obj.name);
$cmd.Parameters.AddWithValue("@Capital", $obj.capital);
$cmd.Parameters.AddWithValue("@LargestCity", $obj.largest_city);
$cmd.ExecuteNonQuery()
$cmd.Parameters.clear();

Test the flow

We will test the entire flow by copying the below code to PS1 file and running it from PowerShell console as shown below.

Full Code

The entire code base for the demo is shared below,

$conn = New - Object System.Data.SqlClient.SqlConnection("Data Source=SP2013S1; Initial Catalog=DB; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
$request = 'http://services.groupkt.com/state/get/USA/all'
$result = Invoke - WebRequest $request
$JSONResult = $result | ConvertFrom - Json | select - expand RestResponse | select - expand result
$JSONResult | Select country, name, capital, largest_city | Sort - Object name
foreach($obj in $JSONResult) {
    $cmd.CommandText = "INSERT USCountryDetails VALUES (@Country, @Name,@Capital, @LargestCity)"
    $cmd.Parameters.AddWithValue("@Country", $obj.country);
    $cmd.Parameters.AddWithValue("@Name", $obj.name);
    $cmd.Parameters.AddWithValue("@Capital", $obj.capital);
    $cmd.Parameters.AddWithValue("@LargestCity", $obj.largest_city);
    $cmd.ExecuteNonQuery()
    $cmd.Parameters.clear();
}
$conn.Close()

After running the script and checking the SQL Server table, we can see that the data returned from REST web service has been updated to the SQL Server table.

Summary

Thus, we saw how to consume REST web service from Powershell, convert the JSON data to custom powershell object, and finally update it to SQL Server table.