SQL Server - Como monitorar suas séries favoritas consumindo um Webservice JSON com a função JSON_VALUE e utilizando CLR (C#)

Visualizações: 844 views

Neste artigo

Tempo de Leitura: 4 minutos

Olá pessoal,
Boa noite!

Neste post, vou demonstrar a vocês como utilizar o novo recurso de tratamento de JSON do SQL Server 2016, que é retornando por uma procedure do CLR (C#) para retornar as informações de suas séries de TV favoritas. Essa é a uma forma legal e divertida de aprender um pouco mais sobre essas duas poderosas ferramentas do SQL Server.

Esse ideia foi inspirada no post Consumindo JSON com Ole Automation Procedures no SQL Server 2016, alertando sobre seus episódios favoritos (Off-Topic), do blog do Reginaldo Silva e resolvi demonstrar essa solução utilizando o CLR.

Caso você não saiba o que é CLR, saiba mais acessando o post Introdução ao SQL CLR (Common Language Runtime) no SQL Server.

Criando a Stored Procedure para Consulta à Web API

Para realizar as consultas ao Web API da TVMaze.com (http://www.tvmaze.com/api), o Reginaldo utilizou procedures OLE Automation, enquanto neste post vou utilizar a procedure stpWs_Requisicao, escrita em C# para ser utilizada no SQLCLR.

Código-fonte da Procedure stpWs_Requisicao:

using System.Data.SqlTypes;
using System.Net;
using System.IO;
using System.Text;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpWs_Requisicao(SqlString Ds_Url, SqlString Ds_Metodo, SqlString Ds_Parametros, SqlString Ds_Codificacao, out SqlString Ds_Retorno_OUTPUT)
    {

        var parametros = (Ds_Parametros.IsNull) ? "" : Ds_Parametros.Value;
        var metodo = (Ds_Metodo.IsNull) ? "GET" : Ds_Metodo.Value;
        var url = Ds_Url.Value;
        var feedData = string.Empty;
        var encoding = (Ds_Codificacao.IsNull) ? "UTF-8" : Ds_Codificacao.Value;

        if (string.IsNullOrEmpty(encoding))
            encoding = "UTF-8";

        var request = (HttpWebRequest) WebRequest.Create(url);
        request.Method = metodo;
        
        if (metodo == "POST" && parametros.Length > 0)
        {
            var data = parametros;
            var dataStream = Encoding.UTF8.GetBytes(data);

            request.ContentLength = dataStream.Length;

            using (var newStream = request.GetRequestStream())
            {
                newStream.Write(dataStream, 0, dataStream.Length);
                newStream.Close();
            }
        }


        using (var response = (HttpWebResponse) request.GetResponse())
        {
            using (var stream = response.GetResponseStream())
            {
                if (stream != null)
                {
                    using (var streamReader = new StreamReader(stream, Encoding.GetEncoding(encoding)))
                    {
                        feedData = streamReader.ReadToEnd();
                    }
                }
            }
        }

        Ds_Retorno_OUTPUT = feedData;

    }

};

using System.Data.SqlTypes;

using System.Net;

using System.IO;

using System.Text;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void stpWs_Requisicao(SqlString Ds_Url, SqlString Ds_Metodo, SqlString Ds_Parametros, SqlString Ds_Codificacao, out SqlString Ds_Retorno_OUTPUT)

{

var parametros = (Ds_Parametros.IsNull) ? "" : Ds_Parametros.Value;

var metodo = (Ds_Metodo.IsNull) ? "GET" : Ds_Metodo.Value;

var url = Ds_Url.Value;

var feedData = string.Empty;

var encoding = (Ds_Codificacao.IsNull) ? "UTF-8" : Ds_Codificacao.Value;

if (string.IsNullOrEmpty(encoding))

encoding = "UTF-8";

var request = (HttpWebRequest) WebRequest.Create(url);

request.Method = metodo;

if (metodo == "POST" && parametros.Length > 0)

{

var data = parametros;

var dataStream = Encoding.UTF8.GetBytes(data);

request.ContentLength = dataStream.Length;

using (var newStream = request.GetRequestStream())

{

newStream.Write(dataStream, 0, dataStream.Length);

newStream.Close();

}

using (var response = (HttpWebResponse) request.GetResponse())

{

using (var stream = response.GetResponseStream())

{

if (stream != null)

{

using (var streamReader = new StreamReader(stream, Encoding.GetEncoding(encoding)))

{

feedData = streamReader.ReadToEnd();

}

Ds_Retorno_OUTPUT = feedData;

}

};

Entendendo a função JSON_VALUE do SQL Server 2016

Uma vez que a Stored Procedure foi criada e publicada no seu database, agora você já pode criar a sua procedure de consulta de séries, utilizando o código abaixo, que também utiliza o novo recurso JSON_VALUE do SQL Server 2016, que permite manipular strings JSON nativamente pelo banco de dados.

Exemplos de uso do JSON_VALUE:

Criando a Stored Procedure para Buscar as Séries

Uma vez que a stpWS_Requisicao do CLR foi criada e você já entendeu como funciona a nova função JSON_VALUE, vamos criar a Stored Procedure que irá retornar as informações das suas séries.

Código-fonte da Stored Procedure:

CREATE PROCEDURE dbo.stpInformacoes_Serie (
    @Nome_Serie NVARCHAR(MAX)
)
AS BEGIN

    DECLARE 
        @URL NVARCHAR(MAX),
        @Retorno1 NVARCHAR(MAX),
        @Retorno2 NVARCHAR(MAX),
        @Retorno3 NVARCHAR(MAX)

    SET @URL = 'http://api.tvmaze.com/search/shows?q=' + @Nome_Serie

    -- Faz uma consulta ao webservice para consultar as informações da série
    EXEC CLR.dbo.stpWs_Requisicao 
        @Ds_Url = @URL, -- nvarchar(max)
        @Ds_Metodo = N'GET', -- nvarchar(max)
        @Ds_Parametros = N'', -- nvarchar(max)
        @Ds_Codificacao = N'UTF-8', -- nvarchar(max)
        @Ds_Retorno_OUTPUT = @Retorno1 OUTPUT -- nvarchar(max)


    -- Remove os caracteres [] do começo e fim da string
    SET @Retorno1 = SUBSTRING(@Retorno1, 2, LEN(@Retorno1) - 2)

    -- Recupera o ID da série
    DECLARE @Id_Serie INT = CAST(JSON_VALUE(@Retorno1,'$.show.id') AS INT)
    DECLARE @Serie NVARCHAR(100) = JSON_VALUE(@Retorno1,'$.show.name')

    -- Recupera informações do episódio anterior
    SET @Url = 'http://api.tvmaze.com/shows/' + CAST(@Id_Serie AS VARCHAR(5)) + '?embed=previousepisode'

    EXEC CLR.dbo.stpWs_Requisicao 
        @Ds_Url = @URL, -- nvarchar(max)
        @Ds_Metodo = N'GET', -- nvarchar(max)
        @Ds_Parametros = N'', -- nvarchar(max)
        @Ds_Codificacao = N'UTF-8', -- nvarchar(max)
        @Ds_Retorno_OUTPUT = @Retorno2 OUTPUT -- nvarchar(max)


    -- Recupera informações do próximo episódio
    SET @Url = 'http://api.tvmaze.com/shows/' + CAST(@Id_Serie AS VARCHAR(5)) + '?embed=nextepisode'

    EXEC CLR.dbo.stpWs_Requisicao 
        @Ds_Url = @URL, -- nvarchar(max)
        @Ds_Metodo = N'GET', -- nvarchar(max)
        @Ds_Parametros = N'', -- nvarchar(max)
        @Ds_Codificacao = N'UTF-8', -- nvarchar(max)
        @Ds_Retorno_OUTPUT = @Retorno3 OUTPUT -- nvarchar(max)


    -- Extrai as informações dos episódios com JSON_VALUE e guarda numa tabela
    IF (OBJECT_ID('tempdb..#Episodios') IS NOT NULL) DROP TABLE #Episodios
    CREATE TABLE #Episodios (
        Ds_Serie NVARCHAR(100),
        Ds_Tipo VARCHAR(50),
        Nr_Temporada INT,
        Ds_Episodio NVARCHAR(100),
        Nr_Episodio INT,
        Dt_Episodio DATE,
        Hr_Episodio NVARCHAR(5)
    )

    INSERT INTO #Episodios
    SELECT  
        @Serie AS Ds_Serie ,
        'Anterior' AS Ds_Tipo,
        JSON_VALUE(@Retorno2, '$._embedded.previousepisode.season') AS Nr_Temporada,
        JSON_VALUE(@Retorno2, '$._embedded.previousepisode.name') AS Ds_Episodio,
        JSON_VALUE(@Retorno2, '$._embedded.previousepisode.number') AS Nr_Episodio,
        JSON_VALUE(@Retorno2, '$._embedded.previousepisode.airdate') AS Dt_Episodio,
        JSON_VALUE(@Retorno2, '$._embedded.previousepisode.airtime') AS Hr_Episodio

    UNION ALL

    SELECT  
        @Serie AS Ds_Serie,
        'Próximo' AS Ds_Tipo,
        ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.season'), '') AS Nr_Temporada,
        ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.name'), '') AS Ds_Episodio,
        ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.number'), '') AS Nr_Episodio,
        ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.airdate'), '') AS Dt_Episodio,
        ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.airtime'), '') AS Hr_Episodio


    SELECT * FROM #Episodios


END

CREATE PROCEDURE dbo.stpInformacoes_Serie (

@Nome_Serie NVARCHAR(MAX)

)

AS BEGIN

DECLARE

@URL NVARCHAR(MAX),

@Retorno1 NVARCHAR(MAX),

@Retorno2 NVARCHAR(MAX),

@Retorno3 NVARCHAR(MAX)

SET @URL = 'http://api.tvmaze.com/search/shows?q=' + @Nome_Serie

-- Faz uma consulta ao webservice para consultar as informações da série

EXEC CLR.dbo.stpWs_Requisicao

@Ds_Url = @URL, -- nvarchar(max)

@Ds_Metodo = N'GET', -- nvarchar(max)

@Ds_Parametros = N'', -- nvarchar(max)

@Ds_Codificacao = N'UTF-8', -- nvarchar(max)

@Ds_Retorno_OUTPUT = @Retorno1 OUTPUT -- nvarchar(max)

-- Remove os caracteres [] do começo e fim da string

SET @Retorno1 = SUBSTRING(@Retorno1, 2, LEN(@Retorno1) - 2)

-- Recupera o ID da série

DECLARE @Id_Serie INT = CAST(JSON_VALUE(@Retorno1,'$.show.id') AS INT)

DECLARE @Serie NVARCHAR(100) = JSON_VALUE(@Retorno1,'$.show.name')

-- Recupera informações do episódio anterior

SET @Url = 'http://api.tvmaze.com/shows/' + CAST(@Id_Serie AS VARCHAR(5)) + '?embed=previousepisode'

EXEC CLR.dbo.stpWs_Requisicao

@Ds_Url = @URL, -- nvarchar(max)

@Ds_Metodo = N'GET', -- nvarchar(max)

@Ds_Parametros = N'', -- nvarchar(max)

@Ds_Codificacao = N'UTF-8', -- nvarchar(max)

@Ds_Retorno_OUTPUT = @Retorno2 OUTPUT -- nvarchar(max)

-- Recupera informações do próximo episódio

SET @Url = 'http://api.tvmaze.com/shows/' + CAST(@Id_Serie AS VARCHAR(5)) + '?embed=nextepisode'

EXEC CLR.dbo.stpWs_Requisicao

@Ds_Url = @URL, -- nvarchar(max)

@Ds_Metodo = N'GET', -- nvarchar(max)

@Ds_Parametros = N'', -- nvarchar(max)

@Ds_Codificacao = N'UTF-8', -- nvarchar(max)

@Ds_Retorno_OUTPUT = @Retorno3 OUTPUT -- nvarchar(max)

-- Extrai as informações dos episódios com JSON_VALUE e guarda numa tabela

IF (OBJECT_ID('tempdb..#Episodios') IS NOT NULL) DROP TABLE #Episodios

CREATE TABLE #Episodios (

Ds_Serie NVARCHAR(100),

Ds_Tipo VARCHAR(50),

Nr_Temporada INT,

Ds_Episodio NVARCHAR(100),

Nr_Episodio INT,

Dt_Episodio DATE,

Hr_Episodio NVARCHAR(5)

)

INSERT INTO #Episodios

SELECT

@Serie AS Ds_Serie ,

'Anterior' AS Ds_Tipo,

JSON_VALUE(@Retorno2, '$._embedded.previousepisode.season') AS Nr_Temporada,

JSON_VALUE(@Retorno2, '$._embedded.previousepisode.name') AS Ds_Episodio,

JSON_VALUE(@Retorno2, '$._embedded.previousepisode.number') AS Nr_Episodio,

JSON_VALUE(@Retorno2, '$._embedded.previousepisode.airdate') AS Dt_Episodio,

JSON_VALUE(@Retorno2, '$._embedded.previousepisode.airtime') AS Hr_Episodio

UNION ALL

SELECT

@Serie AS Ds_Serie,

'Próximo' AS Ds_Tipo,

ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.season'), '') AS Nr_Temporada,

ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.name'), '') AS Ds_Episodio,

ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.number'), '') AS Nr_Episodio,

ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.airdate'), '') AS Dt_Episodio,

ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.airtime'), '') AS Hr_Episodio

SELECT * FROM #Episodios

END

Exemplos de utilização da Procedure:

Uma ótima dica que o Reginaldo nos deu, é de utilizar o site JSON Formatter & Validator para melhorar a visualização do JSON retornado pela consulta ao Webservice, conforme exemplo abaixo.

JSON original:

JSON Formatado:

É isso aí, pessoal!
Espero que tenham gostado do post.

Caso vocês queiram criar um monitoramento automatizado através de um Job no SQL Server Agent, que te envie e-mail quando estiver próximo de algum episódio ser lançado, não deixe de conferir o post que eu citei do blog do Reginaldo Silva, que tem essa solução lá.

Abraço!

FERNANDO PANCETTI disse:

2 de abril de 2018 às 13:58

Boa tarde Dirceu.

Seu post me ajudou muito.
Mas tenho um problema…
O que fazer se o retorno da API vem truncado na variável nvarchar(MAX)?

@Ds_Retorno_OUTPUT = @Retorno1 OUTPUT — nvarchar(max)

Grato.
Fernando Pancetti

Responder

Assinar blog por e-mail

Visualizações do Blog

Microsoft MVP

Arquivo de Posts

Categorias

Posts recentes

SQL Server – Como monitorar suas séries favoritas consumindo um Webservice JSON com a função JSON_VALUE e utilizando CLR (C#)

Criando a Stored Procedure para Consulta à Web API

Entendendo a função JSON_VALUE do SQL Server 2016

Criando a Stored Procedure para Buscar as Séries

You may also like...

1 Response

Deixe uma respostaCancelar resposta

Criando a Stored Procedure para Consulta à Web API

Entendendo a função JSON_VALUE do SQL Server 2016

Criando a Stored Procedure para Buscar as Séries

You may also like...

SQL Server – Como gerar um histórico de deadlocks para análise de falhas em rotinas

Como descobrir a data do último acesso a uma tabela ou view e execução da uma procedure no SQL Server

SQL Server – Como criptografar e descriptografar senhas (com Salt) utilizando o CLR (C#)

1 Response

Deixe uma respostaCancelar resposta