Introduction to the Dapper Class library for. NET database access

In the development of the with database. NET system I used a variety of ways, including the direct use of ADO, the use of various types of ADO-based tools (including their own package), as well as a variety of ORM class libraries, such as NHibernate, Mybatisnet, Linq to SQL, Entity Framwrok, etc., in the above tool or class library, mybatisnet a period of time was my favorite: because its XML-based configuration can be flexibly adapted to a number of special scenarios, but sometimes in the face of small and medium-sized projects and feel mybatisnet somewhat overqualified, Another reason for this is that mybatisnet, the Java-based MyBatis, has recently been almost never updated.
Very early heard of dapper this class library, but has not tried to use, but it is very early to know that it is foreign large it question and answer community StackOverflow the earliest development and open source. Recently used, it feels really convenient. Dapper's source code is hosted on GitHub and can be added to the project in NuGet, except that some of the desktop software I'm currently developing is still using the windowsxp system, so it's not possible to use more than. NET Framrwork4.5 development and development tools are visual Studio 2015, which limits my inability to use the latest version of Dapper, so I chose Dapper 1.50.2 this version.
We can add it directly using NuGet in Visual Studio 2015, by "tools"-"NuGet Package Manager"-"Buget Package for Management solution" as shown in:

Then search for "Dapper" in the popup window, as shown in:

In the above interface, you can choose which projects to install into the current solution, and you can also specify the version of dapper.

The description of this article is for dapper 1.50.2 version.

Introduction to extension methods
Before introducing Dapper, let's first introduce the extension method in. NE. The extension method is. NET3.5 adds an attribute that allows you to extend a method for an existing class without creating a new derived class by using the extension method. Here is an example to illustrate:
When I parse an XML file node, I need to read the properties of a node, but this node does not always have this property value, as follows:
<subnetwork name= "sf-subnetwork" type= "" >
To avoid throwing an exception when the name attribute does not exist, I must first make a judgment as follows:

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

If there are several attributes that may not exist in an XML node, it is necessary to make this judgment everywhere, so I have improved the code to define an extension method for this type of situation, as follows:

public static class ExtendMethodClass{    /// <summary>    /// 获取指定属性的值，如果没有设置指定属性名，则返回空字符串    /// </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;    }}

In this way, the original code can be written as follows:

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

The initial look, like XmlAttributeCollection this kind of originally has Getattributevalue (string attributename) Such a method, in fact this way is our own expansion.
There are several ways to define extension methods:
1. The class that defines the extension method must be statically decorated, that is, a static class.
2, the extension method defined must be static, that is, the first parameter of the method must be added to the this decoration, this must be the class name, the class is represented as this after the extension method, as in this example, this XmlAttributeCollection Attributes represents the addition of extension methods to the XmlAttributeCollection class, and if you need to access instances of the XmlAttributeCollection class in the method body, The following attributes parameters are available (note that the name of this parameter can be arbitrarily taken).

Dapper Introduction
Through the above introduction, you can get an initial understanding of how the extension method is going. In fact, dapper mainly uses the extension method for IDbConnection and IDataReader add extension methods, such as in SqlMapper.cs has the following code for idbconnection Add extension method (excerpt):

<summary>///Execute parameterized sql.///</summary>///<param name= "CNN" >the connection to query O n.</param>///<param name= "SQL" >the SQL to execute for this query.</param>///<param name= "param" & Gt The parameters to, query.</param>///<param name= "Transaction" >the transaction ry.</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, idbtransactio n transaction = null, int? CommandTimeout = null, commandtype? CommandType = null) {var command = new commanddefinition (SQL, param, transaction, CommandTimeout, CommandType, COMMANDF Lags.    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 the this connection.</param>///<returns>the number of rows Affe cted.</returns>public static int Execute (this idbconnection CNN, commanddefinition command) = Executeimpl (CNN , ref command);

Code for adding extension methods to IDataReader in SqlMapper.IDataReader.cs (excerpt):

<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 PA RSE from the <paramref name= "reader"/>.</typeparam>///<param name= "reader" >the data reader to parse re Sults 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 read Er 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 ()); }}

In my July 25, 2011 Blog, entitled "Using the ADO architecture to build a common database access generic class," We introduced the ADO architecture, such as:

is to first define a series of excuses, such as IDbConnection, any database-based access as long as the implementation of the interface definition, can be in. NET access, including Microsoft's own implementation of databases such as SQL Server and Access, and the third-party implementations of MySQL and Oracle for this definition (in fact, JDBC is just a Java implementation). Because databases including SQL Server/mysql/oracle/postgresql/sqlite implement IDbConnection definitions, and Dapper is a idbconnection-based extension, Therefore, using dapper can theoretically access any database that supports ADO (as long as the relevant database driver, DLL form) is required.
In the actual development of the use of dapper, the use of more or for the idbconnection extension methods, mainly:
int Execute (): Equivalent to Command.executenonquery (), specifying the increment, delete, and modify SQL statements to return the number of rows affected.
Object ExecuteScalar (): equivalent to command. ExecuteScalar (), returns the first column of the first row of the result set, used for aggregation functions, and so on.
T executescalar<t> (): equivalent to command. ExecuteScalar () returns the first column of the first row of the result set, but the returned result specifies a specific type.
IDataReader ExecuteReader (): equivalent to 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 ()
For each of the above types of query and return results, there are several cases: Returns a result set that implements the IEnumerable interface, returns a single result, returns a single result, or returns a default value (the default value of a reference type, a numeric type, an enumeration, a date, and so on) if no matching result is found

Basic usage
When Dapper is used, the default when inserting or querying is to match the database field name with the case of the Class property name.
The join has the following table in 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    )

There are also the following class definitions:

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

Then the insert can be written like this:

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);

The query can be written like this:

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 });

字段与属性不一致情况下关联
But in some cases, such as when using a MySQL database, we may divide the names of the fields consisting of multiple words, such as "user_id", "user_name", and so on, while defining the entity class, we define the attributes of the entity class as UserID, UserName, Then it is necessary to establish an association between them, and the simpler way is to use as in select.
Assume that the following table exists in 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    )

The corresponding entity classes are:

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

Then the insert can be written like this:

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);

The query can be written like this:

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);

That is, if the database field names and entity class property names are consistent in cases where case is ignored, we do not need to deal with the mapping between them separately, and if the database field names and entity class attributes are still not consistent, then we need to handle the mappings manually: Insert, DELETE, The update can be handled by dynamicparameters, which can be handled by using as in the SQL statement when select.
For further usage of dapper, you can view the dapper User manual or view the source code directly.

Zhou Jin Qiao
2018/04/22

Introduction to the Dapper Class library for. NET database access