SQLite, C# and Excel

SQLite is a remarkable little database engine. Although it serves a different purpose, it can do much of what the big client-server databases can do. It’s fast, reliable, extremely well tested and feature rich. However, if you need a function that SQLite does not offer, defining one for yourself can be tricky. Not so in Querystorm! in QueryStorm, you can define functions in C# and easily expose them to SQLite.

If you’ve worked with QueryStorm at all, you probably know that it comes with a SQLite engine that it uses to work with data in Excel tables. With the ability to extend this database engine with C#, you can add extra functionality and extra data sources to your queries.

Here’s a video I made when I first implemented this functionality in QueryStorm:

Creating custom functions for SQLite via C# from QueryStorm on Vimeo.

SQLite offers developers a mechanism for registering extra functions, but this is not very handy for ad hoc purposes. QueryStorm internally uses this mechanism quite heavily, though.

Scalar functions

Let’s assume we need a function that will convert a number into words in English, so that e.g. select numberToWords(123) returns one hundred and twenty-three. No such function exists (out of the box) in SQLite, but a C# implementation is just a quick Google search away.

We can just copy and paste it, and use it from C# straight away:

The C# function

But what if some of our coworkers just use SQL and don’t know any C#? To expose the function to them, we simply need to decorate it with a [SQLFunc] attribute and embed it into the workbook.

Exposing the C# function to SQLite

Now if we reconnect with SQLite, we can see the function as if it was a normal SQLite function. Let’s generate some numbers and test the function out:

SELECT
    *, NumberToWords(value)
FROM
    generate_series(5, random(1,10000),random(1,10000)) gs

Here are the results:

C# function in action

The new function is fully supported in autocomplete. We can even add a description for the function and its parameters using XML comments:

Notice the XML comment

The tooltip shows the contents of the XML comment

Requirements for scalar functions

The requirements for a C# function to be visible to SQLite (as a scalar function) are:

  • must be in a script that’s embedded into the workbook
  • must compile
  • must be decorated with [SQLFunc]

Table-valued functions

Suppose we need data from an external data source in our SQL queries. This can be anything really, but for this example I’ll use the Fixer exchange rate API.

To expose this API to SQLite as a table-valued function, I’ll define a C# function that uses it and returns an IEnumerable<> result. To mark it as a table-valued function, all we need to do is decorate it with the [SQLFuncTabular] attribute and embed it into the workbook.

using Newtonsoft.Json.Linq;

///<summary>Gets the exchange rates for the specified currency on the specified date.</summary>
///<param name="date">The date for which to get the currency rates for</param>
[SQLFuncTabular]
private static IEnumerable<Rate> GetExchangeData(DateTime date, string baseCurrency = "EUR")
{
    var response = new WebClient()
        .DownloadString($"https://api.fixer.io/{date.ToString("yyyy-MM-dd")}?base={baseCurrency}");

    string ratesStr = JObject.Parse(response).Property("rates").Value.ToString();

    return JsonConvert
        .DeserializeObject<IDictionary<string, decimal>>(ratesStr)
        .Select(kvp => new Rate() { Currency = kvp.Key, Exchange = kvp.Value });
}

public class Rate
{
    public string Currency { get; set; }
    public decimal Exchange { get; set; }
}

Once the script is embedded into the workbook, we can call the function from SQLite:

select * from GetExchangeData(date(), 'USD')

Here are the results:
Demo application screenshot

We can also join it with other tables or table-valued functions. For example, to fetch the exchange rates for the past 36 months, we could use the following SQL:

SELECT
    date('now', Format('-{0} months', value)) [day], rates.*
FROM
    generate_series (36, 0)
    , GetExchangeData ([day], {baseCurr}) rates
ORDER BY
    day ASC

Here, I’m using the generate_series table-valued function to generate a series of integers which I convert to dates (one for each month) using the date and format functions. I then join each date with the exchange rates for that date. For this, I use the GetExchange function we created earlier.

Requirements for table-valued functions

The requirements for a C# function to be visible to SQLite (as a table-valued function) are:

  • must be in a script that’s embedded into the workbook
  • must compile
  • must be decorated with [SQLFuncTabular]
  • must return IEnumerable<T>

Conclusion

In QueryStorm it’s easy to add additional functions to the SQLite engine. Scalar functions can do calculations, but they can also interact with Excel, write to files, send emails and what not. With table-valued functions, any data source that you can access from C#, you can easily expose to SQLite.

Leave a Comment

Your email address will not be published. Required fields are marked *