CRUD OPERATION WITH .NET CORE WEB APPLICATION USING DAPPER

In this article, we are going to learn how to perform CRUD operation with .Net Core Web Application Using Dapper ORM in step by step way.

Also, you will learn how to create good project architecture in .Net Core such that it will help in the maintenance of your project.

Topic

1. Create Database and Tables
2. Create .Net Core Web Application
3. Adding Connection string
4. Adding Layer Concrete [Class library ]
5. Adding Layer Interface [Class library ]
6. Adding Layer Models [Class library ]
7. Installing Dapper ORM package along with that we need to add SqlClient and ConfigurationExtensions package in MVCWebApp.Concrete [Class Library]
8. Creating Product Model
9. Adding New Product Interface
10. Adding New Product Concrete class
11. Adding Project reference
12. Reading Connection string in Product Concrete class
13. Declaring Method in IProduct Interface in [MVCWebApp.Interface Class Library]
14. Product Concrete class implement IProduct Interface in [MVCWebApp.Concrete Class Library]
15. Setting Dependency Injection in Startup class
16. Adding Product Controller
17. Adding Action Methods in Product Controller
18. Adding Views related to all Action Methods
19. Running Application
20. Finally Completed

Let's Begin

Step 1: Create Database and Tables

In this step, we are going to create new Database with Name "CoreDB".

Adding new table in database with name "Product".

After creating Product table next step we are going to see stored procedure used for CRUD operation.

After completing having a view of stored procedure next step we are going to create .Net Core web application.

Step 2: Create .Net Core Web Application

Now let's create .Net Core Web Application Open Visual Studio IDE from start page Click on New Project link.

After click on New Project link it will open a new dialog with Name "New Project" inside that from left pane choose templates inside that ➜ choose Visual C# ➜ inside that choose .Net Core template then in Middle you pane you will see .Net Core project templates , from templates that choose "ASP.NET Core Web Application (.Net Core)" project templates.

After choosing project template next we are going to name the project as "MVCWebApp" and finally click on ok button to create a project, but it will pop up another dialog with name "New ASP.NET Core Web Application (.Net Core)".

Inside this dialog, we are going to choose "Web application" project template for create "Web application" and click on the ok button for creating a project.

Below is complete project view which is newly created.

Step 3: Adding Connection string and Service

Now after creating a project we are going to add a connection string to appsetting.json file and after that, we are going to add new service in startup class for reading connection in the entire application.

Added connection string in appsetting.json file

After adding Service next step is to add new layer [Class library] which will only communicate with the database.

Step 4: Adding Layer Concrete [Class library] (MVCWebApp.Concrete)

Now in this part, we are going to add a new Class library with name "MVCWebApp.Concrete".

This Class library will have all application Class which will communicate with the database.

For adding Class library just right click on a solution then choose Add ➜ inside that select New Project.

After clicking on New Project a new dialog will pop up with name "Add New Project" in that dialog we are going to choose "Class Library (.Net Core)" template and name it as "MVCWebApp.Concrete" and click on ok button.

Step 5: Adding Layer Interface [Class library] (MVCWebApp. Interface)

Now in this part, we are going to add a new Class library with name "MVCWebApp.Interface".

This Class library will have all application Interface.

For adding Class library just right click on a solution then choose Add à inside that select New Project.

After clicking on New Project a new dialog will pop up with name "Add New Project" in that dialog we are going to choose "Class Library (.Net Core)" template and name it as "MVCWebApp. Interface" and click on ok button.

Note:- Use the same process which we have a use for adding "MVCWebApp.Concrete".

Step 6: Adding Layer Models [Class library] (MVCWebApp. Models)

Now in this part, we are going to add a new Class library with name "MVCWebApp.Models".

This Class library will have all application Models.

For adding Class library just right click on a solution then choose Add ➜ inside that select New Project.

After clicking on New Project a new dialog will pop up with name "Add New Project" in that dialog we are going to choose "Class Library (.Net Core)" template and name it as "MVCWebApp.Models" and click on ok button.

Note:- Use the same process which we have a use for adding "MVCWebApp.Concrete".

After completing with the adding class library now we are going to Installing Dapper ORM in MVCWebApp.Concrete"

Step 7: Installing Dapper ORM package along with that we need to add SqlClient and ConfigurationExtensions package in MVCWebApp.Concrete [Class Library]

