Nonparametric SQL variables with MySql Connector / Net and Dapper?

Question

The following error is generated in the code below:

The '@ID' parameter must be specified.

Am I doing something wrong or cannot use variables in a SQL query with MySQL that are not Dapper parameters?

In this example, @Slug is a Dapper parameter, but @ID is not. I use MySQL, so I do not need DEFINE @ID - it is determined upon first use.

var sql = @"SELECT @ID := id, slug, Title, Text FROM posts WHERE slug = @Slug; SELECT * FROM comments where postid = @ID;"; using (var connection = GetOpenConnection()) { var posts = connection.QueryMultiple(sql, new { Slug = slug }) .Map<Post, Comment, int> ( Post => Post.ID, Comment => Comment.ID, (post, comments) => { post.Comments = comments; } ); return posts.FirstOrDefault(); } 

Answer 1

It turns out that "MySql Connector / Net" generates an error.

To use non-parametric SQL variables with MySql Connector / Net, you must add the following line to the connection string:

• "Allow user variables = True"

See: http://blog.tjitjing.com/index.php/2009/05/mysqldatamysqlclientmysqlexception-parameter-id-must-be-defined.html

Answer 2

I do not think this is a problem with dapper; dapper just takes the SQL you suggest and adds any members from the args object that it explicitly sees in SQL.

The research method is to try to run the same with DbCommand directly - I assume that it will not be identical. There will be some kind of SQL trick to make it work, but it is between you and MySQL. All that dapper does is:

• team building with your sql
• defining the Slug parameter and setting the value
• sql call and analysis of results

it does not concern "ID", and it is correct that it does not.

Answer 3

You need to declare an ID variable in your sql code:

 var sql = @"DECLARE @ID Int; SELECT @ID := id, slug, Title, Text FROM posts WHERE slug = @Slug; SELECT * FROM comments where postid = @ID;";