SQL Server - How to Query the National, State, Municipal, and Optional Holidays of an API Using OLE Automation and CLR (C #)

Views: 4.324 views

Reading Time: 3 minutes

Hello people,
Good afternoon!

In this post, I will follow a reader's tip and use the great free API of the site Calendar.com to return to the list of national, state, municipal and facultative holidays of Brazil, allowing to filter by year, state and / or city. I had already commented on this subject in the post How to create a table of holidays (national, state, and mobile) in SQL Server, but I think this post will bring one more way to accomplish this task.

I believe the solution of this post is more complete than my previous post of holidays, because it has even the municipal and optional holidays. However, each query generates an HTTP request, which applied to a large list of cities / years could take several seconds, while the solution of the other post already calculates and stores all data in a table, which does not prevent this from being done. also made with the solution via API.

To learn more about the OLE Automation feature, click this link.

Using OLE Automation

The fastest way to implement this is by using the OLE Automation MSXML2.ServerXMLHTTP object, which allows you to perform XML requests through the HTTP protocol.

As I have commented in other posts, for reasons of security and stability of the environment, I do NOT recommend activating and using the OLE Automation feature, but I wanted to demonstrate here a solution on how to use this powerful but dangerous database feature.

View source

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

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

Example of use:

Using SQL CLR (C #)

Already well-known here from blog posts, CLR allows you to use .NET Framework C # or VB.NET code within the SQL Server database, thereby gaining new features and extending the capacity of the database.

If you don't know CLR, or don't know how it works and what it is, visit this link.

View source

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;

}

That's it folks!
Thanks for visiting, I hope you enjoyed and see you in the next post.

João Filho said:

26 October 2022 to 14: 26

Hello,
It seems that the site http://www.calendario.com.br/api/api_feriados.php? no longer provides the data, when trying to access it returns the following message.
“Hello, unfortunately we no longer provide the Holiday API service.”
Any other free api?

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

SQL Server - How to query the national, state, municipal, and optional holidays of an API using OLE Automation and CLR (C #)

Using OLE Automation

Using SQL CLR (C #)

You may also like ...

1 Response

Leave a Comment Cancel reply

Using OLE Automation

Using SQL CLR (C #)

You may also like ...

SQL Server - How to disable “sa” login minimizing impacts

Troubleshooting .NET Framework DLL Assembly Signing Issues in SQL Server

SQL Server - Table-valued function to break a string into lines up to N characters long

1 Response

Leave a Comment Cancel reply