For adding packages into MVCWebApp.Concrete [Class Library] just open project.json file and add below dependencies into the project.json file and save file it will automatically download packages in background .

"Microsoft.Extensions.Options.ConfigurationExtensions": "1.0.0",
  "System.Data.SqlClient": "4.1.0",
  "Dapper": "1.50.2"

The ConfigurationExtensions reference is to read appsetting.json file values.

The SqlClient reference is to interact with SQL server.

The Dapper ORM reference it to perform CRUD operation.

After saving file now just have look at Reference of the project it adds packages wow.

After adding dependencies to project next

Step 8: Adding Product Model in MVCWebApp.Models Class library

Now we are going to add a New Model with name Product in MVCWebApp.Models [Class library]

For adding model just right click on MVCWebApp.Models [Class library] then select Add ➜ inside that select Class a Add New Item dialog will pop up with default class selected , then we are going to name class as ProductModel and click on Add button.

After adding class we are going to add validation rule in Model for doing that we required reference of ComponentModel.Annotations.

For adding the package into MVCWebApp.Models [Class Library] just open project.json file and add below dependencies into the project.json file and save file it will automatically download package in the background.

"System.ComponentModel.Annotations": "4.1.0"

You can do this same stuff in another way.

Just right click on MVCWebApp.Models [Class Library] then select Manage Nuget Packages.

A new NuGet dialogs will pop up in that choose browse tab and in search box type "System.ComponentModel.Annotations" and click on install button to add this package.

After adding Reference of "System.ComponentModel.Annotations"

Next step is to Add Properties to ProductModel class and also validation rule.

After adding Model next we are going to add new Interface in MVCWebApp.Interface [Class library]

Step 9: Adding IProduct Interface in MVCWebApp. Interface Class library

For adding model just right click on MVCWebApp.Interface [Class library] then select Add ➜ inside that select "New Item" an "Add New Item" dialog will pop up inside that select Interface , then we are going to name Interface as IProduct and click on Add button.

Step 10: Adding ProductConcrete class in MVCWebApp.Concrete Class library

For adding model just right click on MVCWebApp.Concrete [Class library] then select Add ➜ inside that select Class and Add New Item dialog will pop up with default class selected , then we are going to name class as ProductConcrete and click on Add button.

After completing adding Classes and interface in next we are going to add reference of the project to each other such that Concrete can access Model and interface etc...

Step 11: Adding Project references

Now we have created all Class library project but we cannot access one or others class from this Class library project because they do not reference each other.

Adding Reference to MVCWebApp project

For adding Reference to MVCWebApp project, just right click on MVCWebApp project references then select "Add Reference"

In Main project [MVCWebApp] we need references of all class library that why I have checked all checkbox and finally going to click on OK button to add reference.

Adding Reference to MVCWebApp .Concrete project

For adding Reference to MVCWebApp .Concrete project, just right click on MVCWebApp .Concrete project references then select "Add Reference".

In MVCWebApp .Concrete project we need references of Interface and Models why I have checked MVCWebApp.Interface and MVCWebApp.Models checkbox and finally going to click on OK button to add reference.

Adding Reference to MVCWebApp .Interface project

For adding Reference to MVCWebApp.Interface project, just right click on MVCWebApp .Interface project references then select "Add Reference".

In MVCWebApp.Interface project we need references of Models that's why I have checked MVCWebApp.Models checkbox and finally going to click on OK button to add reference.

Step 12: Reading Connection string in Product Concrete class

For reading connection string from appsettting.json file, we need to add a class in MVCWebApp.Model [Class Library] with name "ReadConfig.cs".

For adding model just right click on MVCWebApp.Models [Class library] then select Add ➜ inside that select Class and Add New Item dialog will pop up with default class selected , then we are going to name class as ReadConfig and click on Add button.

Adding Properties in ReadConfig class

Note: - the name of property must be same as the JSON data which we are reading.

After this, we are going to add service in startup class.

Note: - Data is the Main section which we want to get that's why I have added Data inside this section there is connection string value.

Now we have setup Service which we can access in Class library for reading connection string.

Next step we are going Declare Method in IProduct Interface for CRUD operation.

Step 13: Declaring Method in IProduct Interface in [MVCWebApp.Interface Class Library]

In Interface we are going to declare CRUD method

[GetSingleProduct, GetProductList, InsertProduct, DeleteProduct, UpdateProduct]

