Skip to content

What's new in QueryStorm 2.0

Hola, Hello, Ciao!

We're delighted to welcome you to the release of QueryStorm version 2!

QueryStorm v2 has been over a year in the making and is a comprehensive overhaul of the original version. It includes several new areas, some major new features and hundreds of smaller changes and improvements.

First some warnings about breaking changes:

  • Some features that were available in the original version are not yet available in the new version (quick queries, csv tables and support for certain DBs).
  • QueryStorm2 will not be able to see any embedded code files in workbooks made with the original version.

That said, QueryStorm2 can run side-by-side with the original version of QueryStorm allowing for an incremental transition.

So without further ado, here's what's new in QueryStorm 2...

New project system

Code files in QueryStorm are organized into projects. Projects contain user code, scripts, and configuration files, and are fairly similar to projects in Visual Studio. Projects can be compiled after which the produced dlls are loaded by the QueryStorm runtime.

Creating a workbook project

To read more about the new project system in QueryStorm 2, click here.

Automation changes

In the previous version of QueryStorm, automation was set up using the Automation pane in the UI. The Automation pane allowed defining jobs that consisted of a sequence of scripts to execute and a list of events that trigger the execution of the job.

In the new version of QueryStorm, the automation pane is no longer - setting up automation is now done via code instead.

Removed automation pane

While the old approach worked, it lacked flexibility and error handling and was also quite slow. In the new version, we've made .NET classes the main way to automate the workbook. Instead of using scripts for automation, we now build a dll from user code which is saved into the workbook and used by the runtime. This reduces the burden on the runtime (it no longer needs to include Roslyn) and dramatically improves stability, performance and flexibility of automated workbooks.

We've taken care to ensure that SQL users are not harmed by this change either. They can now set up automation as part of their SQL scripts, using a very simple preprocessor syntax (as demonstrated below). They do not need to know any other programming languages to set up automation. Under the hood, the preprocessor generates .NET code, but this is done automatically and the user does not need to worry about this.

Workbook automation via SQL

SQL users can set up automation using a simple preprocessor syntax, as illustrated below:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- specify the triggers that call the command
{handles orderDate}

-- output results into the 'orders' table
{@orders}
select
    *
from
    Sales.SalesOrderHeader soh
where
    OrderDate = @orderDate -- read the orderDate cell's value

In the example above, the script is executed every time the cell with the name orderData is changed. This cell is also used as a parameter in the query, and the results of the query are outputted into a workbook table named orders.

Read more about setting up automation via the SQL preprocessor by clicking here.

Workbook automation via .NET (model-binding)

Developers can automate workbooks using C# and/or VB.NET and a model-binding API. This API provides strongly typed access to data in Excel tables, and uses bindings to sync changes between component classes and the workbook, allowing you to focus on business logic rather than on interacting with Excel.

The example below illustrates a component that controls (part of) a workbook. The binding attributes "glue" the component to the workbook, leaving the rest of the code free to focus on business logic that manipulates the state of the component.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
public class SampleComponent : ComponentBase
{
    [Bind("searchText")]
    public string SearchText { get; set; }

    [BindTable]
    public PeopleTable People{ get; set; }

    private string _Message;
    [Bind("messages")]
    public string Message
    {
        get => _Message;
        set { _Message = value; OnPropertyChanged(nameof(Message)); }
    }

    [EventHandler("searchText")]
    public void Test()
    {
        Message = $"Searched for '{SearchText}' at {DateTime.Now.ToShortTimeString()}";

        People.ForEach(t =>
        {
            string nameWithoutStar = t.FirstName.TrimEnd('*');
            if(t.FirstName.IndexOf(SearchText, StringComparison.OrdinalIgnoreCase) >= 0)
                t.FirstName = nameWithoutStar + "*";
            else
                t.FirstName = nameWithoutStar;
        });

        People.SaveChanges();
    }
}

Read more about the new model-binding API in QueryStorm 2, by clicking here.

NuGet support

QueryStorm now lets you reference NuGet packages in your projects when automating workbooks and creating custom Excel functions.

NuGet package manager

VB.NET support

QueryStorm now supports using VB.NET for automating workbooks and building custom functions.

Creating a VB.NET workbook project

Independent tabs

In the previous version of QueryStorm, all script tabs shared the same connection. In the new version of QueryStorm, each tab is its own independent context. This means you can have e.g. a SQL Server connection in one tab, a SQLite connection in another and a C# code file in a third and you can switch between them without any ceremony.

Independent tabs

IDE improvements

The IDE now offers additional advanced capabilities such as code refactoring, symbol renaming, navigating from error messages to source code, and better performance for a smoother code editing experience.

Code fixes and refactoring

Read more about the new IDE features by clicking here.

Creating custom functions via .NET and SQL

The new version of QueryStorm lets you define custom functions using .NET as well as SQL. Functions defined with SQL will, obviously, be used to fetch data from databases, while functions written in .NET can perform calculations or fetch data from the outside world (e.g. from the web or some API).

Read more about creating Excel with SQL and with .NET in the documentation.

Publishing and installing functions

QueryStorm now includes a mechanism of sharing custom functions between users, called QueryStorm Extensions. Users of the full version of QueryStorm can create functions and publish them via a network share or via a proprietary Azure Artifacts server. Users with the QueryStorm runtime can then download and install these packages (provided they have access to the repository).

Installing a function

All QueryStorm users (Runtime and Full) can make use of the official packages that the QueryStorm team releases. For example, one such package contains a very useful Query function, which is similar to the one available in GoogleSheets.

Query function

Smaller runtime installer

The QueryStorm runtime is now distributed separately as a smaller installer. The runtime is intended primarily for business users and clients. It allows users to run automated workbooks and to install packages that contain custom Excel functions. The installer for the runtime is ~8MB in size, while the installer for the full version is ~31MB.

Note that the full version includes the runtime, so it's not necessary to download the runtime separately if you're using the full version.

Missing features

As mentioned, some features of the original version of QueryStorm are not yet implemented. This primarily includes:

  • Quick queries
  • Support for using external CSV files as tables
  • Support for Oracle, Access, and DB2

Features on the horizon

In the medium-to-long term, the following major features are on our todo list:

  • A C#/VB.NET debugger
  • A standalone .NET Core version of QueryStorm that will be cross platform and usable for Google Sheets and Office online documents