How to add values for searching dynamically in Dapper.NET

Question

I'm developing purchase order search function using Dapper.NET. User can search by invoice no, and purchase date.

If user fill the invoice no on textbox, it will search by invoice no,
if user fill the invoice no and purchase date, it will search by invoice no and purchase date, and
if user fill the purchase date, it will search by purchase date

So, the query:

string query = "SELECT PurchaseDate, InvoiceNo, Supplier, Total FROM PurchaseOrder WHERE 1 = 1";

if (!string.IsNullOrEmpty(purchaseOrder.InvoiceNo))
{
    query += " AND InvoiceNo = @InvoiceNo";
}
if (purchaseOrder.PurchaseDate != DateTime.MinValue)
{
    query += " AND PurchaseDate = @PurchaseDate";
}

return this._db.Query<PurchaseOrder>(sql, ?).ToList();

The problem is I don't know how to pass the values dynamically based on number of criteria in the query.

Answer 1

Pulling up a sample from https://github.com/StackExchange/dapper-dot-net

 string query = "SELECT PurchaseDate, InvoiceNo, Supplier, Total FROM PurchaseOrder     WHERE 1 = 1";

 if (!string.IsNullOrEmpty(purchaseOrder.InvoiceNo))
 {
     sql += " AND InvoiceNo = @InvoiceNo";
 }
 if (purchaseOrder.PurchaseDate != DateTime.MinValue)
 {
    sql += " AND PurchaseDate = @PurchaseDate";
 }


  return this._db.Query<PurchaseOrder>(sql, new {InvoiceNo = new DbString { Value =     YourInvoiceNoVariable, IsFixedLength = true, Length = 10, IsAnsi = true });

for the Purchase date you need to decide whether to include both parameters in one sql statement or create a separate call to _db.Query for each

Answer 2

You can extract parameters into class

  public class Filter
    {
        public string InvoiceNo { get; set; }
        public DateTime PurchaseDate { get; set; }
    }

So, send filter as parameter

public IEnumerable<PurchaseOrder> Find(Filter filter)
        {
            string query = "SELECT PurchaseDate, InvoiceNo, Supplier, Total FROM PurchaseOrder WHERE 1 = 1";

            if (!string.IsNullOrEmpty(purchaseOrder.InvoiceNo))
            {
                query += " AND InvoiceNo = @InvoiceNo";
            }
            if (purchaseOrder.PurchaseDate != DateTime.MinValue)
            {
                query += " AND PurchaseDate = @PurchaseDate";
            }

            return this._db.Query<PurchaseOrder>(sql, filter);
        }

Answer 3

The simple option: include all the things! Dapper will inspect the query and decide which obviously aren't needed, and remove them - only sending the parameters that it can find mentioned in the query. So if some queries require a @foo, some need @bar, some need both and some need none, then just:

int foo = ...
string bar = ...
...Query<ResultType>(sql, new { foo, bar })...

Or to do it manually: check out DynamicParameters.