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.

possible duplicate of [How to create arguments for a Dapper query dynamically](http://stackoverflow.com/questions/9481678/how-to-create-arguments-for-a-dapper-query-dynamically) — Michael Petito, Dec 23 '14 at 04:29

Marc Gravell · Answer 1 · 2014-12-24T09:25:34.663

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.

edited Dec 24 '14 at 09:25

answered Dec 24 '14 at 09:11

Marc Gravell

1,026,079
266
2,566
2,900

How does the query need to look like in that case? I tried `AND c_foo LIKE @foo`, but if parameter `foo` is null, it complains that column `foo` doesn't exist. Then I tried `DynamicParameters` instead of the anonymous type. That doesn't throw an error, but the query doesn't return anything. Do I need to have `AND (@foo is NULL OR c_foo LIKE @foo)` in the query? – user2727133 Jan 29 '23 at 16:03
@user2727133 I'd need to see a full example of the "if parameter `foo` is null" scenario - that should work fine, just: it won't return any rows; nothing is "like null" - not even null is like null (at least under ANSI rules) - if you don't want that criteria, the best thing to do is to *not have that filter condition*, but the `(@foo is NULL OR c_foo LIKE @foo)` is a valid (but less efficient) alternative – Marc Gravell Jan 30 '23 at 10:27
Thanks for your comment, Marc. I guess I misunderstood your response: With "Dapper will inspect the query and decide which [parameters] aren't needed" referred just to the parameters, not to the filter condition itself. Does Dapper have a feature to automatically apply or omit filter conditions, based on the provided parameters? Or do I need to build the query by concatenating strings, like in the original question? – user2727133 Jan 30 '23 at 14:18
@user2727133 no, basically; there are so many variants of SQL that Dapper *doesn't even attempt* to understand the contents *except for* a very specific usage of `where x in @foo` where `foo` is a list-like type, which it rewrites into something like `where x in (@foo0, @foo1, ... , @foo27)` (although it has a few different strategies it can use, so there's a few different possible outcomes) – Marc Gravell Jan 30 '23 at 14:23

score 1 · Accepted Answer · answered Dec 23 '14 at 03:59

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

answered Dec 23 '14 at 03:59

agentpx

1,051
1
8
24

What I want is flexibility, if user only fill the invoice no then search by invoice no, if user fill invoice no and purchase date then search by invoice no and purchase date. In my code, I can make the query flexible, but not with the values. – Willy Dec 23 '14 at 04:13
I can create a separate call to _db.Query for each condition, but there will be repetitive code `SELECT PurchaseDate, InvoiceNo, Supplier, Total FROM PurchaseOrder` according to the conditions – Willy Dec 23 '14 at 04:19
1

You need to pass both parameters then handle the checking in sql query. something like WHEN fieldname is null THEN... see this link http://stackoverflow.com/questions/5751038/using-case-when-in-sql-statement – agentpx Dec 23 '14 at 04:26
I see, I think the query become more complex right? If there is no way I can pass the values like the query, then I'll create a separate _db.Query, thanks – Willy Dec 23 '14 at 04:34

score 0 · Answer 3 · answered Dec 23 '14 at 05:41

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

How to add values for searching dynamically in Dapper.NET

3 Answers3