How to add values for searching dynamically in Dapper.NET

c# dapper micro-orm

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.

Accepted Answer

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


Expert Answer

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.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why