前言

向来有争议的话题都是公说公的理，婆说婆的理，Entity Framework的爱好者对此可以说是嗤之以鼻，不屑一顾，而Dapper爱好者则是举双手赞成，阅之大快人心。

每个人不同的阅历，社会经验，甚至对简繁的偏见都会影响对此事的看法，凡事都有优劣，取其精华而弃之糟泊，方为上策。

这篇文章则将目光聚焦到Dapper。

Dapper是如此的简单，她只提供了 3 个帮助函数：

1. 执行一个查询，将结果映射到一个强类型列表

2. 执行一个查询，将结果映射到一个动态对象列表

3. 执行一个命令，不返回结果

而在实际的项目中，我们可能只会用到强类型列表，所以上面列出的 3 个帮助函数只会用到 2 个。

有人说了，简单其实就意味着复杂，的确如此。

过少的封装意味着每次可能要书写过多的重复代码，因此每个Dapper开发者可能都会自行扩展一些用着顺手的方法，也就不足为奇了，俗话说一千个人眼里有一千个哈姆雷特。

下面我会分享在将 AppBoxPro 从 EntityFramework 迁移到 Dapper 中遇到的问题，以及解决方法，其中也包含我的小小封装，希望你能喜欢。

下面是 AppBoxPro.Dapper 的项目开发截图：

正文

模型的约定

我们对模型有两个约定：

1. IKeyID接口

2. NotMapped特性

来看一下 User 模型的声明：

public class User : IKeyID
{
    [Key]    
    public int ID { get; set; }

    [Required, StringLength(50)]    
    public string Name { get; set; }

    [Required, StringLength(100)]    
    public string Email { get; set; }   
    public int? DeptID { get; set; }

    [NotMapped]    
    public string UserDeptName { get; set; }
}

其中 IKeyID 是一个接口，定义了模型类必须包含名为 ID 的属性，这个接口是为了计算 FineUIPro 控件中模拟树的下拉列表和表格的数据源。

NotMapped特性表明这个属性没有数据库映射，仅仅作为一个内存中使用的属性，一般有两个用途：

1. 表关联属性，比如 User 模型中的 UserDeptName 属性，在数据库检索时可以通过 inner join 将 Dept 表的 Name 属性映射于此。

2. 内存中计算的值，比如在 Dept 模型中的 TreeLevel, Enabled, IsTreeLeaf，用于在模拟树的表格中确定节点的层次结构和节点属性。

一个请求一个数据库连接

如果你查阅 Dapper 的文档，你会发现一个常见的操作代码段：

using (var conn = new MySqlConnection(connectionString))
{
    connection.Open();    
    var users = conn.Query<User>("select * from users");    
    // ...
}

虽然看起来简单，但是如果每一个地方都有加个 using 代码段，势必也会影响观感和书写体验。

另一方面，一个缩进的代码段会创建一个变量作用域，有时我们可能会希望在 using 外部获取某个变量，这就变成了：

IEnumerable<User> users;using (var conn = new MySqlConnection(connectionString))
{
    connection.Open();
    
    users = conn.Query<User>("select * from users");    // ...}

这样写起来就会感觉磕磕绊绊，一点都不美好了。

为了简化代码，我们遵循之前的逻辑，一个请求一个数据库连接，将 IDbConnection  保存到 HttpContext 上下文中：

public static IDbConnection DB
{    get
    {        
        if (!HttpContext.Current.Items.Contains("__AppBoxProContext"))
        {
            HttpContext.Current.Items["__AppBoxProContext"] = GetDbConnection();
        }        
        return HttpContext.Current.Items["__AppBoxProContext"] as IDbConnection;
    }
}

public static IDbConnection GetDbConnection()
{
    IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ToString());
    connection.Open();    return connection;
}

然后在请求结束时销毁这个连接，在 Global.asax 中：

protected virtual void Application_EndRequest()
{    
   var context = HttpContext.Current.Items["__AppBoxProContext"] as IDbConnection;    
   if (context != null)
    {
        context.Dispose();
    }
}

经过这个简单的封装，上面的获取用户列表的代码可以直接写了：

var users = conn.Query<User>("select * from users");

通过ID检索对象

