Overview

In this lesson we are going to review Wayfast Datasets. A dataset allows us to connect a page with a data source like a database or an API.

Initially all the Project’s information come from the Database through Stored Procedures (SP) and it’s available to be displayed by Controls or Grids. These controls that every page has linked will interact directly with our Project’s database. But this is not the only relationship that we can establish with the DB information, we can apply logic through Actions (as example using Buttons) that allow us to generate changes in our DB like Insert, Update, Delete but it can be as complex as the requirement needs.

To depict how it works we will show you and example that include calling a database store procedure for retrieving information and displaying it in a Wayfast Application.  Let’s review how we can make this interaction possible in Wayfast.

Preconditions

We have 3 important preconditions before start working with Datasets.

1. We need to connect the project with an existing or new database. You can find more details about in another lessons where we attached or created databases.

2. We need to create a table or identify the table where we are going to get the data to display using the Dataset

3. We need to create an object in this database, for example, an Stored Procedure to link the table and interact with Dataset information.

How to create a Stored Procedure in DB and link to Dataset?

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:

CREATE TABLE HWD_Roles (
Name  nvarchar(50),
Role  nvarchar(50),
Email nvarchar(50),
)
GO

CREATE PROCEDURE spHWD_Roles_Get
AS
  BEGIN
    SELECT * FROM HWD_Roles with(nolock)
  END
GO

This is a simple statement where we indicate 3 input parameters “Name”, “Role” and “Email”. In next steps, these parameters are going to be used in Wayfast to connect with Controls. 

Now we can go back to Wayfast and synchronize the stored procedure in the environment. If the project is newly created and connected to a database, there’s no need to synchronize both objects. Dataset will retrieve all the objects associated between both entities automatically.

Let’s review how synchronize the objects in Wayfast. Keep in mind this action must be triggered inside the project before enter to the page where we are going to create the new dataset. Otherwise the stored procedure won’t be available in search field.

On Wayfast, focus on the active task at left side navigator page.

Click on “End Task” button related to project’s requirement.

A popup is displayed, click on “Synchronize” button. Now the stored procedure created in database will be available to connect with dataset.

How to create a new Dataset?

Go to “Project Pages” at top menu. Select any of the pages available in the project. Then click on “New Dataset” button

A popup is displayed and we need to complete the mandatory fields.

Execution Type field is an important feature since this will define what type of performance has the dataset.

In this example, we are going to select “Pre” so we can have our data refreshed at the moment we enter to the page.

After this, focus on “Data Binding” tab. In this section, we can select the Stored Procedure previously created for the information available in DB.

If we click on “Add relationship” button under “Dataset Refresh Binding” field, we can link the dataset with any of controls. We can find another example in “Actions” lesson.

After completing this details, click on “Accept” button to save the dataset. Now Stored Procedure can be quickly referenced from other controls in this page.

Now that we have the information available from DB and connected to the Project, we need to make it visible in the Application’s UI.

How to connect the new Dataset with a “Grid Column” control?

Let’s create an example using grid columns linked to the new dataset. It’s important to complete the “Field” value with the same parameter created in Stored Procedure. In this example, we are going to use “Name”, “Role” and “Email” at “Field” that matches with the stored procedure previously added to database.

Turn to the page where we are going to include this object. Under the “Controls” section, choose the “Grid Column” option. Click on “New” button

A popup will be prompted to generate the control. Complete the required fields, make sure that the new control has the same value than the one in table generated and associated to the stored procedure.

Turn to “Layout” tab and indicate the order where we want to display this column. In our example, we want to display a table with 3 columns as we previously mentioned.

Turn to “Data Binding” tab and connect to the dataset previously generated in the page. Dropdown will retrieve this information to connect the control.

Click on “Submit” button

Repeat the steps for each column in the table “HWD_Roles" created in database.

Resuming our lesson, we created a table and stored procedure in database. Then we created a page to display the values that we have in this table. Dataset is needed to connect the information in database and the controls (grid columns) that will be pointing to the data.

Let’s review how this interaction is displayed on the preview. This is an easy way to check how the interface is representing the information available in our Project DB.

All the information in the table is now available in the page. In the following lesson, we are going to analyze how we can insert values on the table using “Actions” feature.

Recap

In the first part, we learnt how Pages interact with database by creating Datasets. Then we made a first approach using SQL to create a simple Stored Procedure that was linked to Dataset. In the second part, we explained how the Controls under pages can display in different ways the information available on database, assigning the place and source. In the next lessons, we will learn how to connect the Controls with Actions that user will have in pages to make the application more dynamic.