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.

What is a Dapper?

Dapper is a simple object mapper for .NET.

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

It provides 4 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.

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

Output

Source Code

You can find the source code in Github.

The post Use Dapper.NET ORM in ASP.NET MVC appeared first on Venkat Baggu Blog.