在项目中，我们可能经常需要通过 ID 来检索对象，在 Dapper 中实现很简单：

User current = DB.QuerySingleOrDefault<User>("select * from users where ID = @UserID", new { UserID = id });

但是由于这个操作经常用到，我们可能需要多次的拷贝粘贴，而仅仅修改其中的几个字符串。

当事情变得不再美好时，我们就要重构了，这次的提取公共方法没有任何难度：

protected T FindByID<T>(int paramID)
{    
    return FindByID<T>(DB, paramID);
}
protected T FindByID<T>(IDbConnection conn, int paramID)
{    
    // 约定：类型 User 对应的数据库表名 users    
    var tableName = typeof(T).Name.ToLower() + "s";    
    return conn.QuerySingleOrDefault<T>("select * from "+ tableName +" where ID = @ParamID", new { ParamID = paramID });
}

可以看到其中的注释，一个模型类到数据库表的约定：User 模型对应于数据库表名 users，这个约定有助于我们使用泛型，将参数强类型化（User）而无需传递字符串（users）。

经过这次的改造，通过ID检索对象就简单多了：

User current = FindByID<User>(id);

相关页面展示（用户编辑）：

插入和更新

插入和更新是常见的数据库操作，比如对菜单项的操作涉及对 menus 表的插入和更新：

Menu item = new Menu();
item.Name = tbxName.Text.Trim();
item.NavigateUrl = tbxUrl.Text.Trim();
item.SortIndex = Convert.ToInt32(tbxSortIndex.Text.Trim());
item.Remark = tbxRemark.Text.Trim();

DB.Execute("insert menus(Name, NavigateUrl, SortIndex, ImageUrl, Remark, ParentID, ViewPowerID) values (@Name, @NavigateUrl, @SortIndex, @ImageUrl, @Remark, @ParentID, @ViewPowerID);", item);

首先初始化一个 Menu 模型对象，然后从页面上获取属性值并赋值到模型对象，最后通过 Dapper 提供的 Execute 方法执行插入操作。

相应的，更新操作需要首先通过菜单ID获取菜单模型对象，然后更新数据库：

Menu item = FindByID<Menu>(menuID);
item.Name = tbxName.Text.Trim();
item.NavigateUrl = tbxUrl.Text.Trim();
item.SortIndex = Convert.ToInt32(tbxSortIndex.Text.Trim());
item.ImageUrl = tbxIcon.Text;
item.Remark = tbxRemark.Text.Trim();

DB.Execute("update menus set Name = @Name, NavigateUrl = @NavigateUrl, SortIndex = @SortIndex, ImageUrl = @ImageUrl, Remark = @Remark, ParentID = @ParentID, ViewPowerID = @ViewPowerID where ID = @ID;", item);

上面的插入和更新操作存在两个不方便的地方：

1. SQL语句中要包含多个要更新的属性，容易遗漏和写错

2. 插入和更新的属性列表相同时，写法却完全不同，不方便拷贝粘贴

为了克服上面两个弱点，我们对插入更新进行了简单的封装，为了不手工填写属性列表，我们需要一个从模型类读取属性列表的方法：

private string[] GetReflectionProperties(object instance)
{    
    var result = new List<string>();    
    foreach (PropertyInfo property in instance.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public))
    {        
        var propertyName = property.Name;        // NotMapped特性        
        var notMappedAttr = property.GetCustomAttribute<NotMappedAttribute>(false);        
        if (notMappedAttr == null && propertyName != "ID")
        {
            result.Add(propertyName);
        }
    }    
    return result.ToArray();
}

上面函数通过反射获取实例对应模型类（instance.GetType()）的属性列表（GetProperties()），然后过滤掉 ID 属性和拥有 NotMapped 标注的属性，最后返回属性数组。

对插入操作的封装：

protected void ExecuteInsert<T>(object instance,  params string[] fields)
{    
    return ExecuteInsert<T>(DB, instance, fields);
}
    protected void ExecuteInsert<T>(IDbConnection conn, object instance, params string[] fields)
    {    
    // 约定：类型 User 对应的数据库表名 users    
    string tableName = typeof(T).Name.ToLower() + "s";    
    if (fields.Length == 0)
    {
        fields = GetReflectionProperties(instance);
    }    
    var fieldsSql1 = String.Join(",", fields);    
    var fieldsSql2 = String.Join(",", fields.Select(field => "@" + field));    
    var sql = String.Format("insert {0} ({1}) values ({2});", tableName, fieldsSql1, fieldsSql2);    
    return conn.Execute(sql, instance);
}

ExecuteInsert 方法接受如下参数：

1. 类型 T：通过模型类名称获取数据库表名，这是一个命名约定

2. instance：模型实例，需要插入到数据对应表中

3. fields：这是一个可变参数，如果未传入 fields 参数，则通过前面定义的 GetReflectionProperties 函数获取模型类的全部属性列表

最后，经过简单的字符串拼接，就能方便的生成需要的SQL语句，并执行 Dapper 的 Execute 来插入数据了。

使用 ExecuteInsert 方法，我们可以将上面的插入操作简化为：

ExecuteInsert<Menu>(item, "Name", "NavigateUrl", "SortIndex", "ImageUrl", "Remark", "ParentID", "ViewPowerID");

或者，直接这样写：

ExecuteInsert<Menu>(item);

是不是很方便。

同样，对更新的操作也是类似的，只不过在封装时拼接SQL字符串的逻辑稍微不同：

protected void ExecuteUpdate<T>(object instance,  params string[] fields)
{    
return ExecuteUpdate<T>(DB, instance, fields);
}
protected void ExecuteUpdate<T>(IDbConnection conn, object instance, params string[] fields)
{    
        // 约定：类型 User 对应的数据库表名 users    
        string tableName = typeof(T).Name.ToLower() + "s";    
        if (fields.Length == 0)
        {
            fields = GetReflectionProperties(instance);
        }    
        var fieldsSql = String.Join(",", fields.Select(field => field + " = @" + field));    
        var sql = String.Format("update {0} set {1} where ID = @ID", tableName, fieldsSql);    
        return conn.Execute(sql, instance);
}

使用封装后的 ExecuteUpdate 方法，上面的更新操作可以简化为：

ExecuteUpdate<Menu>(item);

相关页面展示（用户角色页面）：

插入后返回自增ID

有时，插入新的数据之后，我们需要立即获取新插入数据的ID属性，方便后续的数据库操作，这就要对上面的 ExecuteInsert 进行改造，在 insert 语句之后加上如下SQL语句：

select last_insert_id();

上面的SQL语句仅适用于 MySQL 数据库，当然对于其他数据库也不难支持，后面会讲解。更新后的 ExecuteInsert 方法如下：

protected int ExecuteInsert<T>(object instance,  params string[] fields)
{    
    return ExecuteInsert<T>(DB, instance, fields);
}
protected int ExecuteInsert<T>(IDbConnection conn, object instance, params string[] fields)
{    
// 约定：类型 User 对应的数据库表名 users    
string tableName = typeof(T).Name.ToLower() + "s";    
if (fields.Length == 0)
    {
        fields = GetReflectionProperties(instance);
    }    
    var fieldsSql1 = String.Join(",", fields);   
    var fieldsSql2 = String.Join(",", fields.Select(field => "@" + field));    
    var sql = String.Format("insert {0} ({1}) values ({2});", tableName, fieldsSql1, fieldsSql2);
 
    sql += "select last_insert_id();";    return conn.QuerySingle<int>(sql, instance);
}

调用时，可以直接拿到新增行的ID，然后执行其他数据库操作：

// 插入用户
var userID = ExecuteInsert<User>(item);
// 更新用户所属角色
DB.Execute("insert roleusers (UserID, RoleID) values (@UserID, @RoleID)", new { UserID = userID, RoleID = 101 });

过滤，分页和排序

分页和排序是使用 Dapper 的一个拦路虎，因为很多初学者一看到 Dapper 居然没有内置的分页功能就放弃了，至少对于 5 年前的我也遭遇了同样的经历。

这是完全没有必要的！

因为分页和排序完全是标准的SQL语句是事情，Dapper没有义务为此负责。

我们可以通过简单的封装化腐朽为神奇，来看看过滤，分页和排序也能如此简单和优雅，这个过程一般可以分解为 3 个步骤：