Step 14: Product Concrete class implement IProduct Interface in [MVCWebApp.Concrete Class Library]

In this part, ProductConcrete class will implement IProduct Interface means all method declared inside IProduct will implement by ProductConcrete class.

In below snapshot, you can see we have implemented all method of IProduct Interface.

Along with that the way we are reading Connection string can also be seen here.

Reading and Passing value to connection string to SQL connection

Code Snippet of ProductConcrete Class

In below code snippet we have implement IProduct interface and for doing CRUD operation I have used dapper ORM.

using Dapper;
using Microsoft.Extensions.Options;
using MVCWebApp.Interface;
using MVCWebApp.Models;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

namespace MVCWebApp.Concrete
{
    public class ProductConcrete : IProduct
    {
        IOptions _ConnectionString;
        public ProductConcrete(IOptions ConnectionString)
        {
            _ConnectionString = ConnectionString;
        }

        public int DeleteProduct(int ProductID)
        {
            using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
            {
                con.Open();
                SqlTransaction sqltrans = con.BeginTransaction();
                var param = new DynamicParameters();
                param.Add("@ProductID", ProductID);
                var result = con.Execute("sprocProductTBDeleteSingleItem", param, sqltrans, 0, System.Data.CommandType.StoredProcedure);

                if (result > 0)
                {
                    sqltrans.Commit();
                }
                else
                {
                    sqltrans.Rollback();
                }
                return result;
            }
        }

        public List GetProductList()
        {
            using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
            {
                return con.Query("sprocProductTBSelectList", null, null, true, 0, System.Data.CommandType.StoredProcedure).ToList();
            }
        }

        public ProductModel GetSingleProduct(int ProductID)
        {
            using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
            {
                var param = new DynamicParameters();
                param.Add("@ProductID", ProductID);
                return con.Query("sprocProductTBSelectSingleItem", param, null, true, 0, System.Data.CommandType.StoredProcedure).SingleOrDefault();
            }
        }

        public int InsertProduct(ProductModel productmodel)
        {
            using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
            {
                con.Open();
                SqlTransaction sqltrans = con.BeginTransaction();
                var param = new DynamicParameters();
                param.Add("@ProductID", productmodel.ProductID);
                param.Add("@ProductName", productmodel.ProductName);
                param.Add("@Price", productmodel.Price);
                param.Add("@QuantityperUnit", productmodel.QuantityperUnit);
                param.Add("@ProductDesc", productmodel.ProductDesc);
                param.Add("@Status", "A");
                var result = con.Execute("sprocProductTBInsertUpdateSingleItem", param, sqltrans, 0, System.Data.CommandType.StoredProcedure);

                if (result > 0)
                {
                    sqltrans.Commit();
                }
                else
                {
                    sqltrans.Rollback();
                }
                return result;
            }
        }

        public int UpdateProduct(ProductModel productmodel)
        {
            using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
            {
                con.Open();
                SqlTransaction sqltrans = con.BeginTransaction();
                var param = new DynamicParameters();
                param.Add("@ProductID", productmodel.ProductID);
                param.Add("@ProductName", productmodel.ProductName);
                param.Add("@Price", productmodel.Price);
                param.Add("@QuantityperUnit", productmodel.QuantityperUnit);
                param.Add("@ProductDesc", productmodel.ProductDesc);
                param.Add("@Status", productmodel.Status);
                var result = con.Execute("sprocProductTBInsertUpdateSingleItem", param, sqltrans, 0, System.Data.CommandType.StoredProcedure);

                if (result > 0)
                {
                    sqltrans.Commit();
                }
                else
                {
                    sqltrans.Rollback();
                }
                return result;
            }
        }
    }
}

Step 15: Setting Dependency Injection in Startup class

Now in this step we are going add new service in startup class in which we are going to add a Singleton instance, in this, we are going to resolve IProduct interface with ProductConcrete Concrete class.

Whenever we are going to use IProduct interface at that time we are going to get an instance of ProductConcrete class.

Code snippet

services.AddSingleton();

After completing with adding Service next we are going Add this main component of MVC is Controller.

Step 16: Adding Product Controller

For adding controller just right click on Controller folder then select Add ➜ inside that select New Item.

Note:- If you compare this with MVC 4 or MVC 5 you will see different the same process of adding a controller in MVC 4 and 5 we have controller option directly to choose here we do not have it.

