CODEWITHSUNDEEP
c#mysql
Josh Faure
Josh Faure

Reputation: 13

Using a MySQL query with variables from C#

In MySQL Workbench the MySQL query

SET @rownum=-1;
SELECT @rownum:=@rownum+1 AS row_num
FROM someTable

returns a table where the value of row_num starts at 0 and goes up by 1 for each row:

+---------+
| row_num |
+---------+
| 0       |
+---------+
| 1       |
+---------+
| 2       |

  ....

I am trying to execute the same query from C#. 

string connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" +
    "UID=" + uid + ";" + "PASSWORD=" + password + ";" + "ALLOW USER VARIABLES = true;";

MySqlConnection connection = new MySqlConnection(connectionString);
MySqlCommand command;
MySqlDataAdapter adapter = new MySqlDataAdapter();

connection.Open();
command = connection.CreateCommand();

command.CommandText = "SELECT @rownum := @rownum + 1 AS row_num FROM someTable";
command.Parameters.Add("@rownum", MySqlDbType.Int32);
command.Parameters["@rownum"].Value = -1;
adapter.SelectCommand = command;

DataTable table = new DataTable();
adapter.Fill(table);

The last line of the above results in the following MySqlException

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= -1 + 1 AS row_num FROM someTable'

Why does the query not work from C#?

Upvotes: 1

Views: 3918

Answers (2)

Paddy
Paddy

Reputation: 21

I found that this solution only works if specify the parameter "AllowUserVariables=True" in the connectionstring

Upvotes: 2

CodingYoshi
CodingYoshi

Reputation: 26989

Remove these lines for parameters:

command.Parameters.Add("@rownum", MySqlDbType.Int32);
command.Parameters["@rownum"].Value = -1;

And use this query:

command.CommandText = "SET @rownum=-1;SELECT @rownum := @rownum + 1 AS row_num FROM someTable";

The way your are doing is both "@rownum" will be replaced with -1. So you end up with a query like this:

SELECT -1:= -1 + 1 AS row_num FROM someTable

Upvotes: 1

Related Questions