在.NET資料庫訪問方面的Dapper類庫介紹

標籤：Dapper   ORM   .NET   ADO.NET   

在開發帶資料庫的.NET系統中我使用過各種方式，包括直接使用ADO.NET、使用基於ADO.NET封裝的各類工具（其中有自己封裝的）、還有各類ORM類庫，比如NHibernate、MyBatisNet、Linq to SQL、Entity Framwrok等，在上面的工具或類庫中，MyBatisNet一段時間曾是我的最愛：由於它基於XML的配置可以靈活適應一些特殊的情境，不過有時候在面對中小型項目時又覺得MyBatisNet有些大材小用，此外還有一個原因是MyBatisNet這個基於Java的MyBatis改造而來的項目最近幾乎沒有更新了。
很早就聽說過Dapper這個類庫了，只不過一直沒有嘗試使用，但是很早就知道它是國外大型IT問答社區StackOverFlow最早開發並開源的。最近用了一下，感覺確實很方便。Dapper的原始碼放在github上託管，並且可以用NuGet方式添加到項目中，只不過我現在開發的案頭軟體有一部分使用者還在使用WindowsXP系統，因此不能使用高於.NET Framrwork4.5以上版本開發且開發工具是Visual Studio 2015，這也限制了我不能使用最新版本的Dapper，於是我選擇了Dapper 1.50.2這個版本。
我們可以在Visual Studio 2015中直接使用NuGet來添加，具體辦法就是“工具”-“NuGet包管理器”-“管理解決方案的BuGet程式包”，如所示：

然後在彈出的視窗中搜尋“Dapper”，如所示：

在上述介面中可以選擇安裝到當前解決方案的那些項目中，並且還可以指定Dapper的版本。

本文的描述都是針對Dapper 1.50.2版本。

擴充方法介紹
在介紹Dapper之前首先要介紹一下.NE中的擴充方法。擴充方法是.NET3.5添加的一個特性，使用擴充方法可以讓你為現有的類擴充方法而無需建立新的衍生類別。下面以一個例子來說明：
在我解析某個XML檔案節點時，需要讀取某個節點的屬性，但是這個節點並不是一直有這個屬性值，如下：
<SubNetwork name="SF-SubNetWork" type="">
為了避免name屬性不存在時拋出異常，我必須先進行判斷，如下：

string name=string.Empty;if (subNetworkNode.Attributes["name"] != null){    name=subNetworkNode.Attributes["name"].Value;}

如果一個XML節點裡有幾個可能不存在的屬性時，就需要處處這樣判斷了，於是我對代碼進行了改進，針對此類情況定義了擴充方法，方法如下：

