0. Preface

The previous four articles introduced an excellent framework SqlSugar developed by domestic developers, which gave us a bright feeling in front of our eyes. In this article, we will try another ORM framework, Dapper, which has a relatively high audience rating.

Dapper is a lightweight ORM framework, which is characterized by high speed, simple and easy to use. In some cases, efficiency can even match ADO.NET Comparable. So, let's actually see its performance.

1. Start using

As usual, create a project first: DapperDemo

dotnet new console --name DapperDemo

Then switch to the directory:

cd DapperDemo

Add package support:

dotnet add package Dapper

If there is no accident, Dapper has been installed in the project. Now let's start using it happily.

First of all, it should be noted that unlike other ORM frameworks, Dapper requires us to create an IConnection manually. All the operations of Dapper are based on IConnection, and Dapper encapsulates the methods it supports as extension methods of IConnection.

Therefore, we need to create an IConnection before using it. In order to facilitate the demonstration, I took the test database used in SqlSugar demonstration. It is a SQLite, so we need to install a SQLite driver first:

dotnet add package Microsoft.Data.SQLite

stay Program.cs Two packages are introduced in:

using Microsoft.Data.Sqlite;
using Dapper;

Create an IConnection in the Main method:

using(var connection = new SqliteConnection("Data Source=./demo.db"))
{
    
}

2. Multiple data query

Dapper's query is quite simple:

var result = connection.Query("select * from Persion");

Pass in a SQL statement and return an enumerable object. If no type is specified, a list of type dynamic is returned. Let's take a look at the declaration related to the Query method:

public static IEnumerable<dynamic> Query(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null);

public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null);

public static IEnumerable<TReturn> Query<TReturn>(this IDbConnection cnn, string sql, Type[] types, Func<object[], TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null);

Let's take the three most commonly used examples to analyze the parameters and call methods:

• cnn is a database connection, so Dapper is not responsible for managing the database connection, which is managed manually by us
• The SQL statement passed in by SQL. Dapper is based on IDbConnection and executed by SQL. Therefore, we must pass in the SQL statement
• param is a nullable object type, indicating the parameterization of SQL. Dapper optimizes the parameterization. In the parameterization of SQL, the parameter name is mapped to the attribute of the object.
• Transaction indicates whether there is an IConnection level transaction. It is null by default. After being passed in, the instruction will be included by the transaction
• buffered cache
• Whether and when the commandTimeout command is executed
• commandType indicates that the command mode has Text normal mode, StoredProcedure stored procedure and TableDirect table query
• splitOn uses Id as the distinction between two objects by default

3. Single data query

Dapper not only supports collection as query result, but also can get single data.

There are four ways to obtain single data:

public static T QueryFirst<T>(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
public static T QueryFirstOrDefault<T>(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);

QueryFirst means to get the first query result. If there is no result, an exception will be thrown.

Queryfirstrodefault is the same as QueryFirst, but the difference is that if it is not, it will not throw an exception, but directly return a default value of the type, the default value of the numerical type is (0), and the default value of the reference type is Null.

public static T QuerySingle<T>(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
public static T QuerySingleOrDefault<T>(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);

QuerySingle can also query a single piece of data as a result, but different from QueryFirst, when QuerySingle queries, if there are multiple rows of data, an exception will be thrown. If you do not want an exception, you can use QuerySingleOrDefault as a query method.

4. QueryMultiple

This other query method has no obvious restrictions on SQL statements, so sometimes we can pass in multiple query SQL statements, and then get the query data from each table separately:

string sql = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID; SELECT * FROM InvoiceItem WHERE InvoiceID = @InvoiceID;";

using (var connection = My.ConnectionFactory())
{
    connection.Open();

    using (var multi = connection.QueryMultiple(sql, new {InvoiceID = 1}))
    {
        var invoice = multi.Read<Invoice>().First();
        var invoiceItems = multi.Read<InvoiceItem>().ToList();
    }
}

Take a look at its basic parameters and method declarations:

public static GridReader QueryMultiple(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);

This method returns a GridReader to obtain the required data through the Read method.

5. Not just query

Of course, dapper does not only query this function. Dapper supports using stored procedures, insert, update, delete and other SQL statements to operate the database. Usage:

string sql = "Invoice_Insert";

using (var connection = My.ConnectionFactory())
{
    var affectedRows = connection.Execute(sql,
        new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
        commandType: CommandType.StoredProcedure);
    var affectedRows2 = connection.Execute(sql,
        new[]
        {
            new {Kind = InvoiceKind.WebInvoice, Code = "Many_Insert_1"},
            new {Kind = InvoiceKind.WebInvoice, Code = "Many_Insert_2"},
            new {Kind = InvoiceKind.StoreInvoice, Code = "Many_Insert_3"}
        },
        commandType: CommandType.StoredProcedure
    );
}

The example is an example of using stored procedures.

string sql = "INSERT INTO Customers (CustomerName) Values (@CustomerName);";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    var affectedRows = connection.Execute(sql, new {CustomerName = "Mark"});

    Console.WriteLine(affectedRows);

    var customer = connection.Query<Customer>("Select * FROM CUSTOMERS WHERE CustomerName = 'Mark'").ToList();

    FiddleHelper.WriteTable(customer);
}
//==Multiple inserts
string sql = "INSERT INTO Customers (CustomerName) Values (@CustomerName);";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    connection.Open();

    var affectedRows = connection.Execute(sql,
    new[]
    {
    new {CustomerName = "John"},
    new {CustomerName = "Andy"},
    new {CustomerName = "Allan"}
    }
);

This is an example of performing an insert.

string sql = "UPDATE Categories SET Description = @Description WHERE CategoryID = @CategoryID;";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{            
    var affectedRows = connection.Execute(sql,new {CategoryID = 1, Description = "Soft drinks, coffees, teas, beers, mixed drinks, and ales"});

    Console.WriteLine(affectedRows);
}

to update.

string sql = "DELETE FROM Customers WHERE CustomerID = @CustomerID";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{            
    var affectedRows = connection.Execute(sql, new {CustomerID = 1});

    Console.WriteLine(affectedRows);
}

Delete.

There's nothing to say about Execute. It's basically to Execute SQL statements to complete operations such as adding, deleting, changing to, etc.

It should be noted that:

public static IDataReader ExecuteReader(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);

Return an instance of idatareaader, which can fill DataTable with data as follows:

DataTable table = new DataTable();
table.Load(reader);

And:

public static T ExecuteScalar<T>(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);

This method returns the elements of the first row and first column of the query result.

6. Summary

There is not much to say about Dapper alone. However, Dapper is really fast. In actual development, Dapper is sometimes used as a supplement to EF Core.

Of course, Dapper has many other plug-ins, which can bring extraordinary improvement to Dappe. Our next article will introduce Dapper's plug-in.