Views: 1.479 views
Speak guys!
In this post today, I'm going to share with you a solution I developed to query the dollar (USD), euro (EUR), real (BRL) or any other currency in real time (almost) using a WebService (API) and SQLCLR on SQL Server. This can turn out to be very useful depending on your area of expertise and is something I see many companies even buying services similar to this.
If you don't know what SQLCLR is, read the article Introduction to SQL Common Language Runtime (CLR) in SQL Server before this post to better understand what this feature is.
What will we be able to do at the end of this post:
Where does the quote data come from?
To return the currency quote data, there are many and many websites and API's for consultation. For this example, I chose to use the website API https://www.currencyconverterapi.com/, which has a free consultation plan, in addition to paid plans, in case you have a greater need for consultations.
API documentation can be found this link here.
On the plan price quotation, I believe that most people will already be very well attended. Even on the website it is mentioned that the service can be used free of charge, even for commercial use.
Free plan limitations:
- Currency pairs on request: 2 (You can consult up to 2 coins at a time)
- Number of requests per hour: 100
- Quotation data update interval: 60 minutes (In other words, it's no use trying to update data on the free plan with a frequency of less than 60 minutes)
- Date range in historical search: 8 days (The fncConverteCurrencyHistoricoRange function can only return a data range of up to 8 days)
- Historical queries: 1 year (The dates passed by parameters in the fncConvertCurrencyHistorical or fncConvertCurrencyHistoricalRange functions cannot be older than 365 days from the current date)
If the free plan is not able to serve you, you can look for a paid plan and continue using this same API and these functions.
How to get API authentication key?
To purchase your API key and start using the resources, access this link here, fill in your email and you will receive your access key:
With this key, you can already use your API, respecting the limits of the plan you are going to use, of course.
How to use the created functions
Before demonstrating its use, I thought it was nice to make a brief description about each of these functions.
- fncConvert Currency: Function that aims to convert one currency to another, based on the most current quote possible. The return from this function is a decimal scalar number.
- fncConverteCurrencyHistory: Function that aims to convert one currency to another, based on the date quoted as a parameter (remembering the API limitations). The return from this function is a decimal scalar number.
- fncConverteCurrencyHistoricalRange: Function that aims to convert one currency to another, based on the quotation of the date range informed as a parameter (remembering the API limitations). The return of this function is a table, containing the quotation date (Datetime) and the quotation value (scalar decimal number), where each day is a row in the table.
Examples of use:
|
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:
How to create objects in the database with T-SQL – Easy/Nutella Mode
If you don't have Visual Studio or don't want to worry about understanding how functions were built or building your own version of the assembly, this code is for you. Simple, fast and objective.
T-SQL code to create the functions:
|
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 |
Be very careful when using the TRUSTWORTHY parameter in the database, as in a given situation, it can be a serious security risk to your environment, as I demonstrated in the article SQL Server - Understanding the Risks of TRUSTWORTHY Property Enabled in a Database. Try to use the SQL Server 2017+ trusted assembly feature or asymmetric key if possible.
How to compile your SQLCLR project in Visual Studio – Hard/Root Mode
If you have Visual Studio and want to understand how functions were built or compile your own version of the assembly, this code is for you.
If you don't know what SQLCLR is or how to compile and publish the project, read the article Introduction to SQL Common Language Runtime (CLR) in SQL Server before this post to better understand what this feature is.
Visual Studio Solution Download (VS2019)
SQLCLR_CurrencyConverter Project Download
Source code of fncConverteCurrency.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("}", "")) ; } } |
Source code of fncConverteCurrencyHistorico.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("}}", "")); } } |
Source code of fncConverteCurrencyHistoricoRange.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 see you next time!
Pasta, !