...

Overview

In this lesson we are going to review Wayfast Datasets . A dataset allows allow 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 syntaxDatabases 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. 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: Associate an instance

First let’s open Wayfast in a web browser and navigate to “Databases” menu then “Instances” submenu.

Here we can see a list of available SQL Server instances we can connect our project to.

Click on the “Associate Instance” button and a popup will be displayed.

Here we can select our development instance from the drop down and click on the “Submit” button.

The list of database instances associated to the project will be updated and now we are ready to create or attach a database.

Step 2: Create a database

Now we can navigate to “Database” submenu where we can see the list of databases related to this project.

Click on the “Create” button and a popup will be displayed.

Select an "Instance" and type the database name in the "Object Name".

In this case we are going to name it "HelloWorldDB".

We can add a comment to understand why we created this database in the future.

Let's type "DB created for training purposes".

And then click on the "Submit" button.

The list of databases will be updated indicating our DB was created successfully.

Step 3: Create a Stored Procedure

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 TABLEPROCEDURE HWD_Roles (
Name  nvarchar(50spHelloWorld_GetRoles
AS
DECLARE @Result TABLE(Id INT, RoleName NVARCHAR(128), RoleRoleDescription  nvarcharNVARCHAR(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.

...

Execution Type

...

Description

...

Pre

...

Allows to execute the dataset while the page is loaded which is helpful to get the information available from the very beginning.

...

Post

...

Allows to bring the Information requested in the moment that we trigger any action and depending the size of the response, it could take some time after the information is loaded on the screen

...

On-demand

...

Runs every time there is a post back. Post-backs are interactions with the backend / web app. In general, we recommend avoiding controls that are loaded on demand so that screen reloads are not generated all the time.

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.

...

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

Step 4: Update Wayfast DB Model

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.

Step 5: Create a new Dataset

Go to “Project Pages” at the top menu and create a new page.

We are going to name it “HR_Roles_Screen”.

...

Click on the “Submit” button and access the page.

Expand the “Dataset” toggle button and click on the “New Dataset” button.

...

In the “New Dataset” popup let’s name it “HrRolesDataset“.

Select “Pre” option In “Execution Type” field. We are going to explain what this field is used for at the end of this lesson.

...

Go to “Data Binding” tab and click on the magnifying glass icon at the right side of the “Stored procedure” field. A list of available stored procedures will be displayed.

...

Select our stored procedure and click on the “Submit” button.

Now our dataset will be displayed in the list.

...

Now that we have with our data source let’s see how display it in the UI.

Step 6: Connect a Dataset with a Grid

For displaying the list of roles we are going to use the grid control.

On the control’s section choose “Grid_Column” in the control type dropdown and click the “New” button.

In the popup provide an object name. This object name needs to match with the field name you want from the stored procedure we want to show.

...

In the “Databinding” tab select our “HrRolesDataset” in the Dataset field:

...

In the “Layout” tab select a placeholder. In this case we are going to use “B1”.

...

Now let’s click on the “Submit” button.

Repeat this process for the rest of the columns in the stored procedure. This is how it looks like after we create the three columns:

...

Now let’s hit the “Preview” button to see it working.

...

As you can see Wayfast generates a full grid control based on the columns we provided. This control includes a bunch of cool features like paging, export to excel and search capabilities among others.

...

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

...

Execution Types

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 these options in detail:

Recap

In this lesson we created a database, a stored procedure, a page and a dataset to connect the page with the stored procedure. Then we created added a grid to display the values from the stored procedure.

We also reviewed the main settings that control Dataset’s behavior. In the subsequent lessons we will be expanding on different ways to interact with databases.