SQL Server - Cómo consultar la cotización del dólar (USD), euro (EUR), real (BRL) o cualquier otra moneda en tiempo real con API y SQLCLR

¡Hola, chicos!
En el post de hoy, compartiré con ustedes una solución que desarrollé para consultar el tipo de cambio del dólar (USD), euro (EUR), real (BRL) o cualquier otra moneda en tiempo (casi) real usando un WebService (API) y SQLCLR en SQL Server. Esto puede terminar siendo muy útil dependiendo de tu área de actividad y es algo que veo que muchas empresas incluso compran servicios similares a este.

Si no sabes qué es SQLCLR, lee el artículo. Introducción a SQL CLR (Common Language Runtime) en SQL Server antes de esta publicación para comprender mejor qué es esta característica.

Qué podremos hacer al final de este post:

¿De dónde provienen los datos de las cotizaciones?

Para devolver datos de cotización de divisas, existen varios sitios web y API que se pueden consultar. Para este ejemplo, elegí utilizar la API del sitio web. https://www.currencyconverterapi.com/, que cuenta con un plan de consultas gratuito, así como planes pagos, si tienes una mayor necesidad de consultas.

Se puede encontrar la documentación API. en este enlace aquí.

en el avion gratis, Creo que la mayoría de la gente ya estará muy bien atendida. De hecho, en el propio sitio web se menciona que el servicio se puede utilizar de forma gratuita incluso para uso comercial.

Limitaciones del plan gratuito:

Pares de divisas por solicitud: 2 (Puedes consultar hasta 2 monedas a la vez)
Número de solicitudes por hora: 100
Intervalo de actualización de datos de cotización: 60 minutos (En otras palabras, no tiene sentido intentar actualizar los datos del plan gratuito con menos frecuencia que cada 60 minutos)
Rango de fechas en búsqueda histórica: 8 dias (La función fncConverteMoedaHistoricoRange solo puede devolver un rango de datos de hasta 8 días)
Consultas históricas: 1 año (Las fechas pasadas por parámetros en las funciones fncConverteMoedaHistorico o fncConverteMoedaHistoricoRange no pueden tener más de 365 días a partir de la fecha actual)

Si el plan gratuito no puede servirle, puede buscar un plan pago y continuar usando esta misma API y estas funciones.

¿Cómo obtener la clave de autenticación API?

Para comprar su clave API y comenzar a utilizar las funciones, accede a este enlace aquí, rellena tu email y recibirás tu clave de acceso:

Con esta clave ya podrás utilizar tu API, respetando, por supuesto, los límites del plan que estés utilizando.

Cómo utilizar las funciones creadas.

Antes de demostrar su uso, pensé que sería bueno dar una breve descripción de cada una de estas funciones.

fncConverteMoneda: Función que tiene como objetivo convertir una moneda a otra, basándose en el tipo de cambio más actual posible. El retorno de esta función es un número decimal escalar.
fncConvertCurrencyHistory: Función que tiene como objetivo convertir una moneda a otra, en base a la cotización de fecha ingresada como parámetro (teniendo en cuenta las limitaciones de la API). El retorno de esta función es un número decimal escalar.
fncConvertCurrencyHistoryRango: Función que tiene como objetivo convertir una moneda a otra, en base a la cotización del rango de fechas ingresada como parámetro (recordando las limitaciones de la API). El retorno de esta función es una tabla que contiene la fecha de cotización (Datetime) y el valor de cotización (número decimal escalar), donde cada día es una línea en la tabla.

Ejemplos de uso:

DECLARE @tokenApi VARCHAR(50) = 'Meu token'


-- Retorna a cotação mais atual da moeda desejada
SELECT dbo.fncConverteMoeda(@tokenApi, 'USD', 'BRL')


-- Retorna a cotação da moeda na data desejada
SELECT dbo.fncConverteMoedaHistorico(@tokenApi, 'USD', 'BRL', '2020-07-30')


-- Retorna uma tabela com os rates no intervalo desejado
SELECT * FROM dbo.fncConverteMoedaHistoricoRange(@tokenApi, 'USD', 'BRL', '2021-01-01', '2021-01-08')

Resultado:

Cómo crear objetos en la base de datos con T-SQL – Modo Fácil/Nutella

Si no tiene Visual Studio o no quiere preocuparse por comprender cómo se crearon las funciones o compilar su propia versión del ensamblado, este código es para usted. Sencillo, rápido y objetivo.

Código T-SQL para crear las funciones:

USE [master]
GO

DECLARE 
    @VersaoSQL INT = CONVERT(INT, (SERVERPROPERTY('ProductMajorVersion')))


IF (@VersaoSQL >= 15) -- SQL Server 2017+
BEGIN
    
    EXEC ('
    DECLARE @asmBin varbinary(max) = 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103000875C2600000000000000000E00022200B01300000120000000600000000000036310000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000E43000004F00000000400000E802000000000000000000000000000000000000006000000C000000AC2F00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000003C110000002000000012000000020000000000000000000000000000200000602E72737263000000E8020000004000000004000000140000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001800000000000000000000000000004000004200000000000000000000000000000000183100000000000048000000020005008C250000200A000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B300400F600000001000011001C8D1000000125167201000070A2251703A225187259000070A2251904A2251A725D000070A2251B02A2280500000A0A06280600000A74080000010B07728B0000706F0700000A000772930000706F0800000A0072B50000700C076F0900000A74090000010D00096F0A00000A130400110414FE03130511052C2E00110472B7000070280B00000A730C00000A13060011066F0D00000A0C00DE0D11062C0811066F0E00000A00DC0000DE0D11042C0811046F0E00000A00DC00DE0B092C07096F0E00000A00DC080872C30000706F0F00000A17586F1000000A72C700007072B50000706F1100000A281200000A13072B0011072A000001280000020090000C9C000D00000000020070003DAD000D000000000200670056BD000B000000001B3005001901000002000011001E8D1000000125167201000070A2251703A225187259000070A2251904A2251A72CB000070A2251B0F03281300000A0D120372F5000070281400000AA2251C720B010070A2251D02A2280500000A0A06280600000A74080000010B07728B0000706F0700000A000772930000706F0800000A0072B50000700C076F0900000A740900000113040011046F0A00000A130500110514FE03130611062C2E00110572B7000070280B00000A730C00000A13070011076F0D00000A0C00DE0D11072C0811076F0E00000A00DC0000DE0D11052C0811056F0E00000A00DC00DE0D11042C0811046F0E00000A00DC080872C30000706F1500000A17586F1000000A721D01007072B50000706F1100000A281200000A13082B0011082A000000012800000200B1000CBD000D00000000020091003DCE000D000000000200870057DE000D000000001B300500170200000300001100731600000A0A1F0A8D1000000125167201000070A2251703A225187259000070A2251904A2251A72CB000070A2251B0F03281300000A1304120472F5000070281400000AA2251C7223010070A2251D0F04281300000A1304120472F5000070281400000AA2251E720B010070A2251F0902A2280500000A0B07280600000A74080000010C08728B0000706F0700000A000872930000706F0800000A0072B50000700D086F0900000A740900000113050011056F0A00000A130600110614FE0313071107392601000000110672B7000070280B00000A730C00000A13080011086F0D00000A0D097237010070196F1700000A16FE0416FE01130B110B2C2800067E1800000A23000000000000F0BF281900000A73060000066F1A00000A2606130CDDEE00000009724301007072B50000706F1100000A72C700007072B50000706F1100000A724701007072B50000706F1100000A0D090972C30000706F0F00000A17586F1000000A0D09178D1700000125161F2C9D6F1B00000A130911098E69130A16130D2B4B001109110D9A130E110E178D1700000125161F3A9D6F1B00000A130F06110F169A281C00000A281D00000A110F179A281E00000A281900000A73060000066F1A00000A2600110D1758130D110D110AFE04131011102DA900DE0D11082C0811086F0E00000A00DC0000DE0D11062C0811066F0E00000A00DC00DE0D11052C0811056F0E00000A00DC06130C2B00110C2A00414C000002000000DD00000004010000E10100000D0000000000000002000000BA00000038010000F20100000D0000000000000002000000B000000052010000020200000D00000000000000133002002100000004000011000274030000020A03067B01000004810C00000104067B0200000481070000012A2202281F00000A002A5E02281F00000A000002037D0100000402047D020000042A000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000010030000237E00007C0300009803000023537472696E677300000000140700004C010000235553006008000010000000234755494400000070080000B001000023426C6F620000000000000002000001571502000902000000FA0133001600000100000018000000030000000200000006000000110000001F000000060000000400000001000000030000000100000000009E0101000000000006002B01D30206004B01D30206000201C0020F00F302000006004603DE010A0016019F020A00970002030E0072034D030E00D1004D030600CB010A0006006E020A000A00AA0002030600B800DE0106007F00170306008103170306008D01DE010E0076034D030E00D5004D03060082018B0306007B020A0006008B00DE010600F301DE0106006902DE0106006A03DE010000000001000000000001000100010010002A030000150001000100030010001F02000015000100060006002C00A30006002702D90050200000000096001400DD0001007C210000000096003702E5000400CC220000000096006000EF0008003C250000000094000402FB000D006925000000008618BA02060010007225000000008618BA020601100000000100ED0100000200D201000003005C0200000100ED0100000200D201000003005C0200000400310000000100ED0100000200D201000003005C0200000400510200000500E501000001001C02020002003100020003002F02000001003100000002002F020900BA0201001100BA0206001900BA020A003100BA02060081003F0320008900FB00260089004D002C008900C1002C008900E10031009100C001360099007F013B005900BA024100A10043004900A900ED000600810077014D008100940152008100580057003900F5005D0061006901750069008B017A00810073014D007900BA020600810077019C006100BB01A30039005803A70079003F00AD0081006403B200C100B600B90061005803BF00C100A100C6002900BA020600200023003F012E000B000E012E00130017012E001B003601400023003F01600023004401100063007F00CB0004800000000000000000000000000000000086020000020000000000000000000000D000360000000000020000000000000000000000D000250000000000020000000000000000000000D000DE0100000000030002000000003C4D6F64756C653E0053797374656D2E494F00666E63436F6E76657274654D6F6564610053797374656D2E446174610064617461006D73636F726C6962004164640052656164546F456E64007365745F4D6574686F64005265706C61636500666E63436F6E76657274654D6F656461486973746F7269636F52616E67650049456E756D657261626C650049446973706F7361626C650053716C446F75626C6500546F446F75626C650053716C4461746554696D6500546F4461746554696D65007365745F436F6E74656E74547970650048747470576562526573706F6E736500476574526573706F6E736500446973706F7365005061727365004372656174650044656275676761626C654174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C7565004C617374496E6465784F6600476574456E636F64696E6700546F537472696E6700537562737472696E670053514C434C525F43757272656E6379436F6E7665727465722E646C6C004E756C6C00476574526573706F6E736553747265616D006D6F6564614F726967656D0053797374656D006461746146696D00746F6B656E00537472696E67436F6D70617269736F6E0046696C6C526F775F436F6E73756C7461436F746163616F006F626A436F6E73756C7461436F746163616F00636F746163616F00666E63436F6E76657274654D6F656461486973746F7269636F0064617461496E6963696F006D6F65646144657374696E6F00436861720053747265616D52656164657200546578745265616465720053514C434C525F43757272656E6379436F6E766572746572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053797374656D2E436F6C6C656374696F6E730055736572446566696E656446756E6374696F6E7300436F6E636174004F626A6563740053797374656D2E4E6574006F705F496D706C696369740053706C697400436F6E766572740048747470576562526571756573740041727261794C6973740053797374656D2E5465787400000057680074007400700073003A002F002F0066007200650065002E00630075007200720063006F006E0076002E0063006F006D002F006100700069002F00760037002F0063006F006E0076006500720074003F0071003D0000035F00002D260063006F006D0070006100630074003D0075006C0074007200610026006100700069004B00650079003D00000747004500540000216100700070006C00690063006100740069006F006E002F006A0073006F006E000001000B5500540046002D00380001033A0000037D000029260063006F006D0070006100630074003D0075006C00740072006100260064006100740065003D00001579007900790079002D004D004D002D0064006400011126006100700069004B00650079003D0000057D007D000013260065006E00640044006100740065003D00000B6500720072006F00720000037B00000322000000815DB5CD93F7514E893DE6B90EF8AE0B000420010108032000010520010111110F07080E12210E1225122902122D111D0500010E1D0E05000112450E042001010E04200012490420001229050001124D0E072002011229124D0320000E042001080E0420010E080520020E0E0E050001111D0E1107090E12210E11351225122902122D111D04200011350420010E0E1C0711123D0E12210E11351225122902122D1D0E08021239080E1D0E02062002080E115903061131050001111D0D042001081C0620011D0E1D0305000111350E060001113111350400010D0E040701120C08B77A5C561934E0890306111D070003111D0E0E0E090004111D0E0E0E11310B000512390E0E0E113111310A0003011C10113110111D072002011131111D0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730108010007010000000004010000006801000200540E1146696C6C526F774D6574686F644E616D651746696C6C526F775F436F6E73756C7461436F746163616F540E0F5461626C65446566696E6974696F6E2544745F436F746163616F204441544554494D452C20566C5F436F746163616F20464C4F4154000000000000000875C26000000000020000001C010000C82F0000C811000052534453867155FADDC9CB49A9C76F5E5FCA004701000000433A5C55736572735C646966696C5C736F757263655C7265706F735C53514C434C525F43757272656E6379436F6E7665727465725C53514C434C525F43757272656E6379436F6E7665727465725C6F626A5C44656275675C53514C434C525F43757272656E6379436F6E7665727465722E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C31000000000000000000002631000000200000000000000000000000000000000000000000000018310000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000008C02000000000000000000008C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004EC010000010053007400720069006E006700460069006C00650049006E0066006F000000C801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000005A001D00010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052005F00430075007200720065006E006300790043006F006E007600650072007400650072002E0064006C006C00000000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000062001D0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052005F00430075007200720065006E006300790043006F006E007600650072007400650072002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000383100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
    DECLARE @hash varbinary(64) = HASHBYTES(''SHA2_512'', @asmBin);

    IF (EXISTS(SELECT TOP(1) NULL FROM sys.trusted_assemblies WHERE [hash] = @hash))
    BEGIN
        EXEC sys.sp_drop_trusted_assembly @hash = @hash
    END


    EXEC sys.sp_add_trusted_assembly 
        @hash = @hash,
        @description = N''sqlclr_currencyconverter, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil'''
    )

END
ELSE BEGIN
    
    EXEC('ALTER DATABASE [dirceuresende] SET TRUSTWORTHY ON')

END


USE [dirceuresende]
GO

IF (EXISTS(SELECT TOP(1) NULL FROM sys.assemblies WHERE [name] = 'SQLCLR_CurrencyConverter'))
BEGIN

    IF (OBJECT_ID('dbo.fncConverteMoeda') IS NOT NULL) DROP FUNCTION dbo.fncConverteMoeda
    IF (OBJECT_ID('dbo.fncConverteMoedaHistorico') IS NOT NULL) DROP FUNCTION dbo.fncConverteMoedaHistorico
    IF (OBJECT_ID('dbo.fncConverteMoedaHistoricoRange') IS NOT NULL) DROP FUNCTION dbo.fncConverteMoedaHistoricoRange

    DROP ASSEMBLY [SQLCLR_CurrencyConverter]

END


CREATE ASSEMBLY [SQLCLR_CurrencyConverter]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103000875C2600000000000000000E00022200B01300000120000000600000000000036310000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000E43000004F00000000400000E802000000000000000000000000000000000000006000000C000000AC2F00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000003C110000002000000012000000020000000000000000000000000000200000602E72737263000000E8020000004000000004000000140000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001800000000000000000000000000004000004200000000000000000000000000000000183100000000000048000000020005008C250000200A000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B300400F600000001000011001C8D1000000125167201000070A2251703A225187259000070A2251904A2251A725D000070A2251B02A2280500000A0A06280600000A74080000010B07728B0000706F0700000A000772930000706F0800000A0072B50000700C076F0900000A74090000010D00096F0A00000A130400110414FE03130511052C2E00110472B7000070280B00000A730C00000A13060011066F0D00000A0C00DE0D11062C0811066F0E00000A00DC0000DE0D11042C0811046F0E00000A00DC00DE0B092C07096F0E00000A00DC080872C30000706F0F00000A17586F1000000A72C700007072B50000706F1100000A281200000A13072B0011072A000001280000020090000C9C000D00000000020070003DAD000D000000000200670056BD000B000000001B3005001901000002000011001E8D1000000125167201000070A2251703A225187259000070A2251904A2251A72CB000070A2251B0F03281300000A0D120372F5000070281400000AA2251C720B010070A2251D02A2280500000A0A06280600000A74080000010B07728B0000706F0700000A000772930000706F0800000A0072B50000700C076F0900000A740900000113040011046F0A00000A130500110514FE03130611062C2E00110572B7000070280B00000A730C00000A13070011076F0D00000A0C00DE0D11072C0811076F0E00000A00DC0000DE0D11052C0811056F0E00000A00DC00DE0D11042C0811046F0E00000A00DC080872C30000706F1500000A17586F1000000A721D01007072B50000706F1100000A281200000A13082B0011082A000000012800000200B1000CBD000D00000000020091003DCE000D000000000200870057DE000D000000001B300500170200000300001100731600000A0A1F0A8D1000000125167201000070A2251703A225187259000070A2251904A2251A72CB000070A2251B0F03281300000A1304120472F5000070281400000AA2251C7223010070A2251D0F04281300000A1304120472F5000070281400000AA2251E720B010070A2251F0902A2280500000A0B07280600000A74080000010C08728B0000706F0700000A000872930000706F0800000A0072B50000700D086F0900000A740900000113050011056F0A00000A130600110614FE0313071107392601000000110672B7000070280B00000A730C00000A13080011086F0D00000A0D097237010070196F1700000A16FE0416FE01130B110B2C2800067E1800000A23000000000000F0BF281900000A73060000066F1A00000A2606130CDDEE00000009724301007072B50000706F1100000A72C700007072B50000706F1100000A724701007072B50000706F1100000A0D090972C30000706F0F00000A17586F1000000A0D09178D1700000125161F2C9D6F1B00000A130911098E69130A16130D2B4B001109110D9A130E110E178D1700000125161F3A9D6F1B00000A130F06110F169A281C00000A281D00000A110F179A281E00000A281900000A73060000066F1A00000A2600110D1758130D110D110AFE04131011102DA900DE0D11082C0811086F0E00000A00DC0000DE0D11062C0811066F0E00000A00DC00DE0D11052C0811056F0E00000A00DC06130C2B00110C2A00414C000002000000DD00000004010000E10100000D0000000000000002000000BA00000038010000F20100000D0000000000000002000000B000000052010000020200000D00000000000000133002002100000004000011000274030000020A03067B01000004810C00000104067B0200000481070000012A2202281F00000A002A5E02281F00000A000002037D0100000402047D020000042A000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000010030000237E00007C0300009803000023537472696E677300000000140700004C010000235553006008000010000000234755494400000070080000B001000023426C6F620000000000000002000001571502000902000000FA0133001600000100000018000000030000000200000006000000110000001F000000060000000400000001000000030000000100000000009E0101000000000006002B01D30206004B01D30206000201C0020F00F302000006004603DE010A0016019F020A00970002030E0072034D030E00D1004D030600CB010A0006006E020A000A00AA0002030600B800DE0106007F00170306008103170306008D01DE010E0076034D030E00D5004D03060082018B0306007B020A0006008B00DE010600F301DE0106006902DE0106006A03DE010000000001000000000001000100010010002A030000150001000100030010001F02000015000100060006002C00A30006002702D90050200000000096001400DD0001007C210000000096003702E5000400CC220000000096006000EF0008003C250000000094000402FB000D006925000000008618BA02060010007225000000008618BA020601100000000100ED0100000200D201000003005C0200000100ED0100000200D201000003005C0200000400310000000100ED0100000200D201000003005C0200000400510200000500E501000001001C02020002003100020003002F02000001003100000002002F020900BA0201001100BA0206001900BA020A003100BA02060081003F0320008900FB00260089004D002C008900C1002C008900E10031009100C001360099007F013B005900BA024100A10043004900A900ED000600810077014D008100940152008100580057003900F5005D0061006901750069008B017A00810073014D007900BA020600810077019C006100BB01A30039005803A70079003F00AD0081006403B200C100B600B90061005803BF00C100A100C6002900BA020600200023003F012E000B000E012E00130017012E001B003601400023003F01600023004401100063007F00CB0004800000000000000000000000000000000086020000020000000000000000000000D000360000000000020000000000000000000000D000250000000000020000000000000000000000D000DE0100000000030002000000003C4D6F64756C653E0053797374656D2E494F00666E63436F6E76657274654D6F6564610053797374656D2E446174610064617461006D73636F726C6962004164640052656164546F456E64007365745F4D6574686F64005265706C61636500666E63436F6E76657274654D6F656461486973746F7269636F52616E67650049456E756D657261626C650049446973706F7361626C650053716C446F75626C6500546F446F75626C650053716C4461746554696D6500546F4461746554696D65007365745F436F6E74656E74547970650048747470576562526573706F6E736500476574526573706F6E736500446973706F7365005061727365004372656174650044656275676761626C654174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C7565004C617374496E6465784F6600476574456E636F64696E6700546F537472696E6700537562737472696E670053514C434C525F43757272656E6379436F6E7665727465722E646C6C004E756C6C00476574526573706F6E736553747265616D006D6F6564614F726967656D0053797374656D006461746146696D00746F6B656E00537472696E67436F6D70617269736F6E0046696C6C526F775F436F6E73756C7461436F746163616F006F626A436F6E73756C7461436F746163616F00636F746163616F00666E63436F6E76657274654D6F656461486973746F7269636F0064617461496E6963696F006D6F65646144657374696E6F00436861720053747265616D52656164657200546578745265616465720053514C434C525F43757272656E6379436F6E766572746572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053797374656D2E436F6C6C656374696F6E730055736572446566696E656446756E6374696F6E7300436F6E636174004F626A6563740053797374656D2E4E6574006F705F496D706C696369740053706C697400436F6E766572740048747470576562526571756573740041727261794C6973740053797374656D2E5465787400000057680074007400700073003A002F002F0066007200650065002E00630075007200720063006F006E0076002E0063006F006D002F006100700069002F00760037002F0063006F006E0076006500720074003F0071003D0000035F00002D260063006F006D0070006100630074003D0075006C0074007200610026006100700069004B00650079003D00000747004500540000216100700070006C00690063006100740069006F006E002F006A0073006F006E000001000B5500540046002D00380001033A0000037D000029260063006F006D0070006100630074003D0075006C00740072006100260064006100740065003D00001579007900790079002D004D004D002D0064006400011126006100700069004B00650079003D0000057D007D000013260065006E00640044006100740065003D00000B6500720072006F00720000037B00000322000000815DB5CD93F7514E893DE6B90EF8AE0B000420010108032000010520010111110F07080E12210E1225122902122D111D0500010E1D0E05000112450E042001010E04200012490420001229050001124D0E072002011229124D0320000E042001080E0420010E080520020E0E0E050001111D0E1107090E12210E11351225122902122D111D04200011350420010E0E1C0711123D0E12210E11351225122902122D1D0E08021239080E1D0E02062002080E115903061131050001111D0D042001081C0620011D0E1D0305000111350E060001113111350400010D0E040701120C08B77A5C561934E0890306111D070003111D0E0E0E090004111D0E0E0E11310B000512390E0E0E113111310A0003011C10113110111D072002011131111D0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730108010007010000000004010000006801000200540E1146696C6C526F774D6574686F644E616D651746696C6C526F775F436F6E73756C7461436F746163616F540E0F5461626C65446566696E6974696F6E2544745F436F746163616F204441544554494D452C20566C5F436F746163616F20464C4F4154000000000000000875C26000000000020000001C010000C82F0000C811000052534453867155FADDC9CB49A9C76F5E5FCA004701000000433A5C55736572735C646966696C5C736F757263655C7265706F735C53514C434C525F43757272656E6379436F6E7665727465725C53514C434C525F43757272656E6379436F6E7665727465725C6F626A5C44656275675C53514C434C525F43757272656E6379436F6E7665727465722E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C31000000000000000000002631000000200000000000000000000000000000000000000000000018310000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000008C02000000000000000000008C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004EC010000010053007400720069006E006700460069006C00650049006E0066006F000000C801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000005A001D00010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052005F00430075007200720065006E006300790043006F006E007600650072007400650072002E0064006C006C00000000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000062001D0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052005F00430075007200720065006E006300790043006F006E007600650072007400650072002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000383100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

CREATE FUNCTION [dbo].[fncConverteMoeda] (@token NVARCHAR (MAX), @moedaOrigem NVARCHAR (MAX), @moedaDestino NVARCHAR (MAX) )
RETURNS FLOAT
AS
EXTERNAL NAME [SQLCLR_CurrencyConverter].[UserDefinedFunctions].[fncConverteMoeda]
GO

CREATE FUNCTION [dbo].[fncConverteMoedaHistorico] ( @token NVARCHAR (MAX), @moedaOrigem NVARCHAR (MAX), @moedaDestino NVARCHAR (MAX), @data DATETIME )
RETURNS FLOAT
AS
EXTERNAL NAME [SQLCLR_CurrencyConverter].[UserDefinedFunctions].[fncConverteMoedaHistorico]
GO

CREATE FUNCTION [dbo].[fncConverteMoedaHistoricoRange] ( @token NVARCHAR (MAX), @moedaOrigem NVARCHAR (MAX), @moedaDestino NVARCHAR (MAX), @dataInicio DATETIME, @dataFim DATETIME )
RETURNS TABLE (
    [Dt_Cotacao] DATETIME   NULL,
    [Vl_Cotacao] FLOAT      NULL
)
AS
EXTERNAL NAME [SQLCLR_CurrencyConverter].[UserDefinedFunctions].[fncConverteMoedaHistoricoRange]
GO

Tenga mucho cuidado al utilizar el parámetro TRUSTWORTHY en la base de datos, ya que en una situación determinada, puede suponer un grave riesgo de seguridad para su entorno, como lo demostré en el artículo. SQL Server: comprensión de los riesgos de la propiedad CONFIABLE habilitada en una base de datos. Intente utilizar la función de ensamblaje confiable de SQL Server 2017+ o la clave asimétrica, si es posible.

Cómo compilar su proyecto SQLCLR en Visual Studio – Modo duro/root

Si tiene Visual Studio y desea comprender cómo se crearon las funciones o compilar su propia versión del ensamblado, este código es para usted.

Si no sabes qué es SQLCLR o cómo compilar y publicar el proyecto, lee el artículo. Introducción a SQL CLR (Common Language Runtime) en SQL Server antes de esta publicación para comprender mejor qué es esta característica.

Descarga de la solución Visual Studio (VS2019)
Descarga del proyecto SQLCLR_CurrencyConverter

Código fuente de fncConverteMoeda.cs

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

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDouble fncConverteMoeda (string token, string moedaOrigem, string moedaDestino) 
    {

        var url = "https://free.currconv.com/api/v7/convert?q=" + moedaOrigem + "_" + moedaDestino + "&compact=ultra&apiKey=" + token;
        var request = (HttpWebRequest)WebRequest.Create(url);

        request.Method = "GET";
        request.ContentType = "application/json";
        var resposta = "";

        using (var response = (HttpWebResponse)request.GetResponse())
        {
            using (var stream = response.GetResponseStream())
            {
                if (stream != null)
                {
                    using (var streamReader = new StreamReader(stream, Encoding.GetEncoding("UTF-8")))
                    {
                        resposta = streamReader.ReadToEnd();
                    }
                }
            }
        }

        return SqlDouble.Parse(resposta.Substring(resposta.IndexOf(":") + 1).Replace("}", "")) ;

    }

}

fncConverteMoedaHistorico.cs código fuente

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

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDouble fncConverteMoedaHistorico(string token, string moedaOrigem, string moedaDestino, SqlDateTime data)
    {

        var url = "https://free.currconv.com/api/v7/convert?q=" + moedaOrigem + "_" + moedaDestino + "&compact=ultra&date=" + data.Value.ToString("yyyy-MM-dd") + "&apiKey=" + token;
        var request = (HttpWebRequest)WebRequest.Create(url);

        request.Method = "GET";
        request.ContentType = "application/json";
        var resposta = "";

        using (var response = (HttpWebResponse)request.GetResponse())
        {
            using (var stream = response.GetResponseStream())
            {
                if (stream != null)
                {
                    using (var streamReader = new StreamReader(stream, Encoding.GetEncoding("UTF-8")))
                    {
                        resposta = streamReader.ReadToEnd();
                    }
                }
            }
        }

        return SqlDouble.Parse(resposta.Substring(resposta.LastIndexOf(":") + 1).Replace("}}", ""));

    }

}

Código fuente fncConverteMoedaHistoricoRange.cs

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

public partial class UserDefinedFunctions
{

    private class ConsultaCotacao
    {

        public SqlDateTime Data;
        public SqlDouble Cotacao;
        
        public ConsultaCotacao(SqlDateTime data, SqlDouble cotacao)
        {

            Data = data;
            Cotacao = cotacao;
            
        }

    }


    [Microsoft.SqlServer.Server.SqlFunction(
        FillRowMethodName = "FillRow_ConsultaCotacao",
        TableDefinition = "Dt_Cotacao DATETIME, Vl_Cotacao FLOAT"
    )]
    public static IEnumerable fncConverteMoedaHistoricoRange(string token, string moedaOrigem, string moedaDestino, SqlDateTime dataInicio, SqlDateTime dataFim)
    {

        var consultaCotacaoCollection = new ArrayList();

        var url = "https://free.currconv.com/api/v7/convert?q=" + moedaOrigem + "_" + moedaDestino + "&compact=ultra&date=" + dataInicio.Value.ToString("yyyy-MM-dd") + "&endDate=" + dataFim.Value.ToString("yyyy-MM-dd") + "&apiKey=" + token;
        var request = (HttpWebRequest)WebRequest.Create(url);

        request.Method = "GET";
        request.ContentType = "application/json";
        var resposta = "";

        using (var response = (HttpWebResponse)request.GetResponse())
        {
            using (var stream = response.GetResponseStream())
            {
                if (stream != null)
                {
                    using (var streamReader = new StreamReader(stream, Encoding.GetEncoding("UTF-8")))
                    {

                        resposta = streamReader.ReadToEnd();

                        if (resposta.IndexOf("error", StringComparison.InvariantCultureIgnoreCase) >= 0)
                        {

                            consultaCotacaoCollection.Add(new ConsultaCotacao(
                                SqlDateTime.Null,
                                -1
                            ));

                            return consultaCotacaoCollection;

                        }

                        resposta = resposta.Replace("{", "").Replace("}", "").Replace("\"", "");
                        resposta = resposta.Substring(resposta.IndexOf(":") + 1);

                        var varLinhas = resposta.Split(',');
                        var numLinhas = varLinhas.Length;

                        for(var i = 0; i < numLinhas; i++)
                        {

                            var linha = varLinhas[i];
                            var palavras = linha.Split(':');

                            consultaCotacaoCollection.Add(new ConsultaCotacao(
                                Convert.ToDateTime(palavras[0]),
                                Convert.ToDouble(palavras[1])
                            ));

                        }

                    }
                }
            }
        }

        return consultaCotacaoCollection;

    }


    protected static void FillRow_ConsultaCotacao(object objConsultaCotacao, out SqlDateTime data, out SqlDouble cotacao)
    {

        var consultaCotacao = (ConsultaCotacao) objConsultaCotacao;

        data = consultaCotacao.Data;
        cotacao = consultaCotacao.Cotacao;

    }

}

¡Y eso es todo, amigos!
Espero que te haya gustado este consejo y ¡hasta la próxima!

Dirceu Resende

Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram Ver mis certificaciones

Comentários (0)

Carregando comentários…