New in 2sxc 7: #1 Visual Query Designer

This is a part of my series The New Features of 2sxc 7 and introduces you to the Visual Query Designer. 

Let's quickly re-cap the most common setup of most web based applications - there are usually 3 MVC-style parts: 

1.  The UI, usually called the View, which is the visual templats, visual actions (mostly in JavaScript) and user-interactions.
2. The data or view-data, usually called the Model. 
3. Something which connects these, usually called the Controller.     

In practical terms this means you need   

1. The Template = View - nowadays built with a smart template - either Razor, Tokens or AngularJS. 
2. The Data = Model - either as an Object for Razor, as an input for Tokens or as JSON for AngularJS. 
3. The Engine = Controller - which connects all this (for the initial output) and handles some user interactions.

The new Visual Query Designer feature in 2sxc 7 is an elegant solution for providing data - the right data - to your view - no matter which technology you're using. It requires no code but you may use some SQL if you want to. 

To discover it, let's just start with some examples. 

Example 1: A Visual Query Retrieving All Event-Modules in DNN

Since this is the first example, I'll explain a bit more about the internals. There is a visual "flow" of data from a source - usually at the bottom - to the destination on top. You can wire up various components to deliver new data (like SQL-Data, 2sxc-Cache, RSS-Streams, Form-And-List, etc.) or to modify data (like apply a filter, sort, etc.). 

Very Simple Visual Query 

Note that this example uses a SQL DataSource to get all Event-Modules. But most queries won't actually use SQL, because they show Content-Items, which are much easier to query. So if you don't like SQL - don't worry :)

Settings of the SQL Data Source

And here the SQL as I wrote it in SQL Manager incl. test-results:

This is the SQL - notice the token which injects the Portal-ID. Note that all Tokens will automatically go through SQL-Injection protection. 

BTW: I'll write another blog specifically for DNN-Events, there I will show more SQL examples.

The Test-Run within the Query Designer

The test-run helps you see what is returned, especially when using various test-values. We found this extremely helpful because otherwise it's to hard to find bugs. The first picture shows the result with the test-parameters used:

...and it will also show you how these parameters were resolved (usefull for complex configuration with multiple tokens and fallback where it's hard to guess what actually was used). This helps you to locate mistakes in your query:

Example 2: A Query returning 2 Streams 

The following query retrieves a list of content-items (in this case all Feedback) and another list of other content-items (Categories for the drop-down) will all be used in one template afterwards:

The Visual Query (with Query Statistics)

The settings

Using Query-Results in your Views

To help you get started we wanted to finish this off telling you how to consume the data. At the moment there are at least 6 common ways to consume data from a Query:

1. Map a token-template to a query, so it visualizes data from that query
2. Map a razor-template to a query
3. Use a query within a razor template just by calling the query, without pre-mapping (for re-using common queries)
4. Use a query within WebAPI to then process the data in WebAPI
5. Auto-convert the query result to JSON through WebAPI
6. Auto-provide the query-data as JSON without WebAPI

I'll teach you how to use each case within the next few blogs. 

Try it Yourself!

Use the Learn-Manual here.

With love from Switzerland,
Daniel 

Daniel Mettler grew up in the jungles of Indonesia and is founder and CEO of 2sic internet solutions in Switzerland and Liechtenstein, an 20-head web specialist with over 600 DNN projects since 1999. He is also chief architect of 2sxc (2SexyContent - see forge), an open source module for creating attractive content and DNN Apps.