1. 添加过滤条件（比如匹配名称中的关键词，只列出启用的行....）

2. 获取总记录行数（数据库分页需要在页面显示总记录数，已经当前页的记录其实序号）

3. 获取当前分页的数据

下面是 AppBoxPro 中角色列表页面的过滤，分页和排序代码，我们可以一目了然：

// 查询条件
var builder = new WhereBuilder();
string searchText = ttbSearchMessage.Text.Trim();
if (!String.IsNullOrEmpty(searchText))
{
    builder.AddWhere("Name like @SearchText");
    builder.AddParameter("SearchText", "%" + searchText + "%");
}
// 获取总记录数（在添加条件之后，排序和分页之前）
Grid1.RecordCount = Count<Role>(builder);
// 排列和数据库分页
Grid1.DataSource = SortAndPage<Role>(builder, Grid1);
Grid1.DataBind();

上面的涉及三个重要的自定义类和函数：

1. WhereBuilder：我们封装的一个简单的类，主要目的是将查询条件，条件参数以及SQL语句 3 则封装在一起。

2. Count：用来返回总记录数。

3. SortAndPage：用来执行分页和排序。

首先来看下WhereBuilder：

public class WhereBuilder
{    
    private DynamicParameters _parameters = new DynamicParameters();    
    public DynamicParameters Parameters
    {        
        get { return _parameters; }        
        set { _parameters = value; }
    }    
    private List<string> _wheres = new List<string>();    
    public List<string> Wheres
    {        
        get { return _wheres; }       
         set { _wheres = value; }
    }    
    private string _fromSql = String.Empty;    
    public string FromSql
    {        
        get { return _fromSql; }        
        set { _fromSql = value; }
    }
    public void AddWhere(string item)
    {
            _wheres.Add(item);
    }
    public void AddParameter(string name, object value)
    {
        _parameters.Add(name, value);
    }
}

其中：

1. _wheres: 对应于SQL的 where 子语句。

2. _parameters: 对应于 where 子语句用到的实际参数。

3. _fromSql: 如果省略此属性，则从模型类名推导出需要操作的数据库表名，对于需要进行表关联的复杂查询，则需要设置此参数，后面会进行详细讲解。

Count 的函数定义：

protected int Count<T>(WhereBuilder builder)
{    
    return Count<T>(DB, builder);
}
protected int Count<T>(IDbConnection conn, WhereBuilder builder)
{    
    var sql = builder.FromSql;    
    if (String.IsNullOrEmpty(sql))
    {        
        // 约定：类型 User 对应的数据库表名 users
        sql = typeof(T).Name.ToLower() + "s";
    }

    sql = "select count(*) from " + sql;    
    if (builder.Wheres.Count > 0)
    {
        sql += " where " + String.Join(" and ", builder.Wheres);
    }    
    return conn.QuerySingleOrDefault<int>(sql, builder.Parameters);
}

SortAndPage的函数定义：

protected IEnumerable<T> SortAndPage<T>(WhereBuilder builder, FineUIPro.Grid grid)
{    
    return SortAndPage<T>(DB, builder, grid);
}
protected IEnumerable<T> SortAndPage<T>(IDbConnection conn, WhereBuilder builder, FineUIPro.Grid grid)
{    
// sql: users    
// sql: select * from users    
// sql: select onlines.*, users.Name UserName from onlines inner join users on users.ID = onlines.UserID    
var sql = builder.FromSql;    
if (String.IsNullOrEmpty(sql))
    {        
    // 约定：类型 User 对应的数据库表名 users
    sql = typeof(T).Name.ToLower() + "s";
    }    
    if (!sql.StartsWith("select"))
    {
        sql = "select * from " + sql;
    }    
    if (builder.Wheres.Count > 0)
    {
        sql += " where " + String.Join(" and ", builder.Wheres);
    }

    sql += " order by " + grid.SortField + " " + grid.SortDirection;

    sql += " limit @PageStartIndex, @PageSize";

    builder.Parameters.Add("PageSize", grid.PageSize);
    builder.Parameters.Add("PageStartIndex", grid.PageSize * grid.PageIndex);    return conn.Query<T>(sql, builder.Parameters);
}

