Introduction
Database handling is often a performance bottleneck in many applications. Entity Framework and other ORM frameworks offer a convenient way to work with database objects, but it comes with a cost.
Dapper is a micro ORM that offers fast database access with a simple interface.
I like that, but I also want to avoid manually writing static SQL strings embedded in my code ...
Dapper Wrapper
Dapper is lightweight and comes with one small assembly. It can easily be added to your project by using Nuget.
I have created a small wrapper class to make it easier to use: DapperRepositoryBase
.
It uses reflection to lookup class names and property names. Just make your own repository class and inherit from DapperRepositoryBase
. Generic methods for Insert
, Update
, Delete
are provided.
Performance Comparison
I inserted 4000 records with different techniques and came up with the following results:
Entity Framework | 12 019 ms |
DapperRepisotoryBase | 1 558 ms |
Dapper bare bone | 1 502 ms |
I find the very small overhead introduced with DapperRepositoryBase
to be negligible. It is very convenient to get the SQL strings generated on the fly and it comes with almost no performance penalty!
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Dapper;
namespace DapperWrapperDemo.DataAccess
{
[AttributeUsage(AttributeTargets.Property)]
public class DapperKey : Attribute
{
}
[AttributeUsage(AttributeTargets.Property)]
public class DapperIgnore : Attribute
{
}
public abstract class DapperRepositoryBase
{
private readonly string _connectionString;
#region Constructor
protected DapperRepositoryBase(string connectionString)
{
_connectionString = connectionString;
}
#endregion
#region Standard Dapper functionality
protected IEnumerable<T>
GetItems<T>(CommandType commandType, string sql, object parameters = null)
{
using (var connection = GetOpenConnection())
{
return connection.Query<T>(sql, parameters, commandType: commandType);
}
}
protected int Execute(CommandType commandType, string sql, object parameters = null)
{
using (var connection = GetOpenConnection())
{
return connection.Execute(sql, parameters, commandType: commandType);
}
}
protected SqlConnection GetOpenConnection()
{
var connection = new SqlConnection(_connectionString);
connection.Open();
return connection;
}
#endregion
#region Automated methods for: Insert, Update, Delete
protected IEnumerable<T> Select<T>(object criteria = null)
{
var properties = ParseProperties(criteria);
var sqlPairs = GetSqlPairs(properties.AllNames, " AND ");
var sql = string.Format("SELECT * FROM [{0}] WHERE {1}", typeof(T).Name, sqlPairs);
return GetItems<T>(CommandType.Text, sql, properties.AllPairs);
}
protected void Insert<T>(T obj)
{
var propertyContainer = ParseProperties(obj);
var sql = string.Format("INSERT INTO [{0}] ({1})
VALUES (@{2}) SELECT CAST(scope_identity() AS int)",
typeof(T).Name,
string.Join(", ", propertyContainer.ValueNames),
string.Join(", @", propertyContainer.ValueNames));
using (var connection = GetOpenConnection())
{
var id = connection.Query<int>
(sql, propertyContainer.ValuePairs, commandType: CommandType.Text).First();
SetId(obj, id, propertyContainer.IdPairs);
}
}
protected void Update<T>(T obj)
{
var propertyContainer = ParseProperties(obj);
var sqlIdPairs = GetSqlPairs(propertyContainer.IdNames);
var sqlValuePairs = GetSqlPairs(propertyContainer.ValueNames);
var sql = string.Format("UPDATE [{0}]
SET {1} WHERE {2}", typeof(T).Name, sqlValuePairs, sqlIdPairs);
Execute(CommandType.Text, sql, propertyContainer.AllPairs);
}
protected void Delete<T>(T obj)
{
var propertyContainer = ParseProperties(obj);
var sqlIdPairs = GetSqlPairs(propertyContainer.IdNames);
var sql = string.Format("DELETE FROM [{0}]
WHERE {1}", typeof(T).Name, sqlIdPairs);
Execute(CommandType.Text, sql, propertyContainer.IdPairs);
}
#endregion
#region Reflection support
private static PropertyContainer ParseProperties<T>(T obj)
{
var propertyContainer = new PropertyContainer();
var typeName = typeof(T).Name;
var validKeyNames = new[] { "Id",
string.Format("{0}Id", typeName), string.Format("{0}_Id", typeName) };
var properties = typeof(T).GetProperties();
foreach (var property in properties)
{
if (property.PropertyType.IsClass && property.PropertyType != typeof(string))
continue;
if (property.GetSetMethod() == null)
continue;
if (property.IsDefined(typeof(DapperIgnore), false))
continue;
var name = property.Name;
var value = typeof(T).GetProperty(property.Name).GetValue(obj, null);
if (property.IsDefined(typeof(DapperKey), false) || validKeyNames.Contains(name))
{
propertyContainer.AddId(name, value);
}
else
{
propertyContainer.AddValue(name, value);
}
}
return propertyContainer;
}
private static string GetSqlPairs
(IEnumerable<string> keys, string separator = ", ")
{
var pairs = keys.Select(key => string.Format("{0}=@{0}", key)).ToList();
return string.Join(separator, pairs);
}
private void SetId<T>(T obj, int id, IDictionary<string, object> propertyPairs)
{
if (propertyPairs.Count == 1)
{
var propertyName = propertyPairs.Keys.First();
var propertyInfo = obj.GetType().GetProperty(propertyName);
if (propertyInfo.PropertyType == typeof(int))
{
propertyInfo.SetValue(obj, id, null);
}
}
}
#endregion
private class PropertyContainer
{
private readonly Dictionary<string, object> _ids;
private readonly Dictionary<string, object> _values;
#region Properties
internal IEnumerable<string> IdNames
{
get { return _ids.Keys; }
}
internal IEnumerable<string> ValueNames
{
get { return _values.Keys; }
}
internal IEnumerable<string> AllNames
{
get { return _ids.Keys.Union(_values.Keys); }
}
internal IDictionary<string, object> IdPairs
{
get { return _ids; }
}
internal IDictionary<string, object> ValuePairs
{
get { return _values; }
}
internal IEnumerable<KeyValuePair<string, object>> AllPairs
{
get { return _ids.Concat(_values); }
}
#endregion
#region Constructor
internal PropertyContainer()
{
_ids = new Dictionary<string, object>();
_values = new Dictionary<string, object>();
}
#endregion
#region Methods
internal void AddId(string name, object value)
{
_ids.Add(name, value);
}
internal void AddValue(string name, object value)
{
_values.Add(name, value);
}
#endregion
}
}
}
Points of Interest
The normal conventions of naming the Id
field are supported: Id
, TypeNameId
or TypeName_Id
. If you want to use some other name, you need to decorate the property with [DapperKey]
attribute.
Properties without public setters are automatically skipped. If you want to skip other properties, you need to decorate them with [DapperIgnore]
attribute.
If you have nested objects that you want to persist, then you need to handle that by making separate calls in the correct order. That is what you give up when you use lightweight ORMs.
SetId
will read the identity field generated by the database and set it on your object. This assumes the primary key used is int IDENTITY
.
Summary
I like to make things fast. This means I like Dapper. With this simple addition, I also make it reasonably easy to use for many scenarios.
History