My Your Code Looks Dapper

In this post I will discuss the benefits of using Dapper micro ORM

Posted on September 21, 2015 7:00PM

There is a forest full of ORM solutions out there! Which one do you choose? It all depends on your situation and depending on your requirements you don’t have to limit yourself to just one. Using the history of Microsoft ORMs in .NET you first had ADO.NET (SqlReader) which is not really an ORM but a lightning fast way to get data out of your database. Then Microsoft came out with Linq to SQL which is essentially a wrapper for ADO.NET followed by Entity Framework. Both require only the System.Data (ADO.NET) and System.Data.Linq (Linq to SQL) objects. Both of these frameworks are incredibly fast not only in performance but “warm up” time. Entity Framework is a very powerful ORM and fairly fast after it’s warm up time but does usually have a slow start up if your site does not get a lot of traffic.

Dapper is a micro-ORM developed by StackExchange and is open source on GitHub. Dapper is very simple to implement and easy to use. Dapper also has proven lightning fast performance. To implement Dapper, you simply include one C# file into your project, set up your connection then go! Dapper simply requires the System.Data object to run. I display everything you will need below to create and run a project using Dapper within MVC to display a table of data on a page.  I have also included the source files for this project as well. I will be using a SQLite database for simplicity purposes.

web.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <add key="webpages:Version" value="3.0.0.0"/>
    <add key="webpages:Enabled" value="false"/>
    <add key="ClientValidationEnabled" value="true"/>
    <add key="UnobtrusiveJavaScriptEnabled" value="true"/>
  </appSettings>
  <connectionStrings>
    <add name="DBConnection" connectionString="Data Source=|DataDirectory|DapperDemo.sqlite;Version=3;"/>
  </connectionStrings>
  <system.web>
    <compilation debug="true" targetFramework="4.5"/>
    <httpRuntime targetFramework="4.5"/>
  </system.web>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35"/>
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0"/>
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35"/>
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0"/>
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35"/>
        <bindingRedirect oldVersion="1.0.0.0-5.2.3.0" newVersion="5.2.3.0"/>
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

ConnectionFactory.cs

using System.Configuration;
using System.Data.SQLite;

namespace DapperBlog.Models
{
    public class ConnectionFactory
    {
        public static SQLiteConnection GetOpenConnection()
        {
            var connection = new SQLiteConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString);
            connection.Open();
            return connection;
        }
    }
}

UserProfile.cs

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using Dapper;

namespace DapperBlog.Models
{
    public class UserProfile
    {
        public int UserId { get; set; }

        [Required]
        [Display(Name = "First Name")]
        public string FirstName { get; set; }

        [Required]
        [Display(Name = "Last Name")]
        public string LastName { get; set; }

        [Required(ErrorMessage="Please enter an email address")]
        [DataType(DataType.EmailAddress, ErrorMessage = "Please enter a valid email address")]
        public string Email { get; set; }

        /// 
        /// Add or edit a user based on the existence of the UserId
        /// 
        public void Save()
        {
            using (var con = ConnectionFactory.GetOpenConnection())
            {
                if (UserId == 0)
                {
                    con.Execute("INSERT INTO [UserProfile] ([FirstName],[LastName],[Email]) VALUES (@FirstName,@LastName,@Email)", new { FirstName, LastName, Email });
                    UserId = (int)con.Query(@"SELECT last_insert_rowid() as ID").First().ID;
                    return;
                }
                con.Execute("UPDATE [UserProfile] SET [FirstName] = @FirstName, [LastName] = @LastName, [Email] = @Email WHERE [UserId] = @UserId", new { FirstName, LastName, Email, UserId });
            }
        }

        /// 
        /// Get all users from the database
        /// 
        /// 
        public static List GetAll()
        {
            using (var con = ConnectionFactory.GetOpenConnection())
                return con.Query("SELECT * FROM [UserProfile]").OrderBy(o => o.LastName).ThenBy(o => o.FirstName).ToList();
        }

        public static UserProfile GetOne(int userId)
        {
            using (var con = ConnectionFactory.GetOpenConnection())
                return con.Query("SELECT * FROM [UserProfile] WHERE [UserId] = @userId", new { userId }).FirstOrDefault();
        }

        /// 
        /// Delete a user from the database
        /// 
        /// 
        public static void Delete(int userId)
        {
            using (var con = ConnectionFactory.GetOpenConnection())
                con.Execute("DELETE FROM [UserProfile] WHERE [UserId] = @userId", new { userId });
        }
    }
}

HomeController.cs

using System.Web.Mvc;
using DapperBlog.Models;

namespace DapperBlog.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return View(new UserProfile());
        }

        [HttpGet]
        [Route("ManageUser/{id}")]
        public ActionResult ManageUser(int id)
        {
            return View("Index", UserProfile.GetOne(id));
        }

        [HttpPost]
        [Route("ManageUser")]
        public ActionResult ManageUser(UserProfile model)
        {
            if (!ModelState.IsValid) return View("Index", model);
            model.Save();
            TempData["SuccessMessage"] = "User successfully updated";
            return Redirect("/");
        }

        [HttpGet]
        [Route("DeleteUser/{id}")]
        public ActionResult DeleteUser(int id)
        {
            UserProfile.Delete(id);
            TempData["SuccessMessage"] = "User successfully deleted";
            return Redirect("/");
        }
    }
}

Index.cshtml

@using DapperBlog.Models
@model UserProfile
@{
    ViewBag.Title = "Index";
    var data = UserProfile.GetAll();
}
@if (Model != null && ViewData.ModelState.SelectMany(x => x.Value.Errors).Select(x => x.ErrorMessage).Any())
{
    <div class="alert alert-danger alert-dismissible" role="alert">
        <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
        @string.Join("<br/>", ViewData.ModelState.Values.SelectMany(x => x.Errors).Select(x => x.ErrorMessage))
    </div><br />
}
@if (TempData["SuccessMessage"] != null)
{
    <div class="alert alert-success alert-dismissible" role="alert">
        <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
        @TempData["SuccessMessage"]
    </div><br />
}

<form class="form-inline" action="/ManageUser" method="POST">
    <div class="form-group">
        <label for="FirstName">First Name</label>
        @Html.TextBoxFor(m => m.FirstName, new { @class = "form-control", placeholder = "Enter first name" })
    </div>
    <div class="form-group">
        <label for="LastName">Last Name</label>
        @Html.TextBoxFor(m => m.LastName, new { @class = "form-control", placeholder = "Enter last name" })
    </div>
    <div class="form-group">
        <label for="Email">Email</label>
        @Html.TextBoxFor(m => m.Email, new { @class = "form-control", placeholder = "Enter email address" })
    </div>
    @Html.HiddenFor(m => m.UserId)
    <button type="submit" class="btn btn-default">Save</button>
</form>
<hr />
@if (data.Any())
{
    <table class="table table-bordered">
        <thead>
            <tr>
                <th>UserId</th>
                <th>Last Name</th>
                <th>First Name</th>
                <th>Email</th>
                <th></th>
            </tr>
        </thead>
        <tbody>
            @foreach (var item in data)
            {
                <tr>
                    <td>@item.UserId</td>
                    <td>@item.LastName</td>
                    <td>@item.FirstName</td>
                    <td>@item.Email</td>
                    <td><a href="/ManageUser/@item.UserId" class="btn btn-xs btn-primary">Edit</a> <a href="/DeleteUser/@item.UserId" class="btn btn-xs btn-danger">Delete</a></td>
                </tr>
            }
        </tbody>
    </table>
}
else
{
    <p>There are no entries</p>
}

Download Source (zip)