Dapper Repository

Repository.cs

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

namespace Example.Data.Dapper {
  public class MessageRepository : RepositoryBase, IMessageRepository {
    /// <summary>
    /// Gets a single message
    /// </summary>
    /// <param name="id">The message Id</param>
    /// <returns>A message</returns>
    public Message GetMessage(long id) {
      return CacheOutput(() =>
        UseConnection(connection => {
          const string sql = @"SELECT TOP 1
                                  m.Id, m.Body, m.UserId, m.CreatedOn, 
                                  u.Id, u.Username, u.RealName
                                FROM [Messages] m
                                LEFT JOIN Users u
                                ON m.UserId = u.Id
                                WHERE m.Id = @Id";
          return connection.Query<Message, User, Message>(sql, (m, u) => { m.User = u; return m; }, new { Id = id }).SingleOrDefault();
        }), "message-" + id, new TimeSpan(0, 2, 0));
    }

    /// <summary>
    /// Gets all the messages written by a user
    /// </summary>
    /// <param name="userId">The user's Id</param>
    /// <param name="page">The page index</param>
    /// <param name="count">The page size</param>
    /// <returns>A collection of Messages</returns>
    public IEnumerable<Message> GetMessagesByUser(long userId, int page = 0, int count = 50) {
      return CacheOutput(() =>
        UseConnection(connection => {
          const string sql = @"WITH Results AS (
                                SELECT 
                                    m.Id, m.Body, m.UserId, m.CreatedOn, 
                                    u.Id AS UId, u.Username, u.RealName,
                                    ROW_NUMBER() OVER (ORDER BY m.CreatedOn DESC, m.Id DESC) AS Row
                                  FROM [Messages] m
                                  LEFT JOIN Users u
                                  ON m.UserId = u.Id
                                  WHERE m.UserId = @UserId
                                )
                                SELECT Id, Body, UserId, CreatedOn, UId As Id, Username, RealName FROM Results WHERE Row BETWEEN @Low AND @High";
          return connection.Query<Message, User, Message>(sql, (m, u) => { m.User = u; return m; }, new { UserId = userId, Low = (page * count) + 1, @High = (page * count) + count });
        }), String.Format("messages-by-user-{0}-{1}-{2}", userId, page, count), new TimeSpan(0, 0, 5));
    }

    /// <summary>
    /// Gets all the messages written by users to whom the user is subscribed
    /// </summary>
    /// <param name="userId">The user's Id</param>
    /// <param name="page">The page index</param>
    /// <param name="count">The page size</param>
    /// <returns>A collection of Messages</returns>
    public IEnumerable<Message> GetMessagesForUser(long userId, int page = 0, int count = 50) {
      return CacheOutput(() =>
        UseConnection(connection => {
          const string sql = @"WITH Results AS (
                                SELECT 
                                  m.Id, m.Body, m.UserId, m.CreatedOn, 
                                  u.Id AS UId, u.Username, u.RealName,
                                  ROW_NUMBER() OVER (ORDER BY m.CreatedOn DESC, m.Id DESC) AS Row
                                FROM [Messages] m
                                LEFT JOIN Users u
                                ON m.UserId = u.Id
                                RIGHT JOIN Subscriptions s
                                ON m.UserId = s.SubscribedUserId
                                WHERE s.SubscribingUserId = @UserId
                              )
                              SELECT Id, Body, UserId, CreatedOn, UId As Id, Username, RealName FROM Results WHERE Row BETWEEN @Low AND @High";
          return connection.Query<Message, User, Message>(sql, AssignUserToMessage, new { UserId = userId, Low = (page * count) + 1, @High = (page * count) + count });
        }), String.Format("messages-for-user-{0}-{1}-{2}", userId, page, count), new TimeSpan(0, 0, 5));
    }

    /// <summary>
    /// Posts a message
    /// </summary>
    /// <param name="text">The body of the message (Limit 140 characters)</param>
    /// <param name="userId">The user's Id</param>
    /// <returns>The posted message</returns>
    public Message PostMessage(string text, long userId) {
      return UseConnection(connection => {
        const string sql = @"INSERT INTO [Messages] (Body, UserId, CreatedOn)
                              VALUES (@Text, @UserId, GETDATE()); 
                              SELECT TOP 1
                                m.Id, m.Body, m.UserId, m.CreatedOn, 
                                u.Id, u.Username, u.RealName
                              FROM [Messages] m
                              LEFT JOIN Users u
                              ON m.UserId = u.Id
                              WHERE m.Id = SCOPE_IDENTITY()";
        return connection.Query<Message, User, Message>(sql, AssignUserToMessage, new { Text = text, UserId = userId }).SingleOrDefault();
      });
    }

    /// <summary>
    /// Used as a delegate to map a user into a message
    /// </summary>
    /// <param name="message">The message written by the user</param>
    /// <param name="user">The user that wrote the message</param>
    /// <returns>The message, with the User-related fields populated</returns>
    private static Message AssignUserToMessage(Message message, User user) {
      message.User = user;
      message.UserId = user.Id;
      return message;
    }
  }
}

RepositoryBase.cs

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Caching;

namespace Example.Data.Dapper {
  /// <summary>
  /// Provides utility methods for actual repositories
  /// </summary>
  public abstract class RepositoryBase {

    /// <summary>
    /// Get a new database connection
    /// </summary>
    /// <returns>A new instance that implements IDbConnection</returns>
    private static IDbConnection GetConnection() {
      return new SqlConnection(ConfigurationManager.ConnectionStrings["ExampleDb"].ConnectionString);
    }

    /// <summary>
    /// Pulls the result from the cache if it exists, otherwise runs the query and caches the result. 
    /// If you want to change the data caching implementation, this would be the place to do it.
    /// </summary>
    /// <typeparam name="T">The type of the result returned by the expression</typeparam>
    /// <param name="query">The expression that queries the database</param>
    /// <param name="key">The cache key to identify this particular query</param>
    /// <param name="duration">Specifies how long the results should be cached</param>
    /// <returns>The result of the expression (which is also stored in the cache)</returns>
    protected T CacheOutput<T>(Func<T> query, string key, TimeSpan duration) where T : class {
      var cachedOutput = HttpContext.Current.Cache[key] as T;
      if (cachedOutput != null) return cachedOutput;

      var output = query.Invoke();
      if (output == null) return output; //don't cache nulls - if it's being requested, it'll probably exist soon
      
      HttpContext.Current.Cache.Add(key, output, null, DateTime.Now + duration, Cache.NoSlidingExpiration, CacheItemPriority.Normal, null);
      return output;
    }

    /// <summary>
    /// Wraps a query expression with a connection inside a using statement. 
    /// Because deferred execution isn't being used, the connection can be safely closed and disposed here.
    /// </summary>
    /// <typeparam name="T">The type of the result returned by the expression</typeparam>
    /// <param name="query">The expression that queries the database</param>
    /// <returns>The result of the expression</returns>
    protected static T UseConnection<T>(Func<IDbConnection, T> query) {
      using (var connection = GetConnection()) {
        connection.Open();
        var output = query.Invoke(connection);
        if (connection.State == ConnectionState.Open) connection.Close();
        return output;
      }
    }
  }
}