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.
...
| Code Block | ||||
|---|---|---|---|---|
| ||||
Create Procedure sp_HelloHWD_World_SearchUser @SrcUser nvarchar(80) AS BEGIN SELECT UserIDID AS SrcUser,Name,FullNameRole,Email,EmployeeID FROM Asignet2Hello_World_Demo.dbo.HRHWD_UserRoles with(nolock) WHERE (FullNameName LIKE '%'+@SrcUser+'%' ) OR EmployeeID LIKE '%'+@SrcUser+'%' OR Email like '%'+@SrcUser+'%' OR CAST(UserID AS varchar(25))=@SrcUser OR @SrcUser='') AND EndDate IS NULL END GO |
In this case, we define the first variable “@SrcUsuario” @SrcUser” which is also the first parameter in the “Search” control that we are going to create in Wayfast (Data Binding).
...
SELECT TOP 20: This query will limit the values to first 20 displaying the SrcUsuario SrcUser, FullName Name, Role and Email and EmployeeID columns from the “HWD_Roles” table that where we need to check 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 variable.
...
Once we execute the query, make sure that the stored procedure was 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.
...
In “Stored Procedure” field, pick the object previously created in SQL and set the parameter &SrcUsuario 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.
| Info |
|---|
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 TOP 20” 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. |
...
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.