In many cases when we working with systems that aim to perform operations on a database, there is a need to include a section within these systems to visualize the corresponding data. Within web pages, tables are typically used to represent information, search sections to filter information, and more.
In this article we will learn how to design in a simple way, a web page to visualize certain data hosted in tables in a database relates through ASP.NET Core and DotVVM. In previous articles, we were able to learn in general how to use predefined DotVVM controls for data visualization as a report. Here are some of those articles:
Also, when you manage forms to manage information, you need to create tables before you can view the processed data. Here are some previous articles in case you want to learn how to design HTML forms with DotVVM:
This time we will learn the basics of visualizing certain data and establishing some search criteria with C- and HTML, through DotVVM's premium controls on ASP.NET Core, referred to as DotVVM Business Pack.
Note: The project source code that we will discuss in this article can be found in this GitHub repository: Reports in DotVVM with Business Pack controls.
Model, View, ViewModel Pattern
DotVVM is based on the Design Pattern Model, View, ViewModel over .NET for communication between HTML (web pages) and C- (source code). The purpose of these parts are as follows:
- Model. — is responsible for all application data and related business logic.
- The view. — Representations for the end-user of the application model. The view is responsible for displaying the data to the user and allowing manipulation of application data.
- Model-View or View-Model. — one or more per view; the model-view is responsible for implementing view behavior to respond to user actions and for easily exposing model data.
How do We access DotVVM Business Pack?
Business Pack is a private NuGet, in which we can make use of the competent premiums already established by DotVVM for the construction of web applications in the business field.
For the installation of the DotVVM Business Pack version, it is necessary to configure a few minutes to be able to use these functionalities. It all comes down to the following:
- Install the DotVVM extension for Visual Studio 2019.
- Purchase Business Pack (trial version exists) at the following address: DotVVM Business Pack.
- Sign in to the DotVVM extension for Visual Studio 2019. To do this, we can go to the Visual Studio options menu in the path: Extensions -> DotVVM -> About. And ready, that'll be it.
Report with ASP.NET Core and DotVVM
To exemplify the use of some DotVVM Business Pack controls for reporting, we have a small application like this:
Taking into account the MVVM – Model, View, ViewModel pattern, we will analyze in general each of these parts for this project, which aims to display the data of particular users.
Model
Application and related business logic data is handled in this section. In this sense, we will see how the data and the corresponding services are handled.
The database consists of two tables: Person and PersonType.
The SQL statements for creating these tables, their attributes, and inserting some records are as follows:
CREATE SCHEMA IF NOT EXISTS `dbperson` DEFAULT CHARACTER SET utf8;
USE `dbperson` ;
CREATE TABLE IF NOT EXISTS `dbperson`.`PersonType` (
`Id` INT NOT NULL,
`Name` VARCHAR(45) NOT NULL,
`Description` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Id`))
;
CREATE TABLE IF NOT EXISTS `dbperson`.`Person` (
`Id` INT NOT NULL AUTO_INCREMENT,
`FirstName` VARCHAR(45) NOT NULL,
`LastName` VARCHAR(45) NOT NULL,
`IdPersonType` INT NOT NULL,
PRIMARY KEY (`Id`),
FOREIGN KEY (`IdPersonType`) REFERENCES `dbperson`.`PersonType` (`Id`))
;
INSERT INTO `persontype` (`Id`, `Name`, `Description`) VALUES ('1', 'Type A', '');
INSERT INTO `persontype` (`Id`, `Name`, `Description`) VALUES ('2', 'Type B', '');
INSERT INTO `person` (`Id`, `FirstName`, `LastName`, `IdPersonType`) VALUES ('1', 'Sergey', 'Brin', '1');
INSERT INTO `person` (`Id`, `FirstName`, `LastName`, `IdPersonType`) VALUES ('2', 'Larry', 'Page', '1');
INSERT INTO `person` (`Id`, `FirstName`, `LastName`, `IdPersonType`) VALUES ('3', 'Tim', 'Barners', '2');
INSERT INTO `person` (`Id`, `FirstName`, `LastName`, `IdPersonType`) VALUES ('4', 'Linus', 'Torvalds', '1');
INSERT INTO `person` (`Id`, `FirstName`, `LastName`, `IdPersonType`) VALUES ('5', 'Larry', 'Ellison', '1');
INSERT INTO `person` (`Id`, `FirstName`, `LastName`, `IdPersonType`) VALUES ('6', 'Steve', 'Ballmer', '2');
INSERT INTO `person` (`Id`, `FirstName`, `LastName`, `IdPersonType`) VALUES ('7', 'Steve', 'Jobs', '2');
INSERT INTO `person` (`Id`, `FirstName`, `LastName`, `IdPersonType`) VALUES ('8', 'Marc', 'Benioff', '1');
INSERT INTO `person` (`Id`, `FirstName`, `LastName`, `IdPersonType`) VALUES ('9', 'Ray', 'Ozzie', '2');
INSERT INTO `person` (`Id`, `FirstName`, `LastName`, `IdPersonType`) VALUES ('10', 'Nicholas', 'Negroponte', '2');
INSERT INTO `person` (`Id`, `FirstName`, `LastName`, `IdPersonType`) VALUES ('11', 'Diane', 'Green', '1');
INSERT INTO `person` (`Id`, `FirstName`, `LastName`, `IdPersonType`) VALUES ('12', 'Sam', 'Palmisano', '1');
INSERT INTO `person` (`Id`, `FirstName`, `LastName`, `IdPersonType`) VALUES ('13', 'Blake', 'Ross', '2');
INSERT INTO `person` (`Id`, `FirstName`, `LastName`, `IdPersonType`) VALUES ('14', 'Ralph', 'Szygenda', '2');
INSERT INTO `person` (`Id`, `FirstName`, `LastName`, `IdPersonType`) VALUES ('15', 'Rick', 'Dalzell', '2');
With the database set, the portion of the data access layer refers to the definition of the classes to work with the database features and the context to establish communication between ASP.NET Core and the database, which in this case MySQL is the one being used.
For this purpose, you need to install three NuGet packages:
Microsoft.EntityFrameworkCore.DesignMicrosoft.EntityFrameworkCore.ToolsMySql.Data.EntityFrameworkCore
If you are working with SQL Server, the NuGet package to install will be: Microsoft.EntityFrameworkCore.SQLServer.
You then need to use the package management console to scaffold from the database (automatically generate the context and feature classes) using the following command:
Scaffold-DbContext "server=servername;port=portnumber;user=username;password=pass;database=databasename" MySql.Data.EntityFrameworkCore -OutputDir Entities -f
With this first part, the connection to the database is listed. What follows is the definition of models with which the website will be worked. These models are:
public class PersonModel
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public int IdPersonType { get; set; }
public string NamePersonType { get; set; }
}
public class PersonTypeModel
{
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
}
For each of these models there is a service, which has the following operations:
GetAllPersonsAsync()GetPersonByIdAsync(int personId)GetPersonByIdAndTypeAsync(int personId, int personTypeId)GetAllPersonsByTypeAsync(int personTypeId)
PersonTypeService:
GetAllPersonTypesAsync()GetPersonTypeByIdAsync(int personTypeId)
In Visual Studio 2019 we'll have something like this:
ViewModel
public class DefaultViewModel : MasterPageViewModel
{
private readonly PersonService personService;
public BusinessPackDataSet<PersonModel> Persons { get; set; } = new BusinessPackDataSet<PersonModel>();
public List<int> PersonTypes { get; set; } = new List<int>();
public string IdSearch { get; set; }
public bool SearchByTextVisible { get; set; } = false;
public bool IsWindowDisplayed { get; set; }
public DefaultViewModel(PersonService personService)
{
this.personService = personService;
}
public async Task UpdatePersonList()
{
IdSearch = null;
if (PersonTypes.Count == 2)
{
Persons.Items = await personService.GetAllPersonsAsync();
SearchByTextVisible = true;
}
else if (PersonTypes.Count == 1)
{
int IdPersonType = PersonTypes.FirstOrDefault();
Persons.Items = await personService.GetAllPersonsByTypeAsync(IdPersonType);
SearchByTextVisible = true;
}
else
{
Persons.Items.Clear();
SearchByTextVisible = false;
}
}
public async Task SearchById()
{
if (PersonTypes.Count == 2)
{
if (!string.IsNullOrEmpty(IdSearch))
{
List<PersonModel> list = new List<PersonModel>(); ;
list.Add(await personService.GetPersonByIdAsync(Int32.Parse(IdSearch)));
Persons.Items = list;
}
else
{
Persons.Items = await personService.GetAllPersonsAsync();
}
}
else if (PersonTypes.Count == 1)
{
if (!string.IsNullOrEmpty(IdSearch))
{
int IdPersonType = PersonTypes.FirstOrDefault();
List<PersonModel> list = new List<PersonModel>(); ;
list.Add(await personService.GetPersonByIdAndTypeAsync(Int32.Parse(IdSearch), IdPersonType));
Persons.Items = list;
}
else
{
int IdPersonType = PersonTypes.FirstOrDefault();
Persons.Items = await personService.GetAllPersonsByTypeAsync(IdPersonType);
}
}
}
}
View
<dot:Content ContentPlaceHolderID="MainContent">
<div class="content">
<img src="/Resources/Images/tree.svg" class="content__background-image content__background-image--left" />
<a href="https://www.dotvvm.com/" target="_blank">
<img src="/Resources/Images/text.svg" class="content__image" />
</a>
<h1>PERSON REPORT FORM</h1>
<img src="~/icon.png" width="15%" height="15%" />
<div class="content__text">
<bp:Window IsDisplayed="{value: IsWindowDisplayed}"
HeaderText="Reporting form"
CloseOnEscape="false"
CloseOnOutsideClick="false">
<p>
<h1>Person report</h1>
</p>
<p>
<h4>Search by type:</h4>
<p />
<bp:CheckBox CheckedItems="{value: PersonTypes}"
Changed="{command: UpdatePersonList()}"
CheckedValue="{value: 1}" Text="Type A" />
<br />
<bp:CheckBox CheckedItems="{value: PersonTypes}"
Changed="{command: UpdatePersonList()}"
CheckedValue="{value: 2}" Text="Type B" />
</p>
<p>
<h4>Search by text:</h4>
<p />
ID Number:
<bp:TextBox Text="{value: IdSearch}" Type="Number" class="page-input" Visible="{value: SearchByTextVisible}" />
<bp:Button Text="Search" Click="{command: SearchById()}" class="page-button" Visible="{value: SearchByTextVisible}" />
<p />
<h4>Report:</h4>
<bp:GridView DataSource="{value: Persons}" class="page-grid">
<Columns>
<bp:GridViewTextColumn Value="{value: Id}" HeaderText="Id" />
<bp:GridViewTextColumn Value="{value: FirstName}" HeaderText="Firstname" />
<bp:GridViewTextColumn Value="{value: LastName}" HeaderText="LastName" />
<bp:GridViewTextColumn Value="{value: NamePersonType}" HeaderText="Type" />
</Columns>
<EmptyDataTemplate>
There are no search results.
</EmptyDataTemplate>
</bp:GridView>
</bp:Window>
<bp:Button Text="LOAD REPORT"
Click="{command: IsWindowDisplayed = true}" />
</div>
<img src="/Resources/Images/tree.svg" class="content__background-image content__background-image content__background-image--right" />
</div>
</dot:Content>
Application Analysis
In the following GIF, we can see in a general way the interaction with this small web page.
The first element we will analyze is the Window component, which represents a modal dialog box, as in HTML. This control allows us to customize directly from its attributes as the window will be displayed. If we were working with DotVVM's base controls, to achieve this functionality we would have to make use of some Javascript functionalities directly to set the window. In this example, the window title can be assigned. You can also customize certain properties, such as not allowing the window to close by pressing the Escape key or clicking outside the window box.
In this example, the Boolean attribute IsWindowDisplayed, according to its value true or false, will allow whether or not to display the settings window.
<bp:Window IsDisplayed="{value: IsWindowDisplayed}"
HeaderText="Reporting form"
CloseOnEscape="false"
CloseOnOutsideClick="false">
Here is the definition of the IsWindowDisplayed attribute for the window display:
public bool IsWindowDisplayed { get; set; }
To display the window, a button is used. This button is also another of the Business Pack components. The premium version allows you to make certain customizations in terms of its styles, for example, enable/disable the button, assign an icon, among other functionalities: Button/2.0.
The result is as follows:
Within this window, the most important item corresponds to the table that allows users registered in the database to be listed. For this purpose, we use the GridView element, another DotVVM control that allows us to create tables to represent specific data. As the main part, the control allows us to indicate the data source through the DataSource property, in this case, the data source is defined as follows in the ViewModel:
public GridViewDataSet<PersonModel> Persons { get; set; } = new GridViewDataSet<PersonModel>();
The GridViewTextColumn tag is used for a column definition. In this case, we can find the Id, FirstName, LastName and Type columns. These names come from the data type of the data source, in this case, from the PersonModel model.
<bp:GridView DataSource="{value: Persons}" class="page-grid">
<Columns>
<bp:GridViewTextColumn Value="{value: Id}" HeaderText="Id" />
<bp:GridViewTextColumn Value="{value: FirstName}" HeaderText="Firstname" />
<bp:GridViewTextColumn Value="{value: LastName}" HeaderText="LastName" />
<bp:GridViewTextColumn Value="{value: NamePersonType}" HeaderText="Type" />
</Columns>
<EmptyDataTemplate>
There are no search results.
</EmptyDataTemplate>
</bp:GridView>
One of the sub-tags in GridView is EmptyDataTemplate, which is also included in DotVVM base controls. This tag allows you to display some HTML content in case the list of items is empty. In the end, with GridView we will visualize something like this:
Learn more about the GridView component here: GridView/2.0.
All right, with this table, we have the most important part of the report. From this, we can add additional components to set search criteria for example, and update this table based on the search.
The first case is using the premium version of the DotVVM CheckBox control. As in HTML or any other design environment, the CheckBox has the role of a checkbox for selecting items in an option set. For this example, the goal is to have two checkboxes, which correspond to the types of people. Depending on the selection, either type A, type B, or both, the table of records will be updated in accordance with this decision.
In the view part, we find the CheckedItems property that stores the value of the items that are selected. We also find the Changed property, which allows you to specify the method that will perform the actions at the time this element is activated or disabled.
<bp:CheckBox CheckedItems="{value: PersonTypes}"
Changed="{command: UpdatePersonList()}"
CheckedValue="{value: 1}" Text="Type A" />
<bp:CheckBox CheckedItems="{value: PersonTypes}"
Changed="{command: UpdatePersonList()}"
CheckedValue="{value: 2}" Text="Type B" />
In the method of updating the records in the table, if we select one of the two types, then we will query the database according to the defined service: PersonService, to get the list of people according to the selected id. With this list retrieved, we will update the database by re-setting the items in the GridView data source.
Persons.Items.Clear();
As we saw in the GIF, the result of using the CheckBox control is as follows:
Learn more about the CheckBox component here: CheckBox/2.0.
Another of the controls that allow us to continue adding functionality to the GridView to set the search criteria for this report is the TextBox and Button elements. In this case, these components can be used to search for something specific to the report through a text entry. To exemplify the use of these elements in your app, the controls are used to find a specific person based on their ID.
ID Number:
<bp:TextBox Text="{value: IdSearch}" Type="Number" class="page-input" Visible="{value: SearchByTextVisible}" />
<bp:Button Text="Search" Click="{command: SearchById()}" class="page-button" Visible="{value: SearchByTextVisible}" />
Updating the elements of the GridView is similar to the CheckBox case. The result is as follows:
All the information about the TextBox control can be found in: TextBox/2.0. Y la del control Button en: Button/2.0.
What's next?
In this article, we learned certain features of the Windows, GridView, CheckBox, TextBox and Button components of DotVVM's Business Pack control set to display a list of data and set search criteria through the Model, View, ViewModel pattern in ASP.NET Core and DotVVM.
The source code for this implementation is available in this repository: Reports in DotVVM with Business Pack controls.
Here are some additional resources that might be of interest to you to continue to gain new knowledge in this area:
- Free and open course on YouTube (Spanish): ASP.NET Core and DotVVM Fundamentals.
- DotVVM and ASP.NET Core: Implementing CRUD Operations.
- Working with web services through ASP.NET Core and DotVVM.
Thank you:
If you have any concerns or need help in something particular, it will be a pleasure to be able to collaborate.
See you on Twitter!! :)
Top comments (0)