SQL Server - How to monitor your favorite series by consuming a JSON Webservice with JSON_VALUE function and using CLR (C #)

Views: 497
Reading Time: 4 minutes

Hello people,
Good night!

In this post, I will demonstrate to you how to use the new SQL Server 2016 JSON handling feature, which is returning by a CLR (C #) procedure to return information from your favorite TV series. This is a cool and fun way to learn a little more about these two powerful SQL Server tools.

This idea was inspired by the post. Consuming JSON with Ole Automation Procedures in SQL Server 2016, Warning of Your Favorite Episodes (Off-Topic), from Reginaldo Silva's blog and decided to demonstrate this solution using the CLR.

If you do not know what is CLR, learn more by accessing the post Introduction to SQL Common Language Runtime (CLR) in SQL Server.

Creating the Stored Procedure for Web API Query

To perform queries to the TVMaze.com Web API (http://www.tvmaze.com/api), Reginaldo used OLE Automation procedures, while in this post I will use the procedure stpWs_Requisicao, written in C # to be used in SQLCLR.

Procedure source code stpWs_Request:

Understanding the SQL Server 2016 JSON_VALUE Function

Once the Stored Procedure has been created and published to your database, you can now create your series query procedure using the code below, which also uses the new SQL Server 2016 JSON_VALUE feature, which allows you to manipulate JSON strings natively. by the database.

Examples of using JSON_VALUE:

Creating the Stored Procedure to Search the Series

Once the CLR stpWS_Resquisition has been created and you already understand how the new JSON_VALUE function works, let's create the Stored Procedure that will return the information from your series.

Stored Procedure Source Code:

Procedure usage examples:

A great tip that Reginaldo gave us is to use the site JSON Formatter & Validator to improve the JSON view returned by the Webservice query, as shown below.

Original JSON:

JSON Formatted:

That's it folks!
I hope you enjoyed the post.

If you want to create automated monitoring through a Job in SQL Server Agent, which will email you when an episode is coming up, check out the post I quoted from Reginaldo Silva's blog, which has this solution. over there.

Hug!