17 Mar

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 < User > 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)

                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());


dapper .net

dapper .net

Source Code

You can find the source code in Github

12 thoughts on “Use Dapper.NET ORM in ASP.NET MVC

  1. Hi Madhan ,

    You shared a lof valuable information and it is very useful.
    I have one question/doubt.
    Drapper is supoort to Oracle database?

  2. Then it shows me this error :
    Error 1 ‘System.Data.IDbConnection’ does not contain a definition for ‘Query’ and no extension method ‘Query’ accepting a first argument of type ‘System.Data.IDbConnection’ could be found (are you missing a using directive or an assembly reference?) D:\TestProjects\SSRSTest\SSRSTest\SSRSTest\UserRepository.cs 17 29 SSRSTest

  3. Nice, simple and clear article of dapper.
    Thanks for sharing your knowledge appreciate it.
    Please add name space of dapper in user repository
    Using dappar;

Leave a Reply

Your email address will not be published. Required fields are marked *