Developing Web Applications With ASP.NET Core, DotVVM, And PostgreSQL

DotVVM is an ASP.NET framework that allows us to create web applications using the MVVM (View-Model Model) design pattern using C# and HTML. In this tutorial, we will learn how to create CRUD (Create, Read, Update, and Delete) operations using a relational database with PostgreSQL, from ASP.NET Core.

Want to know what the steps are to create a DotVVM app? To do this, you can review this article - Steps to Create an MVVM Application (Model-View-ViewModel) with DotVVM and ASP.NET Core.

Introduction to ADO.NET Entity Framework

ADO.NET Entity Framework is an Object Relational Mapping (ORM) framework. It is designed to allow us to create data access applications by programming in a conceptual application model instead of programming directly into a relational storage scheme. The goal is to reduce the amount of code and maintenance required for data-oriented applications.

Resources required

To work with PostgreSQL ASP.NET Core with DotVVM, the recommended tools for setting up our work environment are as follows,

Visual Studio 2019
The workload in Visual Studio 2019: Developing ASP.NET and Web.
DotVVM extension for Visual Studio 2019
PostgreSQL

The database in PostgreSQL

For this tutorial article, let's create a sample database in PostgreSQL, which allows us to work with user data and then implement CRUD operations on ASP.NET Core.

To establish our database we can use the pgAdmin4 tool, which is usually included when installing PostgreSQL. When you create the schema, the SQL code for creating our user table is as follows,

CREATE SEQUENCE id_seq
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 9223372036854775807
CACHE 1;
CREATE TABLE person
(
id_ integer NOT NULL DEFAULT nextval('id_seq'::regclass),
firstname character varying(45) COLLATE pg_catalog."default" NOT NULL,
lastname character varying(45) COLLATE pg_catalog."default" NOT NULL,
username character varying(45) COLLATE pg_catalog."default" NOT NULL,
city character varying(45) COLLATE pg_catalog."default" NOT NULL,
country character varying(45) COLLATE pg_catalog."default" NOT NULL,
postalcode integer NOT NULL,
about character varying(45) COLLATE pg_catalog."default" NOT NULL,
enrollmentdate timestamp without time zone NOT NULL,
CONSTRAINT person_pkey PRIMARY KEY (id_)
)

Here the primary key id_ is in a sequence so that the value of this key is self-incremental.

Developing Web Applications With ASP.NET Core, DotVVM, And PostgreSQL

Project in Visual Studio 2019

Now that the database is set, then we can start developing our solution in Visual Studio. In this new project we will have three parts,

Data Access Layer (DAL): To handle the connection to PostgreSQL and database access.
BL (Business Layer): for the management of the services and the logic of the application domain.
PL: to work with the presentation layer of the application, in this case, with DotVVM.

To get started, we'll create a new project in Visual Studio 2019 of the type DotVVM Web Application,

When creating the project, the DotVVM setup wizard allows you to select a number of options with settings, styles, and templates already preset,

In this case, we will leave everything like this and create the project. When you create a DAL and BL folder for the data access layer and business logic respectively, in the solution you'll have something like this,

Connection to PostgreSQL

All right, as a first point, we're going to relate our project to the database created in PostgreSQL. In the Entity Framework, there are two approaches, the first Code-First, which allows us to generate the database through classes, and the second, Database-First, which allows us to generate feature classes from an existing database. As expected, in this case, we will use the Database-First approach. To meet this goal, we will need to install three Nuget packages,

Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.Tools
Npgsql.EntityFrameworkCore.PostgreSQL

Then we will need to insert command from the package manager console. This console can be activated from the Options Menu -> View -> Other Windows -> the Package Management Console.

In this console we will insert the following command,

Scaffold-DbContext "Host=hostname;port=portnumber;Username=username;Password=pass;Database=databasename" Npgsql.EntityFrameworkCore.PostgreSQL -OutputDir DAL/Entities

Where,

