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.
...
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.
...
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.
...
The list of databases will be updated indicating our DB was created successfully.
Step 3: Create database object and update Wayfast DB Model
Let’s connect to the project’s database using SQL Server Management Studio.
...
Once the popup closes all new database objects are available in wayfast.
Step
...
4: How to create a new Dataset? (IN REVIEW)
Go to “Project Pages” at the top menu and create a new page.
...
Select “Pre” option In “Execution Type” field. We are going to explain what this field is used for shortlyat 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.
...
Now our dataset will be displayed in the list.
...
Now that we have can retrieve data from our DB we need to show with our data source let’s see how display 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, For displaying the list of roles 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
| Info |
|---|
You can find more details regarding the properties on “Grid Column” control lesson. |
...
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
| Info |
|---|
If page has multiple grid columns to reassign, we can use “Massive Mod” button which is a feature to easily make the bulk change. |
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” featurethe 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.
| Info |
|---|
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 this 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. |
On-demand | Runs upon 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 only once.
Recap
...
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.