SqlBuilder returning wrong result

Question

I have a query using Npgsql and Postgres. For building my query I am using Dapper and its SqlBuilder.

When I make the normal statement in the DB it is returning the correct result. When I am doing it via the SqlBuilder it is returning the wrong result.

I tried different way, changed the addTemplate or the parameters, but it changed nothing.

Also I have tried to change the line builder.Where("period = @period", new { model.Period }); in different ways:

builder.Where("period = @Period", new { model.Period });
builder.Where("period = period", new { model.Period });
builder.Where("period = @TestPeriod", new { TestPeriod = model.Period });

Or is this a more common way:

builder.Where("period = '" + model.Period + "'");

using (NpgsqlConnection con = Helper.GetNpgsqlConnection())
        {

            var builder = new SqlBuilder();
            var selector = builder.AddTemplate("SELECT * FROM szzRecord.folders /**where**/");

            if (model.Period != null)
                builder.Where("period = @period", new { model.Period });

            var result = con.Query(selector.RawSql);
            return result;
        }

The result with the normal sql query for example: SELECT * FROM szzRecord.folders WHERE period = 24 is returning 251 rows - which is correct.

The result with the dapper query is 1223, which are all rows. So it kinda looks that the parameter doesn't exist. On expecting the selector I find my parameter for period. I found Period = 24 inselector.parameters.templates[0]. Is this correct? selector.parameters.parameters is empty.

Answer 1

You need to pass the SqlBuilder's parameters into your query. You have:

var result = con.Query(selector.RawSql);

Change this to:

var result = con.Query(selector.RawSql, selector.Parameters);