Hostname, is the name of the server from which the database is located. Example: localhost.
Portnumber, host port. PostgreSQL is usually located on port 5432.
Username, database username.
Password, password of the user who will access the database.
Database, database name.

Also,

-OutputDir, with this keyword we can indicate where the files will be generated.
-Table, additional abbreviation in case you want to indicate the specific tables to generate in our data access layer.

When you enter the command correctly, you'll have something like this,

Where Person is the class that is associated with the entity with the same name in the database and the DBContext is where the corresponding configurations are located.

The Person class is defined as follows,

public partial class Person
{
public int Id { get; set; }
public string Firstname { get; set; }
public string Lastname { get; set; }
public string Username { get; set; }
public string City { get; set; }
public string Country { get; set; }
public int Postalcode { get; set; }
public string About { get; set; }
public DateTime Enrollmentdate { get; set; }
}

And the DBContext, which has the configuration with the database, whose main method OnConfiguring will look something like this,

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{ optionsBuilder.UseMySQL("host=localhost;port=5432;username=<Username>;password=;database=<Database_name>");
}
}

Now, it is not the most appropriate for the database connection string to be specified in this OnConfiguring method, for this, we will specify the connection string in the appsettings.json file as follows,

"AllowedHosts": "*",
"ConnectionStrings": {
"DefaultConnection": "host=localhost;port=5432;username=<Username>;password=;database=<Database_name>");
}

Then, in the Startup class in ConfigureServices method we add as a service to the DBContext and refer to the DefaultConnection property specified in the appsettings.json file:

public void ConfigureServices(IServiceCollection services)
{
services.AddEntityFrameworkNpgsql ()
.AddDbContext<DBContext>(options =>
{
options. UseNpgsql(Configuration.GetConnectionString("DefaultConnection"));
});
}

In this case, returning to the DBContext class, we clear the connection string specified in the OnConfiguring method. In the end, we would have the empty method,

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{}

And the constructor of the DBContext would be defined as follows,

public DBContext(DbContextOptions<DBContext> options)
: base(options) {}

With these steps, we already have ready the connection and configurations needed to work with the PostgreSQL database in ASP.NET Core with the help of Entity Framework.

Services for working on data

Now it's up to us to define the models and create the services to handle the logic of our application. In this case, what you are looking for is to have a general list of users and the specific information of each of them.

Models

The models will allow us to represent what data we will want to work with when designing our website. In this case, the models will be as follows.

A. UserListModel

public class UserListModel
{
public int Id { get; set; }
public string Name { get; set; }
public string City { get; set; }
public string Country { get; set; }
public DateTime Enrollmentdate { get; set; }
}

B. UserDetailModel

public class UserDetailModel
{
public int Id { get; set; }
public string Firstname { get; set; }
public string Lastname { get; set; }
public string Username { get; set; }
public string City { get; set; }
public string Country { get; set; }
public int Postalcode { get; set; }
public string About { get; set; }
public DateTime Enrollmentdate { get; set; }
}

Services

Secondly, it is necessary to define the services of our application, which will have the necessary methods to access and save data about users in the database. In this case, we have a user service that will allow us to perform CRUD operations.

To perform operations on the data we will use LINQ - Language Integrated Query, a component of the Microsoft .NET platform that natively adds query capabilities to data to .NET languages. In other words, LINQ allows us to query collections of objects (the entities defined in the DAL) to handle information and perform operations on the database.

Taking these considerations into account, the initial adjustments and methods for processing the data are as follows,

A. Initial Settings

private readonly DBContext DbContext;
public UserService(DBContext DbContext)
{
this.DbContext = DbContext;
}

B. Get all registered users

public async Task<List<UserListModel>> GetAllUsersAsync()
{
return await DbContext.Person.Select(
s => new UserListModel
{
Id = s.Id,
Name = s.Firstname + " " + s.Lastname,
City = s.City,
Country = s.Country,
Enrollmentdate = s.Enrollmentdate
}
).ToListAsync();
}

