Overview
In this lesson, we are going to analyze the “Search” control and how this functionality allow us to search elements created in the database through the Stored Procedures by checking values in a table or query.
First we need to take a look on the stored procedures, let’s create a new one to verify the search control on the application!
Create Stored Procedures to use on “Search” control
A Stored Procedure is an object that we can define and then link to the Project. This needs to be created in database using SQL. Connect to the database created for the project in previous lessons and click on “New Query” button on SQL Server Manager.
...
SELECT: This query will limit the values to the SrcUser, Name, Role and Email columns from the “HWD_Roles” table where we need to retrieve the information to search in DB.
FROM: It will refer to the database where the table is created. In this example we used the table created in the “Dataset” lesson called “HWD_Roles” from “Hello_World_Demo” database created for this project.
WHERE: We need to set the values using LIKE% in every of the columns where the Search control will look as variableinclude in the where clause all the fields and parameters we want to use to search using the “LIKE” operator and enclosing the value with “%” symbol.
| Info |
|---|
This guidance on the setup will provide a better performance into the development application. |
Once we execute the query, we need make sure that the stored procedure was actually generated. In the next section, we are going to connect this object with the “Search” control.
How to create a new “Search” control using Stored Procedures?
Preconditions
To create a new control, we need to enter on the page that we are going to include this object. Also it’s necessary to connect with our database through any stored procedure previously generated in order to set the parameters in “Search” control.
Steps
Navigate through the Project’s main dashboard and click on page’s hyperlink.
...
Since “Data Binding” tab is the most important set of values that Wayfast need to work for this new control, let’s review the table with all the fields available in the section:
Data Binding
Field | Description |
|---|---|
Dataset | This option gives the ability to load the page with results from a previous query in the Search control |
Default value | We can set a default option to search and this should match with the value in the associated Stored Procedure (SP) |
AutoSearch | If select “Yes”, we set the Control as parameter in the SP to establish the search at the same time that we are looking into DB for the data. We make the search shorter by establishing a “SELECT” and using LIKE % in the SP. |
Type | We can choose between the following options:
|
Parameters | This field is available when selecting “Stored Procedure” as type. Wayfast use the “&” to set the parameter value |
Fields | Normally we set the first 3 columns on the SP as variables and taking into consideration that AutoSearch required that the Control name itself is set at the first one. Then we can retrieve the description and other values to show in Search. |
Columns to Display | In case that the query contains multiple columns, we can select which ones we want to display in Search control |
Instance | This will show the instance where the Project is connected to DB. |
Execution Type | We can select between the following options:
|
Data Refresh Binding | In this option, we can establish a relationship with other control that can trigger the Search. For example, we select an option from dropdrown list and then we can display the Search field to make other selection. |
...
In this example, the search field is displaying the 3 columns that we request in “Columns to Display” at Data Binding tab. Also the application made the autosearch without entering any input character.
Recap
During the first part of the lesson, we learnt how to create stored procedures in database that can be used for Search functionality. This stored procedure pointed to an specific table where we allocated a user index. This object is needed to connect with controls and make a Search in our application. In the second part, we explained how the “Search” control is setup by using this stored procedures among the right parameters and variables to get a better performance.