Post Views 1,884 views
Hey guys!
Nesse post de hoje, vou compartilhar com vocês uma solução que eu desenvolvi para consultar a cotação do dólar (USD), euro (EUR), real (BRL) ou qualquer outra moeda em tempo real (quase) utilizando um WebService (API) e SQLCLR no SQL Server. Isso pode acabar sendo muito útil dependendo da sua área de atuação e é algo que vejo muitas empresas até comprando serviços parecidos com esse.
Se você não sabe o que é SQLCLR, dê uma lida no artigo Introdução ao SQL CLR (Common Language Runtime) no SQL Server antes desse post para entender melhor o que é esse recurso.
O que vamos conseguir fazer ao final desse post:
De onde vêm os dados das cotações?
Para retornar os dados de cotação de moedas, existem vários e vários sites e API’s para consulta. Para esse exemplo, eu optei por utilizar a API do site https://www.currencyconverterapi.com/, que possui plano gratuito para consulta, além de planos pagos, caso você tenha uma necessidade maior de consultas.
A documentação da API pode ser encontrada nesse link aqui.
No plano gratuito, acredito que a maioria das pessoas já serão muito bem atendidas. Inclusive, no próprio site é mencionado que o serviço pode ser utilizado de forma gratuita até para uso comercial.
Limitações do plano gratuito:
- Pares de moedas por solicitação: 2 (Pode consultar até 2 moedas por vez)
- Número de solicitações por hora: 100
- Intevalo de atualização dos dados da cotação: 60 minutos (Ou seja, não adianta tentar atualizar os dados no plano gratuito com frequência menor que 60 minutos)
- Intervalo de datas na busca histórica: 8 dias (A função fncConverteMoedaHistoricoRange só pode retornar um intervalo de dados de até 8 dias)
- Consultas históricas: 1 ano (As datas passadas por parâmetros nas funções fncConverteMoedaHistorico ou fncConverteMoedaHistoricoRange não podem ser mais antigas que 365 dias da data atual)
Caso o plano gratuito não consiga te atender, pode procurar algum plano pago e continuar utilizando essa mesma API e essas funções.
Como conseguir a chave de autenticação da API?
Para adquirir a sua chave da API e começar a utilizar os recursos, acesse esse link aqui, preencha o seu e-mail e você irá receber a sua chave de acesso:
Com essa chave, você já pode utilizar a sua API, respeitando os limites do plano que for utilizar, é claro.
Como utilizar as funções criadas
Antes de demonstrar a utilização, achei legal fazer uma breve descrição sobre cada uma dessas funções.
- fncConverteMoeda: Função que tem como objetivo converter uma moeda para outra, com base na cotação mais atual possível. O retorno dessa função é um número decimal escalar.
- fncConverteMoedaHistorico: Função que tem como objetivo converter uma moeda para outra, com base na cotação da data informada como parâmetro (lembrando das limitações da API). O retorno dessa função é um número decimal escalar.
- fncConverteMoedaHistoricoRange: Função que tem como objetivo converter uma moeda para outra, com base na cotação do range de data informada como parâmetro (lembrando das limitações da API). O retorno dessa função é uma tabela, contendo a data da cotação (Datetime) e o valor da cotação (número decimal escalar), onde cada dia é uma linha da tabela.
Exemplos de uso:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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') |
Result:
Como criar os objetos no banco com T-SQL – Modo Easy/Nutella
Se você não tem o Visual Studio ou não quer se preocupar em entender como as funções foram construídas ou compilar a sua própria versão do assembly, esse código é pra você. Simples, rápido e objetivo.
Código T-SQL para criar as funções:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
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 |
Muito cuidado ao utilizar o parâmetro TRUSTWORTHY no database, pois em determinada situação, pode ser um grave risco de segurança ao seu ambiente, conforme demonstrei no artigo SQL Server – Entendendo os riscos da propriedade TRUSTWORTHY habilitada em um database. Procure utilizar o recurso de trusted assembly do SQL Server 2017+ ou chave assimétrica, caso possível.
Como compilar o seu projeto SQLCLR no Visual Studio – Modo Hard/Raiz
Se você tem o Visual Studio e quer entender como as funções foram construídas ou compilar a sua própria versão do assembly, esse código é pra você.
Caso você não saiba o que é SQLCLR ou como compilar e publicar o projeto, dê uma lida no artigo Introdução ao SQL CLR (Common Language Runtime) no SQL Server antes desse post para entender melhor o que é esse recurso.
Download da solução do Visual Studio (VS2019)
Download do Projeto SQLCLR_CurrencyConverter
Código-fonte do fncConverteMoeda.cs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
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("}", "")) ; } } |
Código-fonte do fncConverteMoedaHistorico.cs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
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-fonte do fncConverteMoedaHistoricoRange.cs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 |
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; } } |
And that's it, folks!
I hope you enjoyed this tip and until next time!
Portuguese 
English
Massa, !