Dapper官方文档(七)【结果之匿名类型,强类型,多映射,多结果,多类型】

结果匿名

描述

可以使用扩展方法执行查询并使用动态类型映射结果。

匿名类型结果可以从以下扩展方法映射：

• Query
• QueryFirst
• QueryFirstOrDefault
• QuerySingle
• QuerySingleOrDefault

这些扩展方法可以从IDbConnection类型的任意对象中调用。

案例 - Query

Query方法可以执行查询并将结果映射到动态类型列表。

string sql = "SELECT * FROM Invoice;";

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

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

案例 - QueryFirst

QueryFirst方法可以执行查询并将第一个结果映射到动态类型列表。

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

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

    var invoice = connection.QueryFirst(sql, new {InvoiceID = 1});
}

案例 - QueryFirstOrDefault

QueryFirstOrDefault方法可以执行查询并将第一个结果映射到动态类型列表，如果序列不包含任何元素则为默认值。

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

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

    var invoice = connection.QueryFirstOrDefault(sql, new {InvoiceID = 1});
}

案例 - QuerySingle

QuerySingle方法可以执行查询并将第一个结果映射到动态类型列表，如果序列中没有元素则会引发异常。

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

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

    var invoice = connection.QuerySingle(sql, new {InvoiceID = 1});
}

案例 - QuerySingleOrDefault

QuerySingleOrDefault方法可以执行查询并将第一个结果映射到动态类型列表，如果序列为空则为默认值；如果序列中有多个元素，则此方法将引发异常。

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

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

    var invoice = connection.QuerySingleOrDefault(sql, new {InvoiceID = 1});
}

---

结果强类型

描述

可以使用扩展方法执行查询并使用动态类型映射结果。

强类型结果可以从以下扩展方法映射：

• Query
• QueryFirst
• QueryFirstOrDefault
• QuerySingle
• QuerySingleOrDefault

这些扩展方法可以从IDbConnection类型的任意对象中调用。

案例 - Query

Query方法可以执行查询并将结果映射到强类型列表。

string sql = "SELECT * FROM Invoice;";

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

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

案例 - QueryFirst

QueryFirst方法可以执行查询并将第一个结果映射到强类型列表。

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

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

    var invoice = connection.QueryFirst<Invoice>(sql, new {InvoiceID = 1});
}

案例 - QueryFirstOrDefault

QueryFirstOrDefault方法可以执行查询并将第一个结果映射到强类型列表，如果序列不包含任何元素则为默认值。

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

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

    var invoice = connection.QueryFirstOrDefault<Invoice>(sql, new {InvoiceID = 1});
}

案例 - QuerySingle

QuerySingle方法可以执行查询并将第一个结果映射到强类型列表，如果序列中没有元素则会引发异常。

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

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

    var invoice = connection.QuerySingle<Invoice>(sql, new {InvoiceID = 1});
}

案例 - QuerySingleOrDefault

QuerySingleOrDefault方法可以执行查询并将第一个结果映射到强类型列表，如果序列为空则为默认值；如果序列中有多个元素，则此方法将引发异常。

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

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

    var invoice = connection.QuerySingleOrDefault<Invoice>(sql, new {InvoiceID = 1});
}

---

结果多映射

描述

可以使用扩展方法执行查询并将结果映射到具有关系的强类型列表。

关系可以是：

• 一对一
• 一对多

这些扩展方法可以从IDbConnection类型的任意对象中调用。

案例 - 查询多映射（一对一）

Query方法可以执行查询并将结果映射到具有一对一关系的强类型列表。

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

案例 - 查询多映射（一对多）

Query方法可以执行查询并将结果映射到具有一对多关系的强类型列表。

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

---

结果多结果

描述

QueryMultiple是一个扩展方法，可以从IDbConnection类型的任意对象中调用。它可以在同一命令中执行多个查询并映射结果。

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

---

结果多类型

描述

ExecuteReader是一个扩展方法，可以从IDbConnection类型的任意对象中调用。它可以执行查询并将结果映射到不同类型的列表。

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