Integrating postgresql with asp.net mvc


👤 Diwas Poudel    🕒 30 Jun 2019    📁 TECH

Postgresql is one of the most advanced Open Source object-relational database systems. It is primarily a relational database and also includes additional features like table inheritance and function overloading — that is more often associated with object databases. It is completely free and has no demo version and licensing terms and conditions.

Now, Without wasting much time lets go to the step by step for integrating PostgreSQL with MVC.


What do you need?

1)Asp.Net MVC Application development environment. I am using Visual Studio 2017

2) PostgreSQL Server. I am using PostgreSQL 9.4

Step 1: Open Visual Studio and Press Ctrl + Shift + N for Creating a new project

create new project

Step 2: Then select "MVC" Template for building Model - View- Controller project and press ok

mvc with npgsql

Step 3: Install npgsql package from the NuGet package manager. Here I have installed  4.0.1 version of npgsql. 

Npgsql: It is the open-source .NET data provider for PostgreSQL.

insatll npgsql package

Step 4: Install EntityFramework6.npgsql via the NuGet package manager. It is  PostgreSQL provider for Entity Framework 6 

 

entity framework 6

I have used a 3.2.1 version with is a current stable version.  Generally, I found people are facing problem due to a version mismatch between Npgsql and EntityFramewor6.Npgsql packages and you may get an error like this.

version error related issue

So, simply use Npgsql version 4.0.7: and EntityFramework6.Npgsql 3.2.1.1.

Then be sure that these two packages are in reference to the project.

check package

Step 5 : Now lets go to PgAdmin and create one database name: "sampledb" and table " user" as shown below

pgadmin

Then I have inserted the data in the user table as shown below.

Step 6 : Then Change Web.Config file setting of asp.net mvc project

Inside the Web.Config 



    ..........

      
          
            
          
        
      
        
          
            
          
        
      
      
        
          
        
      
     ...........
  

Here:

database name: sampledb 

connectionname : TestConnection

user Id: localhost

password: diwas

port: 5432

This password is the password set at pgadmin, user Id is generally localhost if used locally and for a port look at the pgadmin, you can find there.

 

Step 8: Create User.cs class inside the Model folder

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace PostgreWithMvc.Models
{
    [Table("user", Schema = "public")]
    public class User
    {
        [Key]
        public int id { get; set; }
        public string name { get; set; }
    }
}

Let us look what does this model is meant to. This below image explains is enough to explain.

database and class

Primary Key is to be defined for every entity in the entity framework .So we have added a Key decorator to the primary key field. Here, the key is added to id property where id is a primary key of the user table.

Step 8: Create Own dbContext.  Here, I have created TestDbContext class as shown below:

DbContext is the primary class that is responsible for interacting with the database.

TestdbContext.cs

using PostgreWithMvc.Models;
using System.Data.Entity;

namespace PostgreWithMvc.Connection 
{ 
  public class TestDbContext : DbContext { 

     public TestDbContext() : base(nameOrConnectionString: "TestConnection") { } 

     public DbSet User { get; set; } 
   } 
}
 

The User property helps to perform all the operation like insert, update, delete and select on User entities throughout our web application.

Step 9: Then create usercontroller.cs controller and paste this :

UserController.cs

using PostgreWithMvc.Connection;
 using System; using System.Collections.Generic; 
 using System.Linq; using System.Web; using System.Web.Mvc; 

 namespace PostgreWithMvc.Controllers 
 { 
   public class UserController : Controller 
   { 
     TestDbContext db; public UserController() 
     { 
       db = new TestDbContext(); 
    } 

    public ActionResult Index() 
    {
       var data = db.User.ToList();
        return View(data); 
    } 
  } 
}

Step 10 : Inside view of Index Action

index.cshtml

@model IEnumerable
  @{
      ViewBag.Title = "Index";
  }

Index

  @foreach (var item in Model)
  {
      @item.id
      @item.name  
  }

Step 11:

Then build the project and press ctrl + F5 then you will get the list of user present in the database into a browser as shown below:

result

Finally, we are able to get data from the PostgreSQL database to MVC. 

combine

Enjoy Coding.