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.
To read more about the new project system in QueryStorm 2, click here.
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.
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
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
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
Read more about the new model-binding API in QueryStorm 2, by clicking here.
QueryStorm now lets you reference NuGet packages in your projects when automating workbooks and creating custom Excel functions.
QueryStorm now supports using VB.NET for automating workbooks and building custom functions.
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.
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.
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).
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).
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.
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.
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