DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Migrate, Modernize and Build Java Web Apps on Azure: This live workshop will cover methods to enhance Java application development workflow.

Modern Digital Website Security: Prepare to face any form of malicious web activity and enable your sites to optimally serve your customers.

E-Commerce Development Essentials: Considering starting or working on an e-commerce business? Learn how to create a backend that scales.

Related

  • Step-by-Step ASP.NET Core RESTful Web Service Development
  • Spring Boot - How To Use Native SQL Queries | Restful Web Services
  • Building REST API Backend Easily With Ballerina Language
  • Composite Requests in Salesforce Are a Great Idea

Trending

  • Real-Time Remediation Solutions in Device Management Using Azure IoT Hub
  • From Sprout to Sequoia: Orchestrating the Symphony of IoT Growth and Cloud Scalability
  • Embeddings and Vector Databases: A Hands-On Guide!
  • 7 Technical Reasons Why Software Product Engineering Projects Fail
  1. DZone
  2. Data Engineering
  3. Databases
  4. Building a RESTful Service Using ASP.NET Core and dotConnect for PostgreSQL

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

This article looks at RESTful architecture and how we can implement a RESTful service using ASP.NET Core and dotConnect for PostgreSQL.

Joydip Kanjilal user avatar by
Joydip Kanjilal
DZone Core CORE ·
Jul. 29, 21 · Tutorial
Like (2)
Save
Tweet
Share
11.1K Views

Join the DZone community and get the full member experience.

Join For Free

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...


Figure 1


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

Figure 2


The table script is given below for your reference:

MS SQL
 
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:

C#
 
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:

C#
 
   [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:

C#
 
[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.

C#
 
[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:

C#
 
[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.

PostgreSQL Database ASP.NET ASP.NET Core REST Web Protocols

Opinions expressed by DZone contributors are their own.

Related

  • Step-by-Step ASP.NET Core RESTful Web Service Development
  • Spring Boot - How To Use Native SQL Queries | Restful Web Services
  • Building REST API Backend Easily With Ballerina Language
  • Composite Requests in Salesforce Are a Great Idea

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: