Datasets
- Sergio Rodriguez
- Matias
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:
We already have a project. Let’s reuse our Hello World Project.
We need an active requirement so we can create objects in our database.
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 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
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.
You can find more details regarding the properties on “Grid Column” control lesson.
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:
Execution Type | Description |
|---|---|
Pre | Loads the dataset the first time the page is loaded. If there are control events associated to this dataset those events will trigger this dataset refresh too. This option will increase the initial load but will prevent from calling loading this dataset many times which will improve page’s responsiveness. |
Post | Runs upon another associated control’s events. For instance, you could use this option when you need to load the list of states based on the country selected on a dropdown. |
On-demand | Loads the dataset on every post back. This means every time the page interacts with the server. This is the most expensive options but may be required in some scenario were your data is prone to change frequently. |
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.