Search
- Sergio Rodriguez
- Matias
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.
We can take the following example to create the Stored Procedure using this syntax:
Create Procedure sp_HWD_SearchUser
@SrcUser nvarchar(80)
AS
BEGIN
SELECT ID AS SrcUser,Name,Role,Email
FROM Hello_World_Demo.dbo.HWD_Roles with(nolock)
WHERE (Name LIKE '%'+@SrcUser+'%' )
AND EndDate IS NULL
END
GOIn this case, we define the first variable “@SrcUser” which is also the first parameter in the “Search” control that we are going to create in Wayfast (Data Binding).
Inside the query, let’s take a look on the structure:
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 include 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.
Once we execute the query, we need make sure 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.
In this screen, we can observe all the Datasets, Controls and Actions associated to the page previously created.
Under the “Controls” section, choose the “Search” option. Wayfast refreshes the screen with the buttons associated to this control.
Click on “New” button and a popup will be prompted with the necessary details to fill in. At “General” tab, complete the field name and description.
Turn to “Data Binding” tab and take a look on the settings that you can set to make this new control more accurate on what we are programing.
In “Stored Procedure” field, pick the object previously created in SQL and set the parameter &SrcUser (Wayfast use the “&” to set the parameter value). Then you can define the fields and columns to display which should be related to the table where the application will retrieve the data from database.
The first 3 columns can be used as variables. First column is referenced as ControlName, second column as ControlName.Description and third one as ControlName.AdditionalValue.
In this example, we will display Name, Role and Email when the results are retrieved from the stored procedure.
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. |
Now that we know better the conditions to make the “Search” control works, we can verify the “Layout” tab. This section will define which is the place, order and alignment for the new control in the Page.
After clicking on “Accept” button, “Search” control is generated on the corresponding section.
Click on “Preview” button to verify that the “Search” control is ready to be used in project’s application.
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.