Building a RESTful Service Using ASP.NET Core and dotConnect for PostgreSQL

The term REST is an abbreviation for Representational State Transfer. It is a software architectural style created to assist the design and development of the World Wide Web architecture. REST defines a set of constraints that define how a distributed hypermedia system, such as the Web, should be architected. Restful Web Services are HTTP-based, simple, lightweight, fast, scalable, and maintainable services that adhere to the REST architectural style.

The REST architectural style views data and functionality as resources accessed via Uniform Resource Identifiers (URIs). Restful architecture is a client-server paradigm that utilizes a stateless communication protocol, often HTTP, for data exchange between server and client. In REST, the clients and servers interact through a defined and standardized interface.

This article looks at RESTful architecture and how we can implement a RESTful service using ASP.NET Core and dotConnect for PostgreSQL. In this article, we’ll connect to PostgreSQL using dotConnect for PostgreSQL which is high performance and enhanced data provider for PostgreSQL that is built on top of ADO.NET and can work on both connected and disconnected modes.

Prerequisites

To be able to work with the code examples demonstrated in this article, you should have the following installed in your system:

• Visual Studio 2019 Community Edition
• PostgreSQL
• dotConnect for PostgreSQL

You can download .NET Core from here: 

• https://dotnet.microsoft.com/download/archives

You can download Visual Studio 2019 from here:

•  https://visualstudio.microsoft.com/downloads/

You can download PostgreSQL from here:

• https://www.postgresql.org/download/

You can download a trial version of dotConnect for PostgreSQL from here: 

• https://www.devart.com/dotconnect/postgresql/download.html

Create the Database

You can create a database using the pgadmin tool. To create a database using this Launch this tool, follow the steps given below:

1. Launch the pgadmin tool
2. Expand the Servers section
3. Select Databases
4. Right-click and click Create -> Database...
5. Specify the name of the database and leave the other options to their default values
6. Click Save to complete the process

Create a Database Table

Select and expand the database you just created

Select Schemas -> Tables

Right-click on Tables and select Create -> Table...

Specify the columns of the table as shown in Figure 2 below:

The table script is given below for your reference:

CREATE TABLE public."Product"

(

    "Id" bigint NOT NULL,

    code character(5) COLLATE pg_catalog."default" NOT NULL,

    name character varying(100) COLLATE pg_catalog."default" NOT NULL,

    "        quantity" bigint NOT NULL,

    CONSTRAINT "Product_pkey" PRIMARY KEY ("Id")

)

We’ll use this database in the subsequent sections of this article to demonstrate how we can work with Postgresql and dotConnect in ASP.NET Core.

Features and Benefits of dotConnect for PostgreSQL

Some of the key features of dotConnect for PostgreSQL include the following:

• High performance
• Fully-managed code
• Seamless deployment
• Support for the latest version of PostgreSQL
• Support for .NET Framework, .NET Core and also .NET Compact Framework
• Support for both connected and disconnected modes
• Support for all data types of PostgreSQL
• Improved data binding capabilities
• Support for monitoring query execution

You can know more on the features of dotConnect for PostgreSQL here. The following are some of the advantages of dotConnect for PostgreSQL:

• Enables writing efficient and optimized code
• Comprehensive support for ADO.NET
• Support for Entity Framework
• Support for LinqConnect
• Support for both connected and disconnected modes

Introducing dotConnect for PostgreSQL

dotConnect for PostgreSQL is a high-performance data provider for PostgreSQL built on ADO.NET technology. You can take advantage of the new approaches to building application architecture, boosting productivity and making it easier to create database applications. Formerly known as PostgreSQLDirect.NET, it is an improved data provider for PostgreSQL that provides a comprehensive solution for building PostgreSQL-based database applications.

A scalable data access solution for PostgreSQL, dotConnect for PostgreSQL was designed with a high degree of flexibility in mind. You can use it effectively in WinForms, ASP.NET, ASP.NET Core, two-tier, three-tier, and multi-tier applications. The dotConnect for PostgreSQL data provider may be used as a robust ADO.NET data source or an effective application development framework, depending on the edition you select.

Create a New ASP.NET Core Web API Project in Visual Studio 2019

Once you’ve installed the necessary software and/or tools needed to work with dotConnect for PostgreSQL, follow the steps mentioned in an earlier article “Working with Queries Using Entity Framework Core and Entity Developer” to create a new ASP.NET Core 5.0 project in Visual Studio 2019.

Install NuGet Package(s)

To work with dotConnect for PostgreSQL in ASP.NET Core 5, you should install the following package into your project:

Devart.Data.PostgreSql

You have two options for installing this package: either via the NuGet Package Manager or through the Package Manager Console Window by running the following command.

PM> Install-Package Devart.Data.PostgreSql

Programming dotConnect for PostgreSQL

