Overview

Wayfast Datasets allow us to connect a page with a data source like a database or an API. Databases are one of the Wayfast Pillars. Wayfast Application Project’s are mostly used to interact with data sources and Datasets are the mean to achieve that goal. As a general rule we suggest using Stored Procedures to interact with databases but Datasets can run ad-hoc queries too.

Datasets don’t need to be read-only. They can execute write operations like insert, update and delete.

As a Wayfast standard, write operations are called through actions. We will review Actions in a different lesson.

Today we are going to review how to execute a store procedure for reading data and displaying it in a Grid.

Preconditions

Before we start, this lesson assumes the following preconditions:

1. We already have a project connected with a database. You can find more details here: Attach or create databases. Let’s reuse our Hello World Project.

2. We need an active requirement so we can create objects in our database.

3. This lesson assumes you already know what a stored procedure is. If you don’t you can read about them here.

Step 1: Create database object and update Wayfast DB Model

First let’s connect to the project’s database using SQL Server Management Studio.

Click new query and paste the following script to create a stored procedure:

CREATE PROCEDURE spHelloWorld_GetRoles
AS
DECLARE @Result TABLE(Id INT, RoleName NVARCHAR(128), RoleDescription NVARCHAR(256));

INSERT INTO @Result VALUES (1, 'Admin', 'Can do everything')
INSERT INTO @Result VALUES (2, 'HR Manager', 'Can create position and approve hiring processes.')
INSERT INTO @Result VALUES (3, 'HR Lead', 'Can edit positions and start hiring process.')
INSERT INTO @Result VALUES (4, 'HR Analyst', 'Can read positions and and add candidates.')

SELECT Id, RoleName, RoleDescription FROM @Result

Every time we edit our database we need to synchronize Wayfast DB Model so Wayfast is aware of all the changes we just did.

To do that let’s go back to Wayfast, navigate to “Databases” and click on the “Synchronize DB” button on menu.

A popup will be displayed, just click on the “Synchronize” button.

Once the popup closes all new database objects are available in wayfast.

How to create a new Dataset? (IN REVIEW)

Go to “Project Pages” at the 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.

One of the key details we need to understand about Datasets is the “Execution Type”. This field tells Wayfast when we want to execute this query. The first time we display the page, every time load the page or based on another control’s execution. This will impact the time it takes to load your page and how it is perceived by the end user. Let’s review this options in detail:

In this example, we are going to select “Pre” so we can have our data refreshed only once.

After this, go to “Data Binding” tab. In this section, we can select the Stored Procedure we just created.

The “Add relationship” field allows to trigger this Dataset load based on a control’s event. We will review this specific case in the “Actions” lesson.

Let’s click on the “Submit” button to save the dataset and our Dataset will be available to be executed from other controls in this page.

Now that we have can retrieve data from our DB we need to show it in the Application’s UI.

How to connect a Dataset with a Grid?

Let’s create an example using grid columns linked to the new dataset.

It is 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.