Connect Azure Cosmos DB for PostgreSQL with ASP.NET Core: A Step-by-Step Guide

whyyou are A software developer tasked with creating powerful backend web applications for the team. you are Always look for tools that can improve performance, scalability, and ease of use. Azure Cosmos DB for PostgreSQL is A powerful, globally distributed database service that integrates seamlessly with your systems. SDK. In this blog well Learn how to connect Azure Cosmos DB for PostgreSQL to your ASP.NET Core applications to take your projects to new levels of efficiency and reliability..

Topics Covered

1. Create an ASP.NET Core web application
2. Connect to Azure Cosmos DB for PostgreSQL
3. Migration management using Entity Framework Core
4. Perform CRUD operations on data

The source code for the web API we will develop can be found at: CosmosDB-PostgresAPI

prerequisites

To achieve this goal:

Create an ASP.NET Core web application

• To verify that the .NET SDK was installed successfully:Run the following command in terminal: To check the version.

dotnet --version

I have .Net 8 installed.

• men your terminal, Run the following command Create an ASP .NET Core Web API and open it in Visual Studio Code.

dotnet new webapi --use-controllers -o CosmosPostgresApi 
cd CosmosPostgresApi 
code . 

we will use Microsoft Entity Framework, An Object Relational Mapper (ORM) that simplifies data access by allowing developers to interact with databases using .NET objects instead of writing raw SQL queries. You need to install the required packages from nuget.org at integration terminal. Microsoft.EntityFrameworkCore 8.0.8

dotnet add package Microsoft.EntityFrameworkCore 

• The package is added to: CosmosPostgresAPI.csproj

• In the root of your project in Solution Explorer, create the following: model Add the class name to the folder. pharmacy.cs. Add the following code to your class:

• Copy and paste the code pharmacy.cs class

using System; 
namespace CosmosPostgresApi.Models; 
public class Pharmacy 

{ 
    public int PharmacyId { get; set; } 

    public required string PharmacyName { get; set; } 

    public required string City { get; set; } 

    public required string State { get; set; } 

    public int ZipCode { get; set; } 
} 

The above code will help you map data from your database to objects. and On the contrary. reality skeleton We use this to create database tables. feverish Pharmacy ID, Pharmacy name, city, situationand zip code.

Create another file AppDbContext.cs Add the following code to the Models folder:

using System; 

using Microsoft.EntityFrameworkCore; 

namespace CosmosPostgresApi.Models; 

public class AppDbContext : DbContext 

{ 
    public AppDbContext(DbContextOptions options) : base(options) { } 

    public DbSet Pharmacies { get; set; } 

    protected override void OnModelCreating(ModelBuilder modelBuilder) 

    { 

        base.OnModelCreating(modelBuilder); 

        modelBuilder.Entity() 

        .ToTable("pharmacies") 

        .HasKey(p => p.PharmacyId); 
    } 

    public async Task DistributeTableAsync() 

    { 

        await Database.ExecuteSqlRawAsync("SELECT create_distributed_table('pharmacies', 'PharmacyId');"); 

    } 
} 

• This code snippet means: AppDbContext class This is a class inherited from: DbContext class.
• that AppDbContext The class is used to interact with the database and represents a session with the database.
• This includes tangible real estate dispensaries. DbSet This represents the pharmacy collection in the database.
• that Creating model Methods are used to construct entity mappings and relationships in the database. Create a pharmacies table and set the primary key to the PharmacyId property of the Pharmacy class.

Connect to Azure Cosmos DB for PostgreSQL

• To connect Azure Cosmos DB for PostgreSQL to a web API, you need to create a cluster in the Azure portal. We covered this in a previous blog.
• While creating your cluster, don’t forget the following: database name, password and admin user.
• Once the cluster is created, go to r.This is the resource you just created.
• I created the following cluster: csharp-postgres-sdk

Let’s go back to Visual Studio Code and connect to the created database.

You’ll need credentials to connect to the database you just created, so save them here: app settings.json Save the file as a connection string.

Copy and paste this code. app settings.json. change , , With the correct values.

"ConnectionStrings": { 
    "CosmosPostgres": "c-..postgres.cosmos.azure.com; Database = citus; Port = 5432; User Id = citus; Password = ; Ssl Mode = Require; Pooling = true; Minimum Pool Size=0; Maximum Pool Size =50;" 
  }, 

• The connection string should be laid out like this:

Let’s install a few packages: PostgreSQL Provider There are also other topics to help you generate code for CRUD operations and migrations using the Entity Framework.

• Install the following packages using the terminal:

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL 

dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design 

dotnet add package Microsoft.EntityFrameworkCore.Design 

dotnet add package Microsoft.EntityFrameworkCore.SqlServer 

dotnet add package Microsoft.EntityFrameworkCore.Tools 

dotnet tool install -g dotnet-aspnet-codegenerator 

• To see everything package check added ItemGroup.

• Before running migration register AddDbContext Specify the class as a service in the dependency injection container and configure it to use a PostgreSQL database.

Now we can create our controller. The following code is Helps with quick setup Build a controller containing CRUD operations for the Pharmacy model using an asynchronous approach and integrating with the specified database context.

• Run the code in terminal.

dotnet aspnet-codegenerator controller -name PharmacyController -async -api -m Pharmacy -dc AppDbContext -outDir Controllers 

• You should be able to see the CRUD operations generated by: controller named folder pharmacy controller.cs

Migration management using Entity Framework Core

Entity Framework Core allows you to generate SQL code directly from C# objects, giving you the benefit of using an Object Relational Mapper (ORM) to simplify database interaction.

Run the following command in the terminal to create a new migration named: “Initial Creation” in your project. This migration includes the code needed to create an initial database schema based on your current data model.

dotnet ef migrations add InitialCreate 

• new folder migration Generated with initial SQL code that will be used to create the table.

• You must run an update command to apply the following changes to the database.

dotnet ef database update 

quest on Azure door, Quick Start (Preview) below you made resources, launch mailgreSQL shell Command line interface for interacting with the database. Please enter your password. prompt.

• To view the table, run the following command:

\dt

• You should get something like this:

table pharmacy It has been created and we can now perform some CRUD operations.

In Visual Studio Code, click Next. Ctrl + F5 Run the code.

The project is wonderful You can start testing your endpoints from your browser. i will use rest of the clients Test your API. For this Rest of the client extensions Installed in Visual Studio Code.

• to mail pharmacy: (Making a pharmacy)

• to get All pharmacies:

• to get Single Pharmacy:

• to put no way Pharmacy (updated)

In this blog we are successfully preview How to retain your data Azure Cosmos DB for PostgreSQL. I hope the steps were clear and easy to follow. Thank you for reading. Have fun coding!

read more

Use Python to connect and run SQL commands in Azure Cosmos DB for PostgreSQL.

Connect and run SQL commands in Azure Cosmos DB for PostgreSQL using Node.js

Java app to connect and run SQL commands in Azure Cosmos DB for PostgreSQL

Connect and run SQL commands in Azure Cosmos DB for PostgreSQL using Ruby.

Create a web API using ASP.NET Core

Entity Framework Core