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 pictorial trend analyses (graphs or complex results, e.g.) from data-mining tools.
Several different query languages may be used to perform a range of simple to complex database queries.
Most database administrators are familiar with SQL since it is the most well-known and widely used query language.
A query can be executed 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 the WhereCondition. In this case, the data query will be performed in a table, created at Edit > Datasets > Tables. Since the table is already selected, you only need to supply the conditional. You must also run SelectCommand to update the query.
Method 2: Query
The second method is similar to the first one, but it uses a query, created at Edit>Datasets>Queries and linked to a provider (see image below).
To execute the query, you need to select one of the tables from the database and the condition that you want to filter the data. You need to run SelectCommand to update the query.
Method 3: Different Location
This alternative uses the same statement as shown above, but in a different location. Instead of writing it in a task/class or CodeBehind, it can be placed directly into Edit > Datasets > Queries > SqlStatement Column.
Method 4: SQL Query Builder
The last option is to use a feature called a SQL Query Builder, found at Edit>Datasets. It is a graphic interface that facilitates the creation of SQL Statements based on a specific provider. This method is not as usual as the others, so more details regarding its functionalities will be explained below.
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 and click on the Connect button in the top-left corner.
A popup will appear with a combobox containing various types of providers. Select the one you will work with. In this example, we will set up a connection to a SQLite DB.
Now, we need to configure the path to the database. The path is the same one seen below in the Connection String’s DataSource field.
If you wrote the correct path, you should be able to see all of the available tables and their elements in the right corner. Double-click on one of the tables to load its elements into the Sub-Query Structure.
A Properties button is located in the top left corner. When you select it, a popup will open that contains the Query Builder's customizable properties.
The image below shows all of the properties that are available to be customized.
After the selected table is loaded into the sub-query structure, a statement will be initialized at the bottom of the page.
You can filter individual columns from the table 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.
The columns allow you to add conditions which filter values from the table.
The column options are:
To better illustrate the query builder feature, let’s create an example which assumes the following requirements for our query result:
Fill in the columns with these requirements as seen in the image below.
If everything was filled in correctly, the final SQL Statement generated by the Query Builder should be: