Dapper - Query

Description

Query method is an extension method which can be called from any object of type IDbConnection. It can execute a query and map the result.

The result can be mapped to:

Parameters

The following table shows different parameter of an Query method.

Name Description
sql The query to execute.
param The query parameters (default = null).
transaction The transaction to use (default = null).
buffered True to buffer readeing the results of the query (default = true).
commandTimeout The command timeout (default = null)
commandType The command type (default = null)

Example - Query Anonymous

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

Example
string sql = "SELECT * FROM OrderDetails";

using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
{
	connection.Open();
	
	var orderDetails = connection.Query(sql).ToList();

	Console.WriteLine(orderDetails.Count);
}

Try it online

Example - Query Strongly Typed

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

Example
string sql = "SELECT * FROM OrderDetails";

using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
{
	connection.Open();
	
	var orderDetails = connection.Query<OrderDetail>(sql).ToList();

	Console.WriteLine(orderDetails.Count);
}

Try it online

Example - Query Multi-Mapping (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.

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

Example - Query Multi-Mapping (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.

Example
string sql = "SELECT * FROM Orders AS A INNER JOIN OrderDetails AS B ON A.OrderID = B.OrderID;";

using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
{
    connection.Open();
    
    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);
}

Try it online

Example - Query Multi-Type

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

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