In this post we will learn how to use Use Dapper.NET ORM in ASP.NET MVC.

Whats is a Dapper?

Dapper  is a simple object mapper for .NET

Dapper is a single file you can drop in to your project that will extend your IDbConnection interface.

It provides 3 helpers:

  1. Execute a query and map the results to a strongly typed List
  2. Execute a query and map it to a list of dynamic objects
  3. Execute a Command that returns no results
  4. Execute a Command multiple times


A key feature of Dapper is performance. The following metrics show how long it takes to execute 500 SELECT statements against a DB and map the data returned to objects.


Install Dapper using Nuget Package Manager

PM> Install-Package Dapper
  1.  Create a project in ASP.NET MVC
  2. Add a folder named Dapper inside it.

Create User and Address classes

public class Address{ public int AddressID { get; set; } public int UserID { get; set; } public string AddressType { get; set; } public string StreetAddress { get; set; } public string City { get; set; } public string State { get; set; } public string ZipCode { get; set; }}public class User{ public User() { this.Address = new List<Address>(); } public int UserID { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public string Email { get; set; } public List<Address> Address { get; set; }}

Now Create IUserRepository.cs interface  and UserRepository.cs classes for data access.

public interface IUserRepository{ List GetAll(); User Find(int id); User Add(User user); User Update(User user); void Remove(int id); User GetUserInformatiom(int id);}
public class UserRepository : IUserRepository { private IDbConnection _db = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString); public List<User> GetAll() { return this._db.Query<User>("SELECT * FROM Users").ToList(); } public User Find(int id) { return this._db.Query<User>("SELECT * FROM Users WHERE UserID = @UserID", new { id }).SingleOrDefault(); } public User Add(User user) { var sqlQuery = "INSERT INTO Users (FirstName, LastName, Email) VALUES(@FirstName, @LastName, @Email); " + "SELECT CAST(SCOPE_IDENTITY() as int)"; var userId = this._db.Query<int>(sqlQuery, user).Single(); user.UserID = userId; return user; } public User Update(User user) { var sqlQuery = "UPDATE Users " + "SET FirstName = @FirstName, " + " LastName= @LastName, " + " Email = @Email " + "WHERE UserID = @UserID"; this._db.Execute(sqlQuery, user); return user; } public void Remove(int id) { throw new NotImplementedException(); } public User GetUserInformatiom(int id) { using (var multipleResults = this._db.QueryMultiple("GetUserByID", new { Id = id }, commandType: CommandType.StoredProcedure)) { var user = multipleResults.Read<User>().SingleOrDefault(); var addresses = multipleResults.Read<Address>().ToList(); if (user != null && addresses != null) { user.Address.AddRange(addresses); } return user; } } }

Now use the above repository in the HomeController.cs

Create an instance for UserRepository class

private IUserRepository _repository = new UserRepository();

Get All User

public ActionResult Index(){return View(_repository.GetAll());}