...
...
...
...
...
...
...
...
...
...
...
...
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.
...
Databases are one of the Wayfast Pillars. Wayfast Application Project’s are mostly used to interact with databases and Datasets are the mean to achieve that goal. As a general rule we suggest using Stored Procedures to interact with databases bug 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 connected with a database. You can find more details about in another lessons where we attached or created here: Attach or create databases.
We need to create a table or identify the table where we are going to get the data to display using the Dataset
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.
...
an active requirement so we can create objects in our database.
You already know what a stored procedure is. If you don’t you can read about them here.
Step 1: Create database object for this example.
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 table and a stored procedure:
| Code Block | ||
|---|---|---|
| ||
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.
...
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 is displayed, just click on the “Synchronize” button. Now the stored procedure created in database will be available to connect with dataset
...
Once the popup closes all new database objects are available in wayfast.
How to create a new Dataset?
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.
...
Execution Type field is an important feature since this will define what type of performance has the dataset.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:
Execution Type | Description |
|---|---|
Pre | Allows to execute Loads the dataset while the first time the page is loaded which is helpful to get the information available from the very beginning. This option will increase the initial load but will prevent from calling loading this dataset many times which will improve page’s responsiveness. |
PostAllows 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 screenLoads the dataset on every postback. 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. |
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 timeupon another control’s action. For instance, you could use this option when you need to load the list of states based on the country selected on a dropdown. |
In this example, we are going to select “Pre” so we can have our data refreshed at the moment we enter to the pageonly once.
After this, focus on go to “Data Binding” tab. In this section, we can select the Stored Procedure previously created for the information available in DBwe just created.
...
If we click on The “Add relationship” button under “Dataset Refresh Binding” field, we can link the dataset with any of controls. We can find another example in field allows to trigger this Dataset load based on a control’s event. We will review this specific case in the “Actions” lesson.
After completing this details, Let’s click on “Accept” the “Submit” button to save the dataset . Now Stored Procedure can be quickly referenced and our Dataset will be available to be executed from other controls in this page.
...
Now that we have the information available from DB and connected to the Project, can retrieve data from our DB we need to make show it visible 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’s
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.
...
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.