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
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
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

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • 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
  • Creating a Secure REST API in Node.js

Trending

  • AI’s Role in Everyday Development
  • Performing and Managing Incremental Backups Using pg_basebackup in PostgreSQL 17
  • Unmasking Entity-Based Data Masking: Best Practices 2025
  • AI-Based Threat Detection in Cloud Security
  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.

By 
Joydip Kanjilal user avatar
Joydip Kanjilal
DZone Core CORE ·
Jul. 29, 21 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
11.7K 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

  • 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
  • Creating a Secure REST API in Node.js

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • 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:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!