CRUD Operations In ASP.NET Core 2 Razor Page With Dapper And Repository Pattern

This article will demonstrate how to perform CRUD operations in Razor Page which were introduced with 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 latest 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 new window from there 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 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 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 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 inside the NuGet section which lies inside the “Dependencies” as follows in 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 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 called it “Repository” inside the main project folder and add one interface as “IProductRepository” which contains all required server side methods 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 the 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 method available for CRUD operation like “Add”, “EditProduct”, “DeleteProudct”, “GetList” and “GetProduct”.

• Add: It uses for adding 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 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 to 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 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 validations for required properties.

For deleting the data, it is already handled on Index.cshtml page using the corresponding “Delete” button, which deletes the record after user confirmation and refresh of  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 the time has come to write actual CRUD operation code, so add the following code with corresponding files which will handle the CRUD operations.

Index.cshtml File

This file will contain the code to display the list of products with corresponding button to delete and edit the data as well as one button to add 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 deleting 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 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 the responsibility to add a new record and record is 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 validating 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 home page if record is 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 been set up and now we can run the application. To run the application just press F5 and the 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 “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 operations with Asp.Net Core 2.0 Razor Page using Dapper and Repository Pattern.

I hope this post will help you. Please leave your feedback which helps me to improve for the next post. If you have any doubts please ask queries in the comment section and If you like this post, please share it with your friends. Thanks