How to make database connectivity in ASP.NET core with PostgreSQL?

Question

How to make database connection in ASP.NET core with postgres SQL ?

Answer 1

How to make database connection in ASP.NET core with postgres SQL?

There are two ways you add your postgresql database along with the asp.net core project.

Using follwing way:

• ADO.net connection provider
• Nuget extension: Npgsql.EntityFrameworkCore.PostgreSQL

ADO.net connection provider

Here would just need the NpgsqlConnection connection builder class which will execute your sql on Postgre sql database server. See the example below:

C# ASP.NET Core & Postgre SQL Ado.net example:

  NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=postgres;Password=pwd;Database=postgres;");
    
    conn.Open();
    
    // Passing PostGre SQL Function Name
    NpgsqlCommand command = new NpgsqlCommand("EXE GetEmployeePrintInfo", conn);
    
    // Execute the query and obtain a result set
    NpgsqlDataReader reader = command.ExecuteReader();
    
    // Reading from the database rows
    List<string> listOfManager = new List<string>();

    while (reader.Read())
    {
        string WSManager = reader["WSManager"].ToString(); // Remember Type Casting is required here it has to be according to database column data type
        listOfManager.Add(WSManager);
    }
    reader.Close();

    command.Dispose();
    conn.Close();

Npgsql.EntityFrameworkCore.PostgreSQL:

You have to add Nuget extension into the project reference from manage Nuget Packages using Visual studio. Entity framework core has this functionality for many database providers. Just follow below steps on visual studio

ConfigureServices in startup.cs: Once you successfully added the Nuget package then you have to update following code on your project ConfigureServices under startup.cs

        services.AddDbContext<YourDatabaseContextClassName>(options =>
        options.UseNpgsql(Configuration.GetConnectionString("YourDatabaseContextStringNameFromAppsettings")));

Note: If you need any example for entityframework and Postgre SQL implementation you can have look here

For further assistance you can have look on official document here. Hope it would guide you accordingly.