Invoking REST APIs with SQLCLR and Newtonsoft's Json.NET

[MihailoJoksimovic]

In the previous article we discussed the process of importing 3rd party libraries into Azure SQL Managed Instance. Now we are going to cover the process of building a CLR User-defined Function (UDF)  that relies on usage of REST API and Newtonsoft’s Json.NET library to fetch and parse the output. Our how-to example will be based on building a function that provides a currency exchange conversion. 

For the sake of clarity, let’s start first by discussing the end-result and what we’d like to achieve. 

The end-result 

Let’s create the User-defined Function that does the currency conversion. This example should be simple enough to follow, while providing the opportunity to discuss some important aspects that need to be taken care of (setting proper permission sets, adding required libraries, etc.). You can find the source code at the end of this article. 

Here is how’d like to invoke this function from SQL Managed I instance: 

-- Convert $50 USD to EUR 
SELECT ConvertCurrency(50, ‘USD’, ‘EUR’)

Pretty simple, right? You can also use it against the data in your table:

SELECT 
  amount as OriginalAmount, 
  currency as OriginalCurrency, 
  ConvertCurrency (amount, currency, ‘EUR’) as “Amount in EUR” 
FROM sample_values

Do note that as a prerequisite to this, we need to import the Newtosonft’s Json.NET library first. As a reminder, you can find the detailed explanation on how to do this in the previous article.

Importing our CLR UDF

Here is the C# code for the UDF function that we’d like to create:

using System;
using System.Data;
using System.Data.SqlTypes;
using System.IO;
using System.Net;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Microsoft.SqlServer.Server;

public class CurrencyConverter
{
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlDouble Convert(SqlDouble amount, SqlString fromCurrency, SqlString toCurrency)
    {
        // Output contains list of currency parities
        string jsonResponse = GetCurrencyParities(fromCurrency.ToString());

        JObject parities = JObject.Parse(jsonResponse);
        SqlDouble parity = SqlDouble.Parse(parities[toCurrency].ToString());
        return amount * parity;
    }

    /// <summary>
    /// Returns parities for specified currency.
    /// Invokes a fictional Currency API that takes currency name as an input
    /// and returns dictionary where keys represent target currencies, and
    /// values represent the parities to source Currency.
    /// </summary>
    /// <remarks>
    /// For example, for GetCurrencyParities("EUR"), the response would be:
    /// { "USD": 1.2, "CAD": 1.46, "CHF": 0.96 }
    /// </remarks>
    private static string GetCurrencyParities(string fromCurrency)
    {
        string url = String.Format("https://example-api.com/currency/{0}.json", fromCurrency);
        HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
        HttpWebResponse response = (HttpWebResponse)request.GetResponse();
        StreamReader reader = new StreamReader(response.GetResponseStream());
        string responseData = reader.ReadToEnd();
        return responseData;
    }
}

Keep in mind that this code has been simplified as it is aimed at showcasing the example, rather than being production-ready. It purposefully omits any validations whatsoever.

Once you have the code compiled into a DLL, the next step is to have it imported into your SQL Managed Instance. One difference to previously imported libraries is that our code can be imported with EXTERNAL_ACCESS permission set, so that it can execute Network operations.

# Execute the following in Powershell: 

$assembly = "C:\path\to\YourRestApiClient.dll"

(Format-Hex $assembly | Select-Object -Expand Bytes | ForEach-Object { '{0:x2}' -f $_ }) -join '' | Set-Clipboard 

# Execute the following on SQL MI instance: 

CREATE ASSEMBLY Currency_converter 
FROM 0x(paste the content of your clipboard here) 
WITH PERMISSION_SET = EXTERNAL_ACCESS;

Your DLL should now be imported without issues. Time to celebrate!

The next step is to have the UDF created. Here’s how to do it:

# Execute the following on SQL MI instance: 
CREATE FUNCTION ConvertCurrency ( 
  @amount FLOAT, 
  @fromCurrency NVARCHAR(3), 
  @toCurrency NVARCHAR(3) 
) 
RETURNS FLOAT AS EXTERNAL NAME [Currency_converter].[CurrencyConverter].[Convert];

And that’s all! Assuming that the API you are calling works properly, the following function should yield the actual results:

Congratulations!

NOTE: If you have custom Network Security Groups (NSGs) configured on the subnet where your MI instance is, do make sure that the outbound traffic to API’s destination is allowed. Failing to do so will result in network error.

Further reading

Here are some of the additional resources you might find useful:

• Embed C# in the heart of the SQL Query Engine
• Importing .NET FX and 3rd party DLLs into Azure SQL MI
• Importing .NET FX and 3rd party DLLs into Azure SQL MI (YouTube video)
• Azure SQL Database External REST Endpoints Integration Public Preview
• Common Language Runtime Integration
• Architecture of CLR Integration
• CLR Integration Code Access Security

We’d love to hear your feedback! If you’ve enjoyed this article, or think there might be some improvements to be made, please leave your comment below. Thanks for reading!