How to run Dapper/MySql queries in C# using user-defined variables

Question

I've been trying to run this code:

using System;
using Dapper;
using MySql.Data.MySqlClient;

namespace DapperTests
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new MySqlConnection(@"mysql_connstr_here"))
            {
                var sql = @"
                    set @foo := (select count(*) from table1);
                    select table2.*, @foo from table2;";
                var result = db.Query(sql);
            }
            Console.ReadLine();
        }
    }
}

But I get the following exception:

System.NullReferenceException: 'The object reference is not set to an instance of the object. '
This exception was initially thrown in this call stack:
  MySql.Data.MySqlClient.MySqlConnection.Reader.set(MySql.Data.MySqlClient.MySqlDataReader)

My initial guess was that the variable was treated as a SqlParameter, and since I wasn't passing any parameters, my code failed. Is there a way to run a query like this using Dapper?

Answer 1

Or you can write your SQL statements using valid SQL variable declarations:

var sql = @"declare @foo int = 0; select @foo;";

Note: This code has been tested on Sql Server and does not apply to MySql. I don't use it.

Answer 2

I found this in the Dapper documentation:

So all I need to do is add Allow User Variables=True to the connection string. It worked.