Is it possible to use a MySql User Defined Variable in a .NET MySqlCommand?

Question

I'm trying to execute a query that currently works in phpMyAdmin but it does not working when executing it in .NET using the MySqlAdapter. This is the Sql statement.

SELECT @rownum := @rownum +1 rownum, t . *
FROM (
  SELECT @rownum :=0
) r, (
  SELECT DISTINCT
    TYPE FROM `node`
  WHERE TYPE NOT IN ('ad', 'chatroom')
)t

It is using the @rownum to number each distinct row that is returned from my inner scalar query. But if I use it in .NET it's assuming that the @rownum is a parameter and throwing an exception because I didn't define it.

using (var sqlConnection = new MySqlConnection(SOURCE_CONNECTION))
{
    sqlConnection.Open();

    MySqlDataAdapter sqlAdapter = new MySqlDataAdapter(SqlStatement, sqlConnection);

    DataTable table = new DataTable();
    sqlAdapter.Fill(table);
    sqlConnection.Close();

    return table;
}

Any ideas for how I could get around this problem? Or possible ways for me to get a line number?

Answer 1

You could add a column to your table called rownum, then populate it with values:

table.Columns.Add("rownum");
for (int i=0; i < table.Rows.Count; i++)
{
table.Rows[i]["rownum"] = i;
}

Answer 2

What version of the MySQL data provider are you using? You may need to update.

I found this in the 5.0 documentation:

"Prior versions of the provider used the '@' symbol to mark parameters in SQL. This is incompatible with MySQL user variables, so the provider now uses the '?' symbol to locate parameters in SQL. To support older code, you can set 'old syntax=yes' on your connection string. If you do this, please be aware that an exception will not be throw if you fail to define a parameter that you intended to use in your SQL."

From this page: http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqlcommand.html