This article shows how to use PostgreSQL with ASP.NET Core 1.0 using Dapper ORM. We will implement CRUD (Create, Read, Update and Delete) operations in ASP.NET MVC step by step.
Environment:
This article uses following software and versions:
.NET core 1.0.0
Postgresql 9.5.4
Npgsql 3.1.7
Dapper 1.50.2
Visual Studio 2015 update 3
1. Setup Database:
Create a new database “coresample” in PostgreSQL and create table using following sql
CREATE TABLE public.customer ( name text, email text, phone text, address text, id serial )
2. Setup Project:
Open Visual Studio > File > New Project> Select “ASP.NET Core Web Application” > Enter Name “ASPCoreSample” & Location > OK
Select “Web Application” template > OK
It will create web application project using ASP.NET Core. Now open package manager console and run following command to install Npgsql (PostgreSQL driver) and Dapper.
Install-Package Npgsql Install-Package Dapper
After installation, you will get Npgsql and Dapper in project.json dependencies.
3. ConnectionString:
In earlier version, we define connection string in web.config or app.config and access it using ConfigurationManager. But ASP.NET Core allows to read settings from different sources like XML, JSON and INI files.
Open appsettings.json file, you will get Logging settings, we will add connectionstring after it like below:
{ "Logging": { "IncludeScopes": false, "LogLevel": { "Default": "Debug", "System": "Information", "Microsoft": "Information" } }, "DBInfo": { "Name": "coresample", "ConnectionString": "User ID=postgres;Password=xxxxxx;Host=localhost;Port=5432;Database=coresample;Pooling=true;" } }
Open Startup.cs, add following in ConfigureServices method to access generic IConfiguration:
services.AddSingleton<IConfiguration>(Configuration);
4. Models:
For simplicity, we are going to add different layers in the same project.
Create Models folder in solution explorer and add “BaseEntity.cs” class.
namespace ASPCoreSample.Models { public abstract class BaseEntity { } }
Add class “Customer.cs” in Models folder
using System.ComponentModel.DataAnnotations; namespace ASPCoreSample.Models { public class Customer : BaseEntity { [Key] public long Id { get; set; } [Required] public string Name { get; set; } [Required] public string Email { get; set; } [Required] public string Phone { get; set; } public string Address { get; set; } } }
5. Repository:
Create Repository folder in solution explorer and add interface “IRepository.cs”
using ASPCoreSample.Models; using System.Collections.Generic; namespace ASPCoreSample.Repository { public interface IRepository<T> where T : BaseEntity { void Add(T item); void Remove(int id); void Update(T item); T FindByID(int id); IEnumerable<T> FindAll(); } }
Now let’s create CustomerRepository class in Repository folder
using System.Collections.Generic; using System.Linq; using Microsoft.Extensions.Configuration; using Dapper; using System.Data; using Npgsql; using ASPCoreSample.Models; namespace ASPCoreSample.Repository { public class CustomerRepository : IRepository<Customer> { private string connectionString; public CustomerRepository(IConfiguration configuration) { connectionString = configuration.GetValue<string>("DBInfo:ConnectionString"); } internal IDbConnection Connection { get { return new NpgsqlConnection(connectionString); } } public void Add(Customer item) { using (IDbConnection dbConnection = Connection) { dbConnection.Open(); dbConnection.Execute("INSERT INTO customer (name,phone,email,address) VALUES(@Name,@Phone,@Email,@Address)", item); } } public IEnumerable<Customer> FindAll() { using (IDbConnection dbConnection = Connection) { dbConnection.Open(); return dbConnection.Query<Customer>("SELECT * FROM customer"); } } public Customer FindByID(int id) { using (IDbConnection dbConnection = Connection) { dbConnection.Open(); return dbConnection.Query<Customer>("SELECT * FROM customer WHERE id = @Id", new { Id = id }).FirstOrDefault(); } } public void Remove(int id) { using (IDbConnection dbConnection = Connection) { dbConnection.Open(); dbConnection.Execute("DELETE FROM customer WHERE Id=@Id", new { Id = id }); } } public void Update(Customer item) { using (IDbConnection dbConnection = Connection) { dbConnection.Open(); dbConnection.Query("UPDATE customer SET name = @Name, phone = @Phone, email= @Email, address= @Address WHERE id = @Id", item); } } } }
In above code, we implemented IRepository methods and used Dapper to perform operations. To get connectionstring, the following command is used.
configuration.GetValue
As defined in appsettings.json.
6. Controllers:
Add new CustomerController.cs in Controllers folder
using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Configuration; using ASPCoreSample.Models; using ASPCoreSample.Repository; namespace ASPCoreSample.Controllers { public class CustomerController : Controller { private readonly CustomerRepository customerRepository; public CustomerController(IConfiguration configuration) { customerRepository = new CustomerRepository(configuration); } public IActionResult Index() { return View(customerRepository.FindAll()); } public IActionResult Create() { return View(); } // POST: Customer/Create [HttpPost] public IActionResult Create(Customer cust) { if (ModelState.IsValid) { customerRepository.Add(cust); return RedirectToAction("Index"); } return View(cust); } // GET: /Customer/Edit/1 public IActionResult Edit(int? id) { if (id == null) { return NotFound(); } Customer obj = customerRepository.FindByID(id.Value); if (obj == null) { return NotFound(); } return View(obj); } // POST: /Customer/Edit [HttpPost] public IActionResult Edit(Customer obj) { if (ModelState.IsValid) { customerRepository.Update(obj); return RedirectToAction("Index"); } return View(obj); } // GET:/Customer/Delete/1 public IActionResult Delete(int? id) { if (id == null) { return NotFound(); } customerRepository.Remove(id.Value); return RedirectToAction("Index"); } } }
Repository object is created in constructor.
7. Views:
Add Customer folder in Views folder and add Index, Create and Edit views.
Index.cshtml:
@model IEnumerable<ASPCoreSample.Models.Customer> @{ ViewData["Title"] = "Index"; } <h2>Index</h2> <p> <a asp-action="Create">Create New</a> </p> <table class="table"> <tr> <th> @Html.DisplayNameFor(model => model.Name) </th> <th> @Html.DisplayNameFor(model => model.Email) </th> <th> @Html.DisplayNameFor(model => model.Phone) </th> <th> @Html.DisplayNameFor(model => model.Address) </th> <th></th> </tr> @foreach (var item in Model) { <tr> <td> @Html.DisplayFor(modelItem => item.Name) </td> <td> @Html.DisplayFor(modelItem => item.Email) </td> <td> @Html.DisplayFor(modelItem => item.Phone) </td> <td> @Html.DisplayFor(modelItem => item.Address) </td> <td> <a asp-action="Edit" asp-route-id="@item.Id">Edit</a> | <a asp-action="Delete" asp-route-id="@item.Id" onclick="return confirm('Are sure wants to delete?');">Delete</a> </td> </tr> } </table>
It will show Grid of Customer data.
Create.cshtml:
@model ASPCoreSample.Models.Customer @{ ViewData["Title"] = "Create"; } <h2>Create</h2> <form asp-action="Create"> <div class="form-horizontal"> <h4>Customer Information:</h4> <hr /> <div asp-validation-summary="ModelOnly" class="text-danger"></div> <div class="form-group"> <label asp-for="Name" class="col-md-2 control-label"></label> <div class="col-md-10"> <input asp-for="Name" class="form-control" /> <span asp-validation-for="Name" class="text-danger" /> </div> </div> <div class="form-group"> <label asp-for="Email" class="col-md-2 control-label"></label> <div class="col-md-10"> <input asp-for="Email" class="form-control" /> <span asp-validation-for="Email" class="text-danger" /> </div> </div> <div class="form-group"> <label asp-for="Phone" class="col-md-2 control-label"></label> <div class="col-md-10"> <input asp-for="Phone" class="form-control" /> <span asp-validation-for="Phone" class="text-danger" /> </div> </div> <div class="form-group"> <label asp-for="Address" class="col-md-2 control-label"></label> <div class="col-md-10"> <input asp-for="Address" class="form-control" /> <span asp-validation-for="Address" class="text-danger" /> </div> </div> <div class="form-group"> <div class="col-md-offset-2 col-md-10"> <input type="submit" value="Create" class="btn btn-default" /> </div> </div> </div> </form> <div> <a asp-action="Index">Back to List</a> </div> @section Scripts { <script src="~/lib/jquery/dist/jquery.min.js"></script> <script src="~/lib/jquery-validation/dist/jquery.validate.min.js"></script> <script src="~/lib/jquery-validation-unobtrusive/jquery.validate.unobtrusive.min.js"></script> }
Edit.cshtml:
@model ASPCoreSample.Models.Customer @{ ViewData["Title"] = "Create"; } <h2>Edit</h2> <form asp-action="Edit"> <div class="form-horizontal"> <h4>Customer Information:</h4> <hr /> <div asp-validation-summary="ModelOnly" class="text-danger"></div> <div class="form-group"> <label asp-for="Name" class="col-md-2 control-label"></label> <div class="col-md-10"> <input asp-for="Name" class="form-control" /> <span asp-validation-for="Name" class="text-danger" /> </div> </div> <div class="form-group"> <label asp-for="Email" class="col-md-2 control-label"></label> <div class="col-md-10"> <input asp-for="Email" class="form-control" /> <span asp-validation-for="Email" class="text-danger" /> </div> </div> <div class="form-group"> <label asp-for="Phone" class="col-md-2 control-label"></label> <div class="col-md-10"> <input asp-for="Phone" class="form-control" /> <span asp-validation-for="Phone" class="text-danger" /> </div> </div> <div class="form-group"> <label asp-for="Address" class="col-md-2 control-label"></label> <div class="col-md-10"> <input asp-for="Address" class="form-control" /> <span asp-validation-for="Address" class="text-danger" /> </div> </div> <div class="form-group"> <div class="col-md-offset-2 col-md-10"> <input type="submit" value="Save" class="btn btn-default" /> </div> </div> </div> </form> <div> <a asp-action="Index">Back to List</a> </div> @section Scripts { <script src="~/lib/jquery/dist/jquery.min.js"></script> <script src="~/lib/jquery-validation/dist/jquery.validate.min.js"></script> <script src="~/lib/jquery-validation-unobtrusive/jquery.validate.unobtrusive.min.js"></script> }
The logic is same but Tag helpers are provided for easy and quick development.
To add Customer option in menu bar, open _Layout.cshtml in Views\Shared folder and add following line after Contact menu
<li><a asp-area="" asp-controller="Customer" asp-action="Index">Customer</a></li>
Run the application and start adding/editing/deleting Customers.
Source Code:
Conclusion:
In this post, we implemented CRUD operations in ASP.NET Core with PostgreSQL database using Dapper ORM. Basically it covers many things like how to configure connectionstring in .NET core, how to connect PostgreSQL database using Npgsql, how Dapper is used, new tag helpers in Razor views…etc.
Hope, It helps. Enjoy ASP.NET Core !!
Hi you forgot to mention MVC template after selecting “ASP.NET Core Web Application” while creating new project. So i simply selected empty webapplication. It made my waste. But This article is really helped for me to do sample application with postgre. Thanks for your help :) Kudos to you
Can you create same demo with store procedure
sorry, but i can’t run my Customer> Index.cshtml page as it run on Home>Index.cshtml everytime i tried to run the Customer> Index.cshtml. It also shows that the Web did not access my postgreSQL db as the port number display is not the same as my PostgreSQL port number. Can you help me on this?
It was a really nice experience to read about ASP.NET Core with PostgreSQL and Dapper – CRUD Operations Example. provided information very useful for Students. You have explained all the information implement CRUD (Create, Read, Update and Delete) operations in ASP.NET MVC step by step in a very structured manner. Thanks for sharing this informative article.
I simply wanted to thank you so much again. Thanks for sharing this valuable information.
Ꮋi theгe, the whole thing is going weⅼl һere aand ofcourse
everʏ one іs sharing facts, that’s truⅼy fіne, keеp up writing.
This completely defeats the purpose of DI:
public CustomerController(IConfiguration configuration)
{
customerRepository = new CustomerRepository(configuration);
}
it should instead be:
public CustomerController(IRepository repository)
{
_repository = repository;
}
Just wanted to write the same comment. +1 The repositories should be defined as Transient in the Startup class and they should really be used in a service layer…. but yea ok.. inject the repositories directly into the controllers – supposed to be quick and easy!
Could you perhaps demonstrate this?
Thanks for the article.
But what about database migrations in case when we using Dapper ORM?
Great stuff! Thank you. This connected a lot of dots for me. I work as a .NET dev, but mostly in the front end. I am now happily building my own API for the first time, thanks to you!
Excelent post! Only that you can use
services.AddSingleton<IRepository, CustomerRepository>();
In startup.cs and then in the controller you can get this IRepository without doing a new instance.
Hi, what use is it to create BaseEntity if I never use it? It is empty, would not it be better to remove it?
absolutely
What about EF?
The point here is to use Dapper instead of EF, and there is an OEM doc for EF on the asp core website.