C# - Dapper/MySql - How to run a query with user-defined variable

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: '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?

It's probably bugging out because the column has no name. Perhaps just use db.ExecuteScalar(sql) as there is only one column in the SELECT — Charlieface
– Charlieface, Commented Mar 11, 2022 at 16:23
There are more efficient ways of setting an integer to 0 in c#. Can you make the example less contrived/post a query that is reasonable? As is I'd vote to close it as a typo because the code is of very little practical use to anyone — Caius Jard
– Caius Jard, Commented Mar 11, 2022 at 16:30
@CaiusJard I've just edited the example to more clear on what I want to accomplish, although the previous one ran in the same exception. I need count the registers in table1 and return it as a column in the query of table2. I have some constraints due the version of the MySql server I'm using, so my query have to be like that. The query runs as expected, but when I try to run it in C#, I get that error. — thyago
– thyago, Commented Mar 11, 2022 at 16:50

RedStoneMatt · Accepted Answer · 2025-05-04 20:45:23Z

6

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.

edited May 4 at 20:45

RedStoneMatt

1,3541 gold badge16 silver badges32 bronze badges

answered Mar 11, 2022 at 18:00

thyago

811 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AlfMan · Accepted Answer · 2022-03-11 16:40:14Z

0

Or you can else write your sql with a valid sql variable declaration :

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

Note : tested with Sql Server, not MySql. I don't use it.

answered Mar 11, 2022 at 16:40

AlfMan

114 bronze badges

1 Comment

RedStoneMatt Apr 30 at 7:59

Your answer is essentially "Don't use MySQL, use something else"; you're not answering OP's question. answering on SO isn't about telling others what you think is valid or not, it's about solving someone's problem with their constraints in mind. OP's syntax is valid SQL for MySQL, your syntax is for SQL Server. Neither of which are more valid than the other. Besides, OP is asking about an issue with Dapper, which you aren't even mentionning here. Please edit your answer accordingly.

LazyDragon · Accepted Answer · 2022-03-11 17:14:00Z

-1

The NullReferenceException you are receiving is likely because you have not defined foo as a parameter.

  using (var db = new MySqlConnection(@"mysql_connstr_here"))
  {
    var sql = dbConnection.QueryFirstOrDefault<int>(
      @" 
      select @foo;",
      new
      {
        foo = userAssignedVariable
      });
  }

edited Mar 11, 2022 at 17:14

answered Mar 11, 2022 at 16:28

LazyDragon

421 silver badge7 bronze badges

2 Comments

thyago Over a year ago

In my case, @foo is not supposed to be a parameter. It's a user defined variable that is going to be set within the SQL statement, and then returned alongside some other data in the select.

LazyDragon Over a year ago

I've updated my answer to assist with that. Set @foo with the user assigned variable as a new parameter. This will work :)

Collectives™ on Stack Overflow

C# - Dapper/MySql - How to run a query with user-defined variable

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related