This article will demonstrate you about how to perform CRUD operations in Razor Page which introduced Asp.Net Core 2 using Dapper and Repository Pattern.
To complete this demonstration in a simple manner, we will follow some steps as below.
Step 1: Create Razor Page in Asp.Net Core 2
Step 2: Create Database and Table
Step 3: Install Dapper
Step 4: Create Repository Class and Interface with Required Entity
Step 5: Create Connection String and get it on Repository Class
Step 6: Create Razor Page for CRUD Operations
Step 7: Implement Code in Razor Pages for performing CRUD Operations
Now we have defined six steps to complete this practical demonstration. So, let’s move to step by step and complete the task.
STEP 1: Create Razor Page in Asp.Net Core 2
To create Asp.Net Core 2 Razor Page project, open Visual Studio 2017 version 15.3 and above and make sure you have installed .Net Core SDK 2.0 with your system. If you don’t have this configuration with your system, please update your Visual Studio and system with these configurations.
I hope, you have installed the lasted version of Visual Studio with .Net Core SDK 2.0, so open Visual Studio and Go to File Menu > choose New > select Project. It will open “New Project” windows. From the New Project windows, you have to choose .Net Core from the left panel and then from the center panel choose “Asp.Net Core Web Application” and provide the suitable name like “RazorPagesExample” and click to OK.
After clicking OK button, it will pop up with a new window from where we can choose the template for Razor Page.
From here, you have to choose “Web Application” to create Razor Page application and click to OK. It will take minutes or seconds to configure your project and finally, we have the project ready for demonstration.
STEP 2: Create Database and Table
Open SSMS [SQL Server Management Studio] and create new database name called it “TestDB” and inside that, you have to create one table that is called “Product” using following SQL scripts. Now we have a database and its corresponding table is ready.
--Create Database
CREATE DATABASE TestDB
GO
--Use Created Database
USE TestDB
GO
--Create Table "Product"
CREATE TABLE Product(Id INT PRIMARY KEY IDENTITY(1,1), Name VARCHAR(25), Model VARCHAR(50), Price INT)
--Insert dummy reocrd in table
INSERT INTO Product(Name, Model, Price) VALUES('Nokia 1', 'Nokia', 25000)
SELECT * FROM Product
STEP 3: Install Dapper
In one line, Dapper is micro ORM with high performance for SQL, MySQL etc.
It is a simple step to install Dapper. Right click on the project and choose “Manage NuGet Packages”. It will open NuGet Package Manager from there you can search packages which need to be installed. So, now we will search “Dapper” and installed it. After installation, it will be shown in the NuGet section which lies inside the “Dependencies” as follows in the project.
STEP 4: Create Repository Class and Interface with Required Entity
In this demonstration, we will perform CRUD operations for Product. How to see product data, how to add a new product, how to edit existing product and how to delete existing product after confirmation, so here we should have an entity which is related to Product.
Create a new folder inside the project name called “Entity” and add a new class “Product.cs” as follows which should have product’s id, name, model, and price. You can use Data Annotation here for validating the properties while adding or editing the data for the product.
using System.ComponentModel.DataAnnotations;
namespace RazorPagesExample.Entity
{
public class Product
{
[Key]
[Display(Name = "Product Id")]
public int Id { get; set; }
[Required]
[Display(Name="Product Name")]
[StringLength(25, ErrorMessage ="Name should be 1 to 25 char in lenght")]
public string Name { get; set; }
[Required]
[Display(Name = "Model")]
[StringLength(50, ErrorMessage = "Name should be 1 to 50 char in lenght")]
public string Model { get; set; }
[Required]
[Display(Name = "Price")]
public int Price { get; set; }
}
}
Add one more folder and name calling it “Repository” inside the main project folder and add one interface as “IProductRepository” which contains all required server side method to perform CRUD operations.
using RazorPagesExample.Entity;
using System.Collections.Generic;
namespace RazorPagesExample.Repository
{
public interface IProductRepository
{
int Add(Product product);
List<Product> GetList();
Product GetProduct(int id);
int EditProduct(Product product);
int DeleteProdcut(int id);
}
}
To implement above interface “IProductRepository”, we have to create a repository class name called it “ProductRepository” as follows.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using RazorPagesExample.Entity;
using System.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using Dapper;
namespace RazorPagesExample.Repository
{
public class ProductRepository : IProductRepository
{
IConfiguration _configuration;
public ProductRepository(IConfiguration configuration)
{
_configuration = configuration;
}
public int Add(Product product)
{
var connectionString = this.GetConnection();
int count = 0;
using (var con = new SqlConnection(connectionString))
{
try
{
con.Open();
var query = "INSERT INTO Product(Name, Model, Price) VALUES(@Name, @Model, @Price); SELECT CAST(SCOPE_IDENTITY() as INT);";
count = con.Execute(query, product);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return count;
}
}
public int DeleteProdcut(int id)
{
var connectionString = this.GetConnection();
var count = 0;
using (var con = new SqlConnection(connectionString))
{
try
{
con.Open();
var query = "DELETE FROM Product WHERE Id =" + id;
count = con.Execute(query);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return count;
}
}
public int EditProduct(Product product)
{
var connectionString = this.GetConnection();
var count = 0;
using (var con = new SqlConnection(connectionString))
{
try
{
con.Open();
var query = "UPDATE Product SET Name = @Name, Model = @Model, Price = @Price WHERE Id = @Id";
count = con.Execute(query, product);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return count;
}
}
public List<Product> GetList()
{
var connectionString = this.GetConnection();
List<Product> products = new List<Product>();
using (var con = new SqlConnection(connectionString))
{
try
{
con.Open();
var query = "SELECT * FROM Product";
products = con.Query<Product>(query).ToList();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return products;
}
}
public Product GetProduct(int id)
{
var connectionString = this.GetConnection();
Product product = new Product();
using (var con = new SqlConnection(connectionString))
{
try
{
con.Open();
var query = "SELECT * FROM Product WHERE Id =" + id;
product = con.Query<Product>(query).FirstOrDefault();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return product;
}
}
public string GetConnection()
{
var connection = _configuration.GetSection("ConnectionStrings").GetSection("ProductContext").Value;
return connection;
}
}
}
Above “ProductRepository” class has methods available for CRUD operation like “Add”, “EditProduct”, “DeleteProudct”, “GetList” and “GetProduct”.
Add: It uses for adding a new product into database.
EditProduct: It uses for editing existing product data.
DeleteProduct: This method uses for deleting existing product data after confirmation.
GetList: This gets all the available products data
GetProduct: It gets the data for single Product.
These methods are internally making the call to a database using Dapper.
STEP 5: Create Connection String and get it on Repository Class
Now we have to define our database connection string. As we know Asp.Net Core uses appsettings.json to define your app related all configurations. So, we are going to add our connection string inside the appsettings.json file as follows.
{
"Logging": {
"IncludeScopes": false,
"LogLevel": {
"Default": "Warning"
}
},
"ConnectionStrings": {
"ProductContext": "Data Source=AAYUSH-PC; Database=TestDB; UID=sa; Password=*******;"
}
}
To get this connection string on Repository we have already made code with above repository using “GetConnection()” method that gives us “Connection String” as following code shown.
public string GetConnection()
{
var connection = _configuration.GetSection("ConnectionStrings").GetSection("ProductContext").Value;
return connection;
}
STEP 6: Create Razor Pages for CRUD Operations
Now it’s time to create Razor Pages for CRUD operations, so we will create three pages inside the Product folder as shown with below image.
Index.cshtml is responsible to show all product list with Edit and Delete buttons at the last column with corresponding data. Apart from that, it will show one more button at the top of the table which uses to add a new record as “Add New Product”.
Add.cshtml is responsible for adding new records along with validations if required data does not pass.
Edit.cshtml is responsible for editing the existing record with also validations for required properties.
For deleting the data, it is already handled on Index.cshtml page using the corresponding “Delete” button. Which delete the record after user confirmation and refresh the page;
STEP 7: Implement Code in Razor Pages for performing CRUD Operations
Before adding the actual code for the CRUD operations, you have to register your dependency class with Startup.cs as follows inside the ConfigurService method.
public void ConfigureServices(IServiceCollection services)
{
services.AddMvc();
services.AddSingleton<IConfiguration>(Configuration);
services.AddTransient<IProductRepository, ProductRepository>();
}
Now Time has come to write actual CRUD operation code, so add the following code to corresponding files which will handle the CRUD operations.
Index.cshtml File
This file will contain the code to display the list of products with the corresponding button to delete and edit the data as well as one button for adding a new record.
@page
@model IndexModel
@{
ViewData["Title"] = "Product page";
}
<script>
function myFunction(x) {
$("#item_view").modal();
};
$(document).ready(function () {
$("#success-alert").hide();
$("#success-alert").fadeTo(2000, 500).slideUp(500, function () {
$("#success-alert").slideUp(500);
});
});
</script>
<div class="container">
<div class="mail-box">
<aside class="lg-side">
<div class="inbox-head">
<h3>Razor Page CRUD Operation</h3>
</div>
<a class="btn btn-primary btn-sx pull-left" style="margin-top:20px;margin-bottom: 20px;" asp-page="/Product/Add">
<i class="glyphicon glyphicon-plus"></i> Add New Product
</a>
<br />
@{
if (!string.IsNullOrEmpty(Model.Message))
{
<div class="alert alert-success" id="success-alert" style="margin-top: 40px;">
<button type="button" class="close" data-dismiss="alert">x</button>
<strong>@Model.Message ! </strong>
</div>
}
}
<div class="inbox-body" style="margin-top:20px;">
<div class="mail-option">
<table class="table table-inbox table-hover" style="border:2px solid black;">
<thead>
<tr class="unread">
<th class="col-sm-2 view-message dont-show">ID</th>
<th class="view-message col-sm-3">NAME</th>
<th class="col-sm-2">MODEL</th>
<th class="view-message text-left col-sm-2">PRICE</th>
<th class="col-sm-1">OPERATION</th>
</tr>
</thead>
<tbody>
@foreach (var item in Model.productList)
{
<tr>
<td onclick="myFunction(this)" class="view-message dont-show"><h5>@item.Id</h5></td>
<td onclick="myFunction(this)" class="view-message"><h5>@item.Name</h5></td>
<td onclick="myFunction(this)"><h4 style="margin-top: 5px;"><span class="label label-success ">@item.Model</span></h4></td>
<td onclick="myFunction(this)" class="view-message text-left"><h5>@item.Price</h5></td>
<td>
<form method="post">
<span class="btn-group pull-right" style="margin-top: 5px">
<a class="btn btn-warning btn-xs" asp-page="/Product/Edit" asp-route-id="@item.Id" style="background-color: green; height: 29px; margin-top: -1px;">
<i class="glyphicon glyphicon-edit"></i>
</a>
<button type="submit" class="btn btn-danger btn-xs" asp-page-handler="Delete" asp-route-id="@item.Id" style="height: 27px; margin-top: 0px;"
onclick="return confirm('Are you sure to delete this product?');">
<i class="glyphicon glyphicon-remove"></i>
</button>
</span>
</form>
</td>
</tr>
}
</tbody>
</table>
</div>
</div>
</aside>
</div>
</div>
Index.cshtml.cs File
This is basically code-behind class for Index.cshtml file which contains the code for getting the data and delete the selected records.
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using RazorPagesExample.Repository;
using System.Collections.Generic;
namespace RazorPagesExample.Pages.Product
{
public class IndexModel : PageModel
{
IProductRepository _productRepository;
public IndexModel(IProductRepository productRepository)
{
_productRepository = productRepository;
}
[BindProperty]
public List<Entity.Product> productList { get; set; }
[BindProperty]
public Entity.Product product { get; set; }
[TempData]
public string Message { get; set; }
public void OnGet()
{
productList = _productRepository.GetList();
}
public IActionResult OnPostDelete(int id)
{
if (id > 0)
{
var count = _productRepository.DeleteProdcut(id);
if (count > 0)
{
Message = "Product Deleted Successfully !";
return RedirectToPage("/Product/Index");
}
}
return Page();
}
}
}
Add.cshtml File
This file is responsible to create UI for adding a new product with required validation if data does not pass.
@page
@model RazorPagesExample.Pages.Product.AddModel
@{
ViewData["Title"] = "Add New Product Page";
}
<div>
<div>
<div class="modal-content">
<div class="modal-header">
<h3 class="modal-title"><strong>New Product</strong></h3>
</div>
<div class="modal-body">
<div class="row">
<div class="col-md-4 item_img">
<img src="https://www.buehler.ca/images/buttons/Product_Icon.png" class="img-responsive">
</div>
<div class="col-md-8 item_content">
<form method="post">
<h4>
<span>Please Enter New Product Details!!!</span>
</h4>
<div class="container">
<div class="row">
<div class="col-md-4">
<div class="panel">
<div class="panel-body">
<div class="col-md-12" style="margin-top:15px;">
<label asp-for="product.Name" style="font-weight:bold;"></label>
<input type='text' class='form-control' asp-for="product.Name" />
<span class="alert-danger" asp-validation-for="product.Name"></span>
</div>
<div class="col-md-12" style="margin-top:15px;">
<label asp-for="product.Model" style="font-weight:bold;"></label>
<input type='text' class='form-control' asp-for="product.Model" />
<span class="alert-danger" asp-validation-for="product.Model"></span>
</div>
<div class="col-md-12" style="margin-top:15px;">
<label asp-for="product.Price" style="font-weight:bold;"></label>
<input type='text' class='form-control' asp-for="product.Price" />
<span class="alert-danger" asp-validation-for="product.Price"></span>
</div>
<div class="text-center col-md-12" style="margin-top:35px;">
<button class="btn btn-primary btn-sx" type="submit"><i class="glyphicon glyphicon-plus"></i> Add Product</button>
<a class="btn btn-primary btn-sx" asp-page="/Product/Index"><i class="glyphicon glyphicon-backward"></i> Product List</a>
</div>
</div>
</div>
</div>
</div>
</div>
</form>
</div>
</div>
</div>
</div>
</div>
</div>
Add.cshtml.cs File
This is code-behind file for Add.cshtml. It has a responsibility to add a new record and record added successfully then redirect to Product’s home page.
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using RazorPagesExample.Repository;
namespace RazorPagesExample.Pages.Product
{
public class AddModel : PageModel
{
IProductRepository _productRepository;
public AddModel(IProductRepository productRepository)
{
_productRepository = productRepository;
}
[BindProperty]
public Entity.Product product { get; set; }
[TempData]
public string Message { get; set; }
public IActionResult OnGet()
{
return Page();
}
public IActionResult OnPost()
{
if (ModelState.IsValid)
{
var count = _productRepository.Add(product);
if (count > 0)
{
Message = "New Product Added Successfully !";
return RedirectToPage("/Product/Index");
}
}
return Page();
}
}
}
Edit.cshtml File
This is responsible for editing the existing record and validates the data.
@page "{Id:int}"
@model RazorPagesExample.Pages.Product.EditModel
@{
ViewData["Title"] = "Product Edit Page";
}
<div>
<div>
<div class="modal-content">
<div class="modal-header">
<h3 class="modal-title"><strong>Edit Product</strong></h3>
</div>
<div class="modal-body">
<div class="row">
<div class="col-md-4 item_img">
<img src="https://www.buehler.ca/images/buttons/Product_Icon.png" class="img-responsive">
</div>
<div class="col-md-8 item_content">
<form method="post">
<h4>
<span>Please Update Product Informations !!!</span>
</h4>
<div class="container">
<div class="row">
<div class="col-md-4">
<div class="panel">
<div class="panel-body">
<div class="col-md-12" style="margin-top:15px;">
<input asp-for="product.Id" type="hidden" />
<label asp-for="product.Name" style="font-weight:bold;"></label>
<input type='text' class='form-control' asp-for="product.Name" />
<span class="alert-danger" asp-validation-for="product.Name"></span>
</div>
<div class="col-md-12" style="margin-top:15px;">
<label asp-for="product.Model" style="font-weight:bold;"></label>
<input type='text' class='form-control' asp-for="product.Model" />
<span class="alert-danger" asp-validation-for="product.Model"></span>
</div>
<div class="col-md-12" style="margin-top:15px;">
<label asp-for="product.Price" style="font-weight:bold;"></label>
<input type='text' class='form-control' asp-for="product.Price" />
<span class="alert-danger" asp-validation-for="product.Price"></span>
</div>
<div class="text-center col-md-12" style="margin-top:35px;">
<button class="btn btn-primary btn-sx" type="submit">
<i class="glyphicon glyphicon-edit"></i> Edit Product
</button>
<a class="btn btn-primary btn-sx" asp-page="/Product/Index"><i class="glyphicon glyphicon-backward"></i> Product List</a>
</div>
</div>
</div>
</div>
</div>
</div>
</form>
</div>
</div>
</div>
</div>
</div>
</div>
Edit.cshtml.cs File
This file is basically code-behind for Edit.cshtml file and contains the code to edit the existing record and return to Product’s homepage if record edited successfully.
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using RazorPagesExample.Repository;
namespace RazorPagesExample.Pages.Product
{
public class EditModel : PageModel
{
IProductRepository _productRepository;
public EditModel(IProductRepository productRepository)
{
_productRepository = productRepository;
}
[BindProperty]
public Entity.Product product { get; set; }
public void OnGet(int id)
{
product = _productRepository.GetProduct(id);
}
public IActionResult OnPost()
{
var data = product;
if (ModelState.IsValid)
{
var count = _productRepository.EditProduct(data);
if (count > 0)
{
return RedirectToPage("/Product/Index");
}
}
return Page();
}
}
}
As you have noticed two things with above .cs file that we are using product dependency injection as following code shown.
IProductRepository _productRepository;
public EditModel(IProductRepository productRepository)
{
_productRepository = productRepository;
}
Apart from that to bind the data automatically with model, we are using [BindProperty] attribute with the property as follows.
[BindProperty]
public Entity.Product product { get; set; }
Finally, everything has set up and now we can run the application. To run the application just presses F5 and application is ready for you.You have to go to the following URL to perform CRUD operations for Product.
http://localhost:51068/product
To Add New Product, just click to Add New Product button and it will open Add page as follows. If you don’t fill required data and press to “Add Product” button it will show the error as well.?
You can perform Edit and Delete operations using Edit and Delete button at last in the product list view.
Conclusion
So, today we have learned how to perform CRUD operation with Asp.Net Core 2.0 Razor Page using Dapper and Repository Pattern.
I hope this post will help you. Please put your feedback using comment which helps me to improve myself for next post. If you have any doubts please ask your doubts or query in the comment section and If you like this post, please share it with your friends. Thanks
Razor Page
Dapper
Posted Comments :
Andy Posted : 7 Years Ago
Great! I really like this article! I hope the next article can shows how to use the "AutoComplete", "DateTimePicker", "DataTables" and "SelectList" features.
salvo Posted : 6 Years Ago
Great article! Is it possible to have the source code? Best regards, salvo
Rushikesh Raut Posted : 6 Years Ago
Nice Tutorial With Explanation.
Kubilay Posted : 6 Years Ago
Is it enough to change the connection string for this application to work with MYSQL database or some extra modifications needed.
Sue Singh Posted : 6 Years Ago
Thank you. Simple to execute and understand.
leviosa Posted : 5 Years Ago
hi, why dont you use generic repository?
Mike Posted : 5 Years Ago
Nice tutorial of CRUD, Razor, and Repository. However, the Modal pages do not function as Modal Popups.
Imad Posted : 4 Years Ago
Excellent Tutorial. Thanks a lot for your efforts.
Imad Posted : 4 Years Ago
Excellent Tutorial. Thanks a lot for your efforts.
Chris Lane Posted : 3 Years Ago
Where is the html element #item_view in your code to show modal? function myFunction(x) { $("#item_view").modal(); };
Apzor Posted : 7 Years Ago
Very nice tutorial! Worked like a charm! This is the only tutorial I've found that shows how to do this from A to Z. Thanks!