CRUD operation in ASP.NET CORE with Using Dapper ORM
In this part, we are going to learn essential CRUD operation using ADO.NET in details and also how to configure it.
- Creating ASP.NET Core Application
- Setting up Database and Table
- Configuring Connection string in appsettings.json
- Adding Product Model
- Implementing Repository Pattern
- Adding Interface
- Adding Concrete Class
- Installing Dapper from NuGet
- Implemented Code for performing CRUD operation
- Performing CRUD operations
Let’s get ready to learn how to perform CRUD operation with ADO.NET in simple steps.
Creating ASP.NET Core Application
We are going to create ASP.NET Core Web Application for that we are going to choose ‘ASP.NET Core Web Application‘ template.
Next, we are going to set Project Name ‘WebApplication9‘ and location and in last part, we are going to choose .Net Core framework and ASP.NET Core Version 2.2 as framework for application and few advance settings for such as configuring https and enabling docker we are not going to enable both of settings for this project.
Now finally click on create button to create a project.
Project structure
The project structure generated according to the configuration.
After creating a project, we are going to run this project.
Setting up Database and Table
We have created a database with Name Sample2, and then we have added product table in it.
Below is Table structure along with a script to create a table.
Script to Create Product Table
CREATE TABLE [dbo].[Product](
[ProductId] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Quantity] [int] NULL,
[Color] [varchar](50) NULL,
[Price] [decimal](18, 2) NULL,
[ProductCode] [varchar](50) NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]Configuring Connection string in appsettings.json
Here in ASP.NET core, we don’t have web.config file which was there in the older version of ASP.NET and ASP.NET MVC here we have appsettings.json file where we are going to keep application-level settings, and this file is Json based.
appsettings.json file
In this file, we have added ConnectionStrings section inside that we have added ‘DatabaseConnection‘ key and other is value.
Notice: – here I am using SQL based authentication for database connection that why I have added User Id and password of SQL server.
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"DatabaseConnection": "Data Source=SAI-PC\\SQLEXPRESS; initial catalog=Sample2; user id=sa; password=Pass123"
}
}Next, after adding the connection string for accessing it in the entire application, we are going to add a class with Name ‘ShareConnectionString‘ and set class as static inside that class we are going to add a static property named ‘Value’.
public static class ShareConnectionString
{
public static string Value { get; set; }
}After adding class Next we are going to read the value of connection string from appsettings.json file.
var connection = Configuration.GetConnectionString("DatabaseConnection");After reading the value of connection string we are going to assign value to the static class.
ShareConnectionString.Value = connection;Code Snippet of ConfigureServices Method of Startup Class
public void ConfigureServices(IServiceCollection services)
{
//Getting Connection String from Database
var connection = Configuration.GetConnectionString("DatabaseConnection");
ShareConnectionString.Value = connection;
services.AddControllersWithViews();
}After assigning value we can access connection string in the entire application.
Next, we are going to add Model Product in Models folder.
Adding Product Model
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace WebApplication6.Models
{
public class Product
{
public int ProductId { get; set; }
public string Name { get; set; }
public int Quantity { get; set; }
public string Color { get; set; }
public decimal Price { get; set; }
public string ProductCode { get; set; }
}
}After creating Model next, we are going to add ViewModel with name ProductVm this Model will be used to take User input from View.
namespace WebApplication6.Models
{
public class ProductVm
{
public string Name { get; set; }
public int Quantity { get; set; }
public string Color { get; set; }
public decimal Price { get; set; }
public string ProductCode { get; set; }
}
}Next, we are going to implement the Repository pattern.
What is a Repository pattern?
Repository helps us to create a loosely coupled application which separates data access logic and business logic.
Implementing Repository Pattern
We are going to implement Repository pattern for doing that first I am going to create a separate folder with the name ‘Repository‘ for storing Interface and Concrete class which we are going to create further.
After adding folder next, we are going to Add Interface in Repository folder with Name ‘IProduct‘.
Now inside ‘IProduct‘ Interface, we are going declare our CRUD methods that are shown below.
Adding Interface (IProduct)
We have added an interface with name IProduct inside that method we have declared CRUD methods.
using System.Collections.Generic;
using WebApplication6.Models;
namespace WebApplication6.Repository
{
public interface IProduct
{
void InsertProduct(ProductVm product); // C
IEnumerable<Product> GetProducts(); // R
Product GetProductByProductId(int productId); // R
void UpdateProduct(Product product); //U
void DeleteProduct(int productId); //D
}
}After adding Interface next we are going to add Concrete class, this class is going to inherit interface IProduct
Adding Concrete (ProductConcrete)
We have added a Concrete class with name ‘ProductConcrete‘ next we are going to inherit with interface IProduct and implement all methods inside it.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using WebApplication6.Models;
namespace WebApplication6.Repository
{
public class ProductConcrete : IProduct
{
public void DeleteProduct(int productId)
{
throw new NotImplementedException();
}
public Product GetProductByProductId(int productId)
{
throw new NotImplementedException();
}
public IEnumerable<Product> GetProducts()
{
throw new NotImplementedException();
}
public void InsertProduct(ProductVm product)
{
throw new NotImplementedException();
}
public void UpdateProduct(Product product)
{
throw new NotImplementedException();
}
}
}We have implemented the IProduct interface but not written code for performing CRUD operations.
For accessing the database, we are going to use Dapper ORM which we are going to add from NuGet Package.
Installing Dapper and System.Data.SqlClient from NuGet
For adding a package from NuGet right-click on Main project ‘WebApplication6‘ and from the menu select Manage NuGet Packages. As you select it, a New dialogue of NuGet Package Manager with search box is going to pop up.
In choose browse tab and search ‘Dapper‘ and choose the latest version of ‘Dapper‘, then finally click on the install button for adding the package.
Similar way we are going to add ‘System.Data.SqlClient‘ package from NuGet.
After installing Dapper ORM and System.Data.SqlClient from NuGet Package Manager, it will be shown inside Dependency -> NuGet.
After adding Dapper ORM, now Let’s Write code in ProductConcrete class where we have implemented IProduct interface.
Implemented Code for performing CRUD operation
In this part, we have written code to perform crud operation using dapper ORM along with stored procedures, and we have used connection string which we have configured in ConfigureServices method (‘ShareConnectionString‘) also used SQL transaction to perform Insert, update, delete operation for maintaining data integrity.
Note: – I will be sharing all database script along with application source code with you.
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using WebApplication6.Models;
namespace WebApplication6.Repository
{
public class ProductConcrete : IProduct
{
// Delete
public void DeleteProduct(int productId)
{
using var con = new SqlConnection(ShareConnectionString.Value);
var param = new DynamicParameters();
param.Add("@ProductId", productId);
var result = con.Execute("Usp_Delete_Product", param, null, 0, CommandType.StoredProcedure);
}
// Get Single Product
public Product GetProductByProductId(int productId)
{
using var con = new SqlConnection(ShareConnectionString.Value);
var param = new DynamicParameters();
param.Add("@ProductId", productId);
return con.Query<Product>("Usp_Get_Productby_ProductId", param, null, true, 0, CommandType.StoredProcedure).FirstOrDefault();
}
// Get List of Products
public IEnumerable<Product> GetProducts()
{
using var con = new SqlConnection(ShareConnectionString.Value);
return con.Query<Product>("Usp_GetAll_Products", null, null, true, 0, CommandType.StoredProcedure).ToList();
}
// Insert
public void InsertProduct(ProductVm product)
{
try
{
using var con = new SqlConnection(ShareConnectionString.Value);
con.Open();
var transaction = con.BeginTransaction();
try
{
var param = new DynamicParameters();
param.Add("@Name", product.Name);
param.Add("@Quantity", product.Quantity);
param.Add("@Color", product.Color);
param.Add("@Price", product.Price);
param.Add("@ProductCode", product.ProductCode);
var result = con.Execute("Usp_Insert_Product", param, transaction, 0, CommandType.StoredProcedure);
if (result > 0)
{
transaction.Commit();
}
}
catch (Exception)
{
transaction.Rollback();
}
}
catch (Exception)
{
throw;
}
}
// Update
public void UpdateProduct(Product product)
{
using var con = new SqlConnection(ShareConnectionString.Value);
con.Open();
var transaction = con.BeginTransaction();
try
{
var param = new DynamicParameters();
param.Add("@Name", product.Name);
param.Add("@Quantity", product.Quantity);
param.Add("@Color", product.Color);
param.Add("@Price", product.Price);
param.Add("@ProductCode", product.ProductCode);
param.Add("@ProductId", product.ProductId);
var result = con.Execute("Usp_Update_Product", param, transaction, 0, CommandType.StoredProcedure);
if (result > 0)
{
transaction.Commit();
}
}
catch (Exception)
{
transaction.Rollback();
}
}
// Check Product Exists
public bool CheckProductExists(int productId)
{
using var con = new SqlConnection(ShareConnectionString.Value);
var param = new DynamicParameters();
param.Add("@ProductId", productId);
var result = con.Query<bool>("Usp_CheckProductExists", param, null, false,0, CommandType.StoredProcedure).FirstOrDefault();
return result;
}
}
}
After completing implementing IProduct interface next, we are going to add Controller to action method to access all these methods.
Performing CRUD operations
CREATE
In this part, we are going to add Controller with name ‘ManageController‘ Controller.
using Microsoft.AspNetCore.Mvc;
namespace WebApplication5.Controllers
{
public class ManageProductController : Controller
{
}
}Next, to access Product concrete class method in ManageProductController, we need to use Dependency Injection.
For using Dependency Injection, we need to register interface and concrete class with IOC container in ConfigureServices method of the Startup class.
Note: –
Transient :- Service is Created every time they are requested
Scoped :- Service is created once per client request (web request) best to use with entity framework core.
Singleton :- Service is Created only for the first request. Every consecutive request uses the same instance.
Referred from:- https://docs.microsoft.com/en-us/aspnet/core/fundamentals/dependency-injection?view=aspnetcore-2.2
Registering Service in ConfigureServices Method of Startup Class
We are registering Interface and Concrete class in IOC container such that we can resolve dependency automatically. Means will not create an object we will ask for an object from the container.
// registering Service in IOC container
services.AddTransient<IProduct, ProductConcrete>();Code Snippet
public void ConfigureServices(IServiceCollection services)
{
//Getting Connection String from Database
var connection = Configuration.GetConnectionString("DatabaseConnection");
ShareConnectionString.Value = connection;
// registering Service in IOC container
services.AddTransient<IProduct, ProductConcrete>();
services.AddControllersWithViews();
}After registering services Next, we are going add 2 Action method with the name ‘Create‘ one for handling GET request and another for a handling POST request and ManageProductController we are going to use Constructor injection get an instance of ProductConcrete class.
Code Snippet
using Microsoft.AspNetCore.Mvc;
using WebApplication6.Models;
using WebApplication6.Repository;
namespace WebApplication6.Controllers
{
public class ManageProductController : Controller
{
private readonly IProduct _product;
public ManageProductController(IProduct product)
{
_product = product;
}
// GET: ManageProduct/Create
public IActionResult Create()
{
return View();
}
// POST: ManageProduct/Create
[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Create([Bind("Name,Quantity,Color,Price,ProductCode")] ProductVm product)
{
if (ModelState.IsValid)
{
_product.InsertProduct(product);
return RedirectToAction("Index");
}
return View(product);
}
}
}
Note: – Bind attribute – To protect from overposting attacks, we have use bind attribute.
In the above source code the CREATE method which handles POST request in that method we are validating Model first if ModelState is valid then we pass ProductVM Model to InsertProduct method.
If ModelState is not valid then we return View along with model which was posted which carry Display error message to View.
ValidationSummary: – ValidationSummary reads all errors from the model state and displays them in a bulleted list.
ValidationMessageFor: – ValidationMessageFor displays only errors for to the property specified.
Referenced from:- https://exceptionnotfound.net/asp-net-mvc-demystified-modelstate/
After adding the Controller and action method next we are going to Add View for ‘Create‘ Action Method.
@model WebApplication6.Models.ProductVm
@{
ViewData["Title"] = "Create";
}
<h4>Create</h4>
<hr />
<div class="row">
<div class="col-md-4">
<form asp-action="Create">
<div asp-validation-summary="ModelOnly" class="text-danger"></div>
<div class="form-group">
<label asp-for="Name" class="control-label"></label>
<input asp-for="Name" class="form-control" />
<span asp-validation-for="Name" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Quantity" class="control-label"></label>
<input asp-for="Quantity" class="form-control" />
<span asp-validation-for="Quantity" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Color" class="control-label"></label>
<input asp-for="Color" class="form-control" />
<span asp-validation-for="Color" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Price" class="control-label"></label>
<input asp-for="Price" class="form-control" />
<span asp-validation-for="Price" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="ProductCode" class="control-label"></label>
<input asp-for="ProductCode" class="form-control" />
<span asp-validation-for="ProductCode" class="text-danger"></span>
</div>
<div class="form-group">
<input type="submit" value="Create" class="btn btn-primary" />
</div>
</form>
</div>
</div>
<div>
<a asp-action="Index">Back to List</a>
</div>
@section Scripts {
@{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}
Next, after inserting data we are going to show data which we have inserted for that we are going to add the Index and Details action Method.
READ
In this part, we are going to show data which we have created for that we are going to add 2 action method one is Index which will show all rows which we have inserted and another one is Details which will show details of individual record.
In the Index action method, we are going to get a list of products and display it on view.
The Details action method takes product id as an input parameter and will show details of that product
If you see code snippet of Details action method you will see we are getting data from product table by passing id which is product id and then we render it on Details View.
// GET: ManageProduct
public IActionResult Index()
{
return View(_product.GetProducts());
}
// GET: ManageProduct/Details/5
public IActionResult Details(int? id)
{
if (id == null)
{
return RedirectToAction("Index");
}
var product = _product.GetProductByProductId(Convert.ToInt32(id));
if (product == null)
{
return RedirectToAction("Index");
}
return View(product);
}Note: – This @model directive allows you to access the object that we pass from controller to the view
Index View
The Index view expects strongly type of object products list (IEnumerable<WebApplication6.Models.Product>) which we are passing from the Index Action method to view. Because we have a collection of products, we need to iterate to display each product.
@model IEnumerable<WebApplication6.Models.Product>
@{
ViewData["Title"] = "Index";
}
<h1>Index</h1>
<p>
<a asp-action="Create">Create New</a>
</p>
<table class="table">
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
<th>
@Html.DisplayNameFor(model => model.Quantity)
</th>
<th>
@Html.DisplayNameFor(model => model.Color)
</th>
<th>
@Html.DisplayNameFor(model => model.Price)
</th>
<th>
@Html.DisplayNameFor(model => model.ProductCode)
</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.Quantity)
</td>
<td>
@Html.DisplayFor(modelItem => item.Color)
</td>
<td>
@Html.DisplayFor(modelItem => item.Price)
</td>
<td>
@Html.DisplayFor(modelItem => item.ProductCode)
</td>
<td>
<a asp-action="Edit" asp-route-id="@item.ProductId">Edit</a> |
<a asp-action="Details" asp-route-id="@item.ProductId">Details</a> |
<a asp-action="Delete" asp-route-id="@item.ProductId">Delete</a>
</td>
</tr>
}
</tbody>
</table>
Details View
The Details view expects strongly type of object product (WebApplication6.Models.Product) which we are passing from Details Action method to view to display product details.
@model WebApplication6.Models.Product
@{
ViewData["Title"] = "Details";
}
<h1>Details</h1>
<div>
<h4>Product</h4>
<hr />
<dl class="row">
<dt class = "col-sm-2">
@Html.DisplayNameFor(model => model.Name)
</dt>
<dd class = "col-sm-10">
@Html.DisplayFor(model => model.Name)
</dd>
<dt class = "col-sm-2">
@Html.DisplayNameFor(model => model.Quantity)
</dt>
<dd class = "col-sm-10">
@Html.DisplayFor(model => model.Quantity)
</dd>
<dt class = "col-sm-2">
@Html.DisplayNameFor(model => model.Color)
</dt>
<dd class = "col-sm-10">
@Html.DisplayFor(model => model.Color)
</dd>
<dt class = "col-sm-2">
@Html.DisplayNameFor(model => model.Price)
</dt>
<dd class = "col-sm-10">
@Html.DisplayFor(model => model.Price)
</dd>
<dt class = "col-sm-2">
@Html.DisplayNameFor(model => model.ProductCode)
</dt>
<dd class = "col-sm-10">
@Html.DisplayFor(model => model.ProductCode)
</dd>
</dl>
</div>
<div>
<a asp-action="Edit" asp-route-id="@Model.ProductId">Edit</a> |
<a asp-action="Index">Back to List</a>
</div>
After we saw how to display data on index and details view next, we are going to learn how to perform Edit and Update.
Update
In this part, we are going to perform an update on product data which we have created. To perform edit we are first going to add 2 Action Method with Name ‘Edit‘ one for handling GET Request and Another for handling Post Request to edit individual Product details we are going to View Index View there we will find edit link in front of each product after click on link it will redirect to Edit Action method which takes id (product id) as input parameter according to that id we are going to get product detail from database and display it on edit view after editing details will submit details which will be handled by Edit Action Method with handles POST Request.
// GET: ManageProduct/Edit/5
public IActionResult Edit(int? id)
{
if (id == null)
{
return RedirectToAction("Index");
}
var product = _product.GetProductByProductId(Convert.ToInt32(id));
return View(product);
}In Edit Get Action method which handles GET Request where we are getting id (product id) parameter as input if it is not null, then we are going get Product details from database using product id and send product model to view for rendering.
[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Edit(int id, [Bind("ProductId,Name,Quantity,Color,Price,ProductCode")] Product product)
{
if (id != product.ProductId)
{
return RedirectToAction("Index");
}
if (ModelState.IsValid)
{
try
{
if (_product.CheckProductExists(id))
{
_product.UpdateProduct(product);
}
else
{
ModelState.AddModelError("","Product Does Not Exists");
return View(product);
}
}
catch (Exception)
{
throw;
}
return RedirectToAction(nameof(Index));
}
return View(product);
}The Edit POST Action method which handles POST request This action method takes Product Model and Id as input id (product id) according to which Product details will get updated before updating we validate Model first if ModelState is valid then we are going to Update Product details.
For that, we pass the product Model it to UpdateProduct method of ProductConcrete Class for updating records.
While updating if it throws an error it is handled in the catch method.
Edit View
@model WebApplication6.Models.Product
@{
ViewData["Title"] = "Edit";
}
<h1>Edit</h1>
<h4>Product</h4>
<hr />
<div class="row">
<div class="col-md-4">
<form asp-action="Edit">
<div asp-validation-summary="ModelOnly" class="text-danger"></div>
<input type="hidden" asp-for="ProductId" />
<div class="form-group">
<label asp-for="Name" class="control-label"></label>
<input asp-for="Name" class="form-control" />
<span asp-validation-for="Name" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Quantity" class="control-label"></label>
<input asp-for="Quantity" class="form-control" />
<span asp-validation-for="Quantity" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Color" class="control-label"></label>
<input asp-for="Color" class="form-control" />
<span asp-validation-for="Color" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Price" class="control-label"></label>
<input asp-for="Price" class="form-control" />
<span asp-validation-for="Price" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="ProductCode" class="control-label"></label>
<input asp-for="ProductCode" class="form-control" />
<span asp-validation-for="ProductCode" class="text-danger"></span>
</div>
<div class="form-group">
<input type="submit" value="Save" class="btn btn-primary" />
</div>
</form>
</div>
</div>
<div>
<a asp-action="Index">Back to List</a>
</div>
@section Scripts {
@{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}Snapshot
Index View
Edit View
Index View after updating data.
Delete
In this part, we are going to Delete product which we have created for doing that we are going to add 2 Delete Action method one for handling GET request and another for a handling POST request.
In Delete Get Action method which handles GET Request which takes id as input parameter on bases of id (product id), we are going to fetch data from database and render on Delete View.
// GET: ManageProduct/Delete/5
public IActionResult Delete(int? id)
{
if (id == null)
{
return RedirectToAction("Index");
}
var product = _product.GetProductByProductId(Convert.ToInt32(id));
if (product == null)
{
return RedirectToAction("Index");
}
return View(product);
}The Delete POST Action method which handles POST request This action method takes id (product id) as input and then we pass product id to Delete method of Product Concrete Class for deleting a record.
// POST: ManageProduct/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public IActionResult DeleteConfirmed(int id)
{
_product.DeleteProduct(id);
return RedirectToAction(nameof(Index));
}Delete View
@model WebApplication6.Models.Product
@{
ViewData["Title"] = "Delete";
}
<h1>Delete</h1>
<h3>Are you sure you want to delete this?</h3>
<div>
<h4>Product</h4>
<hr />
<dl class="row">
<dt class="col-sm-2">
@Html.DisplayNameFor(model => model.Name)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.Name)
</dd>
<dt class="col-sm-2">
@Html.DisplayNameFor(model => model.Quantity)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.Quantity)
</dd>
<dt class="col-sm-2">
@Html.DisplayNameFor(model => model.Color)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.Color)
</dd>
<dt class="col-sm-2">
@Html.DisplayNameFor(model => model.Price)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.Price)
</dd>
<dt class="col-sm-2">
@Html.DisplayNameFor(model => model.ProductCode)
</dt>
<dd class="col-sm-10">
@Html.DisplayFor(model => model.ProductCode)
</dd>
</dl>
<form asp-action="Delete">
<input type="hidden" asp-for="ProductId" />
<input type="submit" value="Delete" class="btn btn-danger" /> |
<a asp-action="Index">Back to List</a>
</form>
</div>Snapshot
Index View
Delete View
Conclusion
In this part we have learned how to create an application, how to create a repository, registering Interface and concrete method in IOC container using Constructor Injection to resolve dependency and perform CRUD operation.