C. Get a specific user by their ID

public async Task<UserDetailModel> GetUserByIdAsync(int UserId)
{
return await DbContext.Person.Select(
s => new UserDetailModel
{
Id = s.Id,
Firstname = s.Firstname,
Lastname = s.Lastname,
Username = s.Username,
City = s.City,
Country = s.Country,
Postalcode = s.Postalcode,
Enrollmentdate = s.Enrollmentdate,
About = s.About
})
.FirstOrDefaultAsync(s => s.Id == UserId);
}

D. Insert a new user

public async Task InsertUserAsync(UserDetailModel User)
{
var entity = new Person()
{
Firstname = User.Firstname,
Lastname = User.Lastname,
Username = User.Username,
City = User.City,
Country = User.Country,
Postalcode = User.Postalcode,
Enrollmentdate = User.Enrollmentdate,
About = User.About
};
DbContext.Person.Add(entity);
await DbContext.SaveChangesAsync();
}

E. Update a user's data

public async Task UpdateUserAsync(UserDetailModel User)
{
var entity = await DbContext.Person.FirstOrDefaultAsync(s => s.Id == User.Id);
entity.Firstname = User.Firstname;
entity.Lastname = User.Lastname;
entity.Username = User.Username;
entity.City = User.City;
entity.Country = User.Country;
entity.Postalcode = User.Postalcode;
entity.Enrollmentdate = User.Enrollmentdate;
entity.About = User.About;
await DbContext.SaveChangesAsync();
}

F. Delete a user

public async Task DeleteUserAsync(int UserId)
{
var entity = new Person()
{
Id = UserId
};
DbContext.Person.Attach(entity);
DbContext.Person.Remove(entity);
await DbContext.SaveChangesAsync();
}

For more details on how LINQ works, see the Microsoft documentation at here.

To finish with this section, this service must be referenced in the Startup class, in the ConfigureServices method (the same one where the adjustment was made with EntityFramework). Here we need to add the following statement,

services.AddTransient(typeof(UserService));

Note

if you generate more services, all of them must be specified in this method of the Startup class.

In the end, the solution in Visual Studio about the Business Logic Layer section will look like this:

Representation of data in a web application

Now that we have defined data access layers and business logic, we can now design the web page so that people can interact with it and in this case, use the CRUD operations implemented for user management.

In this section, we'll look at how to design a dashboard to enter data through forms and list them in tables. This process is described in the following article: Building a Dashboard with ASP.NET Core and DotVVM.

The end result of the design using DotVVM and the previously implemented services that access PostgreSQL is as follows,

Plus: Hosting the PostgreSQL database in Azure

Today the trend is to publish web pages in the cloud, for this, there are various services that allow us to meet these objectives, whatever database manager is being used, the database must also be in the cloud for the web page to work. For PostgreSQL, in Azure, we can find the resource: Azure Database for PostgreSQL servers.

To create the resource in Azure we'll need the following,

An Azure subscription, specify server details (name, source, location, version, and compute and storage features), and credentials to access this database as an administrator.

Once it has been created, we can go to the resource in the Connection Strings section and find the connection string that we will have to change in our project, in Azure we can copy the string that is in section ADO.NET and adapt it to the string that we already have set in the file appsettings.json.

What's next?

Below, as shown above, you can continue with the second part of this article where you can see step by step how to generate a dashboard with DotVVM: Building a Dashboard with ASP.NET Core and DotVVM.

The project-entire source code for building the dashboard with ASP.NET Core and DotVVM with PostgreSQL is available in the following repository: User Dashboard.

As an additional resource, in this article, you can see step by step how to deploy a web app to Azure: Deploy DotVVM and .NET Core web applications to Azure (Everything you need to know).

Thank you so much for reading.

If you have any questions or ideas that you need to discuss, it will be nice to be able to collaborate and together exchange knowledge with each other.

See you on Twitter! :)