SQL Server - Como consultar os feriados nacionais, estaduais, municipais e facultativos de uma API utilizando OLE Automation e CLR (C#)

Post Views 4,721 views

Reading time 3 minutes

Olá pessoal,
Boa tarde!

Neste post, vou seguir a dica de um leitor e utilizar a excelente API gratuita do site Calendario.com.br para retornar a listagem de feriados nacionais, estaduais, municipais e facultativos do Brasil, permitindo filtrar por ano, estado e/ou cidade. Eu já havia comentado sobre este assunto no post How to create a table with holidays (national, state and mobile) in SQL Server, mas acho que esse post vai trazer mais uma forma de se realizar essa tarefa.

Acredito que a solução deste post é mais completa que a do meu post anterior de feriados, por ter até os feriados municipais e facultativos. Entretanto, cada consulta gera uma requisição HTTP, o que aplicado para uma lista grande de cidades/anos, poderia demorar vários segundos, enquanto a solução do outro post já calcula e armazena todos os dados em uma tabela, o que não impede que isso seja feito também com a solução via API.

Para saber mais sobre o recurso OLE Automation, clique neste link.

Utilizando OLE Automation

A forma mais rápida de implementar isso, é utilizando o objeto MSXML2.ServerXMLHTTP do OLE Automation, que permite realizar requisições XML através do protocolo HTTP.

Como já havia comentado em outros posts, por motivos de segurança e estabilidade do ambiente, NÃO recomendo a ativação e utilização do recurso OLE Automation, mas quis demonstrar aqui uma solução de como utilizar esse poderoso, mas perigoso, recurso do banco de dados.

Visualizar código-fonte

CREATE PROCEDURE dbo.stpConsulta_Feriado (
    @Ds_Token VARCHAR(100),
    @Nr_Ano INT,
    @Ds_Cidade VARCHAR(50),
    @Sg_UF VARCHAR(2) = 'ES'
)
AS BEGIN


    DECLARE 
        @obj INT,
        @Url VARCHAR(8000),
        @xml XML,
        @resposta VARCHAR(MAX)
        
 
    IF (OBJECT_ID('tempdb..#xml') IS NOT NULL) DROP TABLE #xml
    CREATE TABLE #xml ( 
        Ds_Dados VARCHAR(MAX)
    )
    
 
    SET @Url = 'http://www.calendario.com.br/api/api_feriados.php?token=' + @Ds_Token + '&ano=' + CAST(@Nr_Ano AS VARCHAR(4))
 
    IF (NULLIF(LTRIM(RTRIM(@Sg_UF)), '') IS NOT NULL)
        SET @Url += '&estado=' + @Sg_UF


    IF (NULLIF(LTRIM(RTRIM(@Ds_Cidade)), '') IS NOT NULL)
        SET @Url += '&cidade=' + @Ds_Cidade

    
    EXEC sys.sp_OACreate @progid = 'MSXML2.ServerXMLHTTP', @objecttoken = @obj OUT, @context = 1
    EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url, false
    EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
    EXEC sys.sp_OAMethod @obj, 'send'
 
 
    INSERT INTO #xml(Ds_Dados)
    EXEC sys.sp_OAGetProperty @obj, 'responseText' --, @resposta OUT
    
    
    EXEC sys.sp_OADestroy @obj
 
 
    SELECT TOP 1 @resposta = Ds_Dados FROM #xml
    SET @xml = @resposta COLLATE SQL_Latin1_General_CP1251_CS_AS

    SELECT
        eventos.linha.value('date[1]','varchar(100)') AS [Data],
        eventos.linha.value('name[1]','varchar(100)') AS Nome,
        eventos.linha.value('type[1]','varchar(30)') AS Tipo,
        eventos.linha.value('type_code[1]','int') AS Codigo_Tipo,
        eventos.linha.value('link[1]','varchar(100)') AS Link
    FROM   
        @xml.nodes('//events/event') eventos(linha)
    

END

CREATE PROCEDURE dbo.stpConsulta_Feriado (

@Ds_Token VARCHAR(100),

@Nr_Ano INT,

@Ds_Cidade VARCHAR(50),

@Sg_UF VARCHAR(2) = 'ES'

)

AS BEGIN

DECLARE

@obj INT,

@Url VARCHAR(8000),

@xml XML,

@resposta VARCHAR(MAX)

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

CREATE TABLE #xml (

Ds_Dados VARCHAR(MAX)

)

SET @Url = 'http://www.calendario.com.br/api/api_feriados.php?token=' + @Ds_Token + '&ano=' + CAST(@Nr_Ano AS VARCHAR(4))

IF (NULLIF(LTRIM(RTRIM(@Sg_UF)), '') IS NOT NULL)

SET @Url += '&estado=' + @Sg_UF

IF (NULLIF(LTRIM(RTRIM(@Ds_Cidade)), '') IS NOT NULL)

SET @Url += '&cidade=' + @Ds_Cidade

