Overview
In this document, we are going to focus on “Search” control and how this functionality allow us to search elements created in DB through the Stored Procedures by checking values in a table or query.
Create Stored Procedures to use on “Search” control
A Stored Procedure is an object that we can define and then relate it to the Project. This needs to be created in DB using SQL. First connect to the DB created for the Project and click “New Query”. Then create the Stored Procedure with the following syntax:
...
| Code Block | ||||
|---|---|---|---|---|
| ||||
Create Procedure sp_Hola_Mundo_SearchUser
@SrcUsuario nvarchar(80)
AS
BEGIN
SELECT UserID AS SrcUsuario,FullName,Email,EmployeeID
FROM Asignet2.dbo.HR_User with(nolock)
WHERE (FullName LIKE '%'+@SrcUsuario+'%' )
OR EmployeeID LIKE '%'+@SrcUsuario+'%'
OR Email like '%'+@SrcUsuario+'%'
OR CAST(UserID AS varchar(25))=@SrcUsuario
OR @SrcUsuario='')
AND EndDate IS NULL
END
GO |
In this case, we define the first variable “@SrcUsuario” 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.0 a look on the structure:
SELECT TOP 20: This query will limit the values to first 20 displaying the SrcUsuario, FullName, Email and EmployeeID columns from the table that we need to check the information to search in DB.
FROM: It will refer to the DB where the table is created.
WHERE: In this case, we need to set the values using LIKE% to establish in every of the columns where the Search control will look as variable.
NOTE: This configuration set it up properly will provide a better performance into the development application.
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 out DB 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 hyperlink.
...
Page’s details form page is displayed and 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 (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 DB.
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:
|
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 “Preview” button to verify that the “Search” control is ready to be use in Project’s application.
...
Recap
In the first part, we learnt about how to interact with DB by creating Stored Procedures. This object is able to be connected with controls in order to make a Search in our application. In the second part, we explained how the “Search” control is setup by using the SP and using the right parameters and variables to get a better performance.