Click on the banner to learn about and purchase my database training on Azure

SQL Server – How to consult the dollar (USD), euro (EUR) or any other currency in real time with API and SQLCLR

Views: 488
Reading Time: 6 minutes

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 free, 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 years (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:

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:

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

Source code of fncConverteCurrencyHistorico.cs

Source code of fncConverteCurrencyHistoricoRange.cs

And that's it folks!
I hope you enjoyed this tip and see you next time!