After selecting New Item a new dialog of Add New Item will pop up.

Inside that just choose "MVC Controller Class" then name Controller as ProductController and Click on Add button to create Controller.

After we have clicked on Add button it has created ProductController in Controller folder as shown in below view.

Step 17: Adding Action Methods in Product Controller

In this, we are going to add Action Method in Product Controller which we have newly created to perform CRUD operation.

1. Index - for display list of product
2. Create - for creating New product
3. Edit - for updating product information
4. Delete - for Deleting product
5. Details – For showing details of single product

Below snapshot shows how an instance of ProductConcrete is injected when we call Product controller.

Code Snippet of Product Controller

Complete Code snippet of Product Controller.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using MVCWebApp.Interface;
using MVCWebApp.Models;

// For more information on enabling MVC for empty projects, visit http://go.microsoft.com/fwlink/?LinkID=397860

namespace MVCWebApp.Controllers
{
    public class ProductController : Controller
    {
        IProduct _IProduct;
        public ProductController(IProduct iproduct)
        {
            _IProduct = iproduct;
        }

        public IActionResult Index()
        {
            var ProductList = _IProduct.GetProductList();
            return View(ProductList);
        }

        public IActionResult Details(int id)
        {
            var Product = _IProduct.GetSingleProduct(id);
            return View(Product);
        }

        public IActionResult Create()
        {
            return View();
        }

        [HttpPost]
        public IActionResult Create(ProductModel productmodel)
        {
            try
            {
                // TODO: Add insert logic here

                if (ModelState.IsValid)
                {
                    _IProduct.InsertProduct(productmodel);
                    return RedirectToAction("Index");
                }

                return View(productmodel);
            }
            catch
            {
                return View();
            }
        }

        public IActionResult Edit(int id)
        {
            var Product = _IProduct.GetSingleProduct(id);
            return View(Product);
        }

        [HttpPost]
        public IActionResult Edit(int id, ProductModel productmodel)
        {
            try
            {
                // TODO: Add update logic here
                _IProduct.UpdateProduct(productmodel);
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        public IActionResult Delete(int id)
        {
            var Product = _IProduct.GetSingleProduct(id);
            return View(Product);
        }

        [HttpPost]
        public IActionResult Delete(ProductModel productmodel)
        {
            try
            {
                // TODO: Add delete logic here
                _IProduct.DeleteProduct(productmodel.ProductID);
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }
    }
}

In next step, we are going to add Views related to action methods which we have created.

Step 18: Adding Views related to all Action Methods

In this step, we are going to add Views related to all action methods to perform CRUD operation.

Adding View

In .Net Core for adding View just Right click on the Views folder, and then Add ➜ New Folder and name the folder "Product"

After adding Product folder now we are going to add View inside this folder.

For adding View Right click on the Product folder which we have added and then select Add ➜ inside that select New Item then a new dialog with Name of Add New Item will pop up.

From Add New Item dialogs just choose "MVC View Page" for adding View, then next step is to give name to View, the View name must be the name same as Action method name, we are going name it as "Index" ["Index.cshtml"] and click on Add button to add View.

In same way we are going to add remaining view one by one.

Now after adding view for taking Input from user we have created 2 Views using new tag helpers

1. Create
2. Edit

For Taking Input Views we have Used new Tag Helper in MVC6

1. 1. Label
2. 2. Textbox
3. 3. Validation
4. 4. Action Link

<a asp-area="" asp-controller="Product" asp-action="Index">Back to Home</a>

Note: - One Cool thing you will also get while writing new tag helper is intelligence and another good thing you will get with new tag helper is intelligence for writing bootstrap css.

Below is snapshot of Create View

Note:- Article will be too lengthy if I show code snippet of all Views which I have created, but you can download entire project in why you can go through all code.

Step 19: Running Application

This is the final step in which all the part which we have created till now will work together.

Let's run the application and see how entire process works with screenshot such that you can have a clear idea of what we have created.

Create Product

All Product Details

Edit / Update Product Detail

Product Detail

Delete Product

Product Table after inserting data

Step 20: Finally Completed

Finally, we saw entire process of how to create .Net Core Web Application Using Dapper and perform CRUD operation with it in step by step way I hope you have enjoyed it.

And I also hope the person new to .Net Core has got good knowledge about .Net Core after reading this article.

Finally, if you like the article please share it let other learn from it.