Typed DB queries

App code can easily make use of any SQL scripts contained inside the project. All SQL scripts in QueryStorm can make use of the SQL preprocessor. One ability of the preprocessor is generating strongly-typed DB query classes.

This feature makes it easy for .NET code to interact with databases. The SQL code is written using all of the niceties of the code editor and is simply invoked from .NET using strongly typed classes for both the query as well as the results.

Defining a query

Decorating a SQL query with the {query()} directive will result in a new class file being generated and nested under the script whenever the script file is saved.

The generated file contains a strongly typed class that represents the query. The query can be instantiated and invoked by other classes in the app in order to interact with the database.

Using DB queries from .NET code

Just like other services, query instances can be obtained through dependency injection.

For example, if a component needs a db query to perform a task, it can request it via its constructor:

public class Component1 : ComponentBase
{
    Scripts.MsSQL_query_1 query;
    public Component1(Scripts.MsSQL_query_1 query)   
    {
        this.query = query;
    }

    [EventHandler("some_event")]
    public void HandleSomeEvent()
    {
        var results = query.Run();
    }
}

Note that the query class is defined in the Project.Scripts namespace, because scripts are usually saved in the scripts folder.

Alternatively, an instance of the query can be created by resolving it directly from the IOC container:

var query = Container.Resolve<Scripts.MsSQL_query_1>();

Query classes have dependencies of their own, so letting the container instantiate them is easier than doing it manually. These dependencies are used to read connection strings, access the data context and prompt the user for DB credentials when necessary.

Query arguments

Queries can also have arguments. For example, if we want to define a query which lets us fetch a list of departments whose names contain a specified search term, we could do it as follows:

{query(string searchTerm = "Sales")}
select
    *
from
    HumanResources.Department d
where
    [Name] like '%' + @searchTerm + '%'

In the above example, the parameter searchTerm is defined in the preprocessor directive and used in the where clause to filter the rows. A default value of "Sales" has been defined for this parameter which will be used when executing the script manually.

When this script is saved, it will generate the query class in which the Run and RunAsync methods have a searchTerm parameter.

// find rows that have Marke in the name (e.g. Marketing) 
var results1 = query.Run("Marke");

// we can also use the async version
var results2 = await query.RunAsync("Marke");

Strongly-typed results

If the query has been executed prior to being saved, the results will be analyzed and used to generate a strongly typed accessor class for the results.

In this case, the the Run and RunAsync methods will return strongly typed results:

On the other hand, if the script is saved without any results in the results-grid, strongly typed accessors will not be generated and the Run and RunAsync methods will return the raw Tabular instance which allows access to the data, but not in a strongly typed way.