上面的封装很简单，对分页的处理只有这三行代码：

sql += " limit @PageStartIndex, @PageSize";

builder.Parameters.Add("PageSize", grid.PageSize);
builder.Parameters.Add("PageStartIndex", grid.PageSize * grid.PageIndex);

当然这里的 limit 子句只适用于 MySQL，其他数据库的用法后面会有介绍。

对于 builder.FromSql 属性，如果留空，则检索当前数据表的全部数据。而对于表关联查询，可以设置完整的 select 子句，下面会进行介绍。

表关联

在线用户列表页面，对于某个用户，我们不仅要列出用户的登录时间，最后操作时间，IP地址，还要列出用户名和用户中文名称。

这里就需要用到表关联，因为 onlines  只记录用户ID，而用户名称需要从 users 表获取，下面就是此页面的过滤，分页和排序逻辑：

// 查询条件
var builder = new WhereBuilder();
string searchText = ttbSearchMessage.Text.Trim();
if (!String.IsNullOrEmpty(searchText))
{
    builder.AddWhere("users.Name like @SearchText");
    builder.AddParameter("SearchText", "%" + searchText + "%");
}

DateTime twoHoursBefore = DateTime.Now.AddHours(-2);
builder.AddWhere("onlines.UpdateTime > @TwoHoursBefore");
builder.AddParameter("TwoHoursBefore", twoHoursBefore);
// 获取总记录数（在添加条件之后，排序和分页之前）
Grid1.RecordCount = Count<Online>(builder);
// 排列和数据库分页
builder.FromSql = "select onlines.*, users.Name UserName, users.ChineseName UserChineseName from onlines inner join users on users.ID = onlines.UserID";

Grid1.DataSource = SortAndPage<Online>(builder, Grid1);
Grid1.DataBind();

相关页面展示（用户列表）：

事务（Transaction）

Dapper对事务有两种支持，一种是直接在 Query 或者 Execute 中传递 transaction 对象，而另外一种则更加简单。

在更新用户信息时，首先是更新 users 表，然后还要操作用户角色表和用户部门表，对于多个数据表的多次操作，可以放到一个事务中：

using (var transactionScope = new TransactionScope())
{    
 // 更新用户
    ExecuteUpdate<User>(DB, item);    // 更新用户所属角色
    int[] roleIDs = StringUtil.GetIntArrayFromString(hfSelectedRole.Text);
    DB.Execute("delete from roleusers where UserID = @UserID", new { UserID = userID });
    DB.Execute("insert roleusers (UserID, RoleID) values (@UserID, @RoleID)", roleIDs.Select(u => new { UserID = userID, RoleID = u }).ToList());    // 更新用户所属职务
    int[] titleIDs = StringUtil.GetIntArrayFromString(hfSelectedTitle.Text);
    DB.Execute("delete from titleusers where UserID = @UserID", new { UserID = userID });
    DB.Execute("insert titleusers (UserID, TitleID) values (@UserID, @TitleID)", titleIDs.Select(u => new { UserID = userID, TitleID = u }).ToList());


    transactionScope.Complete();
}

相关页面展示（角色权限）：

匿名参数（对象和数组）

Dapper支持方便的传入匿名参数，前面已经多次看到，比如下面这个更新用户角色的代码：

DB.Execute("insert roleusers (UserID, RoleID) values (@UserID, @RoleID)", new { UserID = userID, RoleID = 101 });

不仅如此，Dapper还支持多次执行一个命令，只需要传入一个匿名数组即可。

在 AppBoxPro 中，有多处应用场景，比如前面的更新用户角色的代码：

DB.Execute("insert roleusers (UserID, RoleID) values (@UserID, @RoleID)", roleIDs.Select(u => new { UserID = userID, RoleID = u }).ToList());

这里通过 Select 表达式获取一个动态对象数组。

在 ConfigHelper 中，我们还有手工创建匿名数组的场景，用来更新 configs 表中的多个行数据：

