Skip to content

Changes in version 2.5

This version updates all third party libraries in QueryStorm to the latest version. The main improvements achieved with this update are upgrading the version of C# from 7.1 to 9, as well as upgrading SQLite to the latest version which has window functions.

Additionally, this version also adds convenient function caching as well as several smaller fixes.

Moving to C# 9

Roslyn is used extensively in QueryStorm for running and compiling user code. In this version, Roslyn has been updated from v3.5 to v3.11. As a consequence, the version of C# in QueryStorm has been upgraded from C# 7.1 to C# 9.

Some of the more impactful new features this adds are:

Detailed information on the new language features in C# can be found on the official Microsoft what's new in C# page.

Window functions in SQLite

The version of SQLite used by QueryStorm has been upgraded from 3.15.2 to 3.36.0.

The major change this introduces is availability of SQLite window functions. These make it easy to perform calculations that depend on surrounding rows. A typical example would be calculating a running total or a moving average.

For a concrete example, the following query calculates a moving average that takes into account one preceding and one following row in addition to the current row:

1
2
3
4
5
6
7
8
9
SELECT 
    month,
    amount,
    AVG(amount) OVER (
        ORDER BY month
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) SalesMovingAverage
FROM
    SalesInfo;

Useful examples of window functions can be found in this tutorial. A more in-depth exploration of window functions can be found in the official SQLite documentation.

Function results caching

This release of QueryStorm also introduces a new [Cached] attribute that can be applied to user defined Excel functions.

For example, for a function that translates text from one language to another, it makes sense to cache the result so it can be reused later if the function is evaluated again with the same input text in the future.

1
2
3
4
5
[ExcelFunction, Cached]
public async Task<string> TranslateEnglishToSpanish(string textInEnglish)
{
    // ... use e.g. Google Translate API here
}

The [Cached] attribute ensures the result on evaluating a function is cached and reused every time the function is reevaluated using the same input parameters. This can reduce load on APIs as well as conserve paid cloud resources.

For now, the cache is only in-memory so the cache starts out empty each time Excel is started. We're considering adding support for persistent caching as well as more advanced expiration policies in the future.

Misc fixes

  • Fixed logging in C# scripts via the static DebugHelpers.Log() method.
  • Fixing several issues with return values of custom Excel functions (e.g. when returning Task<IEnumerable<T>> results were not laid out correctly).
  • Allow binding component properties with enum types.
  • Limiting the Package Manager to stable NuGet packages since, most often, choosing a stable release is the correct option. An option to include pre-release versions will likely be added later on.
  • Fixing an race condition with QueryStorm Apps that define a ribbon (they were crashing on load occasionally).
  • Support for opening txt files in the IDE.