Dynamic Parameterized query with dapper

Question

I want to write a query with a dynamic list of parameters (depending on parameter is set or not). I want to execute the query on an oracle database using dapper.

Sample code:

        var sqlParams = new List<object>();
        var sqlBuilder = new StringBuilder();
        sqlBuilder.Append("SELECT * FROM EXAMPLE WHERE 1 = 1 ");

        if (!string.IsNullOrEmpty(aParam))
        {
            sqlBuilder.Append(" AND A LIKE ?");
        }

        if (!string.IsNullOrEmpty(bParam))
        {
            sqlBuilder.Append(" AND B LIKE ? ");
        }

        var sql = sqlBuilder.ToString();

        return this.Connection.Query<Equipment>(
            sql,
            new { aParam, bParam }   // ??
            ).ToList();

Marc Gravell · Accepted Answer · 2014-05-13 10:42:45Z

2

Dapper only really works with named parameters. I seem to recall that in oracle they are colon-prefixed, so:

if (!string.IsNullOrEmpty(aParam))
{
    sqlBuilder.Append(" AND A LIKE :aParam");
}

if (!string.IsNullOrEmpty(bParam))
{
    sqlBuilder.Append(" AND B LIKE :bParam");
}

Now your existing code here:

return this.Connection.Query<Equipment>(
    sql,
    new { aParam, bParam }
    ).ToList();

should work. Dapper uses the names of the members of the anonymous type as parameter names. It additionally includes some very basic code to check whether any given member does not exist in the sql, so if your sql only mentions :bParam, it won't actually add the aParam value as a parameter.

In more complicated scenarios, there is also a DynamicParameters object you can use, that functions more like a dictionary - but you don't need that here.

answered May 13, 2014 at 10:42

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kkuilla Over a year ago

How would you add aParam, bParam dynamically in the second snippet? Is it possible to replace new { aParam, bParam } with an ExpandoObject? I don't want to hard-code aParam, bParam. I want to add them via a loop. It seems like Query<Equipment>() does not support an ExpandoObject as its second argument though.

Marc Gravell Over a year ago

@kkuilla you can use DynamicParameters, or frankly you can just pass both in (dapper will ignore any that obviously aren't used)

Collectives™ on Stack Overflow

Dynamic Parameterized query with dapper

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related