11

Is it possible to add and remove criteria on the fly with dapper? I need this to implement user driven filtering. It is not feasible to have a query for each filter as there are too many combinations.

NabilS
  • 1,421
  • 1
  • 19
  • 31

1 Answers1

3

At the most basic level, you can just build the TSQL dynamically in a StringBuilder - adding extra and c.Name = @name etc . Don't worry about extra parameters; send them all - the library inspects the command and doesn't add any parameters that obviously aren't used. There is a second, newer API for this specific scenario, but I can't remember the specifics without checking (and I'm not at a computer). I can try to add more details later (assuming Sam doesn't).

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • I am currently building the sql as a string. The problem is I am using a number of IN's in the WHERE. Some are sometimes empty collections and so it is incorrectly passing empty collections into it. Ideally I only want the add the IN clause if there are items in the collection to filter. – NabilS Oct 15 '11 at 12:42
  • @NabilS but if you don't *add* that part of the where, it should still work correctly - but I'll investigate – Marc Gravell Oct 15 '11 at 18:40
  • 1
    @NabilS see: http://samsaffron.com/archive/2011/09/05/Digging+ourselves+out+of+the+mess+Linq-2-SQL+created#commentsTitle – Sam Saffron Oct 16 '11 at 03:18
  • Thanks Sam just what I'm looking for. How would I handle a number of or's within a clause. They would only be added based on a condition. From what I can see this is not possible in the API. – NabilS Oct 16 '11 at 15:30
  • But if this filtering is user-driven... isn't this code exposing some sql-injection possibilies? – Mattias Nordqvist Sep 27 '16 at 15:09
  • 1
    @MattiasNordqvist no; as long as the actual inputs are handled via parameters, which they should be (and which I encourage in the answer) – Marc Gravell Sep 27 '16 at 16:24