Dapper学习(一)之Execute和Query

  • 2019 年 10 月 10 日
  • 筆記

Dapper是一个用于.NET的简单的对象映射,并且在速度上有着轻ORM之王的称号。

Dapper扩展IDbConnection,提供有用的扩展方法来查询数据库。

那么Dapper是怎样工作的呢?

总共三步:

  • 创建一个IDbConnection对象
  • 写一个语句来执行CRUD操作
  • 传递语句作为Execute方法的一个参数

因为这篇文章主要是为了学习其中一些方法的使用,所以,这里不再叙述安装等的一些使用,有需要的同学可以参考:https://dapper-tutorial.net/dapper

1.Execute

Execute是可以被IDbConnection类型的任何对象调用的扩展方法。它可以执行一个命令一次或者很多次,并且返回受影响的行数。

这个方法可以用于执行:

  • 存储过程(Stored Procedure)
  • 插入语句(INSERT statement)
  • 更新语句(UPDATE statement)
  • 删除语句(DELETE statement)

下面的表格,展示了Execute方法的参数

这里给出一个实现代码的示例,其余部分直接在官网上的示例上面记录学习。

using Dapper;  using System;  using System.Data.SqlClient;  using System.Runtime.Serialization;    namespace Dapper_Demo  {        public class Customer      {          public int ID { get; set; }            public string Name { get; set; }            public string About { get; set; }            public DateTime UpdateDate { get; set; }        }      class Program      {          private static readonly string connectionString = @"Data Source = 127.0.0.1;Initial Catalog = DapperDemo;User Id = sa;Password = 111111;";          static void Main(string[] args)          {              Console.WriteLine("Hello World!");                var customer = new Customer { ID = 2, Name = "jack", About = "jack hh", UpdateDate = DateTime.Now };                var Insertsql = @"insert into Customer values(@ID,@Name,@About,@UpdateDate)";                using(var connection=new System.Data.SqlClient.SqlConnection(connectionString))              {                  var affectedRows = connection.Execute(Insertsql,customer);                  Console.WriteLine(affectedRows);              }                Console.ReadKey();          }      }  }

注意,在使用之前,可以nuget程序集引入Dapper和System.Data.SqlClient

下面的部分是官方代码记录学习。

1.1 执行存储过程

单次(Single)

执行一次存储过程

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);        My.Result.Show(affectedRows);  }

多次(Many)

执行存储过程多次。数组列表中的每个对象执行一次

string sql = "Invoice_Insert";    using (var connection = My.ConnectionFactory())  {      var affectedRows = 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      );        My.Result.Show(affectedRows);  }

1.2 执行插入

单次(Single)

执行一次插入语句

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

多次(Many)

执行多次插入语句。数组列表中的每个对象执行一次

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"}      }  );    Console.WriteLine(affectedRows);

1.3 执行更新

单次(Single)

执行一次更新语句

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

多次(Many)

执行多次更新语句。数组列表中的每个对象执行一次

string sql = "UPDATE Categories SET Description = @Description WHERE CategoryID = @CategoryID;";    using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))  {      var affectedRows = connection.Execute(sql,      new[]      {      new {CategoryID = 1, Description = "Soft drinks, coffees, teas, beers, mixed drinks, and ales"},      new {CategoryID = 4, Description = "Cheeses and butters etc."}      }  );    Console.WriteLine(affectedRows);

1.4 执行删除

单次(Single)

执行一次删除语句

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

多次(Many)

执行多次删除语句。数组列表中的每个对象执行一次

string sql = "DELETE FROM OrderDetails WHERE OrderDetailID = @OrderDetailID";    using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))  {      var affectedRows = connection.Execute(sql,          new[]      {      new {OrderDetailID = 1},      new {OrderDetailID = 2},      new {OrderDetailID = 3}      }  );    Console.WriteLine(affectedRows);

1.5 场景说明

对于上面的execute方法在执行少量数据时,比较合适;但是如果执行数据量太大,速度就会很慢,就不适用了。下面会有对于大数据量的操作方法。

下面给出使用excute在执行批量插入数据时的一些结果。

代码如下:

 1 using Dapper;   2 using System;   3 using System.Collections.Generic;   4 using System.Data.SqlClient;   5 using System.Diagnostics;   6 using System.Runtime.Serialization;   7   8 namespace Dapper_Demo   9 {  10  11     public class Customer  12     {  13         public int ID { get; set; }  14  15         public string Name { get; set; }  16  17         public string About { get; set; }  18  19         public DateTime UpdateDate { get; set; }  20  21     }  22     class Program  23     {  24         private static readonly string connectionString = @"Data Source = 127.0.0.1;Initial Catalog = DapperDemo;User Id = sa;Password = 111111;";  25         static void Main(string[] args)  26         {  27             Console.WriteLine("Hello World!");  28  29             var list = new List<Customer>();  30             for(int i = 0; i < 100; i++)  31             {  32                 var customer = new Customer { ID = i, Name = "jack"+i, About = "jack hh"+i, UpdateDate = DateTime.Now };  33                 list.Add(customer);  34             }  35  36  37             var Insertsql = @"insert into Customer values(@ID,@Name,@About,@UpdateDate)";  38  39             var stopWatch = new Stopwatch();  40             stopWatch.Start();  41             using(var connection=new System.Data.SqlClient.SqlConnection(connectionString))  42             {  43                 var affectedRows = connection.Execute(Insertsql,list);  44                 Console.WriteLine(affectedRows);  45             }  46             stopWatch.Stop();  47             Console.WriteLine("花费的时间:" + stopWatch.ElapsedMilliseconds);  48  49             Console.ReadKey();  50         }  51     }  52 }

插入100条数据

插入500条数据

插入1000条数据

可以看出当数据量逐渐增大时,execute方法就不太适用了。

2. Query

这个方法使用来执行查询和映射结果的。

它的结果可以映射到:

  • Anonymous
  • Strongly Typed
  • Multi-Mapping(One to One)
  • Multi-Mapping(One to Many)
  • Multi-Type

可以执行的参数

2.1 Query Anonymous

Query方法可以执行原生 SQL 查询并且映射结果到动态集合

string sql = "SELECT TOP 10 * FROM OrderDetails";    using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))  {      var orderDetail = connection.Query(sql).FirstOrDefault();        FiddleHelper.WriteTable(orderDetail);  }

