Is it possible to do this build this query with Dapper?

Question

Is it possible do do the following? I tried, but when it got to the Query, it just said there was a null reference.

   var builder = new StringBuilder("select * from my table1 where 1 = 1");

if(x==1)
    builder.Append(" and x = @x");

if(y==2)
    builder.Append(" and y = @y");

// when it gets here, it just says null reference

 db.Query<table1>(builder.ToString(), new {x,y});

I got SqlBuilder to run in .net 3.5, but when I do this:

var builder = new SqlBuilder();

var sql = builder.AddTemplate("select * from table /**where**/ /**orderby**/");

 builder.Where("a = @a", new { a = 1 })
        .OrWhere("b = @b", new { b = 2 });

I expected select * from table WHERE a = @a OR ( b = @b )

but I got:

I expected select * from table WHERE a = @a AND ( b = @b )

I don't see anything immediately wrong there; do you have a stack-trace from the exception? — Marc Gravell, Mar 20 '15 at 00:11
@MarcGravell - Sorry Marc, I originally typed this on my phone and don't have the original error message. If x and y are not empty or null, it works, but if I only specify one of them, it throws an error. I am using .NET 3.5 of SqlMapper. — xaisoft, Mar 20 '15 at 00:26
Well, it isn't something I've seen. I can look, but a stack trace would really help — Marc Gravell, Mar 20 '15 at 00:43
@MarcGravell - I will update my post with something I got working, but it is a bit confusing. — xaisoft, Mar 20 '15 at 12:05

ajbeaven · Answer 1 · 2017-05-10T02:17:52.267

0

Presumably this was due to this bug. The attempted fix was made on Jul 31 2016 however there are still issues with this approach. The plan is that this would be fixed in the next major release.

edited May 10 '17 at 02:17

answered May 10 '17 at 01:53

ajbeaven

9,265
13
76
121

score 0 · Answer 2 · answered Aug 07 '20 at 23:33

You can easily create that dynamic condition using DapperQueryBuilder:

var query = cn.QueryBuilder($@"
    SELECT * 
    FROM Table
    WHERE 1=1
");

if(x==1)
    query.Append($"and x = {x}");

if(y==2)
    builder.Append($" and y = {y}");

var results = query.Query<YourPOCO>();

The output is fully parametrized SQL (WHERE 1=1 AND x = @p0 AND y = @p1).

Disclaimer: I'm one of the authors of this library

Is it possible to do this build this query with Dapper?

2 Answers2

Linked