Page History

...

Overview

A query is a request for data or information from a database table or a combination of tables. This data may be generated as results returned by the Structured Query Language (SQL) or as pictorials, pictorial trend analyses (graphs or complex results, e.g., trend analyses ) from data-mining tools.

One of several Several different query languages may be used to perform a range of simple to complex database queries. SQL,

Most database administrators are familiar with SQL since it is the most well-known and widely - used query language, is familiar to most database administrators. 

A query can be executed There are a couple different ways to execute a query in a project through a couple different methods. Below you will find them listed with a short description.

...

Method 1: WhereCondition

The first method you can use is to use the WhereCondition. In this case, the data query will be performed on in a Tabletable, created at Edit > Datasets > Tables. Since the table is already selected, you only need to supply the conditional is required. You must also need to run SelectCommand to update the query.

Creation of a Table

Method 2: Query

The second method is similar to the first one, but it uses a Queryquery, created at Edit>Datasets>Queries and linked to a Provider provider (see image below).

To execute the query, you will have need to select one of the Tables tables from the database , and what condition will be used the condition that you want to filter the data. You need to run SelectCommand to update the query.

...

@Dataset . Query . Query1 . Sql Statement = ” s e l e c t ? from Table1 where 
UTCTimestamp  Ticks>=”  +  StartTime . Utc Ticks  +  ”   and   UTCTimestamp  Ticks<=”  +
EndTime . Utc Ticks + ”” ;
@Tag . TableTag = @Dataset . Query . Query1 . SelectCommand () ;

Creation of a Query

Method 3: Different Location

This alternative uses the same statement as presented beforeshown above, but in a different location. Instead of writing it in a Tasktask/Class class or CodeBehind, it can be placed directly into Edit > Datasets > Queries > SqlStatement Column.

SqlStatement field

Method 4: SQL Query Builder

The last option is to use a feature called a SQL Query Builder, that can be found at Edit>Datasets(see image below). It is a graphic interface that facilitates the creation of SQL Statements based on a specific Provider.

Image Removed

Where to find SQL Query Builder

This provider. This method is not as usual as the others, so more details regarding it its functionalities will be detailed below.

How to use the Feature

Loading Data

Once you got your databases and providers configured correctly in the project, the first thing that needs to be done is to load the Data into the Query Builder.

explained below.

Image Added

Appendix A

For the SQLStatement, which is a property of the Query, and for the WhereCondition, which is the property of the Table of the Dataset, you can customize your query in the database, they are properties of type server, so if you modify this property via Script , either on the server or on the clients, the property value will be synced between all clients.

However, we created the possibility for the user to configure Client Tags in the configuration of these properties, for example:

String1: Select * from {tagTable} where {tagWhereCondition}

Where, "tagTable" and "tagWhereCondition" are Client tags.

Note that String1 will always be the same, not being modified in the scripts, what will change is the tags of type Client. When we execute the SelectCommand or SelectCommandWithStatus methods, we resolve these tags in the client's context, passing to the server to correct the right query. Multiple clients can use the same query or table without conflict. Although, they will still be entering the same execution queue on the server (this does not mean multithreading).

To summarize, you could even just put a { tagSQLStatementClient } in the SQLStatement, and the content of the query would be what was in that tag, specific to each client. It is important to remember that in this way it makes no sense to use the Select and Next properties, as they are only for server execution.

...

How to use SQL Query Builder

Loading Data

Before you can begin, you need to make sure your databases and providers are configured correctly in the project. The first thing you need to do is load the data into the Query Builder. To do so, open the Builder builder and click on the Connect button on in the top-left corner.

Starting loading process

A popup will appear , with a ComboBox combobox containing various types of Providersproviders.  Select the one you will work with. In this example, we will set up a connection to a SQLite DB.

Connection Type options

Now, we need to configure the path to the Databasedatabase. It The path is the same one as seen below in the Connection - String’s DataSource field.

DataSource Path

If you wrote the right correct path, you should be able to see all of the available Tables tables and its their elements on in the right corner. Double-click on one of the Tables tables to load its elements into the Sub-Query Structure.

Loaded Tables

Properties

Properties

At A Properties button is located in the top - left corner, there is a Properties Button. When you select it, a popup will open with some customizable properties for that contains the Query Builder's customizable properties.

In the The image below shows all available of the properties that are presentedavailable to be customized.  

Query Builder Properties

Creating Statements

After one of the selected table is loaded tables is selected into the Subsub-query Structurestructure, you can see that a statement is will be initialized at the bottom of the page. 

To You can filter individual columns from the table , it is required only to check the element CheckBox. If none is selected, then your querie will return all table elements.

Image Removed

Check columns

by selecting specific checkboxes. When you do, the system will filter the table results based on your selection. If you do not select any checkboxes, the table will have not have any filters. 

Image Added

The columns allow you to add conditions which There are some columns in which it is possible to add conditions to filter values from the table.

The column options are:

• Visible: Remove the entire column from the query result;results
• Expression: Original The original column name;
• Column Name: Give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of the query.
• Sort Type: Sort the result-set results in ascending or descending order.
• Sort Order: Sort the order of the columns order in resultthe results
• Aggregate: the The values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning. E.g.: Avg, Count, Sum.
• Grouping: Group column elements. Enable creating filtering filter conditions for groups or
• Criteria: Selection condition criteria. Criteria for the selection condition E.g.: =, >, <, ! =.
• Or: Same as the Criteria

...

Statement Example

To better illustrate the Query Builder query builder feature, let’s create an example . Assuming which assumes the following requirements for our query result:

• Only the UTCTimestamp Ticks and HistoricalTag columns are required
• All column names need to have easy to understandbe easily understandable
• Elements will be sorted in Ascendingascending order
• We only want values from HistoricalTag the HistoricaTag values between 10 and 35

Filling Fill in the columns with the required information to perform the query as these requirements as seen in the image below. 

Configuration for Query

The If everything was filled in correctly, the final SQL Statement generated by the Query Builder wasshould be:

Select Table1 . Historical Tag As TagValue , Table1 . UTCTimestamp Ticks As Date
From Table1
Where Table1 . Historical Tag = Table1 . Historical Tag > 10 And Table1 . Historical Tag <= 35
Order By TagValue , Date

...