ASP.NET MVC CRUD With Dapper (Micro ORM)

In this article, you will learn about CRUD (Create, Retrieve, Update, Delete) functionality in ASP.NET MVC with the help of DAPPER, the Micro-ORM tool.

Recently, in one project, I used Dapper. Here, I am trying to give you an idea to achieve CRUD functionality in avery easy manner.

You will get the following information in this article,

What is Dapper?
How to work with Dapper?
Dapper Query Execution Comparison
How to install Dapper?

What is Dapper?

Dapper is a light-weight ORM (Object Relational Mapping) tool, which helps the developers to map their database with POCO (Plain Old CLR Object) /MODEL. Dapper is free and open source. Dapper was developed by Sam Saffron, Marc Gravell, and Nick Craver.

If you are willing to write the SQL Query and take the taste of ORM, then Dapper is a good choice for you. Dapper makes it very easy to handle all kinds of database operations.

Dapper Resources

http://dapper-tutorial.net/dapper
https://github.com/StackExchange/Dapper
https://en.wikipedia.org/wiki/Dapper_ORM

How to work with Dapper or how to implement / run query in Dapper?

To start with Dapper, you have to perform the following actions in your code.

Create an object of IDbConnection.
Attach the query to perform.
Pass Query as a parameter to execute method.

Dapper Query Execution Comparison

Please, visit this link for more information - https://github.com/StackExchange/Dapper

You can install Dapper through NuGet, a free, open source package manager specifically designed for Microsoft development platform.

How to install Dapper?

OR

Your Dapper is installed successfully.

Step by Step Asp.Net MVC CRUD functionality with DAPPER

Before start working with Visual Studio and Coding part, first let's have a look on the table structure, which we are going to work on further.

Table Structure