EXEC sys.sp_OACreate @progid = 'MSXML2.ServerXMLHTTP', @objecttoken = @obj OUT, @context = 1

EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url, false

EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'

EXEC sys.sp_OAMethod @obj, 'send'

INSERT INTO #xml(Ds_Dados)

EXEC sys.sp_OAGetProperty @obj, 'responseText' --, @resposta OUT

EXEC sys.sp_OADestroy @obj

SELECT TOP 1 @resposta = Ds_Dados FROM #xml

SET @xml = @resposta COLLATE SQL_Latin1_General_CP1251_CS_AS

SELECT

events.linha.value('date[1]','varchar(100)') AS [Data],

events.linha.value('name[1]','varchar(100)') AS Name,

events.linha.value('type[1]','varchar(30)') AS Tipo,

events.linha.value('type_code[1]','int') AS Codigo_Tipo,

events.linha.value('link[1]','varchar(100)') AS Link

FROM

@xml.nodes('//events/event') events(linha)

END

Exemplo de uso:

Utilizando o SQL CLR (C#)

Já velho conhecido aqui das postagens do blog, o CLR permite utilizar código C# ou VB.NET do .NET Framework dentro do banco de dados SQL Server, e assim, obter novos recursos e estender a capacidade do banco de dados.

Se você não conhece o CLR, ou não sabe como funciona e o que é, acesse este link.

Visualizar código-fonte

using System.Collections;
using System.Data.SqlTypes;
using System.IO;
using System.Net;
using System.Text;
using System.Xml;
using Bibliotecas.Model;
using System;

public partial class UserDefinedFunctions
{

    private class ConsultaFeriado
    {

        public SqlDateTime Date;
        public SqlString Name;
        public SqlString Type;
        public SqlInt32 Type_Code;
        public SqlString Link;

        public ConsultaFeriado(SqlDateTime date, SqlString name, SqlString type, SqlInt32 typeCode, SqlString link)
        {

            Date = date;
            Name = name;
            Type = type;
            Type_Code = typeCode;
            Link = link;

        }

    }
    
    [Microsoft.SqlServer.Server.SqlFunction(
        FillRowMethodName = "FillRow_ConsultaFeriado",
        TableDefinition = "Dt_Feriado DATETIME, Ds_Nome NVARCHAR(100), Ds_Tipo NVARCHAR(50), Nr_Tipo INT, Ds_Link NVARCHAR(500)"
    )]
    public static IEnumerable fncFeriados(int Ano, string Sg_UF, string Ds_Cidade)
    {

        var consultaFeriadoCollection = new ArrayList();

        if (Ano < 1900 || Ano > 2099)
            return consultaFeriadoCollection;


        // Gere o seu token no endereço: http://www.calendario.com.br/api_feriados_municipais_estaduais_nacionais.php
        var token = "";
        var parametros = "?token=" + token + "&ano=" + Ano;

        if (!string.IsNullOrEmpty(Sg_UF))
            parametros += "&estado=" + Sg_UF;

        if (!string.IsNullOrEmpty(Ds_Cidade))
            parametros += "&cidade=" + Ds_Cidade;


        var request = (HttpWebRequest)WebRequest.Create("http://www.calendario.com.br/api/api_feriados.php" + parametros);
        //request.Proxy = new WebProxy(Servidor.Ds_Proxy_Url, Servidor.Ds_Proxy_Porta) { Credentials = new NetworkCredential(Servidor.Ds_Proxy_Usuario, Servidor.Ds_Proxy_Senha, Servidor.Ds_Proxy_Dominio) };

        request.Method = "GET";
        request.UserAgent = "curl/7.45.0";
        request.ContentType = "application/x-www-form-urlencoded";


        using (var response = (HttpWebResponse)request.GetResponse())
        {
            using (var stream = response.GetResponseStream())
            {

                if (stream == null) return consultaFeriadoCollection;

                using (var streamReader = new StreamReader(stream, Encoding.GetEncoding("UTF-8")))
                {

                    var resposta = streamReader.ReadToEnd();

                    var xml = new XmlDocument();
                    xml.LoadXml(resposta);

                    var root = xml.DocumentElement;
                    var nodes = root?.SelectNodes("/events/event");

                    if (nodes == null) return consultaFeriadoCollection;

                    foreach (XmlNode node in nodes)
                    {

                        if (node != null)
                        {

                            consultaFeriadoCollection.Add(new ConsultaFeriado(
                                (string.IsNullOrEmpty(node["date"]?.InnerText)) ? SqlDateTime.Null : Convert.ToDateTime(node["date"].InnerText),
                                node["name"]?.InnerText,
                                node["type"]?.InnerText,
                                int.Parse(node["type_code"]?.InnerText),
                                node["link"]?.InnerText
                            ));

                        }
                        
                    }
                }
            }
        }


        return consultaFeriadoCollection;

    }

    protected static void FillRow_ConsultaFeriado(object objConsultaFeriado, out SqlDateTime date, out SqlString name, out SqlString type, out SqlInt32 typeCode, out SqlString link)
    {

        var consultaFeriado = (ConsultaFeriado)objConsultaFeriado;

        date = consultaFeriado.Date;
        name = consultaFeriado.Name;
        type = consultaFeriado.Type;
        typeCode = consultaFeriado.Type_Code;
        link = consultaFeriado.Link;

    }

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

using System.Collections;

using System.Data.SqlTypes;

using System.IO;

using System.Net;

using System.Text;

using System.Xml;

using Bibliotecas.Model;

using System;

public partial class UserDefinedFunctions

{

private class ConsultaFeriado

{

public SqlDateTime Date;

public SqlString Name;

public SqlString Type;

public SqlInt32 Type_Code;

public SqlString Link;

public ConsultaFeriado(SqlDateTime date, SqlString name, SqlString type, SqlInt32 typeCode, SqlString link)

{

Date = date;

Name = name;

Type = type;

Type_Code = typeCode;

Link = link;

}

[Microsoft.SqlServer.Server.SqlFunction(

FillRowMethodName = "FillRow_ConsultaFeriado",

TableDefinition = "Dt_Feriado DATETIME, Ds_Nome NVARCHAR(100), Ds_Tipo NVARCHAR(50), Nr_Tipo INT, Ds_Link NVARCHAR(500)"

)]

public static IEnumerable fncFeriados(int Ano, string Sg_UF, string Ds_Cidade)

{

var consultaFeriadoCollection = new ArrayList();

if (Ano < 1900 || Ano > 2099)

return consultaFeriadoCollection;

// Gere o seu token no endereço: http://www.calendario.com.br/api_feriados_municipais_estaduais_nacionais.php

var token = "";

var parametros = "?token=" + token + "&ano=" + Ano;

if (!string.IsNullOrEmpty(Sg_UF))

parametros += "&estado=" + Sg_UF;

if (!string.IsNullOrEmpty(Ds_Cidade))

parametros += "&cidade=" + Ds_Cidade;

var request = (HttpWebRequest)WebRequest.Create("http://www.calendario.com.br/api/api_feriados.php" + parametros);

//request.Proxy = new WebProxy(Servidor.Ds_Proxy_Url, Servidor.Ds_Proxy_Porta) { Credentials = new NetworkCredential(Servidor.Ds_Proxy_Usuario, Servidor.Ds_Proxy_Senha, Servidor.Ds_Proxy_Dominio) };

request.Method = "GET";

request.UserAgent = "curl/7.45.0";

request.ContentType = "application/x-www-form-urlencoded";

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

{

using (var stream = response.GetResponseStream())

{

if (stream == null) return consultaFeriadoCollection;

using (var streamReader = new StreamReader(stream, Encoding.GetEncoding("UTF-8")))

{

var resposta = streamReader.ReadToEnd();

var xml = new XmlDocument();

xml.LoadXml(resposta);

var root = xml.DocumentElement;

var nodes = root?.SelectNodes("/events/event");

if (nodes == null) return consultaFeriadoCollection;

foreach (XmlNode node in nodes)

{

if (node != null)

{

consultaFeriadoCollection.Add(new ConsultaFeriado(

(string.IsNullOrEmpty(node["date"]?.InnerText)) ? SqlDateTime.Null : Convert.ToDateTime(node["date"].InnerText),

node["name"]?.InnerText,

node["type"]?.InnerText,

int.Parse(node["type_code"]?.InnerText),

node["link"]?.InnerText

));

}

return consultaFeriadoCollection;

}

protected static void FillRow_ConsultaFeriado(object objConsultaFeriado, out SqlDateTime date, out SqlString name, out SqlString type, out SqlInt32 typeCode, out SqlString link)

{

var consultaFeriado = (ConsultaFeriado)objConsultaFeriado;

date = consultaFeriado.Date;

name = consultaFeriado.Name;

type = consultaFeriado.Type;

typeCode = consultaFeriado.Type_Code;

link = consultaFeriado.Link;

}

And that's it, folks!
Obrigado pela visita, espero que tenham gostado e até o próximo post.

João Filho disse:

October 26, 2022 às 14:26

Olá,
Parace que o site http://www.calendario.com.br/api/api_feriados.php? não fornece mais os dados, ao tentar acessar retorna a seguinte mensagem.
“Olá, infelizmente não fornecemos mais o serviço de API de Feriados.”
Alguma outra api gratuita?

Responder

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server – Como consultar os feriados nacionais, estaduais, municipais e facultativos de uma API utilizando OLE Automation e CLR (C#)

Utilizando OLE Automation

Utilizando o SQL CLR (C#)

You may also like...

1 Response

Deixe uma respostaCancelar resposta

Utilizando OLE Automation

Utilizando o SQL CLR (C#)

You may also like...

Habilitando e utilizando a conexão remota dedicada para administrador (DAC) no SQL Server

SQL Server - How to avoid brute force attacks on your database

Identificando e resolvendo problemas de usuários órfãos no SQL Server com a sp_change_users_login

1 Response

Deixe uma respostaCancelar resposta