DB.Execute("update configs set ConfigValue = @ConfigValue where ConfigKey = @ConfigKey",new[] { 
    new { ConfigKey = "Title", ConfigValue = Title }, 
    new { ConfigKey = "PageSize", ConfigValue = PageSize.ToString() }, 
    new { ConfigKey = "Theme", ConfigValue = Theme }, 
    new { ConfigKey = "HelpList", ConfigValue = HelpList },    new { ConfigKey = "MenuType", ConfigValue = MenuType }
});

多数据库支持

多数据库支持真的不难，在我们支持的 MySQL 和 SQLServer 两个数据库中，只有少数几处需要特殊处理。

1. 数据库连接，我们可以根据 ProviderName 来生成不同的 IDbConnection 实例。

首先来看下 Web.config 中数据库相关的配置节：

<appSettings>
    <!-- 需要连接的数据库，对应于 connectionStrings 节的 name 属性 -->
    <add key="Database" value="MySQL" />
</appSettings>
<connectionStrings>
    <clear />
    <add name="SQLServer" connectionString="Password=pass;Persist Security Info=True;User ID=sa;Initial Catalog=appbox;Data Source=." providerName="System.Data.SqlClient" />
    <add name="MySQL" connectionString="Server=localhost;Database=appbox;Uid=root;Pwd=pass;Charset=utf8" providerName="MySql.Data.MySqlClient" />
</connectionStrings>

然后是对 GetDbConnection 的扩展：

public static IDbConnection GetDbConnection()
{
    var database = ConfigurationManager.AppSettings["Database"];

    var connectionStringSection = ConfigurationManager.ConnectionStrings[database];
    var connectionString = connectionStringSection.ToString();

    IDbConnection connection;
    if (connectionStringSection.ProviderName.StartsWith("MySql"))
    {
        connection = new MySqlConnection(connectionString);
    }
    else
    {
        connection = new SqlConnection(connectionString);
    }

    // 打开数据库连接
    connection.Open();

    return connection;
}

2. 插入后获取新增的行ID

protected int ExecuteInsert<T>(IDbConnection conn, object instance, params string[] fields)
{
    // 约定：类型 User 对应的数据库表名 users
    string tableName = typeof(T).Name.ToLower() + "s";

    if (fields.Length == 0)
    {
        fields = GetReflectionProperties(instance);
    }

    var fieldsSql1 = String.Join(",", fields);
    var fieldsSql2 = String.Join(",", fields.Select(field => "@" + field));

    var sql = String.Format("insert {0} ({1}) values ({2});", tableName, fieldsSql1, fieldsSql2);

    if (conn is MySqlConnection)
    {
        sql += "select last_insert_id();";
    }
    else
    {
        sql += "SELECT @@IDENTITY;";
    }

    return conn.QuerySingle<int>(sql, instance);
}

3. 数据库分页处理，更新后的 SortAndPage 函数：

protected IEnumerable<T> SortAndPage<T>(IDbConnection conn, WhereBuilder builder, FineUIPro.Grid grid)
{
    var sql = builder.FromSql;
    if (String.IsNullOrEmpty(sql))
    {
        // 约定：类型 User 对应的数据库表名 users
        sql = typeof(T).Name.ToLower() + "s";
    }

    if (!sql.StartsWith("select"))
    {
        sql = "select * from " + sql;
    }

    if (builder.Wheres.Count > 0)
    {
        sql += " where " + String.Join(" and ", builder.Wheres);
    }

    sql += " order by " + grid.SortField + " " + grid.SortDirection;

    // 分页
    if (conn is MySqlConnection)
    {
        sql += " limit @PageStartIndex, @PageSize";
    }
    else
    {
        sql += " OFFSET @PageStartIndex ROWS FETCH NEXT @PageSize ROWS ONLY";
    }

    builder.Parameters.Add("PageSize", grid.PageSize);
    builder.Parameters.Add("PageStartIndex", grid.PageSize * grid.PageIndex);


    return conn.Query<T>(sql, builder.Parameters);
}

好了，上面就是全部的多数据库处理代码了。相比 jQuery 对不同浏览器的封装，这里的多数据库支持真是的小巫见大巫了。

小结

这篇文章主要描述了从 Entity Framework 迁移到 Dapper 时遇到的问题，以及我们给出的简单封装，希望你能喜欢。