USE [MBKTest]
GO
/****** Object: Table [dbo].[tblFriends] Script Date: 27-Jun-17,Tue 3:44:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblFriends](
[FriendID] [int] IDENTITY(1,1) NOT NULL,
[FriendName] [varchar](50) NULL,
[City] [varchar](25) NULL,
[PhoneNumber] [varchar](15) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Sample Records View of Table tblFriends.

STEP 1 - FILE --> NEW --> Project.

STEP 2 - Select the following.

Template : Empty
Select MVC CheckBox.

After creation of project, you can see that the default project structure will look like the following (Solution Explorer).

STEP 3 Installation of Dapper

Install the Dapper through NuGet: All the installation steps are given above.

STEP 4 Creating a Friend Model

Select Solution Explorer and right click on Models.

Friend Model (Friend.cs) Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace DapperMvc.Models
{
public class Friend
{
public int FriendID { get; set; }
public string FriendName { get; set; }
public string City { get; set; }
public string PhoneNumber { get; set; }
}
}

STEP 5 Creating a Friend Controller

Default Code of Controller FriendController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace DapperMvc.Controllers
{
public class FriendController : Controller
{
// GET: Friend
public ActionResult Index()
{
return View();
}
// GET: Friend/Details/5
public ActionResult Details(int id)
{
return View();
}
// GET: Friend/Create
public ActionResult Create()
{
return View();
}
// POST: Friend/Create
[HttpPost]
public ActionResult Create(FormCollection collection)
{
try
{
// TODO: Add insert logic here
return RedirectToAction("Index");
}
catch
{
return View();
}
}
// GET: Friend/Edit/5
public ActionResult Edit(int id)
{
return View();
}
// POST: Friend/Edit/5
[HttpPost]
public ActionResult Edit(int id, FormCollection collection)
{
try
{
// TODO: Add update logic here
return RedirectToAction("Index");
}
catch
{
return View();
}
}
// GET: Friend/Delete/5
public ActionResult Delete(int id)
{
return View();
}
// POST: Friend/Delete/5
[HttpPost]
public ActionResult Delete(int id, FormCollection collection)
{
try
{
// TODO: Add delete logic here
return RedirectToAction("Index");
}
catch
{
return View();
}
}
}
}

You can see above that the following ActionMethods are created by default.

SR. NO.	ACTION METHOD NAME	ACTION METHOD DESCRIPTION
1.	Index	List of all friends.
2.	Details	Detail view of friend.
3.	Create	Create a new friend.
4.	Edit	Edit / Modify / Change existing selected friend detail.
5.	Delete	Erase / Delete friend detail.

Now, let's understand each ActionMethod of FriendController before starting the coding of Index,Details, Create,Edit, Delete methods.

STEP 6 Use Namespace on the top of the HomeController.cs

using DapperMvc.Models;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Dapper;

STEP 7 Implementing Index ActionMethod

Index

// GET: Friend
public ActionResult Index()
{
List<Friend> FriendList = new List<Friend>();
using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["friendConnection"].ConnectionString))
{
FriendList = db.Query<Friend>("Select * From tblFriends").ToList();
}
return View(FriendList);
}

STEP 8 Creating View for Index ActionMethod

Note - Generally, we create View with the same name as of Action Method.

Index.cshtml Code

Auto-Generated code for List (Index), this will display all the records of Friends.

@model IEnumerable<DapperMvc.Models.Friend>
@{
ViewBag.Title = "Index";
}
<h2>Index</h2>
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.FriendName)
</th>
<th>
@Html.DisplayNameFor(model => model.City)
</th>
<th>
@Html.DisplayNameFor(model => model.PhoneNumber)
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.FriendName)
</td>
<td>
@Html.DisplayFor(modelItem => item.City)
</td>
<td>
@Html.DisplayFor(modelItem => item.PhoneNumber)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id=item.FriendID }) |
@Html.ActionLink("Details", "Details", new { id=item.FriendID }) |
@Html.ActionLink("Delete", "Delete", new { id=item.FriendID })
</td>
</tr>
}
</table>

Look at the output of Index of Friend controller

STEP 9 Set the RouteConfig.cs file

Why do we need to configure RouteConfig.Cs?

By default RouteConfig.cs file called controller = HOME and action = INDEX now we want our FRIEND controller’s INDEX action method to run by default that’s why we change the RouteConfig.cs file.

Default RouteConfig.cs file view

Default RouteConfig.cs file code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Routing;
namespace DapperMvc
{
public class RouteConfig
{
public static void RegisterRoutes(RouteCollection routes)
{
routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
routes.MapRoute(
name: "Default",
url: "{controller}/{action}/{id}",
defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional }
);
}
}
}

Now change your RouteConfig.cs as follows:

Changed RouteConfig.cs file Image

Changed RouteConfig.cs file Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Routing;
namespace DapperMvc
{
public class RouteConfig
{
public static void RegisterRoutes(RouteCollection routes)
{
routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
routes.MapRoute(
name: "Default",
url: "{controller}/{action}/{id}",
defaults: new { controller = "Friend", action = "Index", id = UrlParameter.Optional }
);
}
}
}

STEP 10 Run the Project to check Index

Press F5 to check the Index actionmethod of friend. This will run by default because we had set the FRIEND Controller’s INDEX ActtionMethod default in ROUTECONFIG.CS in step number 9.

Our Index Method of Friend Controller Output

Even you can write this also on Address Bar of browser: localhost:54501/Friend/Index

STEP 11 Implementing Details ActionMethod

Details

ActionMethod Code,

// GET: Friend/Details/5
public ActionResult Details(int id)
{
Friend _friend = new Friend();
using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["friendConnection"].ConnectionString))
{
_friend = db.Query<Friend>("Select * From tblFriends " +
"WHERE FriendID =" + id, new { id }).SingleOrDefault();
}
return View(_friend);
}

NOTE

While we want to run this Action Method on browser sample URL given on the top of Details Action Method.

Example localhost:54501/Friend/Create

( // GET: Friend/Details/5)

STEP 12 Creating View for Details ActionMethod

Right click on ActionMethod area of Details and Select ADD VIEW, As we did in STEP 8.

After click on ADD button you will see solution explorer Detail.cshtml file has been created

Details.cshtml Code

@model DapperMvc.Models.Friend
@{
ViewBag.Title = "Details";
}
<h2>Details</h2>
<div>
<h4>Friend</h4>
<hr />
<dl class="dl-horizontal">
<dt>
@Html.DisplayNameFor(model => model.FriendName)
</dt>
<dd>
@Html.DisplayFor(model => model.FriendName)
</dd>
<dt>
@Html.DisplayNameFor(model => model.City)
</dt>
<dd>
@Html.DisplayFor(model => model.City)
</dd>
<dt>
@Html.DisplayNameFor(model => model.PhoneNumber)
</dt>
<dd>
@Html.DisplayFor(model => model.PhoneNumber)
</dd>
</dl>
</div>
<p>
@Html.ActionLink("Edit", "Edit", new { id = Model.FriendID }) |
@Html.ActionLink("Back to List", "Index")
</p>

STEP 13 Run the Project to check Details.

You can execute details method directly or through INDEX method.

By default Index will view on browser you can select details of any friend or directly type URL.

STEP 14 To create a new friend entry

Why Create ActionMethod having two method?

First method is for GET process which will run when we call the method.

Second method is for POST process which will run when we submit the entry to the server.

Create ActionMethod Code

// GET: Friend/Create
public ActionResult Create()
{
return View();
}
// POST: Friend/Create
[HttpPost]
public ActionResult Create(Friend _friend)
{
using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["friendConnection"].ConnectionString))
{
string sqlQuery = "Insert Into tblFriends (FriendName,City,PhoneNumber) Values("+ _friend.FriendName+","+ _friend.City+","+ _friend.PhoneNumber + ")";
int rowsAffected = db.Execute(sqlQuery);
}
return RedirectToAction("Index");
}

STEP 15 Creating View for Create ActionMethod

Right click on ActionMethod area of Create and Select ADD VIEW, As we did in STEP 8.

Create.cshtml Code

@model DapperMvc.Models.Friend
@{
ViewBag.Title = "Create";
}
<h2>Create</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-horizontal">
<h4>Friend</h4>
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
<div class="form-group">
@Html.LabelFor(model => model.FriendName, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.FriendName, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.FriendName, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.PhoneNumber, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.PhoneNumber, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.PhoneNumber, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Create" class="btn btn-default" />
</div>
</div>
</div>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>

STEP 16 Run the Project to check Create.

You can execute create method directly or through INDEX method.

Example

localhost:54501/Friend/Create

By default Index will view on browser you can select Create option or directly type URL given above.

I had filled entry form as given below,

Index Page showing my new entry.

STEP 17 To create a Change/Edit friend entry

Why EDIT ActionMethod having two method?

First method for GET process which will run when we call the method.

Second method for POST process which will run when we submit the entry to the server.

Here we are changing MAHESH THANVI to MOHAN THANVI

EDIT ActionMethod Code

// GET: Friend/Edit/5
public ActionResult Edit(int id)
{
Friend _friend = new Friend();
using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["friendConnection"].ConnectionString))
{
_friend = db.Query<Friend>("Select * From tblFriends " +
"WHERE FriendID =" + id, new { id }).SingleOrDefault();
}
return View(_friend);
}
// POST: Friend/Edit/5
[HttpPost]
public ActionResult Edit(Friend _friend)
{
using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["friendConnection"].ConnectionString))
{
string sqlQuery = "update tblFriends set FriendName='"+_friend.FriendName+ "',City='"+_friend.City+"',PhoneNumber='"+_friend.PhoneNumber + "' where friendid="+_friend.FriendID;
int rowsAffected = db.Execute(sqlQuery);
}
return RedirectToAction("Index");
}

STEP 18 Creating View for EDIT ActionMethod

Right click on ActionMethod area of EDIT and Select ADD VIEW, As we did in STEP 8.

Edit.cshtml Code

@model DapperMvc.Models.Friend
@{
ViewBag.Title = "Edit";
}
<h2>Edit</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-horizontal">
<h4>Friend</h4>
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
@Html.HiddenFor(model => model.FriendID)
<div class="form-group">
@Html.LabelFor(model => model.FriendName, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.FriendName, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.FriendName, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.PhoneNumber, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.PhoneNumber, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.PhoneNumber, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Save" class="btn btn-default" />
</div>
</div>
</div>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>

STEP 19 Run the Project to check EDIT.

You can execute EDIT method directly or through INDEX method.

Example

localhost:54501/Friend/Edit/5

By default Index will view on browser you can select EDIT option or directly type URL given above.

Change entry to,

STEP 20 To create a Erase/Delete friend entry

Why DELETE ActionMethod having two method?

First method for GET process which will run when we call the method.

Second method for POST process which will run when we submit delete request to the server.

Here we want to delete MOHAN THANVI Record

DELETE ActionMethod Code

// GET: Friend/Delete/5
public ActionResult Delete(int id)
{
Friend _friend = new Friend();
using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["friendConnection"].ConnectionString))
{
_friend = db.Query<Friend>("Select * From tblFriends " +
"WHERE FriendID =" + id, new { id }).SingleOrDefault();
}
return View(_friend);
}
// POST: Friend/Delete/5
[HttpPost]
public ActionResult Delete(int id, FormCollection collection)
{
using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["friendConnection"].ConnectionString))
{
string sqlQuery = "Delete From tblFriends WHERE FriendID = " +id;
int rowsAffected = db.Execute(sqlQuery);
}
return RedirectToAction("Index");
}

STEP 21 Creating View for DELETE ActionMethod

Right click on ActionMethod area of DELETE and Select ADD VIEW, As we did in STEP 8.

Delete.cshtml Code

@model DapperMvc.Models.Friend
@{
ViewBag.Title = "Delete";
}
<h2>Delete</h2>
<h3>Are you sure you want to delete this?</h3>
<div>
<h4>Friend</h4>
<hr />
<dl class="dl-horizontal">
<dt>
@Html.DisplayNameFor(model => model.FriendName)
</dt>
<dd>
@Html.DisplayFor(model => model.FriendName)
</dd>
<dt>
@Html.DisplayNameFor(model => model.City)
</dt>
<dd>
@Html.DisplayFor(model => model.City)
</dd>
<dt>
@Html.DisplayNameFor(model => model.PhoneNumber)
</dt>
<dd>
@Html.DisplayFor(model => model.PhoneNumber)
</dd>
</dl>
@using (Html.BeginForm()) {
@Html.AntiForgeryToken()
<div class="form-actions no-color">
<input type="submit" value="Delete" class="btn btn-default" /> |
@Html.ActionLink("Back to List", "Index")
</div>
}
</div>

STEP 22 Run the Project to check DELETE.

You can execute DELETE method directly or through INDEX method.

Example

localhost:54501/Friend/Delete/5

By default, Index will display on browser. You can select DELETE option or directly type the above URL.

You can see that MOHAN THANVI entry is deleted successfully.

I have zipped the source code and attached with this article.

How to use Zipped Source Code?

Download the zip file.
Overwrite the following folders in your Solution path.

I have given four folders:

App_Start
Controllers
Models
Views

13 Comments

Join the conversation! Your thoughts help the community grow.

Toan Tran4y ago
Thank you very much
Poonam Pawale5y ago
String sqlQuery = "Insert Into tblFriendss(FriendName,City,PhoneNumber) Values('" + _friend.FriendName + "','" + _friend.City + "','" + _friend.PhoneNumber + "')";
Sunil Patil7y ago
Sir do you have master detail crud operation pl help us we are not able to work on it...
Johan Palsson7y ago
Nice intro, have only two remarks, 1. You should explain how to create the connectionsstring, 2. The code for CREATE is missing quotes enclosing the string fields.
Ashutosh Kushawaha7y ago
Nice article
Gaurav Joshi8y ago
Nice article ... thank you
Nicholas Cokas8y ago
How should my startup.cs look like? I am having trouble getting the dbcontext.
Tuncay Cengiz8y ago
Nice intro both mvs and dapper. Its simple and understandable. Thanks
Sundaram Subramanian9y ago
Sir nice intro. Thanks. looking forward
- Sundaram Subramanian9y ago
  going great sir. there. ?!
- Manoj Kalla9y ago
  Thank You... Sundar sir ji... How are you?
Prabu Elavarasan9y ago
Sir: Appreciate your effort on creating such a nice article.
- Manoj Kalla9y ago
  Thank you.... Prabhu E sir