Dapper QueryMultipleAsync ignoring parameters

Question

I'm trying to use Dapper QueryMultipleAsync. When I use a testing query without parameters, it works as expected, but when I try to use parameters, the execution hangs indefenitely.

This is the code I'm trying to execute:

public async Task<IHttpActionResult> Get (string cnpj)
{
    string cs = ConfigurationManager.ConnectionStrings["connectionStringBaseReceita"].ConnectionString;

    using(var conn = new SqlConnection(cs))
    {
        string sql = @"
        SELECT * FROM Empresas WHERE Cnpj = @cnpj ;
        SELECT * FROM QuadroSocietario WHERE Cnpj = @cnpj ;
        SELECT * FROM EmpresasCnaesSecundarios WHERE Cnpj = @cnpj ;
        ";

        var multi = await conn.QueryMultipleAsync(sql, new { cnpj }); //line where the code hangs
        var empresa = await multi.ReadSingleAsync<EmpresaModel>();
        empresa.QuadrosSocietarios = await multi.ReadAsync<QuadroSocietarioModel>();
        empresa.CnaesSecundarios = await multi.ReadAsync<CnaeSecundarioModel>();

        return Content(HttpStatusCode.OK, empresa);
    }
}

but if I use a testing query like this without parameters, everythings works:

string sql = @"
SELECT * FROM Empresas WHERE Cnpj = '08078861000102';
SELECT * FROM QuadroSocietario WHERE Cnpj = '08078861000102';
SELECT * FROM EmpresasCnaesSecundarios WHERE Cnpj = '08078861000102';
";

in the line where I call QueryMultipleAsync I tryed to specify the parameters in the following ways:

• conn.QueryMultipleAsync(sql, new { cnpj });
• conn.QueryMultipleAsync(sql, new { cnpj = cnpj });
• conn.QueryMultipleAsync(sql, new { @cnpj });
• conn.QueryMultipleAsync(sql, new { @cnpj = cnpj });

I also tryed to specify '?' in the query to pass a not named parameter:

string sql = @"
SELECT * FROM Empresas WHERE Cnpj = ? ;
SELECT * FROM QuadroSocietario WHERE Cnpj = ? ;
SELECT * FROM EmpresasCnaesSecundarios WHERE Cnpj = ? ;
";

but this fired a Incorrect syntax near '?' in the database.

Answer 1

Looking at your sql closer, you want to make sure you have models set up, and I didn't see that in your question. You probably know this, but Dapper is a micro ORM, where it maps the sql to .NET models. Look closer at the QueryMulitpleAsync documentation. They have

using (var multi = connection.QueryMultipleAsync(sql, new { InvoiceID = 1 
}).Result)

where they're "newing up" or creating an inline instance of the model class that corresponds to this table. In your case, you'd want to use

var multi = await conn.QueryMultipleAsync(sql, new { Cnpj = cnpj});

I believe it's case sensitive - it should match the Cnpj property defined in your model class and the case-sensitive name of your table field. If those differ, you can use sql aliases to rename a column to match your model class property. FWIW, there's a very good video at pluralsight on Dapper - Getting Started.

In order to use parameters with an anonymous type, like

var multi = await conn.QueryMultipleAsync(sql, new { cnpj });

you'd have to have your table defined with an Id - Dapper looks specifically for the Id column in order for this to work. You can't pass it as an anonymous type if you use something other than Id, so in your case you have to specify it this way:

var multi = await conn.QueryMultipleAsync(sql, new { Cnpj = cnpj});

Originally, I had suggested using an interpolated verbatim string, but as @AndySchmitt and @PalleDue both suggested, it opens you up to sql injection attacks. So, don't use sql that looks like this:

var sql = $@"SELECT * FROM Empresas WHERE Cnpj = '{cnpj}'"

One of the great features of Dapper is that it allows you write inline sql and you can pass parameters to avoid sql injection attacks.

Answer 2

Try using DynamicParameters instead.

E.g.

var parameters = new DynamicParameter();
parameters.Add("cnpj", cnpj, DbType.String);

Also consider apply 'using' statement:

using(var multi = connection.QueryMultipleAsync(sql, new { cnpj }).Result)
{
    //Read from multi
}