[SOLVED] C# – Dapper/MySql – How to run a query with user-defined variable

Issue

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: 'Object reference not set to an instance of an object.'
This exception was originally thrown at this call stack:
  MySql.Data.MySqlClient.MySqlConnection.Reader.set(MySql.Data.MySqlClient.MySqlDataReader)

My first guess is that the variable is being treated as a SqlParameter, and since I’m not passing any argument to it, my code fails.
Is there a way to run a query like that using Dapper?

Solution

I’ve found this in the Dapper documentation:

In order to use Non-parameter SQL variables with MySql Connector, you have to add the following option to your connection string:

Allow User Variables=True

Make sure you don’t provide Dapper with a property to map.

So all I needed to do was to Allow User Variables=True to the connection string. It worked.

Answered By – thyago

Answer Checked By – Marie Seifert (BugsFixing Admin)