首页 > 代码库 > Dapper入门教程（三）——Dapper Query查询

Dapper入门教程（三）——Dapper Query查询

2024-11-20 14:03:02   203人阅读

介绍

查询方法（Query）是IDbConnection的扩展方法，它可以用来执行查询（select）并映射结果到C#实体（Model、Entity）类

查询结果可以映射成如下类型：

  • Anonymous 匿名类型
  • Strongly Typed 强类型
  • Multi-Mapping (One to One) 多映射 一对一
  • Multi-Mapping (One to Many) 多映射 一对多
  • Multi-Type 多类型

 

参数

下面表格中显示了Query方法的不同参数

 

名称描述
sql

要执行的sql语句文本
param

command的参数
transaction

事务
buffered

True to buffer readeing the results of the query (default = true).

翻译不来。。。
commandTimeout

command超时时间
commandType

command类型

 

示例 - 匿名查询

Raw SQL query can be executed using Query method and map the result to a dynamic list.

直接执行SQL语句字符串，然后将结果映射成 dynamic类型的List中

string sql = "SELECT * FROM Invoice;";

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

    var invoices = connection.Query(sql).ToList();
    
    My.Result.Show(invoices);
}

技术分享

 

 

示例 - 强类型查询（最常用）

直接执行SQL语句字符串，然后将结果映射成强类型类型的List中

string sql = "SELECT * FROM Invoice;";

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

    var invoices = connection.Query<Invoice>(sql).ToList();
    
    My.Result.Show(invoices);
}

技术分享

示例 - 多映射查询 (One to One)

Raw SQL query can be executed using Query method and map the result to a strongly typed list with a one to one relation.（没太理解）

直接执行SQL语句字符串，然后将结果用一对一的关系映射成强类型类型的List中

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

技术分享

示例 - 查询多映射 (One to Many)

Raw SQL query can be executed using Query method and map the result to a strongly typed list with a one to many relations.

直接执行SQL语句字符串，然后将结果用一对多的关系映射成强类型类型的List中

string sql = "SELECT * FROM Invoice AS A INNER JOIN InvoiceItem AS B ON A.InvoiceID = B.InvoiceID;";

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

    var invoiceDictionary = new Dictionary<int, Invoice>();

    var invoices = connection.Query<Invoice, InvoiceItem, Invoice>(
            sql,
            (invoice, invoiceItem) =>
            {
                Invoice invoiceEntry;
                
                if (!invoiceDictionary.TryGetValue(invoice.InvoiceID, out invoiceEntry))
                {
                    invoiceEntry = invoice;
                    invoiceEntry.Items = new List<InvoiceItem>();
                    invoiceDictionary.Add(invoiceEntry.InvoiceID, invoiceEntry);
                }

                invoiceEntry.Items.Add(invoiceItem);
                return invoiceEntry;
            },
            splitOn: "InvoiceID")
        .Distinct()
        .ToList();
        
    My.Result.Show(invoices);
}

技术分享

示例 - Query Multi-Type

Raw SQL query can be executed using Query method and map the result to a list of different types.

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

技术分享

 

 参考文章：

http://dapper-tutorial.net/query

Dapper入门教程（三）——Dapper Query查询


联系
我们