public static class ExtendMethodClass{    /// <summary>    /// 擷取指定屬性的值，如果沒有設定指定屬性名稱，則返回Null 字元串    /// </summary>    /// <param name="attributes">XML節點的屬性集合</param>    /// <param name="attributeName">屬性名稱</param>    /// <returns></returns>    public static string GetAttributeValue(this XmlAttributeCollection attributes,string attributeName)    {        if (string.IsNullOrEmpty(attributeName))        {            throw new ArgumentNullException("attributeName", "不可為空");        }        if (attributes == null||attributes[attributeName]==null)        {            return string.Empty;        }        return attributes[attributeName].Value;    }}

這樣一來，原來的代碼就可以寫成如下了：

string name = subNetworkNode.Attributes.GetAttributeValue("name");

初一看，就像是XmlAttributeCollection這類原來就有GetAttributeValue(string attributeName)這樣一個方法，其實這個方式是我們自己擴充的。
定義擴充方法有幾點：
1、定義擴充方法的類必須用static修飾，即必須為靜態類。
2、定義的擴充方法必須用static修飾，即必須為靜態方法，同時方法的第一個參數前必須加this修飾，this後必須是類名，表示為this後的類添加擴充方法，如本例中this XmlAttributeCollection attributes表示為XmlAttributeCollection這個類添加擴充方法，如果需要在方法體內訪問XmlAttributeCollection這個類的執行個體，通過後面的attributes參數即可（注意這個參數的名稱可以隨便取）。

Dapper介紹
通過上面的介紹，大家可以初步瞭解擴充方法是怎麼回事。其實Dapper主要也是用了擴充方法為IDbConnection和IDataReader添加擴充方法，比如在SqlMapper.cs中有如下代碼為IDbConnection添加擴充方法（節選）：

// <summary>/// Execute parameterized SQL./// </summary>/// <param name="cnn">The connection to query on.</param>/// <param name="sql">The SQL to execute for this query.</param>/// <param name="param">The parameters to use for this query.</param>/// <param name="transaction">The transaction to use for this query.</param>/// <param name="commandTimeout">Number of seconds before command execution timeout.</param>/// <param name="commandType">Is it a stored proc or a batch?</param>/// <returns>The number of rows affected.</returns>public static int Execute(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null){    var command = new CommandDefinition(sql, param, transaction, commandTimeout, commandType, CommandFlags.Buffered);    return ExecuteImpl(cnn, ref command);}/// <summary>/// Execute parameterized SQL./// </summary>/// <param name="cnn">The connection to execute on.</param>/// <param name="command">The command to execute on this connection.</param>/// <returns>The number of rows affected.</returns>public static int Execute(this IDbConnection cnn, CommandDefinition command) => ExecuteImpl(cnn, ref command);

在SqlMapper.IDataReader.cs為IDataReader添加擴充方法的代碼（節選）：

/// <summary>/// Parses a data reader to a sequence of data of the supplied type. Used for deserializing a reader without a connection, etc./// </summary>/// <typeparam name="T">The type to parse from the <paramref name="reader"/>.</typeparam>/// <param name="reader">The data reader to parse results from.</param>public static IEnumerable<T> Parse<T>(this IDataReader reader){    if (reader.Read())    {        var deser = GetDeserializer(typeof(T), reader, 0, -1, false);        do        {            yield return (T)deser(reader);        } while (reader.Read());    }}/// <summary>/// Parses a data reader to a sequence of data of the supplied type (as object). Used for deserializing a reader without a connection, etc./// </summary>/// <param name="reader">The data reader to parse results from.</param>/// <param name="type">The type to parse from the <paramref name="reader"/>.</param>public static IEnumerable<object> Parse(this IDataReader reader, Type type){    if (reader.Read())    {        var deser = GetDeserializer(type, reader, 0, -1, false);        do        {            yield return deser(reader);        } while (reader.Read());    }}

在本人2011年7月25日寫的一篇名為《利用ADO.NET的體系架構打造通用的資料庫訪問通用類》的部落格當中介紹了ADO.NET的體系架構，如：

就是首先定義了一系列的借口，如IDbConnection之類的，任何基於資料庫訪問只要實現了介面的定義，就都能在.NET訪問，包括了微軟自己對SQL Server和Access等資料庫的實現以及MySQL和Oracle針對這個定義的第三方實現（其實JDBC也是這個道理，只不過是基於Java實現罷了）。因為包括SQL Server/MySQL/Oracle/PostgreSQL/SQLite在內的資料庫都實現了IDbConnection的定義，而Dapper又是基於IDbConnection的擴充，因此使用Dapper理論上可以訪問任何支援ADO.NET訪問的資料庫（前提是需要相關的資料庫驅動，dll形式）。
在使用Dapper的實際開發中，用得較多的還是針對IDbConnection的擴充方法，主要有：
int Execute():相當於Command.ExecuteNonQuery()，指定增加、刪除、修改SQL語句，返回受影響的行數。
object ExecuteScalar()：相當於Command. ExecuteScalar()，返回結果集第一行第一列，用於彙總函式等。
T ExecuteScalar<T>()：相當於Command. ExecuteScalar()，返回結果集第一行第一列，不過返回的結果指定了具體類型。
IDataReader ExecuteReader()：相當於Command. ExecuteReader()。
IEnumerable<dynamic> Query()
IEnumerable<T> Query<T>()
IEnumerable<object> Query()
IEnumerable<dynamic> Query()
dynamic QueryFirst()
dynamic QueryFirstOrDefault()
dynamic QuerySingle()
dynamic QuerySingleOrDefault()
IEnumerable<T> Query<T>()
T QueryFirst<T>()
T QueryFirstOrDefault<T>()
T QuerySingle<T>()
T QuerySingleOrDefault<T>()
IEnumerable<object> Query()
object QueryFirst()
object QueryFirstOrDefault()
object QuerySingle()
object QuerySingleOrDefault()
對於上面各種類型的Query和返回結果，就是分幾種情況：返回一個實現IEnumerable介面的結果集，返回單個結果，返回單個結果或在沒有找到匹配結果下返回預設值（參考型別、數實值型別、枚舉、日期等的預設值）

基本用法
使用了Dapper之後，在插入或者查詢時預設是按照資料庫欄位名與類屬性名稱不區分大小寫情況下對應。
加入有在SQL Server中有如下表：

CREATE TABLE IF NOT EXISTS tblBay (    Id                   integer              NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,    Name                 nvarchar(50)         not null,    Desc                 nvarchar(100)        not null    )

同時有如下類定義：

public class Bay{    public int ID { get; set; }    public string Name { get; set; }    public string Desc { get; set; }}

那麼插入可以這麼寫：

string connectionString = ".";//將這裡改成你自己的資料庫連接字串string sqlInsert = "INSERT INTO tblBay(Name,Desc)VALUES(@Name,@Desc)";SqlConnection connection = new SqlConnection(connectionString);Bay bay = new Bay { Name = "test", Desc = "desc" };connection.Execute(sqlInsert, bay);

查詢可以這麼寫：

string connectionString = ".";//將這裡改成你自己的資料庫連接字串string sqlQuery = "select * from tblBay where [email protected]";int id = 1;SqlConnection connection = new SqlConnection(connectionString);IEnumerable<Bay> bayList = connection. QueryFirstOrDefault<Bay>(sqlQuery,new { @Id = id });

欄位與屬性不一致情況下關聯
但是在某些情況下，比如使用MySQL資料庫時我們可能會在由多個單詞構成的欄位名之間以底線分割，如”user_id”、”user_name”等，而定義實體類時我們又將實體類的屬性定義為UserId、UserName，那麼就需要為他們之間建立關聯，比較簡單的一種方式就是在select的時候使用as。
假定在MySQL中存在如下表：

CREATE TABLE IF NOT EXISTS tblperson (    user_id               integer              NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,    user_name            nvarchar(50)         not null,    email                 nvarchar(100)        not null    )

而對應的實體類為：

public class Person{    public int UserId { get; set; }    public string UserName { get; set; }    public string Email { get; set; }}

那麼插入可以這麼寫：

string connectionString = ".";//將這裡改成你自己的資料庫連接字串string sqlInsert = "INSERT INTO tblperson(user_name,email)VALUES(@Name,@Email)";SqlConnection connection = new SqlConnection(connectionString);Person person = new Person { UserName = "test", Email = "[email protected]" };DynamicParameters parameters = = new DynamicParameters();parameters.Add("@Name", person.UserName);parameters.Add("@Email", person.Email);connection.Execute(sqlInsert, parameters);

查詢可以這麼寫：

string connectionString = ".";//將這裡改成你自己的資料庫連接字串string sqlQuery = "select user_id as userId,user_name as username,email from tblperson where [email protected]";int userId = 1;SqlConnection connection = new SqlConnection(connectionString);DynamicParameters parameters = = new DynamicParameters();parameters.Add("@UserId ", userId);IEnumerable<Person> bayList = connection. QueryFirstOrDefault<Person>(sqlQuery, parameters);

也就是資料庫欄位名與實體類屬性名稱如果忽略大小寫情況下是一致的，則我們無需單獨處理它們之間的映射關係，如果資料庫欄位名與實體類屬性在忽略大小寫情況下仍然不一致，那麼我們需要手動處理映射：在INSERT、DELETE、UPDATE時可以通過DynamicParameters來處理；在SELECT時可以通過在SQL語句中使用AS來處理。
有關Dapper的更進一步用法可以查看Dapper的使用者手冊或直接查看原始碼。

周金橋
2018/04/22

在.NET資料庫訪問方面的Dapper類庫介紹