Fix "Unable to Find Package" NuGet Error in Visual Studio C#
🕤 09 Aug 2024 Read more
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
Step 2: Then select "MVC" Template for building Model - View- Controller project and press ok
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.
Step 4: Install EntityFramework6.npgsql via the NuGet package manager. It is PostgreSQL provider for 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.
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.
Step 5 : Now lets go to PgAdmin and create one database name: "sampledb" and table " user" as shown below
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.
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:
Finally, we are able to get data from the PostgreSQL database to MVC.
Enjoy Coding.
-(1).jpg)
.png)
