Deprecated: Array and string offset access syntax with curly braces is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\script-loader.php on line 707

Deprecated: Array and string offset access syntax with curly braces is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\script-loader.php on line 707

Deprecated: Array and string offset access syntax with curly braces is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\script-loader.php on line 708

Deprecated: Array and string offset access syntax with curly braces is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\script-loader.php on line 708

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\load.php on line 656

Deprecated: Function create_function() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-content\themes\integral\inc\widgets\feature_widget.php on line 127

Deprecated: Function create_function() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-content\themes\integral\inc\widgets\service_widget.php on line 126

Deprecated: Function create_function() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-content\themes\integral\inc\widgets\testimonials_widget.php on line 96

Deprecated: Function create_function() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-content\themes\integral\inc\widgets\our_clients_widget.php on line 99

Deprecated: Function create_function() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-content\themes\integral\inc\widgets\our_team_widget.php on line 178

Deprecated: Function create_function() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-content\themes\integral\inc\widgets\projects_single_widget.php on line 169

Notice: Trying to access array offset on value of type bool in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\theme.php on line 2245

Notice: Trying to access array offset on value of type null in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-content\themes\integral\inc\widgets.php on line 17

Notice: Trying to access array offset on value of type null in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-content\themes\integral\inc\widgets.php on line 39

Notice: Trying to access array offset on value of type null in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-content\themes\integral\inc\widgets.php on line 61

Notice: Trying to access array offset on value of type null in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-content\themes\integral\inc\widgets.php on line 73

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359
SQLite, C# and Excel - QueryStorm Blog
Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Notice: Trying to access array offset on value of type null in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-content\themes\elemental\functions.php on line 104

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

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.


Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Leave a Comment
Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

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


Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359

Deprecated: Function get_magic_quotes_gpc() is deprecated in E:\Websites\thingieq\blog.querystorm.com\wwwroot\wp-includes\formatting.php on line 4359