17 Mar

Use Dapper.NET ORM in ASP.NET MVC

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

Performance

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.

Implementation

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.

Untitled

 

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

 OutPut

dapper .net

dapper .net

Source Code

You can find the source code in Github

11 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 *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>