2.2 Query Strongly Typed

Query方法可以执行原生 SQL 查询并且映射结果到强类型集合

string sql = "SELECT TOP 10 * FROM OrderDetails";    using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))  {      var orderDetails = connection.Query<OrderDetail>(sql).ToList();        Console.WriteLine(orderDetails.Count);        FiddleHelper.WriteTable(orderDetails);  }

2.3 Query Multi-Mapping(One to One)

Query方法可以执行原生 SQL 查询并且用一对一的关系映射结果到强类型集合

string sql = "SELECT * FROM Invoice AS A INNER JOIN InvoiceDetail AS B ON A.InvoiceID = B.InvoiceID;";    using (var connection = My.ConnectionFactory())  {      connection.Open();        var invoices = connection.Query<Invoice, InvoiceDetail, Invoice>(              sql,              (invoice, invoiceDetail) =>              {                  invoice.InvoiceDetail = invoiceDetail;                  return invoice;              },              splitOn: "InvoiceID")          .Distinct()          .ToList();  }

2.4 Query Multi-Mapping (One to Many)

Query方法可以执行原生 SQL 查询并且用一对多的关系映射结果到强类型集合

string sql = "SELECT TOP 10 * FROM Orders AS A INNER JOIN OrderDetails AS B ON A.OrderID = B.OrderID;";    using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))  {      var orderDictionary = new Dictionary<int, Order>();          var list = connection.Query<Order, OrderDetail, Order>(      sql,      (order, orderDetail) =>      {          Order orderEntry;            if (!orderDictionary.TryGetValue(order.OrderID, out orderEntry))          {          orderEntry = order;          orderEntry.OrderDetails = new List<OrderDetail>();          orderDictionary.Add(orderEntry.OrderID, orderEntry);          }            orderEntry.OrderDetails.Add(orderDetail);          return orderEntry;      },      splitOn: "OrderID")      .Distinct()      .ToList();        Console.WriteLine(list.Count);        FiddleHelper.WriteTable(list);      FiddleHelper.WriteTable(list.First().OrderDetails);  }

2.5 Query Multi-Type

Query方法可以执行原生 SQL 查询并且映射结果到有多个类型的集合

string sql = "SELECT * FROM Invoice;";    using (var connection = My.ConnectionFactory())  {      connection.Open();        var invoices = new List<Invoice>();        using (var reader = connection.ExecuteReader(sql))      {          var storeInvoiceParser = reader.GetRowParser<StoreInvoice>();          var webInvoiceParser = reader.GetRowParser<WebInvoice>();            while (reader.Read())          {              Invoice invoice;                switch ((InvoiceKind) reader.GetInt32(reader.GetOrdinal("Kind")))              {                  case InvoiceKind.StoreInvoice:                      invoice = storeInvoiceParser(reader);                      break;                  case InvoiceKind.WebInvoice:                      invoice = webInvoiceParser(reader);                      break;                  default:                      throw new Exception(ExceptionMessage.GeneralException);              }                invoices.Add(invoice);          }      }        My.Result.Show(invoices);  }