This section talks about how you can work with dotConnect for PostgreSQL.

Create the Model

Create a class named Product with the following code in there:

public class Product

    {

        public int Id { get; set; }

        public string Code { get; set; }

        public string Name { get; set; }

        public int Quantity { get; set; }

    }

This is our model class which we'll use for storing and retrieving data.

Create the RESTful Endpoints

Create a new controller class in this project and name it as ProductController. Now replace the generated code with the following code in there:

   [Route("api/[controller]")]

   [ApiController]

    public class ProductController : ControllerBase
    {

        [HttpGet]
        public List<Product> Get()
        {
            throw new NotImplementedException();
        }

        [HttpPost]
        public void Post([FromBody] Product product)
        {
            throw new NotImplementedException();
        }

        [HttpPut]
        public void Put([FromBody] Product product)
        {
            throw new NotImplementedException();
        }
    }

As you can see, there are three RESTful endpoints in the ProductController class. Note the usage of the HTTP verbs in the controller methods. We’ll implement each of the controller methods shortly.

Insert Data Using dotConnect for PostgreSQL

The following code snippet can be used to insert data into the product table of the PostgreSQL database we created earlier:

[HttpPost]

public int Post([FromBody] Product product) {

  try {

    using(PgSqlConnection pgSqlConnection = 

    new PgSqlConnection("User 

      Id=postgres;Password=sa123#;host=localhost;database=postgres;")) {

      using(PgSqlCommand cmd = new PgSqlCommand()) {

        cmd.CommandText = "INSERT INTO public.product 
        (id, code, name, quantity) 
         VALUES (@id, @code,@name, @quantity)"; 

        cmd.Connection = pgSqlConnection;
        cmd.Parameters.AddWithValue("id", product.Id);
        cmd.Parameters.AddWithValue("code", product.Code);
        cmd.Parameters.AddWithValue("name", product.Name);
        cmd.Parameters.AddWithValue("quantity", product.Quantity); 

        if (pgSqlConnection.State != System.Data.ConnectionState.Open) 
            pgSqlConnection.Open();

        return cmd.ExecuteNonQuery();
      }
    }
  }

  catch 
  {
    throw;
  }
}

Read Data Using dotConnect for PostgreSQL

Reading data using dotConnect is fairly straight forward. The following code snippet illustrates how you can read data from the product database table using dotConnect for PostgreSQL.

[HttpGet]

public List <Product> Get() 
{

  try {

    List <Product> products = new List < Product > ();
    using(PgSqlConnection pgSqlConnection = 
     new PgSqlConnection("User 
     Id=postgres;Password=sa123#;host=localhost;database=postgres;")) 
     {

      using(PgSqlCommand pgSqlCommand = new PgSqlCommand()) {
        
        pgSqlCommand.CommandText = "Select * From public.Product";
        pgSqlCommand.Connection = pgSqlConnection; 
        if (pgSqlConnection.State != System.Data.ConnectionState.Open) 
        pgSqlConnection.Open();

        using(PgSqlDataReader pgSqlReader = pgSqlCommand.ExecuteReader()) {
          while (pgSqlReader.Read()) {
            Product product = new Product();
            product.Id = int.Parse(pgSqlReader.GetValue(0).ToString());
            product.Code = pgSqlReader.GetValue(1).ToString();
            product.Name = pgSqlReader.GetValue(2).ToString();
            product.Quantity = 
            int.Parse(pgSqlReader.GetValue(3).ToString()); 
            products.Add(product);
          }
        }
      }
    }
                         
    return products;
  }

  catch 
  {
    throw;
  }
} 

Modify Data Using dotConnect for PostgreSQL

The following code listing illustrates how you can take advantage of dotConnect for PostgreSQL to modify an existing record:

[HttpPut("{id}")]

public void Put([FromBody] Product product) {

  try {

    using(PgSqlConnection pgSqlConnection = 
     new PgSqlConnection("User 
     Id=postgres;Password=sa123#;host=localhost;database=postgres;")) {
     
      using(PgSqlCommand cmd = new PgSqlCommand()) {
        cmd.CommandText = "UPDATE Product SET Name = @name WHERE Id = @id";

        cmd.Parameters.AddWithValue("id", product.Id);
        cmd.Parameters.AddWithValue("name", product.Name);
        cmd.Connection = pgSqlConnection;
        
        if (pgSqlConnection.State != System.Data.ConnectionState.Open) 
           pgSqlConnection.Open();
        cmd.ExecuteNonQuery();
      }
    }
  }

  catch 
  {
    throw;
  }
}

Summary

dotConnect for PostgreSQL is a high-performance object-relational mapper (ORM) for PostgreSQL built on top of the ADO.NET framework. It provides high-performance native connections to the PostgreSQL database. It offers new methods to building application architecture and increases developer productivity.