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 )

Bahadur

Please quote the entire error message. – Aculeus

I don't see anything immediately wrong there; do you have a stack-trace from the exception? – Regularly

@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. – Bahadur

Well, it isn't something I've seen. I can look, but a stack trace would really help – Regularly

Are you sure db is non-null? – Regularly

@MarcGravell - I will update my post with something I got working, but it is a bit confusing. – Bahadur

Answer 1

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

Prevail

Answer 2

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.

Brimmer